# Distance in km

### From PostgreSQL wiki

Distance between 2 points

Works with PostgreSQL

Any version

Written in

PL/Perlu

Depends on

Nothing

To get the distance between two points (lat1, long1, lat2, long2), and accounting for the curvature of the Earth. Note, the plperlu function is not exact, but should be pretty close.

*This example uses the contrib modules "Earthdistance" and "Cube" and yields virtually the same results as the Math::Trig function below*

select ROUND( ('(33.0, 97.1)'::point <@> '(24.0, 88.6)')::NUMERIC * 1.609344 ) as km;

*If you are unable to/do not want to install earthdistance and cube contrib modules, you can write a plperlu function to get simiar results*

create or replace function distance_in_km (numeric,numeric,numeric,numeric) returns numeric as $body$ use strict; use Math::Trig qw(great_circle_distance deg2rad); # from http://perldoc.perl.org/Math/Trig.html my $lat1 = shift(@_); my $lon1 = shift(@_); my $lat2 = shift(@_); my $lon2 = shift(@_); #elog WARNING, "$lat1, $lon1, $lat2, $lon2\n"; # Notice the 90 - latitude: phi zero is at the North Pole. sub NESW { deg2rad($_[0]), deg2rad(90 - $_[1]) } my @L = NESW( $lat1, $lon1 ); my @T = NESW( $lat2, $lon2 ); my $km = great_circle_distance(@L, @T, 6378); return $km; $body$ language plperlu strict security definer;

select ROUND(km) as km from distance_in_km(33.0,97.1,24.0,88.6) as km; km ----- 945

*Written by bricklen, feel free to correct or enhance!*