calculating distance between longitude and latitude

2010/6/9 Geoffrey <lists@serioustechnology.com>

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

Hi,
the simplest way is to use PostGis, all spatial functions are there.

regards
Szymon Guz

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