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
