duplicated nodes

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: duplicated nodes

Regina Obe-2

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:[hidden email]] On Behalf Of Olivier Leprêtre
Sent: Wednesday, August 02, 2017 3:28 PM
To: [hidden email]
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

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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:[hidden email]] De la part de Regina Obe
Envoyé : mercredi 2 août 2017 23:15
À : 'PostGIS Users Discussion' <[hidden email]>
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 [[hidden email]] On Behalf Of Olivier Leprêtre
Sent: Wednesday, August 02, 2017 3:28 PM
To: [hidden email]
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

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: duplicated nodes

Regina Obe-2

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.

 

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

 

 

Thanks,

Regina

http://postgis.us

 

 

From: postgis-users [mailto:[hidden email]] On Behalf Of Olivier Leprêtre
Sent: Thursday, August 03, 2017 4:07 AM
To: 'PostGIS Users Discussion' <[hidden email]>
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

 

Olivier

 

De : postgis-users [[hidden email]] De la part de Regina Obe
Envoyé : mercredi 2 août 2017 23:15
À : 'PostGIS Users Discussion' <[hidden email]>
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 [[hidden email]] On Behalf Of Olivier Leprêtre
Sent: Wednesday, August 02, 2017 3:28 PM
To: [hidden email]
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

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: duplicated nodes

Olivier Leprêtre

Thanks for this helpful precision/explanation. This function needs to be known, a bit harsh to understand but many applications for geomatics…

 

Olivier

 

De : postgis-users [mailto:[hidden email]] De la part de Regina Obe
Envoyé : jeudi 3 août 2017 15:28
À : 'PostGIS Users Discussion' <[hidden email]>
Objet : Re: [postgis-users] duplicated nodes

 

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.

 

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

 

 

Thanks,

Regina

http://postgis.us

 

 

From: postgis-users [[hidden email]] On Behalf Of Olivier Leprêtre
Sent: Thursday, August 03, 2017 4:07 AM
To: 'PostGIS Users Discussion' <[hidden email]>
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

 

Olivier

 

De : postgis-users [[hidden email]] De la part de Regina Obe
Envoyé : mercredi 2 août 2017 23:15
À : 'PostGIS Users Discussion' <[hidden email]>
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 [[hidden email]] On Behalf Of Olivier Leprêtre
Sent: Wednesday, August 02, 2017 3:28 PM
To: [hidden email]
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.

 

cid:image001.jpg@01D30C78.0439C4E0

 

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

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...