[postgis-users] duplicated nodes

[Olivier Leprêtre]

Thanks very much Regina, it’s exactly what I searched for, it solved me two
problems : false positives and  groups display.

 

Typo : I just added ‘over()’  after ST_ClusterDBSCAN

 

Olivier

 

De : postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] De la part
de Regina Obe
Envoyé : mercredi 2 août 2017 23:15
À : 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>
Objet : Re: [postgis-users] duplicated nodes

 

If you are running PostGIS 2.3 or above, I think ST_ClusterDBSCAN might do
the trick for you:

 

http://postgis.net/docs/manual-2.3/ST_ClusterDBSCAN.html

 

You can set distance to 0 and minpoints to 2.  All points that are not 0
distance from any other point will have NULL in the bucket column.

All other ones that have bucket numbers are duplicated.

 

So:

 

SELECT n.code, n.geom, ST_ClusterDBSCAN(n.geom, 0,2) AS bucket

FROM nodes 

 

 

If you want the counts instead of the bucket, you can do

 

SELECT o.code, o.geom, COUNT(*) OVER(PARTITION BY bucket)  AS npoints,
bucket

FROM (SELECT n.code, n.geom, ST_ClusterDBSCAN(n.geom, 0,2) AS bucket

FROM nodes  ) AS o

WHERE bucket IS NOT NULL;

 

 

I didn't test so I might have a typo.

 

Hope that helps,

Regina

 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf
Of Olivier Leprêtre
Sent: Wednesday, August 02, 2017 3:28 PM
To: postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
Subject: [postgis-users] duplicated nodes

 

Hi,

 

I have a point layer that contains several superposed nodes two, three times
or more. I would like to display each group list and the number of item in
each. I found this query which “works” but returns some false positive

 

select case when lag(o.geom) OVER (PARTITION BY o.geom) = o.geom then ''
else 'x ' || o.nbre::text end,d.code from

(select geom,count(*) as nbre from nodes group by geom having count(*) >1)
as o,

lateral (select * from nodes) as d where st_within(o.geom,d.geom)

 

I understand that false positives (yellow beneath) comes  from “group by
geom” which use a bounding box and catch close but not duplicated points.

 



 

I tried other solutions which all get too complicated with several “join of
join”. I would appreciate if someone has an idea on the top of his/her head
!

 

Thanks

 

Olivier

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170803/d0944869/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 5853 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170803/d0944869/attachment.jpg>