Re: find close (duplicate) points + create index

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: find close (duplicate) points + create index
Дата
Msg-id 2730.1078896447@sss.pgh.pa.us
обсуждение исходный текст
Ответ на find close (duplicate) points + create index  (Elinor Medezinski <elinor@bellatrix.tau.ac.il>)
Ответы Re: find close (duplicate) points + create index  (<ghaverla@freenet.edmonton.ab.ca>)
Re: find close (duplicate) points + create index  (Elinor Medezinski <elinor@bellatrix.tau.ac.il>)
Re: find close (duplicate) points + create index  (Elinor <elinor@wise.tau.ac.il>)
Список pgsql-novice
Elinor Medezinski <elinor@bellatrix.tau.ac.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

В списке pgsql-novice по дате отправления:

Предыдущее
От: Bruno Wolff III
Дата:
Сообщение: Re: using pgsql on my comp only without tcp
Следующее
От: stm23
Дата:
Сообщение: installing postgresql