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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq