From: Mario Splivalo <mario.splivalo@megafon.hr>
To: pgsql-sql@postgresql.org
Subject: CREATE INDEX on column of type 'point'
Date: Wed, 16 Sep 2009 22:19:08 +0200
Message-ID: <4AB1483C.5030701@megafon.hr> (raw)
As I have discovered, there is no way to just create index on a column
of type 'point' - postgres complains about not knowing the default
operator class, no matter what index type I use.
Now, my table looks like this:
CREATE TABLE places (
place_id integer primary key,
coordinates point,
value integer,
owner_id integer
);
owner_id is foreign-keyed to the owners table and there is an index on
that column.
Now, my queries would search for places that are of certain value, maybe
owned by certain owner(s), in 'range' within specified circle. Something
like this:
SELECT
*
FROM
places
WHERE
coordinates <@ '<(320,200),200>'::circle
AND value BETWEEN 27 AND 80;
I get a sequential scan on that table.
Reading trough the mailinglist archives I found suggestion Tom Lane
made, saying that I should create functional index on table places
create index ix_coords on places using gist (circle(coordinates, 0));
And then change the WHERE part of my query like this:
WHERE circle(coordinates, 0) <@ '<(320,200),200'>::circle AND value
BETWEEN 27 AND 80;
Am I better of using 'circle' as data type for column 'coordinates'?
Are there any other options? I know there is PostGIS, but that seems
like a quite a big overhead. I'll only be checking if some point is in
our out of some circle.
Mario
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: mario.splivalo@megafon.hr
Subject: Re: CREATE INDEX on column of type 'point'
In-Reply-To: <4AB1483C.5030701@megafon.hr>
* 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