Instantly share code, notes, and snippets.

# aramonc/distance.sql Last active Aug 22, 2018

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.

### antoniogarcia78 commented Nov 27, 2015

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

### MatthiasGemelli commented 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 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 ) );

### usamamashkoor commented 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.