Re: [GENERAL] distance calculation usng lat and long in miles
On Wed, Mar 08, 2006 at 09:10:39AM -0800, [EMAIL PROTECTED] 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.093181954 | 69.1707247134693
POINT(0 60) | POINT(1 60) | 34.5462620892688 | 34.6721834372296
(2 rows)
Non-PostGIS users could use contrib/earthdistance.
--
Michael Fuhr
---(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
Re: [GENERAL] distance calculation usng lat and long in miles
There is the earthdistance package in the contrib directory. You may want to
look into that.
On Wednesday 08 March 2006 09:10, [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] distance calculation usng lat and long in miles
> 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 ([EMAIL PROTECTED]) Check out my Stock Option Covered Call website http://www.callpix.com and my Musician's Online Database Exchange http://www.TheMode.com == ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] 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.
---(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
