Skip to content

Instantly share code, notes, and snippets.

@arvindkumarbadwal
Created May 31, 2020 14:53
Show Gist options
  • Save arvindkumarbadwal/3d92de1ce7de8eaf4ce6ae8d575705a3 to your computer and use it in GitHub Desktop.
Save arvindkumarbadwal/3d92de1ce7de8eaf4ce6ae8d575705a3 to your computer and use it in GitHub Desktop.
Postgres Routine to calculates distance between two points (latitude/longitude)
/*::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
/*:: 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