Re: input out of error with haversine formula

On 15 October 2010 06:03, Vince Carney <vincecarney@gmail.com> wrote:
> The following will return an input out of error as the acos() function
> cannot be -1 <= x <= 1.
> SELECT * FROM
>                 (SELECT *, (3959 * acos(cos(radians(37.7438640)) *
> cos(radians(37.7438640)) * cos(radians(-97.4631299) -
>                 radians(-97.4631299)) + sin(radians(37.7438640)) *
> sin(radians(37.7438640))))
>                 AS distance
>                 FROM foo) AS distances
>                 WHERE distance < 10
>                 ORDER 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 or workaround suggestions?
> Thanks.
> --Vince--

This form of the Haversine formula is known to suffer from large
rounding errors when the distance between the points is small. It is
much better to use the arcsin(..) form of this formula, which has much
greater accuracy, particularly for the common case of small distances.
See http://en.wikipedia.org/wiki/Great-circle_distance

Regards,
Dean