Skip to main content
Geographic Information Systems

Return to Question

Bumped by Community user
Bumped by Community user
code formating + remove qgis tag
Source Link
JGH
JGH
  • 43.3k
  • 3
  • 46
  • 93

iI am tracing the postgisPostgis topological network with postgresqlPostgresql via:

WITH RECURSIVE path AS ( SELECT edge_id,start_node, end_node FROM simulation.edge_data WHERE start_node = 1 OR end_node = 1 OR start_node = 8 OR end_node = 8 UNION SELECT e.edge_id, e.start_node, e.end_node FROM simulation.edge_data e INNER JOIN path s ON (s.start_node = e.start_node OR s.start_node = e.end_node OR s.end_node = e.end_node OR s.end_node = e.start_node) ) SELECT * FROM path;

WITH RECURSIVE path AS (
 SELECT edge_id,start_node, end_node
   FROM simulation.edge_data
   WHERE start_node = 1 OR end_node = 1 OR start_node = 8 OR end_node = 8
 UNION
 SELECT e.edge_id, e.start_node, e.end_node
   FROM simulation.edge_data e
   INNER JOIN path s ON 
     (s.start_node = e.start_node 
     OR s.start_node = e.end_node 
     OR s.end_node = e.end_node 
     OR s.end_node = e.start_node) ) 
SELECT * FROM path;

I want to improve the querryquery in only visiting every node once. Also this network is full of gas pipelines. If the node is a valve that is closed i want to not further track in that direction. This returns me all nodes that have access to gas but not all edges. What am I doing wrong?

WITH RECURSIVE path AS ( SELECT edge_id,start_node, end_node FROM simulation.edge_data WHERE start_node = 1 OR end_node = 1 OR start_node = 8 OR end_node = 8 UNION SELECT e.edge_id, e.start_node, e.end_node FROM simulation.edge_data e INNER JOIN path s ON (SELECT * FROM topology.validateAttribute('topology','valve','status','closed',e.start_node,1)) = false AND (s.start_node = e.start_node OR s.start_node = e.end_node OR s.end_node = e.end_node OR s.end_node = e.start_node) ) SELECT * FROM path;

WITH RECURSIVE path AS (
 SELECT edge_id,start_node, end_node
   FROM simulation.edge_data
   WHERE start_node = 1 OR end_node = 1 OR start_node = 8 OR end_node = 8
 UNION
 SELECT e.edge_id, e.start_node, e.end_node
   FROM simulation.edge_data e
   INNER JOIN path s ON 
     (SELECT * FROM topology.validateAttribute('topology','valve','status','closed',e.start_node,1)) = false 
     AND (s.start_node = e.start_node 
          OR s.start_node = e.end_node 
          OR s.end_node = e.end_node 
          OR s.end_node = e.start_node) ) 
SELECT * FROM path;

This is my graph, where purple represents the gas sources. The yellow marked are closed valves. The result of the above querryquery is missing the two edges 2 and 3, but includes the correct nodes:

i am tracing the postgis topological network with postgresql via:

WITH RECURSIVE path AS ( SELECT edge_id,start_node, end_node FROM simulation.edge_data WHERE start_node = 1 OR end_node = 1 OR start_node = 8 OR end_node = 8 UNION SELECT e.edge_id, e.start_node, e.end_node FROM simulation.edge_data e INNER JOIN path s ON (s.start_node = e.start_node OR s.start_node = e.end_node OR s.end_node = e.end_node OR s.end_node = e.start_node) ) SELECT * FROM path;

I want to improve the querry in only visiting every node once. Also this network is full of gas pipelines. If the node is a valve that is closed i want to not further track in that direction. This returns me all nodes that have access to gas but not all edges. What am I doing wrong?

