MySQL calculate distance between all geo locations in result

Question

I want to calculate the distance between all geo locations(represented as latitude and longitude) in the result

the MySQL table looks like this:

Location 1, Latitude: x, Longitude: y
Location 2, Latitude: a, Longitude: b
Location 3, Latitude: c, Longitude: d
...

The output should be the distance from Location 1 to Location 2 summed up with the distance between Location 2 and Location 3 and so on.

I know how to calculate the distance between two location but i don't know how to sum up the distances and how to always calculate the distance between two successive locations/rows

Thanks for your help!

Edit: I am planning to use the following function to calculate the distance between two locations

CREATE FUNCTION `GeoDistance`(  
 latitude1  numeric (9,6),  
 longitude1  numeric (9,6),  
 latitude2  numeric (9,6),  
 longitude2  numeric (9,6)  
) RETURNS decimal(10,5)  
    READS SQL DATA  
BEGIN  

DECLARE  x  decimal (20,20);  
DECLARE  pi  decimal (21,20);  
SET  pi = 3.14159265358979323846;  
SET  x = sin( lat1 * pi/180 ) * sin( lat2 * pi/180 ) 
       + cos( lat1 * pi/180 ) * cos( lat2 * pi/180 ) 
       * cos( abs( (lon2 * pi/180) - (lon1 *pi/180)));  
  SET  x = acos( x );  
  RETURN ( 1.852 * 60.0 * ((x/pi)*180) );
END

Maybe you should post the formula you wish to use and why you wont get it to work in mysql. — stefan
If you want to add up the distance between each location in the result and the next, the sorting of those locations actually does matter. If it's about getting the best/shortest route between locations, I would download all these locations and use a recursive call to get the shortest distance to the total of them. SQL -and especially MySQL- isn't very good at recursion. — GolezTrol
@GolezTrol the order of the location should not be changed. I will benchmark the calculation with MySQL and then i am going to check if it is faster to load all locations from the database and then calculate the distance otherwise. — alex
@Alex. Then you will have to apply a certain ordering in the query itself. You can never guarantee that a database will always return the rows in the same order. — GolezTrol

Romain Romain · Accepted Answer · 2011-11-09T08:34:07

Assuming the table is LOCATIONS (ID, LAT, LON), for simplification:

SELECT SUM(SQRT(POW(A.LAT - B.LAT, 2)+POW(A.LON - B.LON, 2)))
FROM LOCATIONS A
JOIN LOCATIONS B ON (A.ID = B.ID - 1)

Essentially, we self-join locations on itself, with an offset of 1 between locations (I'm freely assuming the IDs are assigned without gaps), then for each pair of points we calculate the distance (formula is quite simple, explained in detailed by google), then sum up all of these distances.

MySQL calculate distance between all geo locations in result

1 Answers