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: 200403101122.47748.elinor@bellatrix.tau.ac.il
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
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
"rtree"
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.

Thanks,
Elinor

In response to

Responses

Browse pgsql-novice by date

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