Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id D6E229FB2FD for ; Sun, 4 Feb 2007 01:18:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 64410-06-2 for ; Sun, 4 Feb 2007 01:16:54 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id 576839FB270 for ; Sun, 4 Feb 2007 01:16:52 -0400 (AST) Received: (qmail 11398 invoked by uid 500); 4 Feb 2007 05:16:44 -0000 Date: Sat, 3 Feb 2007 23:16:44 -0600 From: Bruno Wolff III To: Mark Stosberg Cc: pgsql-performance@postgresql.org Subject: Re: optimizing a geo_distance() proximity query Message-ID: <20070204051644.GA3754@wolff.to> Mail-Followup-To: Bruno Wolff III , Mark Stosberg , pgsql-performance@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/46 X-Sequence-Number: 23100 On Sat, Feb 03, 2007 at 14:00:26 -0500, Mark Stosberg 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. 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. However, if you have actually addresses you could use the tiger database to locate them instead of just zip code locations.