Re: BUG #5711: input out of error with haversine formual

From: Dean Rasheed Vince pgsql-bugs(at)postgresql(dot)org Re: BUG #5711: input out of error with haversine formual 2010-10-15 07:23:24 AANLkTik5FUynC1h9ECZguBE+TH4T6EjPeShXJvT_FXAa@mail.gmail.com (view raw, whole thread or download thread mbox) 2010-10-15 04:58:23 from "Vince"  2010-10-15 07:23:24 from Dean Rasheed  2010-10-15 14:16:17 from Tom Lane pgsql-bugs
```On 15 October 2010 05:58, Vince <vincecarney(at)gmail(dot)com> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5711
> Logged by:          Vince
> Email address:      vincecarney(at)gmail(dot)com
> PostgreSQL version: 8.4
> Operating system:   Linux
> Description:        input out of error with haversine formual
> Details:
>
> 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)) *
>                AS distance
>                FROM foo) AS distances
>                WHERE distance < 10
>                ORDER BY distance
>
> If I break this down the following returns 1:
>
> 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

```

pgsql-bugs by date

 Next: From: Tom Lane Date: 2010-10-15 14:16:17 Subject: Re: BUG #5711: input out of error with haversine formual Previous: From: Vince Date: 2010-10-15 04:58:23 Subject: BUG #5711: input out of error with haversine formual