FInd closest coordinates in array of coordinates

Question

I have a MYSQL table with millions of positions, the structure is as follows:

id  latitude    longitude
1240451 6.893   79.85412
1240450 6.932   79.85005
1240448 7.082   79.89186
1240449 6.932   79.86133

I have another table that records journeys with the starting and ending positions, the structure is as follows:

id  jrny_startlat   jrny_startllong jrny_endlat jrny_endlong
17  6.81462565  79.88666117 6.80900081  79.91052210

Basically, I have to find the possible path by using the journey start lat/lon and journey end lat/lon by comparing with the positions table.

Answer 1

Look here -- How does Google Maps and Nokia Maps generate routes from point to point -- or on the Wikipedia page http://en.wikipedia.org/wiki/Pathfinding which describes the well-known algorithms. You might then want to study up on the A* (A-star) algorithm, or (if you will be solving many routes over the same set of data) look into contraction hierarchies: http://en.wikipedia.org/wiki/Contraction_hierarchies