Re: find close (duplicate) points + create index

Elinor Medezinski <elinor(at)bellatrix(dot)tau(dot)ac(dot)il> writes:
> I'm trying to find duplicate entries, where two entries are considered
> duplicates if they're within a radius of 1, meaning something like
> "select point from pointtable where distance between points <=1".
> Obviously this is not SQL syntax.

Well, it is if you do a self-join:

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

Postgres spells the "distance" operator as "<->", so this becomes

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

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.

> Also, I also tried to build an index on that column, but there's no operator
> class for type point. How can I do that?

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

regards, tom lane