1

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?

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 query is missing the two edges 2 and 3, but includes the correct nodes:

graph

2

You put a condition that the valve at the start of the edge must not be closed, but you also allow to flow "backward" (from end node to start node). You would need to have refined join conditions.

In pseudo code: Include edge if / join on:

(path.end_node = edge.start_node and path.end_node_valve is open) 
OR (path.end_node = edge.end_node and path.end_node_valve is open)
OR (path.start_node = edge.start_node and path.start_node_valve is open) 
OR (path.start_node = edge.end_node and path.start_node_valve is open)
  • I did this with: (s.end_node = e.end_node AND (SELECT * FROM topology.validateAttribute('topology','valve','status','closed',s.end_node,1)) = false) but gives me the same result. – Saverio Mancuso Oct 4 '17 at 13:09
  • @SaverioMancuso you need to do it 4 times – JGH Oct 4 '17 at 13:23
  • I did it 4 times just like u wrote it. And replaced path with s and edge with e. – Saverio Mancuso Oct 4 '17 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.