I'm running PostgreSQL 8.0 beta 1. I'm using the
earthdistance to find the distance between two
different latitude and logitude locations.
Unfortunately, the result seems to be wrong.

Here is what I'm doing:
select
earth_distance(ll_to_earth('122.55688','45.513746' ),ll_to_earth('122.396357','47.648845'));

The result I get is this:

128862.563227506

The distance from Portland to Seattle is not 128862
miles.






__________________________________
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

mikecoxlinux@yahoo.com (mike cox) writes:
[color=blue]
> I'm running PostgreSQL 8.0 beta 1. I'm using the
> earthdistance to find the distance between two
> different latitude and logitude locations.
> Unfortunately, the result seems to be wrong.
>
> Here is what I'm doing:
> select
> earth_distance(ll_to_earth('122.55688','45.513746' ),ll_to_earth('122.396357','47.648845'));
>
> The result I get is this:
>[/color]

I believe ll_to_earth() is expecting ll_to_earth(latitude, longitude),

Also, I think earth_distance returns it's value in meters.

mike cox <mikecoxlinux@yahoo.com> writes:[color=blue]
> The distance from Portland to Seattle is not 128862
> miles.[/color]

How about 128.8 kilometers? The earthdistance docs say it's in meters
unless you've redefined the base unit.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Sat, Oct 02, 2004 at 07:09:25PM -0400, Tom Lane wrote:[color=blue]
> mike cox <mikecoxlinux@yahoo.com> writes:[color=green]
> > The distance from Portland to Seattle is not 128862
> > miles.[/color]
>
> How about 128.8 kilometers? The earthdistance docs say it's in meters
> unless you've redefined the base unit.[/color]

128.8 kilometers is about 80 miles; the distance from Portland to
Seattle is more like 150 miles.
[color=blue][color=green]
> > earth_distance(ll_to_earth('122.55688','45.513746' ),ll_to_earth('122.396357','47.648845'));[/color][/color]

I haven't played with earthdistance, but I'd guess that the arguments
to ll_to_earth should be (latitude, longitude) instead of (longitude,
latitude).

Here are some queries from my own implementation of the haversine
function, which is another way to measure distances on a sphere:

=> select haversine(122.55688, 45.513746, 122.396357, 47.648845);
haversine
------------------
79.9258188445352

That distance is miles, which is almost exactly equivalent to the
128.8km figure from earth_distance(). Correcting the order of the
arguments gives this:

=> select haversine(45.513746, 122.55688, 47.648845, 122.396357);
haversine
------------------
147.614987754694

That's more like the true distance in miles between Portland and
Seattle

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Sat, Oct 02, 2004 at 17:55:31 -0600,
Michael Fuhr <mike@fuhr.org> wrote:[color=blue]
> On Sat, Oct 02, 2004 at 07:09:25PM -0400, Tom Lane wrote:[color=green]
> > mike cox <mikecoxlinux@yahoo.com> writes:[color=darkred]
> > > The distance from Portland to Seattle is not 128862
> > > miles.[/color]
> >
> > How about 128.8 kilometers? The earthdistance docs say it's in meters
> > unless you've redefined the base unit.[/color]
>
> 128.8 kilometers is about 80 miles; the distance from Portland to
> Seattle is more like 150 miles.
>[color=green][color=darkred]
> > > earth_distance(ll_to_earth('122.55688','45.513746' ),ll_to_earth('122.396357','47.648845'));[/color][/color]
>
> I haven't played with earthdistance, but I'd guess that the arguments
> to ll_to_earth should be (latitude, longitude) instead of (longitude,
> latitude).[/color]

I double checked to make sure the README file says the right thing and it
does say that latitude is the first argument and longitude the second.
So it just looks like the arguments were given in the wrong order.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Maybe it would work with the right long & lat...
try
Protland OR -122.67555, 45.51184
Seattle WA -122.32956, 47.60342

Also, do not forget that it is the line distance not the driving distance.




