Created
May 31, 2020 14:53
-
-
Save arvindkumarbadwal/3d92de1ce7de8eaf4ce6ae8d575705a3 to your computer and use it in GitHub Desktop.
Postgres Routine to calculates distance between two points (latitude/longitude)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/*::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/ | |
/*:: lat1, lon1 = Latitude and Longitude of point 1 (in decimal degrees) :*/ | |
/*:: lat2, lon2 = Latitude and Longitude of point 2 (in decimal degrees) :*/ | |
/*:: unit = the unit you desire for results :*/ | |
/*:: where: 'M' is statute miles (default) :*/ | |
/*:: 'K' is kilometers :*/ | |
/*:: 'N' is nautical miles :*/ | |
/*:: :*/ | |
/*::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/ | |
CREATE OR REPLACE FUNCTION calculate_distance(lat1 float, lon1 float, lat2 float, lon2 float, units varchar) | |
RETURNS float AS $dist$ | |
DECLARE | |
dist float = 0; | |
radlat1 float; | |
radlat2 float; | |
theta float; | |
radtheta float; | |
BEGIN | |
IF lat1 = lat2 OR lon1 = lon2 | |
THEN RETURN dist; | |
ELSE | |
radlat1 = pi() * lat1 / 180; | |
radlat2 = pi() * lat2 / 180; | |
theta = lon1 - lon2; | |
radtheta = pi() * theta / 180; | |
dist = sin(radlat1) * sin(radlat2) + cos(radlat1) * cos(radlat2) * cos(radtheta); | |
IF dist > 1 THEN dist = 1; END IF; | |
dist = acos(dist); | |
dist = dist * 180 / pi(); | |
dist = dist * 60 * 1.1515; | |
IF units = 'K' THEN dist = dist * 1.609344; END IF; | |
IF units = 'N' THEN dist = dist * 0.8684; END IF; | |
RETURN dist; | |
END IF; | |
END; | |
$dist$ LANGUAGE plpgsql; | |
SELECT calculate_distance(32.9697, -96.80322, 29.46786, -98.53506, 'M'); | |
SELECT calculate_distance(32.9697, -96.80322, 29.46786, -98.53506, 'K'); | |
SELECT calculate_distance(32.9697, -96.80322, 29.46786, -98.53506, 'N'); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment