CODEWITHSUNDEEP
mysqlsqlgeolocationdistance
alex
alex

Reputation: 5002

MySQL calculate distance between all geo locations in result

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

the MySQL table looks like this:

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

Upvotes: 0

Views: 3544

Answers (1)

Romain
Romain

Reputation: 12809

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.

Upvotes: 2

Related Questions