CREATE INDEX on column of type 'point'

From: Mario Splivalo <mario(dot)splivalo(at)megafon(dot)hr>
To: pgsql-sql(at)postgresql(dot)org
Subject: CREATE INDEX on column of type 'point'
Date: 2009-09-16 20:19:08
Message-ID: 4AB1483C.5030701@megafon.hr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Browse pgsql-sql by date

  From Date Subject
Next Message rawi 2009-09-17 12:47:38 Hibernate, web application and only one sequence for all primary keys
Previous Message Psicopunk 2009-09-16 09:01:10 Re: hardware information