Skip site navigation (1) Skip section navigation (2)

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

From: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
To: Vince <vincecarney(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5711: input out of error with haversine formual
Date: 2010-10-15 07:23:24
Message-ID: AANLkTik5FUynC1h9ECZguBE+TH4T6EjPeShXJvT_FXAa@mail.gmail.com (view raw or flat)
Thread:
Lists: 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)) *
> 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?
>

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

In response to

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group