Skip to content

Instantly share code, notes, and snippets.

@venomjke
Last active January 3, 2016 02:49
Show Gist options
  • Save venomjke/8397812 to your computer and use it in GitHub Desktop.
Save venomjke/8397812 to your computer and use it in GitHub Desktop.
mysql function to calculate distance between to earth points on a globe in kilometers
DROP FUNCTION IF EXISTS distance;
DELIMITER $$
CREATE FUNCTION distance(lat1 FLOAT, lng1 FLOAT, lat2 FLOAT, lng2 FLOAT)
RETURNS INT
BEGIN
DECLARE earth_radius FLOAT;
DECLARE d_lat FLOAT;
DECLARE d_lng FLOAT;
DECLARE a FLOAT;
DECLARE c FLOAT;
DECLARE dist FLOAT;
SET earth_radius = 3958.75;
SET d_lat = RADIANS(lat2 - lat1);
SET d_lng = RADIANS(lng2 - lng1);
SET a = sin(d_lat/2) * sin(d_lat/2) + cos(radians(lat1)) * cos(radians(lat2)) * sin(d_lng/2) * sin(d_lng/2);
SET c = 2 * atan2(sqrt(a), sqrt(1-a));
SET dist = earth_radius * c;
RETURN round((dist * 1609) / 1000);
END;
$$
DELIMITER ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment