Can I simply generate values for startpoint and endpoint of a line? Put these into startpoint and endpoint column Modify the recursive query to see whether one can walk the network?
Regards, David On Sun, 8 May 2022 at 02:13, Imre Samu <[email protected]> wrote: > > How to generate source and target values? > > The simplest method: > Use the pgrouting ready made functions / tools > - https://docs.pgrouting.org/latest/en/topology-functions.html > - https://docs.pgrouting.org/latest/en/pgr_createTopology.html > * SELECT pgr_createTopology('edge_table', 0.001, 'id', 'the_geom');* > There is a nice Pgrouting tutorial: > https://workshop.pgrouting.org/2.7/en/index.html > and they have a chat: https://gitter.im/pgRouting/pgrouting ( see more > about support: https://pgrouting.org/support.html ) > > > Can I understand that source means startpoint of a line, and target > means an endpoint of a line? > > it is a "Graph"; so you have to split the lines into "edges" > https://en.wikipedia.org/wiki/Graph_theory#Graph > > regards, > Imre > > > Shaozhong SHI <[email protected]> ezt írta (időpont: 2022. máj. 8., > V, 2:37): > >> >> >> 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 >>> >>> >> How to generate source and target values? >> >> Regards, >> >> David >> >> >> >>> 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 >> > _______________________________________________ > 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
