Distance in km

Library Snippets

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!