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
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 |