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
<http://et.id>,et.source,et.target,ST_EndPoint(the_geom) as
targetPoint
FROM edge_table et WHERE et.id <http://et.id> = *12*
UNION ALL
SELECT e.id <http://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.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
<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
