How to find passing routes?

Question

MySQL

There is a list of cargo in the database, transportation from point A to point B. A and B have GPS geo-coordinates and have an array of route points in the Linestring field. In the search, I specify my route and I need to display the loads that fit my route along the specified route from the database. Now done through this LINESTRING field and just a MYSQL function iterates through all the points and looks for how far they are from the route trajectory. On samples of 50-200 loads, everything works ok, but when there are 2-5-10 thousand of them, the script execution time can take minutes. Has anyone faced similar issues? It is very urgent to optimize this search, help

Answer 1

Use spatial functions in your database extension that are capable of a spatial index rather than iterating over the point manually.

Answer 2

postgis.net