From: Mark Stosberg <mark@summersault.com>
Subject: Re: optimizing a geo_distance() proximity query
Date: Mon, 05 Feb 2007 14:47:25 -0500
Organization: Hub.Org Networking Services
Lines: 28
Message-ID: <eq81l5$2na3$1@news.hub.org>
References: <eq2m5o$8cr$1@sea.gmane.org> <20070204051644.GA3754@wolff.to>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
User-Agent: Thunderbird 1.5.0.8 (X11/20061109)
In-Reply-To: <20070204051644.GA3754@wolff.to>
To: pgsql-performance@postgresql.org

Bruno Wolff III wrote:
> On Sat, Feb 03, 2007 at 14:00:26 -0500,
>   Mark Stosberg <mark@summersault.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()?

> There are functions for creating boxes that include all of the points some distance
> from a fixed point. This is lossy, so you need to recheck if you don't
> want some points a bit farther away returned. Also you would need to
> pick a point to be where the zip code is located, rather than using area
> based zip codes. 

This is also interesting. Is this approach practical if I want to index
what's near each of about 40,000 US zipcodes, or the approach mostly
useful if you there are just a small number of fixed points to address?

I'm going to start installing the cube() and earth_distance() functions
today and see where I can get with the approach.

  Mark