Michael Fuhr wrote:[color=blue]
> On Sat, Oct 02, 2004 at 07:09:25PM -0400, Tom Lane wrote:
>[color=green]
>>mike cox <mikecoxlinux@yahoo.com> writes:
>>[color=darkred]
>>>The distance from Portland to Seattle is not 128862
>>>miles.[/color]
>>
>>How about 128.8 kilometers? The earthdistance docs say it's in meters
>>unless you've redefined the base unit.[/color]
>
>
> 128.8 kilometers is about 80 miles; the distance from Portland to
> Seattle is more like 150 miles.
>
>[color=green][color=darkred]
>>>earth_distance(ll_to_earth('122.55688','45.5137 46'),ll_to_earth('122.396357','47.648845'));[/color][/color]
>
>
> I haven't played with earthdistance, but I'd guess that the arguments
> to ll_to_earth should be (latitude, longitude) instead of (longitude,
> latitude).
>
> Here are some queries from my own implementation of the haversine
> function, which is another way to measure distances on a sphere:
>
> => select haversine(122.55688, 45.513746, 122.396357, 47.648845);
> haversine
> ------------------
> 79.9258188445352
>
> That distance is miles, which is almost exactly equivalent to the
> 128.8km figure from earth_distance(). Correcting the order of the
> arguments gives this:
>
> => select haversine(45.513746, 122.55688, 47.648845, 122.396357);
> haversine
> ------------------
> 147.614987754694
>
> That's more like the true distance in miles between Portland and
> Seattle
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Sat, Oct 02, 2004 at 09:29:16PM -0400, Jean-Luc Lachance wrote:[color=blue]
> Maybe it would work with the right long & lat...
> try
> Protland OR -122.67555, 45.51184
> Seattle WA -122.32956, 47.60342[/color]

It doesn't matter which hemisphere the longitudes are in as long
as they're in the same hemisphere:

test=> select earth_distance(ll_to_earth('122.55688','45.513746' ),ll_to_earth('122.396357','47.648845'));
earth_distance
------------------
128862.563227506
(1 row)

test=> select earth_distance(ll_to_earth('-122.55688','45.513746'),ll_to_earth('-122.396357','47.648845'));
earth_distance
------------------
128862.563227506
(1 row)

What *does* matter is that one specify (lat, lon) instead of
(lon, lat):

test=> select earth_distance(ll_to_earth('45.513746', '122.55688'),ll_to_earth('47.648845', '122.396357'));
earth_distance
------------------
237996.256627247
(1 row)

That's 238km, or about 148mi; using your coordinates gives almost
the same answer, about 234km or 146mi. As I said, the distance
between Portland and Seattle is around 150mi.
[color=blue]
> Also, do not forget that it is the line distance not the driving distance.[/color]

I doubt anybody thought that earth_distance() was calculating driving
distance. How would it know what route to follow without an extensive
road database and a route specification?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Michael Fuhr <mike@fuhr.org> writes:[color=blue]
> What *does* matter is that one specify (lat, lon) instead of
> (lon, lat):[/color]

The earthdistance README does specify that latitude is the first
argument, but it doesn't get the function name right :-( ... it
says ll_to_cube instead of ll_to_earth. Anyone want to go through
the file and fix any other obvious documentation errors?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

I agree, NS or EW long lat should be the same.
I was just pointing to the wrong figure. Also, if ll_to_earth takes lat
first, it should report an error for a |lat| > 90...


