Skip to content

Instantly share code, notes, and snippets.

@nomatteus
Created January 3, 2013 16:43
Show Gist options
  • Save nomatteus/4444799 to your computer and use it in GitHub Desktop.
Save nomatteus/4444799 to your computer and use it in GitHub Desktop.
get_distance_km function for postgres
CREATE FUNCTION get_distance_km(alat double precision, alon double precision, lat double precision, lon double precision) RETURNS double precision
LANGUAGE plpgsql
AS $$
DECLARE
radius_earth FLOAT;
radian_lat FLOAT;
radian_lon FLOAT;
distance_v FLOAT;
distance_h FLOAT;
distance FLOAT;
BEGIN
-- Insert earth radius
SELECT INTO radius_earth 6378.137;
-- Calculate difference between lat and alat
SELECT INTO radian_lat radians(lat - alat);
-- Calculate difference between lon and alon
SELECT INTO radian_lon radians(lon - alon);
-- Calculate vertical distance
SELECT INTO distance_v (radius_earth * radian_lat);
-- Calculate horizontal distance
SELECT INTO distance_h (cos(radians(alat)) * radius_earth * radian_lon);
-- Calculate distance(km)
SELECT INTO distance sqrt(pow(distance_h,2) + pow(distance_v,2));
-- Returns distance
RETURN DISTANCE;
END;
$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment