Hi, Imre, What happens if more than 1 result from the Walk the Network?
Can recursive query return all possible results? How to handle such results? My guess that memory allocation error occurred because that more than 1 result is found and the recursive query does not know what to do. What is your thought? Regards, David On Fri, 22 Apr 2022 at 22:14, Imre Samu <[email protected]> wrote: > > as St_intersects or recursive query used, > > The other alternative ( ~ less efficient ) is using a “noded” network > table ( "edge_table" ) > in the recursive query. ( and don't forget to add indexes to the > "source" and "target" columns ) > > WITH RECURSIVE walk_network(id, source, target, targetPoint) AS > (SELECT et.id,et.source,et.target,ST_EndPoint(the_geom) as targetPoint > FROM edge_table et WHERE et.id = *12* > UNION ALL > SELECT e.id, e.source, e.target ,ST_EndPoint(the_geom) as targetPoint > FROM edge_table e > , walk_network w > WHERE w.target = e.source > ) > SELECT ST_AsText(ST_MakeLine(targetPoint)) > FROM walk_network > ; > +---------------------------------+ > | st_astext | > +---------------------------------+ > | LINESTRING(4 2,3 2,2 1,1 1,0 0) | > +---------------------------------+ > (1 row) > > regards, > Imre > > > Imre Samu <[email protected]> ezt írta (időpont: 2022. ápr. 22., P, > 16:39): > >> > With a large data set, >> >> :-) >> please give more detail: >> - How large? >> - and what is your real "business problem"? what type of network? >> >> >> > I tried to use this >> http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html in >> the PostGIS. >> >> As I see this is a directed "network graph", and I will try using the >> pgRouting tool - for a large graph! >> *( "pgRouting extends the PostGIS/PostgreSQL geospatial database to >> provide geospatial routing and other network analysis functionality." )* >> The pgRouting project did not exist in 2010/07 when this blogpost was >> written! >> >> [image: image.png] >> >> so I have adapted the example network ( from the original blogpost ) >> to pgRouting and this is my sample result >> >> ---------- ALL "downstream path" from "all deadends" sorted by descending >> cost --------- >> >> +------------+-----------+---------+-------------------------------------+--------------+ >> | route_cost | start_vid | end_vid | the_geom_text >> | edge_ids | >> >> +------------+-----------+---------+-------------------------------------+--------------+ >> | 6.24 | 3044 | 3000 | LINESTRING(4 4,3 4,2 3,1 2,1 1,0 0) >> | {13,9,6,3,1} | >> | 5.83 | 3043 | 3000 | *LINESTRING(4 3,4 2,3 2,2 1,1 1,0 >> 0) | {12,8,5,2,1} |* >> | 4.83 | 3024 | 3000 | LINESTRING(2 4,2 3,1 2,1 1,0 0) >> | {10,6,3,1} | >> | 4.41 | 3014 | 3000 | LINESTRING(1 4,1 3,1 2,1 1,0 0) >> | {11,7,3,1} | >> | 3.41 | 3031 | 3000 | LINESTRING(3 1,2 1,1 1,0 0) >> | {4,2,1} | >> >> +------------+-----------+---------+-------------------------------------+--------------+ >> and the second line is same as in the blogpost ( *"Downstream(12)" *example) >> , >> just with an extra "deadends" points ; the edges :* {12,8,5,2,1} * >> >> start_vid : starting node/vertex id ( "deadends" in this example ) >> end_vid : ending node/vertex id constant 3000 (0,0) >> node/vertex id = 3000 + X*10+Y coordinate // ( 2,1 ) --> 3021 ; (0,0) >> --> 3000 >> >> >> > Whenever geospatial functions such as St_intersects or recursive query >> used, >> >> IMHO: A good scalable data model is extremely important. >> pgRouting has 2 important (separated) steps. >> - creating a routing topology - route optimized database ( with "start" >> - and "end" node/vertex ) >> - fast routing/graph/"network-walking" functions - without the geometry >> ( using Boost Graph c++ library ) >> ( in this example I have used >> https://docs.pgrouting.org/3.3/en/pgr_dijkstra.html ) >> >> >> and this is my adapted "routing" topology edge table : >> >> DROP TABLE IF EXISTS edge_table CASCADE; >> CREATE TABLE edge_table ( >> id bigint primary key, >> source bigint, >> target bigint, >> cost float, >> reverse_cost float, >> the_geom geometry >> ); >> -- network example from >> -- >> http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html >> INSERT INTO edge_table VALUES( 1, 3011, 3000, 1, -1, 'LINESTRING(1 1, 0 >> 0)'); >> INSERT INTO edge_table VALUES( 2, 3021, 3011, 1, -1, 'LINESTRING(2 1, 1 >> 1)'); >> INSERT INTO edge_table VALUES( 3, 3012, 3011, 1, -1, 'LINESTRING(1 2, 1 >> 1)'); >> INSERT INTO edge_table VALUES( 4, 3031, 3021, 1, -1, 'LINESTRING(3 1, 2 >> 1)'); >> INSERT INTO edge_table VALUES( 5, 3032, 3021, 1, -1, 'LINESTRING(3 2, 2 >> 1)'); >> INSERT INTO edge_table VALUES( 6, 3023, 3012, 1, -1, 'LINESTRING(2 3, 1 >> 2)'); >> INSERT INTO edge_table VALUES( 7, 3013, 3012, 1, -1, 'LINESTRING(1 3, 1 >> 2)'); >> INSERT INTO edge_table VALUES( 8, 3042, 3032, 1, -1, 'LINESTRING(4 2, 3 >> 2)'); >> INSERT INTO edge_table VALUES( 9, 3034, 3023, 1, -1, 'LINESTRING(3 4, 2 >> 3)'); >> INSERT INTO edge_table VALUES(10, 3024, 3023, 1, -1, 'LINESTRING(2 4, 2 >> 3)'); >> INSERT INTO edge_table VALUES(11, 3014, 3013, 1, -1, 'LINESTRING(1 4, 1 >> 3)'); >> INSERT INTO edge_table VALUES(12, 3043, 3042, 1, -1, 'LINESTRING(4 3, 4 >> 2)'); >> INSERT INTO edge_table VALUES(13, 3044, 3034, 1, -1, 'LINESTRING(4 4, 3 >> 4)'); >> >> full example code - with data&code: >> https://gist.github.com/ImreSamu/efda6093b67391a0edafff39d8056cb5 >> >> if you are interested in more examples.. check the pgRouting tutorial >> for example: *"Pre-processing waterways data"* >> >> https://workshop.pgrouting.org/2.7/en/un_sdg/sdg11-cities.html#pre-processing-waterways-data >> >> regards, >> Imre >> >> >> Shaozhong SHI <[email protected]> ezt írta (időpont: 2022. ápr. >> 22., P, 1:22): >> >>> Whenever geospatial functions such as St_intersects or recursive query >>> used, the PostGIS appears to spawn away to many child queries and just >>> obliterate the CPU. Nothing finishes. >>> >>> That forced me to try out to do the some tasks on the FME server. >>> >>> I tried to use this http://blog.cleverelephant.ca/2010/07/network >>> -walking-in-postgis.html in the PostGIS. >>> >>> I tried to linecombiner in FME. LineCombiner | FME (safe.com) >>> <https://www.safe.com/transformers/line-combiner/>. >>> >>> With a large data set, the running of processors were monitored. It was >>> estimated the PostGIS one would take 16 days to complete. >>> >>> But, it only took a few minute to do the same thing in FME. >>> >>> This suggests that something is not right with the PostGIS Server. >>> >>> Have anyone got experience with configuration and improving perfomance >>> of PostGIS Server? >>> >>> Regards, >>> >>> David >>> _______________________________________________ >>> postgis-users mailing list >>> [email protected] >>> https://lists.osgeo.org/mailman/listinfo/postgis-users >>> >> _______________________________________________ > postgis-users mailing list > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
