[postgis-users] duplicated nodes

Regina Obe lr at pcorp.us
Wed Aug 2 14:14:55 PDT 2017

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




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.




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,

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

FROM nodes  ) AS o




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


Hope that helps,



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
Subject: [postgis-users] duplicated nodes




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






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

More information about the postgis-users mailing list