From: David Cottingham <dnc25@cam.ac.uk>
To: pgsql-sql@postgresql.org
Subject: Indexing a field of type point
Date: Thu, 9 Aug 2007 14:02:38 +0100 (BST)
Message-ID: <Pine.LNX.4.64.0708091356030.10805@hermes-1.csi.cam.ac.uk> (raw)
Hi,
I realise this isn't strictly an SQL question, but I figured this list
might be better suited than the general one. Please let me know if not.
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.
To do this efficiently (rather than as a linear scan), I would like to
create an index over this field. However, using GIST under Postgresql
8.2.4 I can't do this:
test=# create index points_location_index on points using gist
(location);
ERROR: data type point has no default operator class for access method
"gist"
HINT: You must specify an operator class for the index or define a
default operator class for the data type.
Looking through the available classes, there are none defined for points
:-(.
I have seen a post saying that one could use
create index points_location_index on points using gist (location
box_ops);
but that comes back with the error that (rightly) box_ops doesn't have
operators for data of type point.
Is anyone aware of a way of creating a suitable index? I am aware of
PostGIS, but would prefer not to have to rework a whole load of code to
use the different geometrical field types it provides.
Thanks for any help!
David.
--
David Cottingham
Computer Laboratory, University of Cambridge
http://www.cl.cam.ac.uk/users/dnc25/
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: dnc25@cam.ac.uk
Subject: Re: Indexing a field of type point
In-Reply-To: <Pine.LNX.4.64.0708091356030.10805@hermes-1.csi.cam.ac.uk>
* 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