[pgrouting-users] Dangling sub-networks
Hans Skov-Petersen
hsp at ign.ku.dk
Sun Jun 27 15:41:59 PDT 2021
You are a pal.
Thanks
I’ll go for it.
Cheers
Hans Skov-Petersen
Professor of Geoinformatics
University of Copenhagen
Department of Geosciences and Natural Resource Management
Section of Landscape Architecture and Planning
Rolighedsvej 23
DK-1958 Frederiksberg
DIR +45 35 33 18 16
MOB +45 23 82 80 45
hsp at ign.ku.dk<mailto:hsp at ign.ku.dk>
[Title: SD_Logo]
[cid:image002.png at 01D76BB6.6683E9E0]<https://www.facebook.com/universitet> [cid:image003.png at 01D76BB6.6683E9E0] <https://www.instagram.com/university_of_copenhagen> [cid:image004.png at 01D76BB6.6683E9E0] <https://www.linkedin.com/company/university-of-copenhagen> [cid:image005.png at 01D76BB6.6683E9E0] <https://twitter.com/koebenhavns_uni> [cid:image006.png at 01D76BB6.6683E9E0] <https://www.futurity.org/university/university-of-copenhagen/> [cid:image007.png at 01D76BB6.6683E9E0] <https://theconversation.com/institutions/university-of-copenhagen-1186> [cid:image008.png at 01D76BB6.6683E9E0] <http://www.ku.dk/>
How we protect personal data<https://informationssikkerhed.ku.dk/english/protection-of-information-privacy/privacy-policy/>
From: Pgrouting-users <pgrouting-users-bounces at lists.osgeo.org> On Behalf Of Imre Samu
Sent: 28. juni 2021 00:37
To: pgRouting users mailing list <pgrouting-users at lists.osgeo.org>
Subject: Re: [pgrouting-users] Dangling sub-networks
> Would you happen to have a hint to how to delete such components from a network/topology?
my proposal:
-create "components_table"
-insert new column ("component" id ) to the edge_table ( and fill from components_table )
- calculate sum(cost ) by component .. and find the long tails
- filter OR delete by small component id-s ( I prefer creating new edge table instead of delete .. )
-- public.edge_table
-- https://docs.pgrouting.org/3.1/en/sampledata.html<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.pgrouting.org%2F3.1%2Fen%2Fsampledata.html&data=04%7C01%7Chsp%40ign.ku.dk%7Cce5ae1d0bc4f477bcee308d939bc157c%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604302855314642%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=Y9j7MdfhjL2bABw1mwGbb8trEHTx8%2B%2B%2Fge59aBFnpPE%3D&reserved=0>
CREATE TABLE components_table as
SELECT * FROM pgr_connectedComponents(
'SELECT id, source, target, cost, reverse_cost FROM edge_table'
)
;
-- add a new column to the edge_table
ALTER TABLE edge_table ADD COLUMN component BIGINT;
UPDATE edge_table as e
SET component = c.component
FROM components_table c
WHERE (e.component is NULL) AND e.source=c.node
;
-- calculate small component -> sum(cost)
CREATE TABLE small_components as
SELECT component,
sum(cost) as sum_cost
FROM edge_table
GROUP BY 1
HAVING sum(cost) < 2 ;
-- create new_edge_table
-- filtering: not in small components
CREATE TABLE new_edge_table as
SELECT * FROM edge_table
WHERE component not in ( SELECT component FROM small_components )
ORDER BY id
;
the small_components tables
select * from small_components;
component | sum_cost
-----------+----------
14 | 1
16 | 1
(2 rows)
all components:
SELECT component,
sum(cost) as sum_cost
FROM edge_table
GROUP BY 1
ORDER BY 2 DESC ;
component | sum_cost
-----------+----------
1 | 12
14 | 1
16 | 1
(3 rows)
In your case .. probably the cost is "in meter length "
ps:
- if you are using osm2pgrouting - increase the "--chunk" value: https://github.com/pgRouting/osm2pgrouting/issues/296<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FpgRouting%2Fosm2pgrouting%2Fissues%2F296&data=04%7C01%7Chsp%40ign.ku.dk%7Cce5ae1d0bc4f477bcee308d939bc157c%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604302855324594%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=0wVr29ReGdh%2FkITis7WEzhpNquYnMq0Nc4m2rryF8aA%3D&reserved=0>
- Welcome to the community :-)
Regards,
Imre
Hans Skov-Petersen <hsp at ign.ku.dk<mailto:hsp at ign.ku.dk>> ezt írta (időpont: 2021. jún. 27., V, 23:17):
Hi Imre
It seems like something I can make work to identify small subnets/components. Thanks.
Would you happen to have a hint to how to delete such components from a network/topology?
Cheers
Hans
From: Pgrouting-users <pgrouting-users-bounces at lists.osgeo.org<mailto:pgrouting-users-bounces at lists.osgeo.org>> On Behalf Of Imre Samu
Sent: 27. juni 2021 22:59
To: pgRouting users mailing list <pgrouting-users at lists.osgeo.org<mailto:pgrouting-users at lists.osgeo.org>>
Cc: pgRouting developers mailing list <pgrouting-dev at lists.osgeo.org<mailto:pgrouting-dev at lists.osgeo.org>>
Subject: Re: [pgrouting-users] Dangling sub-networks
> Is there a way to identify (and potentially delete) such minor sub-nets?
IMHO: this is similar for detecting "Component" ( https://en.wikipedia.org/wiki/Component_(graph_theory)<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fen.wikipedia.org%2Fwiki%2FComponent_(graph_theory)&data=04%7C01%7Chsp%40ign.ku.dk%7Cce5ae1d0bc4f477bcee308d939bc157c%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604302855334555%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=19SJTDXi27chWJ9dlO5gkvGoqoFQoCluMKOfzPrymag%3D&reserved=0> )
Have you checked the https://docs.pgrouting.org/3.1/en/components-family.html<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.pgrouting.org%2F3.1%2Fen%2Fcomponents-family.html&data=04%7C01%7Chsp%40ign.ku.dk%7Cce5ae1d0bc4f477bcee308d939bc157c%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604302855344506%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=5oOKlCScOXYT7eDpbFY4eSYwMfQl9PcOZ06m%2BB9FoRU%3D&reserved=0> functions?
If you know the components - you can calculate the aggregated length of edges for each component.
Regards,
Imre
Hans Skov-Petersen <hsp at ign.ku.dk<mailto:hsp at ign.ku.dk>> ezt írta (időpont: 2021. jún. 27., V, 21:42):
Hi list
I realize that the network (OSM) I am working on is not always connected as it should (big surprise ☺)…. Some parts are disjoint from the rest – even when not being situated on islands, or other places where a disjoint is expected. Accordingly, routes from nodes on such sub-nets cannot be generated.
Is there a way to identify (and potentially delete) such minor sub-nets? For instance by asking for the aggregated length of edges of a sub-net.
[cid:image009.jpg at 01D76BB6.6683E9E0]
Hans Skov-Petersen
Professor of Geoinformatics
University of Copenhagen
Department of Geosciences and Natural Resource Management
Section of Landscape Architecture and Planning
Rolighedsvej 23
DK-1958 Frederiksberg
DIR +45 35 33 18 16
MOB +45 23 82 80 45
hsp at ign.ku.dk<mailto:hsp at ign.ku.dk>
[Title: SD_Logo]
[cid:image002.png at 01D76BB6.6683E9E0]<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.facebook.com%2Funiversitet&data=04%7C01%7Chsp%40ign.ku.dk%7Cce5ae1d0bc4f477bcee308d939bc157c%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604302855344506%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=ZkP7Eos%2FrKCI8ehOGdSUf19B7p5xaY9BrATUuGHkSXQ%3D&reserved=0> [cid:image003.png at 01D76BB6.6683E9E0] <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.instagram.com%2Funiversity_of_copenhagen&data=04%7C01%7Chsp%40ign.ku.dk%7Cce5ae1d0bc4f477bcee308d939bc157c%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604302855354479%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=tS0%2BSjj1AyPikmVVPqCAEq8ghuGcWlk5yfqiNrrnXyY%3D&reserved=0> [cid:image004.png at 01D76BB6.6683E9E0] <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.linkedin.com%2Fcompany%2Funiversity-of-copenhagen&data=04%7C01%7Chsp%40ign.ku.dk%7Cce5ae1d0bc4f477bcee308d939bc157c%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604302855364426%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=k6DSSwVaH5tcA8pPA9OuUR4CVQFwQlJWipkMLEn%2BTXw%3D&reserved=0> [cid:image005.png at 01D76BB6.6683E9E0] <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2Fkoebenhavns_uni&data=04%7C01%7Chsp%40ign.ku.dk%7Cce5ae1d0bc4f477bcee308d939bc157c%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604302855364426%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=Xaw6114h8xVVKjL0nJ2YNrY%2BcKWzrJuX4nPOrrjxP1A%3D&reserved=0> [cid:image006.png at 01D76BB6.6683E9E0] <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.futurity.org%2Funiversity%2Funiversity-of-copenhagen%2F&data=04%7C01%7Chsp%40ign.ku.dk%7Cce5ae1d0bc4f477bcee308d939bc157c%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604302855374383%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=tiY%2BxHAS%2FUgieNMU4ygrMX79SukuyaKitxM%2F1N2H8ts%3D&reserved=0> [cid:image007.png at 01D76BB6.6683E9E0] <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftheconversation.com%2Finstitutions%2Funiversity-of-copenhagen-1186&data=04%7C01%7Chsp%40ign.ku.dk%7Cce5ae1d0bc4f477bcee308d939bc157c%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604302855384336%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=0hl1XzXj1B0nKEtCkZalyysA6vjrLGKoBZGnB0pi6OA%3D&reserved=0> [cid:image008.png at 01D76BB6.6683E9E0] <https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.ku.dk%2F&data=04%7C01%7Chsp%40ign.ku.dk%7Cce5ae1d0bc4f477bcee308d939bc157c%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604302855384336%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=3meGHBtF3%2Bj5qnXz1tu%2FQb%2BSz10gSAwg3BXDT0x29qo%3D&reserved=0>
How we protect personal data<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Finformationssikkerhed.ku.dk%2Fenglish%2Fprotection-of-information-privacy%2Fprivacy-policy%2F&data=04%7C01%7Chsp%40ign.ku.dk%7Cce5ae1d0bc4f477bcee308d939bc157c%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604302855394289%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=ntg2NBE1X%2BvJTbVFVleEXGVIKzmK%2FffFIfu1cEjJ7Wc%3D&reserved=0>
_______________________________________________
Pgrouting-users mailing list
Pgrouting-users at lists.osgeo.org<mailto:Pgrouting-users at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/pgrouting-users<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fpgrouting-users&data=04%7C01%7Chsp%40ign.ku.dk%7Cce5ae1d0bc4f477bcee308d939bc157c%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604302855404251%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=aW%2BLVP6oexNEfHNS4M6%2BzLivH9a2Ef2BbOvtOOPicDc%3D&reserved=0>
_______________________________________________
Pgrouting-users mailing list
Pgrouting-users at lists.osgeo.org<mailto:Pgrouting-users at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/pgrouting-users<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fpgrouting-users&data=04%7C01%7Chsp%40ign.ku.dk%7Cce5ae1d0bc4f477bcee308d939bc157c%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604302855404251%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=aW%2BLVP6oexNEfHNS4M6%2BzLivH9a2Ef2BbOvtOOPicDc%3D&reserved=0>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210627/3e87cf5a/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.png
Type: image/png
Size: 1244 bytes
Desc: image002.png
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210627/3e87cf5a/attachment-0009.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image003.png
Type: image/png
Size: 1422 bytes
Desc: image003.png
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210627/3e87cf5a/attachment-0010.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image004.png
Type: image/png
Size: 1270 bytes
Desc: image004.png
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210627/3e87cf5a/attachment-0011.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image005.png
Type: image/png
Size: 1363 bytes
Desc: image005.png
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210627/3e87cf5a/attachment-0012.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image006.png
Type: image/png
Size: 1440 bytes
Desc: image006.png
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210627/3e87cf5a/attachment-0013.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image007.png
Type: image/png
Size: 1591 bytes
Desc: image007.png
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210627/3e87cf5a/attachment-0014.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image008.png
Type: image/png
Size: 1359 bytes
Desc: image008.png
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210627/3e87cf5a/attachment-0015.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image009.jpg
Type: image/jpeg
Size: 6600 bytes
Desc: image009.jpg
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210627/3e87cf5a/attachment-0001.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image010.png
Type: image/png
Size: 12021 bytes
Desc: image010.png
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210627/3e87cf5a/attachment-0016.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image011.png
Type: image/png
Size: 12021 bytes
Desc: image011.png
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210627/3e87cf5a/attachment-0017.png>
More information about the Pgrouting-users
mailing list