Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 127259FC1E5 for ; Thu, 9 Aug 2007 14:33:35 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 62659-04 for ; Thu, 9 Aug 2007 14:33:31 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id CE2079FC184 for ; Thu, 9 Aug 2007 14:33:30 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l79HXR1l017551; Thu, 9 Aug 2007 13:33:27 -0400 (EDT) To: David Cottingham cc: pgsql-sql@postgresql.org Subject: Re: Indexing a field of type point In-reply-to: References: Comments: In-reply-to David Cottingham message dated "Thu, 09 Aug 2007 15:01:34 +0100" Date: Thu, 09 Aug 2007 13:33:27 -0400 Message-ID: <17550.1186680807@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/54 X-Sequence-Number: 28992 David Cottingham writes: > I have a table containing a field named location, of type point, i.e. a > position in two dimensions. The table has several million records in, and I > need to extract those records whose location value is contained within a > certain bounding box. Given the standard opclasses, your best bet is to convert the point into a zero-volume box or circle, eg create index i on t using gist (circle(pointcol,0)) and then express queries as "circle(pointcol,0) overlaps target-box". PostGIS might have something more nicely adapted ... regards, tom lane