[postgis-users] distance calculation usng lat and long in miles

[Michael Fuhr]

On Wed, Mar 08, 2006 at 09:43:18AM -0800, sunithab at travelpost.com wrote:
> I have the lat and long data. I have created the geom column based on the 
> lat and long data as below.

I just replied to your message in pgsql-general (and suggested that
you visit postgis-users :-).  Below are the same comments I posted
there.

>     UPDATE property SET geom =GeometryFromText('POINT(' || long ||
>     ' ' || lat || ')',4326);

Instead of building a string you could use MakePoint() and SetSRID.

UPDATE property SET geom = SetSRID(MakePoint(long, lat), 4326);

> Now I have the geom columns in two tables
> 
> I am calculating the distance as below
> 
> select distance(geom1, geom2)* 69.055
> 
> It seems to be right. But I want to make sure.

That won't work in general because distance() returns the distance
in the same units as the input geometries, and distances in lon/lat
(spherical) coordinates have varying distances in units like miles
or km depending on latitude.  For example:

SELECT AsText(geom1) AS geom1,
       AsText(geom2) AS geom2,
       distance(geom1, geom2),
       distance(geom1, geom2) * 69.055 AS distance_mi
FROM foo;
    geom1    |    geom2    | distance | distance_mi
-------------+-------------+----------+-------------
 POINT(0 0)  | POINT(1 0)  |        1 |      69.055
 POINT(0 60) | POINT(1 60) |        1 |      69.055
(2 rows)

In each case the points are one degree apart, but the points at 60N
should be much closer in miles because longitude lines converge as
they approach the poles.  Instead of distance() use distance_sphere()
or distance_spheroid(), which return distances in meters:

SELECT AsText(geom1) AS geom1,
       AsText(geom2) AS geom2,
       distance_sphere(geom1, geom2) / 1609.344 AS sphere_mi,
       distance_spheroid(geom1, geom2, 'SPHEROID["WGS 84",6378137,298.257223563]')
         / 1609.344 AS spheroid_mi
    geom1    |    geom2    |    sphere_mi     |   spheroid_mi
-------------+-------------+------------------+------------------
 POINT(0 0)  | POINT(1 0)  | 69.0931819000054 | 69.1707247134693
 POINT(0 60) | POINT(1 60) | 34.5462620892688 | 34.6721834372296
(2 rows)

-- 
Michael Fuhr