On 15 October 2010 05:58, Vince <vincecarney@gmail.com> wrote:
>
> The following bug has been logged online:
>
> Bug reference: =A0 =A0 =A05711
> Logged by: =A0 =A0 =A0 =A0 =A0Vince
> Email address: =A0 =A0 =A0vincecarney@gmail.com
> PostgreSQL version: 8.4
> Operating system: =A0 Linux
> Description: =A0 =A0 =A0 =A0input out of error with haversine formual
> Details:
>
> The following will return an input out of error as the acos() function
> cannot be -1 <=3D x <=3D 1.
>
> SELECT * FROM
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0(SELECT *, (3959 * acos(cos(radians(37.743=
8640)) *
> cos(radians(37.7438640)) * cos(radians(-97.4631299) -
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0radians(-97.4631299)) + sin(radians(37.743=
8640)) *
> sin(radians(37.7438640))))
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0AS distance
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0FROM foo) AS distances
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0WHERE distance < 10
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0ORDER BY distance
>
> If I break this down the following returns 1:
> SELECT (cos(radians(37.7438640)) * cos(radians(37.7438640)) *
> cos(radians(-97.4631299) - radians(-97.4631299)) + sin(radians(37.7438640=
))
> * sin(radians(37.743864000)));
>
> acos(1) would give me 0.
>
> Thoughts?
>
I don't think this is a bug. It's a well known issue with the
arccos(..) form of the the Haversine formula that it suffers from
large rounding errors when the distance between the points is small.
In this case the intermediate value is a little over 1, due to these
rounding errors, but you can't see that, due to limited precision of
the output format.
Using the arcsin(..) form of the Haversine formula cures that -
http://en.wikipedia.org/wiki/Great-circle_distance
Regards,
Dean