Skip site navigation (1) Skip section navigation (2)

Re: find close (duplicate) points + create index

From: Elinor Medezinski <elinor(at)bellatrix(dot)tau(dot)ac(dot)il>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: find close (duplicate) points + create index
Date: 2004-03-10 09:22:47
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
You suggested: 	

> 	select * from pointtable a, pointtable b
> 	where (a.point <-> b.point) <= 1;

Thanks, Thats what I'll do.

> Making it fast is a more difficult problem :-( ... if you write the
> above query as-is then the system will sit there and compare each row of
> pointtable to each other row, looking for pairs of rows that match the
> where-clause.  Okay if you just have some thousands of rows, but on a
> big table this will take longer than you want to wait.

This query will only work on a few thousand lines, but I will have other 
queries on type point that will require comparing tables with millions of 
rows. Therefore I must learn how to build indexes on points.

> A btree index on a point column would be quite useless, since btree
> understands only a one-dimensional continuum with less-than, equal,
> greater-than relationships.  But I think you might be able to do
> something with an rtree index.

That much I know. I didn't find how I can use rtree to build an index on  
points, seeing how when I tried the following:
"create INDEX Phot_point_a ON Phot USING RTREE (point_a);"
I got this error:
"ERROR:  data type point has no default operator class for access method 
HINT:  You must specify an operator class for the index or define a default 
operator class for the data type."

And then I found out that in postgres the only operator classes defined for 
rtree indexes are: bigbox_ops, box_ops and poly_ops. Neither of which works 
with points, only with type box and polygon. Therefore I also have to create 
an operator class. I didn't understand how to do that. Do you know how?

> I'd look at making an rtree index on
> the unit box around each point, and then using an "overlaps" test as
> an indexable coarse filter before the exact distance check.

I didn't understand this either.


In response to


pgsql-novice by date

Next:From: Costin MandaDate: 2004-03-10 11:34:08
Subject: Problems with 'make'ing PostgreSQL with --with-perl
Previous:From: stm23Date: 2004-03-10 08:15:39
Subject: installing postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group