Обсуждение: distance calculation usng lat and long in miles

I have the lat and long data. I have created the geom column based on the
lat and long data as below.


     UPDATE property SET geom =GeometryFromText('POINT(' || 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.

Thanks for help in advance.

> I am calculating the distance as below
>
> select distance(geom1, geom2)* 69.055
>
>
> It seems to be right. But I want to make sure.

One way to calculate is the Great Circle Distance, that's the way the FCC
(Federal Communications Commission) uses to calculate distance between two
Radio Stations, using the lat and long for each.

Read about it at:

http://www.fcc.gov/mb/audio/bickel/distance.html

And better still, check your calculations against their online calculator
on the page for 'Distance and Azimuths Between 2 Sets of Coordinates'.

I accounts for the curve of the earth, too.  It may be more accurate than
you need, but it will be interesting to compare against, at any rate.

brew

 ==========================================================================
                  Strange Brew   (brew@theMode.com)
  Check out my Stock Option Covered Call website  http://www.callpix.com
     and my Musician's Online Database Exchange http://www.TheMode.com
 ==========================================================================

There is the earthdistance package in the contrib directory. You may want to
look into that.

On Wednesday 08 March 2006 09:10, sunithab@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.
>
>
>      UPDATE property SET geom =GeometryFromText('POINT(' || 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.
>
> Thanks for help in advance.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

--
    UC

--
Open Source Solutions 4U, LLC    1618 Kelly St
Phone:  +1 707 568 3056        Santa Rosa, CA 95401
Cell:   +1 650 302 2405        United States
Fax:    +1 707 568 6416

On Wed, Mar 08, 2006 at 09:10:39AM -0800, sunithab@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.
>
>
>     UPDATE property SET geom =GeometryFromText('POINT(' || long ||
>     ' ' || lat || ')',4326);

This looks like PostGIS.  You might get more help on the postgis-users
mailing list.

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
FROM foo;
    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)

Non-PostGIS users could use contrib/earthdistance.

--
Michael Fuhr