how to implement the query based on the places range from x minutes to xx minutes time for drive?

Question

There are some places I can choose from. I want to choose one to be my source place, and select driving time cost is less than 30 minutes. So there are maybe some places I can drive there cost less than 30 minutes will be showed.

So, what is the best way I should to save all these places data and query them on specific conditions?

Before I asking this question, I've tried to save all these places latitude and longitude. Whenever a new place has been saved to the database, I will request HERE map routing API to calculate distances and drive time between the new one with all old places info in a database, then save them in the distance table.

When a user wants to query places like the above example. I will join places table and distance table to query like:

SELECT place.id, place.name from place join distance on place_id = place.id where distance cost_time < 30;

There are some problem make me upset. If the number of old places is too big(actually it will), the time hanging after saving a place to the database will be much more.

So, I know I used a bad method to implement my goal. But I don't know how can I do, can someone help me with this problem?

last but not least, forget my poor English, if something is unclear, I'll try my best to describe it. Thank you.

Answer 1

You probably need to build a connected graph and compute the distances to other points on the fly.

When a new point is added, compute its distance with the X nearest neighbours only and store them in a database.

Then, you can use a algorithm like Dijkstra to find all the points at less than 30 units from your source.

You will lose some precision, as the cost to drive from A to C, then C to B will be usually greater then the direct path from A to B. And the time you saved on adding a new point, you will "lost" it to do the computation of the Dijkstra algorithm.