[pgrouting-users] Dangling sub-networks
Imre Samu
pella.samu at gmail.com
Sun Jun 27 15:36:43 PDT 2021
> 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
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
- Welcome to the community :-)
Regards,
Imre
Hans Skov-Petersen <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> *On
> Behalf Of *Imre Samu
> *Sent:* 27. juni 2021 22:59
> *To:* pgRouting users mailing list <pgrouting-users at lists.osgeo.org>
> *Cc:* pgRouting developers mailing list <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%7C0080224f167f4bfad97d08d939ae6292%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604243862347472%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=gJ98Sl5TkTmIEfcuR137NEFMXHzJ6UzgTF8yc4vZXJI%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%7C0080224f167f4bfad97d08d939ae6292%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604243862352447%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=18ks3lmIRl1BLgtS%2FmPSjZ%2B7S3Q8r0IdufOdD871rA4%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> 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 J)…. 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.
>
>
>
>
>
>
>
>
>
> *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
>
>
>
> [image: Title: SD_Logo]
>
>
>
>
> <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.facebook.com%2Funiversitet&data=04%7C01%7Chsp%40ign.ku.dk%7C0080224f167f4bfad97d08d939ae6292%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604243862352447%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=sboKTxPerLi7QmgTi8SBYHcSKkpu4RnMyjn%2B5JYo%2BUE%3D&reserved=0>
>
> <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.instagram.com%2Funiversity_of_copenhagen&data=04%7C01%7Chsp%40ign.ku.dk%7C0080224f167f4bfad97d08d939ae6292%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604243862357420%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=tP02S9mszIgAeXgEGuaR2%2Bc9qspWhFmtmhw8639aBMY%3D&reserved=0>
>
> <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.linkedin.com%2Fcompany%2Funiversity-of-copenhagen&data=04%7C01%7Chsp%40ign.ku.dk%7C0080224f167f4bfad97d08d939ae6292%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604243862362399%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=U9Er7p32%2B4Bxng3mGPe35tFYusH3pUcjYzNtx0qV6bE%3D&reserved=0>
>
> <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2Fkoebenhavns_uni&data=04%7C01%7Chsp%40ign.ku.dk%7C0080224f167f4bfad97d08d939ae6292%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604243862367388%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=W2jAiVY%2BPjD62%2Bh3kIPKBW%2BDOV3eJ9lzxKGifeZ5agg%3D&reserved=0>
>
> <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%7C0080224f167f4bfad97d08d939ae6292%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604243862372353%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=wnYpChLeZAP8HtJ5xLY87FsHXY0Uf9yoV5Yk%2BsMpC7U%3D&reserved=0>
>
> <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftheconversation.com%2Finstitutions%2Funiversity-of-copenhagen-1186&data=04%7C01%7Chsp%40ign.ku.dk%7C0080224f167f4bfad97d08d939ae6292%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604243862372353%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=s9Gz%2FSMDAuFEtfE3CKrONS1qqfE1kPf%2FVio%2B1M%2F8nJs%3D&reserved=0>
>
> <https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.ku.dk%2F&data=04%7C01%7Chsp%40ign.ku.dk%7C0080224f167f4bfad97d08d939ae6292%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604243862377340%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=qn3Q2hZEvBIsu0DcBg2GxJa%2F62NRKjXktb1tJ10YNyM%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%7C0080224f167f4bfad97d08d939ae6292%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604243862382321%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=iC4BKXmSgmFCpq6n%2FdZHD5SzUZEUYF9nP2%2FrcD1LEdI%3D&reserved=0>
>
>
>
>
>
>
>
> _______________________________________________
> Pgrouting-users mailing list
> 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%7C0080224f167f4bfad97d08d939ae6292%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C637604243862387299%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000&sdata=nCPdra9K97387t22%2BKTv57%2BXG5tjhq1nrjuSAgi0%2Bk8%3D&reserved=0>
>
> _______________________________________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/pgrouting-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210628/257e0a1b/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 6600 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210628/257e0a1b/attachment-0001.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.png
Type: image/png
Size: 12021 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210628/257e0a1b/attachment-0008.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image003.png
Type: image/png
Size: 1244 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210628/257e0a1b/attachment-0009.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image004.png
Type: image/png
Size: 1422 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210628/257e0a1b/attachment-0010.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image005.png
Type: image/png
Size: 1270 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210628/257e0a1b/attachment-0011.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image006.png
Type: image/png
Size: 1363 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210628/257e0a1b/attachment-0012.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image007.png
Type: image/png
Size: 1440 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210628/257e0a1b/attachment-0013.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image008.png
Type: image/png
Size: 1591 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210628/257e0a1b/attachment-0014.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image009.png
Type: image/png
Size: 1359 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20210628/257e0a1b/attachment-0015.png>
More information about the Pgrouting-users
mailing list