[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