Re: optimizing a geo_distance() proximity query

On 2/5/07, Mark Stosberg <mark(at)summersault(dot)com> wrote:
> Bruno Wolff III wrote:
> > On Sat, Feb 03, 2007 at 14:00:26 -0500,
> > Mark Stosberg <mark(at)summersault(dot)com> wrote:
> >> I'm using geo_distance() from contrib/earthdistance would like to find a
> >> way to spend up the geo distance calculation if possible. This is for a
> >> proximity search: "Show me adoptable pets within 250 miles of this
> >> zipcode".
> >
> > If you are using the "cube" based part of the earth distance package,
> > then you can use gist indexes to speed those searches up.
>
> Thanks for the tip. Any idea what kind of improvement I can expect to
> see, compared to using geo_distance()?

a lot. be aware that gist takes longer to build than btree, but very
fast to search. Index search and filter to box is basically an index
lookup (fast!). for mostly static datasets that involve a lot of
searching, gist is ideal.

keep in mind that the cube based gist searches out a the smallest
lat/lon 'square' projected onto the earth which covers your circular
radius so you have to do extra processing if you want exact matches. (
you can speed this up to, by doing an 'inner box' search and not
recomputing distance to those points)

merlin