Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
MySQL function to calculate the distance between two coordinates using the Haversine formula. Leaving it here for future reference.
DELIMITER $$
CREATE FUNCTION `haversine` (lat1 DECIMAL(8,6), lng1 DECIMAL(8,6), lat2 DECIMAL(8,6), lng2 DECIMAL(8,6)) RETURNS DECIMAL(8,6)
BEGIN
DECLARE R INT;
DECLARE dLat DECIMAL(30,15);
DECLARE dLng DECIMAL(30,15);
DECLARE a1 DECIMAL(30,15);
DECLARE a2 DECIMAL(30,15);
DECLARE a DECIMAL(30,15);
DECLARE c DECIMAL(30,15);
DECLARE d DECIMAL(30,15);
SET R = 3959; -- Earth's radius in miles
SET dLat = RADIANS( lat2 ) - RADIANS( lat1 );
SET dLng = RADIANS( lng2 ) - RADIANS( lng1 );
SET a1 = SIN( dLat / 2 ) * SIN( dLat / 2 );
SET a2 = SIN( dLng / 2 ) * SIN( dLng / 2 ) * COS( RADIANS( lng1 )) * COS( RADIANS( lat2 ) );
SET a = a1 + a2;
SET c = 2 * ATAN2( SQRT( a ), SQRT( 1 - a ) );
SET d = R * c;
RETURN d;
END$$
DELIMITER ;
Owner

aramonc commented Sep 24, 2013

Tested it with some GTFS data and it seems to work pretty well.

R=6371; -- Earth's radius in Km
R=6371000; -- Earth's radius in metres

Nice code - works well.
I did not manage yet to setup mySQL functions on my AWS RDS yet...so I crunched the function into a query.

See resulting data visualization of ADS-B aircraft position data at
http://10pm-blog.blogspot.de/2016/03/geographical-distance-calculation.html

haversine01

Many thanks!!

reense commented Aug 23, 2016

Could you please elaborate on what this does exactly?

amitkarmakar13 commented Apr 14, 2017 edited

There is a bug on this line,

SET a2 = SIN( dLng / 2 ) * SIN( dLng / 2 ) * COS( RADIANS( lng1 )) * COS( RADIANS( lat2 ) );

The correct formula is,

SET a2 = SIN( dLng / 2 ) * SIN( dLng / 2 ) * COS( RADIANS( lat1 )) * COS( RADIANS( lat2 ) );

Source: http://www.movable-type.co.uk/scripts/latlong.html

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment