Need advice. Download search algorithm. What are your suggestions for DBMS architecture?

Question

satellite navigation

Never worked before with similar tasks . Need advice!

There is a code that uses geocoding for 2 addresses and an open street map to build a route.
In the json column - the response from the open street map API is written.
Such routes are stored in a table

transportation_orders (id, user_id, driver_id, lat_from, lon_from, lat_to, lon_to, json)

1, 1, null, 50.11123, 30.2524, 35.11123, 10.2524, {...}

A driver is traveling along this route, he must be informed if there is a passing additional load!
How it works now:
When storing data in transportation_orders, all navigation coordinates from "A" to "B" are processed to an accuracy of 0.01 from lat, lon. and stored in

order_waypoints (order_id, lat, lon, progress_from, progress_to )

.

1, 50.11, 30.25, 0% , 0,1222%
1, 50.12, 30.25, 0,1222% , 0,2233%
...
1, 35.11, 10.24, 98.881% , 99.594%
1, 35.11, 10.25, 98.594% , 100%

.
In turn, 1 such point is a square about 1100m X 1100m.
The algorithm for searching for additional loads is a selection of additional loads that pass through the same order_waypoints (inner join) but with a progress condition of 0..25% and 75..100% (i.e. at least 1 point with 25% of the beginning of the route and 1 the point with 25% of the end must overlap with the main route).

For long routes from city to city, everything is considered excellent (Since the route will converge on the same track if it is passing). Here is an example purely to visualize the correctness of my approach:
(the grid is my squares 1.1kmX1.1km) What will not work in this case 1) It will be bad to count for short distances 2) There is a problem when navigation will route along a parallel street.


3) A lot more
Visualization: As a matter of fact, what are the considerations of how to design such systems! Thanks for any advice!

Answer 1

Master the Buffer operation - and it will be clear whether routes pass nearby or not.
With the opposite direction, I did not understand at all what the difficulty was. If you have to load a ride at 65% and unload at 30%, then obviously it will not work.

Answer 2

This is more of a philosophical question.
According to the Input data, how would I build:
There is a route A -> B
There is a product C -> D
There is a car that goes E
I would get a route from the map if it is possible E -> B and (E -> C -> D -> B) and (E -> C -> B -> D)
If the difference in routes does not exceed ...%, then choose the best route and load