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

Re: find close (duplicate) points + create index

From: Bruno Wolff III <bruno(at)wolff(dot)to>
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 04:16:24
Message-ID: 20040310041624.GB31629@wolff.to (view raw or flat)
Thread:
Lists: pgsql-novice
On Thu, Mar 04, 2004 at 12:42:23 +0200,
  Elinor Medezinski <elinor(at)bellatrix(dot)tau(dot)ac(dot)il> wrote:
> Hello,
> 
> I have a table with one column --
> "CREATE TABLE pointtable (point POINT)".
> 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. Is there a way to do this - search within a 
> column itself?

Yes, you can join a table to itself and return records matching some
critera. This isn't going to be fast. I didn't see a distance between
two points function, but it is probably there somewhere. If not you
can check if one point is contained in a circle of radius 1 centered
at the other point. This is something that could use an index, though
it probably wouldn't help when checking the whole table. It probably
would speed up checking a single point for conflicts.

In response to

pgsql-novice by date

Next:From: stm23Date: 2004-03-10 04:44:56
Subject: pgsql server package
Previous:From: george youngDate: 2004-03-10 01:51:23
Subject: Re: SQL Query - Performance Problem

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