Michael Fuhr wrote:[color=blue]
> On Sat, Oct 02, 2004 at 09:29:16PM -0400, Jean-Luc Lachance wrote:
>[color=green]
>>Maybe it would work with the right long & lat...
>>try
>>Protland OR -122.67555, 45.51184
>>Seattle WA -122.32956, 47.60342[/color]
>
>
> It doesn't matter which hemisphere the longitudes are in as long
> as they're in the same hemisphere:
>
> test=> select earth_distance(ll_to_earth('122.55688','45.513746' ),ll_to_earth('122.396357','47.648845'));
> earth_distance
> ------------------
> 128862.563227506
> (1 row)
>
> test=> select earth_distance(ll_to_earth('-122.55688','45.513746'),ll_to_earth('-122.396357','47.648845'));
> earth_distance
> ------------------
> 128862.563227506
> (1 row)
>
> What *does* matter is that one specify (lat, lon) instead of
> (lon, lat):
>
> test=> select earth_distance(ll_to_earth('45.513746', '122.55688'),ll_to_earth('47.648845', '122.396357'));
> earth_distance
> ------------------
> 237996.256627247
> (1 row)
>
> That's 238km, or about 148mi; using your coordinates gives almost
> the same answer, about 234km or 146mi. As I said, the distance
> between Portland and Seattle is around 150mi.
>
>[color=green]
>>Also, do not forget that it is the line distance not the driving distance.[/color]
>
>
> I doubt anybody thought that earth_distance() was calculating driving
> distance. How would it know what route to follow without an extensive
> road database and a route specification?
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Sun, Oct 03, 2004 at 11:36:20 -0400,
Jean-Luc Lachance <jllachan@sympatico.ca> wrote:[color=blue]
> I agree, NS or EW long lat should be the same.
> I was just pointing to the wrong figure. Also, if ll_to_earth takes lat
> first, it should report an error for a |lat| > 90...[/color]

I disagree with this. Latitudes greater than 90 degrees have a reasonable
meaning and it can be useful to use 0 to 180 instead of -90 to 90.
The same thing applies to longitude.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sunday 03 October 2004 20:22, Bruno Wolff III wrote:[color=blue]
> Latitudes greater than 90 degrees have a reasonable
> meaning and it can be useful to use 0 to 180 instead of -90 to 90.[/color]

Just a curious question: What is 100°N latitude supposed to mean?

Mit freundlichem Gruß / With kind regards
Holger Klawitter
- --
lists <at> klawitter <dot> de
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQFBY5ZH1Xdt0HKSwgYRApMcAJ9en3fBq+OFiMReD1Bbm0/+0njkdACgmp4E
yTuzi83I+8H3oRXKBPLjgH8=
=t/Qo
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

[color=blue]
> I've never seen this, but at a guess it could mean starting with 0 at
> one pole and counting to 180 at the other.[/color]

Yes, that makes sense; thanks!
But this scheme will certainly not be compatible to the distance fomula :-)

Mit freundlichem Gruß / With kind regards
Holger Klawitter
- --
lists <at> klawitter <dot> de
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQFBY+cQ1Xdt0HKSwgYRAr8oAJ9q+f02iMEPOdaSSc+oru LjVFYjygCfeO1a
iSHsRLYwmmPPgsECGVGVq8w=
=6T11
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Wed, Oct 06, 2004 at 08:52:55 +0200,
Holger Klawitter <lists@klawitter.de> wrote:[color=blue]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Sunday 03 October 2004 20:22, Bruno Wolff III wrote:[color=green]
> > Latitudes greater than 90 degrees have a reasonable
> > meaning and it can be useful to use 0 to 180 instead of -90 to 90.[/color]
>
> Just a curious question: What is 100°N latitude supposed to mean?[/color]

It means 80 degrees north and longitude + 180 degrees.

I shouldn't have used 0 to 180 as the example for latitude, because it
really needs to range from 0 to 360, since 0 to 180 is all in the
northern hemisphere. Longitude works similarly in that you can use
0 to 360 instead of -180 to +180.

The advantage of this is that your application can do things like add degrees
to a position and not have to check for wrapping around. You can get similar
issues due to rounding after switching coordinate systems where you might
get a value slightly greater than 90 degrees for latitude or get a value
slightly greater than 180 degrees for longitude.

As long as the principal values are returned when going from cartesian
coordinates (which is how earth distance stores points) to latitude
and longitude accepting values outside of the principal ones when
going from spherical coordinates to cartesian coordinates isn't a problem.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org