[FEATURE] Adding intermediate steps

[ShockedPlot7560]

Justification

Currently, when we add a trip, it takes two cities, the departure and the arrival. So, when searching for a trip, if we search for a trip Departure City -> City on the trip, we will not find.
Trips are restricted to the single cities of the trip, and no flexibility is really possible. This is further emphasized by the fact that we cannot see the entire list of trips, not allowing manual search.

A system allowing this flexibility is therefore more than necessary

Explanation

If we consider that when the person, during the entry of the trip, enters the arrival and departure of his trip. It is theoretically possible to find his route and therefore the possible stopovers.
It would therefore be necessary to add, between step 2 and 3, a part that lists all the possible stopovers found on the route (within 5km of the route).
This may be possible using the APIs of google maps or openstreet map. The user would have the possibility to modify / delete / add steps which would recalculate the itinary and potentially the possible steps.
A visualization with a map can also be possible to allow the user to better visualize how his route is arranged.

Thus, when searching for a route, the algorithm would no longer be based solely on the city of departure and arrival but also on all the automatic / informed by the user.

[ShockedPlot7560]

After much research, here's what you should focus on:

If we consider that the interesting cities on a route are medium/large cities, it will be necessary to define a limit of X inhabitants.

1. Use OpenStreetMap or any other API to transform our route, Point -> Point into a WKT file
2. Load a PostgreSQL + PostGIS extension database containing the triplet: Name villet, lattitude, longitude of all cities in France with more than X inhabitants.
3. From this database, use the spatial functions of the database to find the cities next to the road

A use of functions such as Buffer to extend the LineString of the WKT format into a Polygonal format can be potentially more interesting. However, the compatibility with PostgreSQL has to be checked

4. Sort the cities and points previously found to put them in the order of the route.
   • A simple approximate answer would be to sort them by distance from the starting point, under the assumption that most routes don't backtrack
   • A more robust approach might be to find the closest city/village to the starting point, then find the point on the route that is closest to that city/village, then repeat using that point as the new starting point and excluding cities that have already been visited.

[ShockedPlot7560]

Cache system

As the cities are not supposed to move and the growth of the inhabitants is minimal, we can consider that each calculation made will keep its result for several years.
It is therefore not possible to calculate continuously the same things, a cache system is mandatory.

Several solutions are available to us.

Use PostgreSQL database

To make the most of the performance of SQL, it would make more sense to store the cached data directly in another table linked by a foreign key directly to the cities in the database.
This means that before calculating the surrounding cities, it must be determined whether or not the calculation has already been made.

However, this system may not be the most efficient.

Use Redis database

Redis, key-value database used in particular for its ultra fast access to data. However, using such a system will imply processing the WKT format outside of PostgreSQL. Thus, the intervention of an external code, in this case, javascript that will manage the call to the Redis database and then PostegreSQL.
A loss of performance can be caused by this, but this is counterbalanced by the access speed of redis.

Other solutions are surely possible, but these are the two most effective that come to mind

[ShockedPlot7560]

Dataset to load database:

• https://sql.sh/736-base-donnees-villes-francaises

[finxol]

Maybe merge with addok ?

[ShockedPlot7560]

Maybe merge with addok ?

I don't really know addok, but I don't think it's a good idea to integrate it. Adding it to addok, under redis will not allow us to benefit from the spatial methods integrated in DBMS like PostgreSQL

[finxol]

It's also important to have both suggestions from UniCovoit for stops, but also let the driver add custom ones (autocompleted by addok?) in case they pass through smaller towns or a different route