Calculate the distance between two GPS-Coordinates (ctd.)
September 17th, 2007 by exhuma.twn
And here’s the same thing as plpgsql function for Postgres:
CREATE OR REPLACE FUNCTION lldistance( a point, b point)
RETURNS FLOAT AS $$
DECLARE x FLOAT;
dlat FLOAT;
dlon FLOAT;
BEGIN
dlat = radians(a[0]-b[0]);
dlon = radians(a[1]-b[1]);
x := sin(dlat/2)^2 +
cos(radians(a[0])) * cos(radians(b[0])) *
sin(dlon/2)^2;
RETURN 6367442.5 * (2*atan2(SQRT(x), SQRT(1-x)));
END;
$$ LANGUAGE plpgsql
RETURNS FLOAT AS $$
DECLARE x FLOAT;
dlat FLOAT;
dlon FLOAT;
BEGIN
dlat = radians(a[0]-b[0]);
dlon = radians(a[1]-b[1]);
x := sin(dlat/2)^2 +
cos(radians(a[0])) * cos(radians(b[0])) *
sin(dlon/2)^2;
RETURN 6367442.5 * (2*atan2(SQRT(x), SQRT(1-x)));
END;
$$ LANGUAGE plpgsql
This results in more accurate distances than simply using
SELECT point1 <-> point2 FROM sometable;
You could also use plpython as function language and copy/paste the earlier post, but plpgsql is more portable.
Posted in Coding Voodoo | 1 Comment »