From: | David Cottingham <david(dot)cottingham(at)cl(dot)cam(dot)ac(dot)uk> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Indexing a field of type point |
Date: | 2007-08-09 14:01:34 |
Message-ID: | Pine.LNX.4.64.0708091500360.10805@hermes-1.csi.cam.ac.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-08-09 17:33:27 | Re: Indexing a field of type point |
Previous Message | David Cottingham | 2007-08-09 13:02:38 | Indexing a field of type point |