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

Re: find close (duplicate) points + create index

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Elinor Medezinski <elinor(at)bellatrix(dot)tau(dot)ac(dot)il>
Cc: pgsql-novice(at)postgresql(dot)org, elinor(at)wise1(dot)tau(dot)ac(dot)il
Subject: Re: find close (duplicate) points + create index
Date: 2004-03-10 05:27:27
Message-ID: 2730.1078896447@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: stm23Date: 2004-03-10 08:15:39
Subject: installing postgresql
Previous:From: Bruno Wolff IIIDate: 2004-03-10 05:02:16
Subject: Re: using pgsql on my comp only without tcp

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