WITH RECURSIVE path AS ( SELECT edge_id,start_node, end_node FROM simulation.edge_data WHERE start_node = 1 OR end_node = 1 OR start_node = 8 OR end_node = 8 UNION SELECT e.edge_id, e.start_node, e.end_node FROM simulation.edge_data e INNER JOIN path s ON (SELECT * FROM topology.validateAttribute('topology','valve','status','closed',e.start_node,1)) = false AND (s.start_node = e.start_node OR s.start_node = e.end_node OR s.end_node = e.end_node OR s.end_node = e.start_node) ) SELECT * FROM path;

This is my graph, where purple represents the gas sources. The yellow marked are closed valves. The result of the above querry is missing the two edges 2 and 3, but includes the correct nodes:

I am tracing the Postgis topological network with Postgresql via:

WITH RECURSIVE path AS (
 SELECT edge_id,start_node, end_node
   FROM simulation.edge_data
   WHERE start_node = 1 OR end_node = 1 OR start_node = 8 OR end_node = 8
 UNION
 SELECT e.edge_id, e.start_node, e.end_node
   FROM simulation.edge_data e
   INNER JOIN path s ON 
     (s.start_node = e.start_node 
     OR s.start_node = e.end_node 
     OR s.end_node = e.end_node 
     OR s.end_node = e.start_node) ) 
SELECT * FROM path;

I want to improve the query in only visiting every node once. Also this network is full of gas pipelines. If the node is a valve that is closed i want to not further track in that direction. This returns me all nodes that have access to gas but not all edges. What am I doing wrong?

WITH RECURSIVE path AS (
 SELECT edge_id,start_node, end_node
   FROM simulation.edge_data
   WHERE start_node = 1 OR end_node = 1 OR start_node = 8 OR end_node = 8
 UNION
 SELECT e.edge_id, e.start_node, e.end_node
   FROM simulation.edge_data e
   INNER JOIN path s ON 
     (SELECT * FROM topology.validateAttribute('topology','valve','status','closed',e.start_node,1)) = false 
     AND (s.start_node = e.start_node 
          OR s.start_node = e.end_node 
          OR s.end_node = e.end_node 
          OR s.end_node = e.start_node) ) 
SELECT * FROM path;

This is my graph, where purple represents the gas sources. The yellow marked are closed valves. The result of the above query is missing the two edges 2 and 3, but includes the correct nodes:

Source Link
Saverio Mancuso
Saverio Mancuso
  • 21
  • 1

PostGIS - Tracing topological network

i am tracing the postgis topological network with postgresql via:

WITH RECURSIVE path AS ( SELECT edge_id,start_node, end_node FROM simulation.edge_data WHERE start_node = 1 OR end_node = 1 OR start_node = 8 OR end_node = 8 UNION SELECT e.edge_id, e.start_node, e.end_node FROM simulation.edge_data e INNER JOIN path s ON (s.start_node = e.start_node OR s.start_node = e.end_node OR s.end_node = e.end_node OR s.end_node = e.start_node) ) SELECT * FROM path;

I want to improve the querry in only visiting every node once. Also this network is full of gas pipelines. If the node is a valve that is closed i want to not further track in that direction. This returns me all nodes that have access to gas but not all edges. What am I doing wrong?

I do this with:

WITH RECURSIVE path AS ( SELECT edge_id,start_node, end_node FROM simulation.edge_data WHERE start_node = 1 OR end_node = 1 OR start_node = 8 OR end_node = 8 UNION SELECT e.edge_id, e.start_node, e.end_node FROM simulation.edge_data e INNER JOIN path s ON (SELECT * FROM topology.validateAttribute('topology','valve','status','closed',e.start_node,1)) = false AND (s.start_node = e.start_node OR s.start_node = e.end_node OR s.end_node = e.end_node OR s.end_node = e.start_node) ) SELECT * FROM path;

This is my graph, where purple represents the gas sources. The yellow marked are closed valves. The result of the above querry is missing the two edges 2 and 3, but includes the correct nodes:

graph