Skip to content

Instantly share code, notes, and snippets.

@zerothabhishek
Forked from aramonc/distance.sql
Created February 12, 2017 10:59
Show Gist options
  • Save zerothabhishek/51c7c8cc5bae83c3c9a7e235d224de16 to your computer and use it in GitHub Desktop.
Save zerothabhishek/51c7c8cc5bae83c3c9a7e235d224de16 to your computer and use it in GitHub Desktop.
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 ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment