From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Cottingham <david.cottingham@cl.cam.ac.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Indexing a field of type point
Date: Thu, 09 Aug 2007 13:33:27 -0400
Message-ID: <17550.1186680807@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.64.0708091500360.10805@hermes-1.csi.cam.ac.uk>
References: <Pine.LNX.4.64.0708091500360.10805@hermes-1.csi.cam.ac.uk>
David Cottingham <david.cottingham@cl.cam.ac.uk> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.cottingham@cl.cam.ac.uk
Subject: Re: Indexing a field of type point
In-Reply-To: <17550.1186680807@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox