Thread: calculating distance between longitude and latitude

Does postgresql have functions to calculate the distance between two
sets of longitude and latitude.

--
Until later, Geoffrey

"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson

> Does postgresql have functions to calculate the distance between two
> sets of longitude and latitude.
>

You're looking for the earthdistance contrib module. With most Linux distros
it's installed under /usr/share/postgresql/8.xx/contrib
You may have to install a "postgresql-contrib" package depending on your
distro.
Typing "locate earthdistance.sql" should reveal the location if it's available
already. To activate you'd just do a "pgsql [database] <
/whereever/earthdistance.sql"

HTH
  Uwe

On Wed, Jun 9, 2010 at 3:02 PM, Geoffrey <lists@serioustechnology.com> wrote:
> Does postgresql have functions to calculate the distance between two sets of
> longitude and latitude.

for posterity, if you are not:
*) very interested in high performance (that is, ok w/sql implementation)
*) needing super accurate results (ok with GC distance)
*) wanting to deal with dependencies (postgis, earthdistance)
*) interested in gist for indexed spacial searches

try this:

create or replace function gc_dist(_lat1 float8, _lon1 float8, _lat2
float8, _lon2 float8) returns float8 as
$$
  select ACOS(SIN($1)*SIN($3)+COS($1)*COS($3)*COS($4-$2))*6371;
$$ language sql immutable;

I took that from here:
http://www.movable-type.co.uk/scripts/latlong.html

postgres=# select gc_dist(42, -74, 29, -81);
     gc_dist
------------------
 3725.88928230352

results in km.  for serious stuff postgis is definitely the way to go.

merlin