pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIndexing a field of type point
3+ messages / 3 participants
[nested] [flat]
* Indexing a field of type point
@ 2007-08-09 13:02 David Cottingham <dnc25@cam.ac.uk>
0 siblings, 0 replies; 3+ messages in thread
From: David Cottingham @ 2007-08-09 13:02 UTC (permalink / raw)
To: 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
http://www.cl.cam.ac.uk/users/dnc25/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Indexing a field of type point
@ 2007-08-09 14:01 David Cottingham <david.cottingham@cl.cam.ac.uk>
0 siblings, 1 reply; 3+ messages in thread
From: David Cottingham @ 2007-08-09 14:01 UTC (permalink / raw)
To: 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
http://www.cl.cam.ac.uk/users/dnc25/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Indexing a field of type point
@ 2007-08-09 17:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Cottingham <david.cottingham@cl.cam.ac.uk>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2007-08-09 17:33 UTC (permalink / raw)
To: David Cottingham <david.cottingham@cl.cam.ac.uk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-08-09 17:33 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-09 13:02 Indexing a field of type point David Cottingham <dnc25@cam.ac.uk>
2007-08-09 14:01 Indexing a field of type point David Cottingham <david.cottingham@cl.cam.ac.uk>
2007-08-09 17:33 ` Tom Lane <tgl@sss.pgh.pa.us>
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