[postgis-users] duplicated nodes

[Olivier Leprêtre]

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/20170802/34d2e924/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image003.jpg
Type: image/jpeg
Size: 9371 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170802/34d2e924/attachment.jpg>