Re: haversine formula with postgreSQL

On Fri, Sep 18, 2009 at 5:50 PM, Scott Bailey <artacus(at)comcast(dot)net> wrote:
>> And I think that might work for you.  The performance is going to be
>> miserable for large stat sets, because it's going to scan the whole
>> aaafacilities table every time and recompute every distance, but as an
>> example goes it's probably acceptable.
>
> Something I did when implementing haversine in Oracle was first calculate a
> bounding box for lat/lon and only calculate the distance for cities w/in the
> bounding box.

This is easily done in postgresql with gist operator on box type. In
some cases you can actually optimize it even further...you can make a
'inner' bounding box that captures points that do not need the
expensive distance function. So you use gist to pull points that
'may' be inside the distance, box overlap the points that 'must' be
inside the distance, and distance function the rest.

Also I highly suggest moving the distance calculation inside an
immutable function.

merlin