On Fri, Nov 11, 2005 at 09:26:47PM +0000, Tom wrote:
> This surfaced when calculating the distances between zip codes using
> latidtudes and longitudes. When the latitudes and longitudes are the same
> the distance should be zero. Number 1 gives the error. Number 2 differs by a
> digit in the last decimal place and works. Number 3 is another test with
> identical latitudes and longitudes and does calculate zero.
If you use psql and increase VERBOSITY you can see where the error
is happening:
test=> \set VERBOSITY verbose
test=> select find(42.3202,-83.2687,42.3202,-83.2687);
ERROR: 22003: input is out of range
CONTEXT: SQL function "find" statement 1
LOCATION: dacos, float.c:1602
"dacos" is the internal name of PostgreSQL's acos() function. I'd
guess the value that acos() receives is a wee bit bigger than 1.0
due to rounding and the inaccuracy of representing floating point
numbers in binary; you don't see the problem with another pair of
identical locations because the value calculated to the 16th or so
digit is slightly different.
Your function uses the law of cosines; consider using haversine
instead.
http://www.movable-type.co.uk/scripts/GIS-FAQ-5.1.html
If you're working with geospatial data then you might want to look
at PostGIS.
http://postgis.refractions.net/
See also PostgreSQL's contrib/earthdistance module.
--
Michael Fuhr
