Thread: earthdistance is not giving correct results.
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
mike cox <mikecoxlinux@yahoo.com> writes:
> The distance from Portland to Seattle is not 128862
> miles.
How about 128.8 kilometers? The earthdistance docs say it's in meters
unless you've redefined the base unit.
regards, tom lane
On Sat, Oct 02, 2004 at 07:09:25PM -0400, Tom Lane wrote:
> mike cox <mikecoxlinux@yahoo.com> writes:
> > The distance from Portland to Seattle is not 128862
> > miles.
>
> How about 128.8 kilometers? The earthdistance docs say it's in meters
> unless you've redefined the base unit.
128.8 kilometers is about 80 miles; the distance from Portland to
Seattle is more like 150 miles.
> > earth_distance(ll_to_earth('122.55688','45.513746'),ll_to_earth('122.396357','47.648845'));
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/
On Sat, Oct 02, 2004 at 17:55:31 -0600,
Michael Fuhr <mike@fuhr.org> wrote:
> On Sat, Oct 02, 2004 at 07:09:25PM -0400, Tom Lane wrote:
> > mike cox <mikecoxlinux@yahoo.com> writes:
> > > The distance from Portland to Seattle is not 128862
> > > miles.
> >
> > How about 128.8 kilometers? The earthdistance docs say it's in meters
> > unless you've redefined the base unit.
>
> 128.8 kilometers is about 80 miles; the distance from Portland to
> Seattle is more like 150 miles.
>
> > > earth_distance(ll_to_earth('122.55688','45.513746'),ll_to_earth('122.396357','47.648845'));
>
> 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).
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.
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:
> On Sat, Oct 02, 2004 at 07:09:25PM -0400, Tom Lane wrote:
>
>>mike cox <mikecoxlinux@yahoo.com> writes:
>>
>>>The distance from Portland to Seattle is not 128862
>>>miles.
>>
>>How about 128.8 kilometers? The earthdistance docs say it's in meters
>>unless you've redefined the base unit.
>
>
> 128.8 kilometers is about 80 miles; the distance from Portland to
> Seattle is more like 150 miles.
>
>
>>>earth_distance(ll_to_earth('122.55688','45.513746'),ll_to_earth('122.396357','47.648845'));
>
>
> 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
>
On Sat, Oct 02, 2004 at 09:29:16PM -0400, Jean-Luc Lachance wrote:
> Maybe it would work with the right long & lat...
> try
> Protland OR -122.67555, 45.51184
> Seattle WA -122.32956, 47.60342
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.
> Also, do not forget that it is the line distance not the driving distance.
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/
Michael Fuhr <mike@fuhr.org> writes:
> What *does* matter is that one specify (lat, lon) instead of
> (lon, lat):
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
mikecoxlinux@yahoo.com (mike cox) writes:
> 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:
>
I believe ll_to_earth() is expecting ll_to_earth(latitude, longitude),
Also, I think earth_distance returns it's value in meters.
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:
> On Sat, Oct 02, 2004 at 09:29:16PM -0400, Jean-Luc Lachance wrote:
>
>>Maybe it would work with the right long & lat...
>>try
>>Protland OR -122.67555, 45.51184
>>Seattle WA -122.32956, 47.60342
>
>
> 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.
>
>
>>Also, do not forget that it is the line distance not the driving distance.
>
>
> 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?
>
On Sun, Oct 03, 2004 at 11:36:20 -0400, Jean-Luc Lachance <jllachan@sympatico.ca> wrote: > 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... 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.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Sunday 03 October 2004 20:22, Bruno Wolff III wrote:
> Latitudes greater than 90 degrees have a reasonable
> meaning and it can be useful to use 0 to 180 instead of -90 to 90.
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-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> 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.
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+oruLjVFYjygCfeO1a
iSHsRLYwmmPPgsECGVGVq8w=
=6T11
-----END PGP SIGNATURE-----
On Wed, Oct 06, 2004 at 08:52:55 +0200, Holger Klawitter <lists@klawitter.de> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Sunday 03 October 2004 20:22, Bruno Wolff III wrote: > > Latitudes greater than 90 degrees have a reasonable > > meaning and it can be useful to use 0 to 180 instead of -90 to 90. > > Just a curious question: What is 100°N latitude supposed to mean? 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.