[postgis-users] duplicated nodes

Regina Obe lr at pcorp.us
Thu Aug 3 06:28:12 PDT 2017

Ah great.  Sorry about forgetting about the over on that one. Glad you
figured it out.


BTW in new docs, I cut in a picture so it's hopeful a bit clearer to folks
what it does.  I've got to clean up the formatting a bit.










From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf
Of Olivier Leprêtre
Sent: Thursday, August 03, 2017 4:07 AM
To: 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] duplicated nodes


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




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
<mailto: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:




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 <mailto: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/20170803/3d590d07/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/3d590d07/attachment.jpg>

More information about the postgis-users mailing list