Message-ID: <4AB1483C.5030701@megafon.hr>
Date: Wed, 16 Sep 2009 22:19:08 +0200
From: Mario Splivalo <mario.splivalo@megafon.hr>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: CREATE INDEX on column of type 'point'
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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