[postgis-users] Eliminate duplicate points with nearest neighbor

[Pedro Doria Meunier]

Hey Jason,

First of all you have to execute a proximity query on the wanted point....

Look at this query:

// first perform a search within a 10 metres radius of current coordinates
to get the road name.
$sql ="SELECT name, city FROM myroadnetwork WHERE ";
$sql.="transform(geometry,$srid) &&
setsrid(expand(transform(geomfromtext('POINT($lon
$lat)',4326),$srid),10),$srid) ";
$sql.="and distance(transform(geomfromtext('POINT($lon $lat)',4326),$srid),
transform(geometry,$srid)) <=10 LIMIT 1";

Btw: this involved creating a GiST index on the wanted projection... ;-)
-- this is a linestring layer but could easily be adapted to a point layer
;-) 

You'll also want to use the operator 'LIKE' at the end of that query...

Hope this helps,

Pedro Doria Meunier
(351) 91 302 49 72 - (351) 96 247 99 12
MSN - pdoriam at hotmail.com
ICQ - 308-182-126
Skype: pdoriam


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
PICKERING, Jason
Sent: quarta-feira, 25 de Outubro de 2006 17:45
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Eliminate duplicate points with nearest neighbor

Hi, I am new to PostGIS and was wondering if anyone could offer advice
on the following problem. 

I have a point dataset in PostGIS and I would like to identify points
which are potential duplicates. The dataset contains points collected in
various surveys with GPS units. Each point also has a name, however,
sometimes the names are spelled slightly differently. Using soundex has
helped to narrow the potential duplicate points a bit, but because many
of the names are not in English, its utility is pretty limited. So, I
would like to identify each points nearest neighbor whose soundex value
is equal to another point. The point being trying to identify points
that are close to each other and whose names are approximately alike. 

Any advice would be much appreciated. 

Thanks,
Jason Pickering