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 ;
@aramonc

This comment has been minimized.

Show comment Hide comment
@aramonc

aramonc Sep 24, 2013

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

Owner

aramonc commented Sep 24, 2013

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

@antoniogarcia78

This comment has been minimized.

Show comment Hide comment
@antoniogarcia78

antoniogarcia78 Nov 27, 2015

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

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

@MatthiasGemelli

This comment has been minimized.

Show comment Hide comment
@MatthiasGemelli

MatthiasGemelli Mar 14, 2016

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!!

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

This comment has been minimized.

Show comment Hide comment
@reense

reense Aug 23, 2016

Could you please elaborate on what this does exactly?

reense commented Aug 23, 2016

Could you please elaborate on what this does exactly?

@amitkarmakar13

This comment has been minimized.

Show comment Hide comment
@amitkarmakar13

amitkarmakar13 Apr 14, 2017

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

amitkarmakar13 commented Apr 14, 2017

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

@usamamashkoor

This comment has been minimized.

Show comment Hide comment
@usamamashkoor

usamamashkoor Aug 6, 2017

Thanks i have latitude/longitude coordinates i want to get the users who are within 50km of these latitude/longitude coordinates can some one please help me on this.
Thanks in advance.

Thanks i have latitude/longitude coordinates i want to get the users who are within 50km of these latitude/longitude coordinates can some one please help me on this.
Thanks in advance.

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