I have a water network in which there exist valves at nodes between two edges (pipes). When the valve is open water can flow, if not, it can't.

How can I calculate the catchment area from a start point in the network(e.g. a break/pipe burst) that takes into account not just pipe/edge costs (e.g. pipe diameter), but also the cost at the stop valves/nodes (e.g. high cost when closed, no cost when open)?


pgRouting's network topology does not handle nodes but only edges, so you have to apply costs of the nodes to the edges.


You can simply join your vertices to edges and then use vertex close/open information to set edge cost acordingly. I supose your graph is directed, so then you want only left join on target vertex because when target vertex of odge is closed, then that edge should be removed from graph by setting its cost value to -1. Something like this should work:

SELECT et.id, et.source, et.target, coalesce(vt.cost, et.cost), et.reverse_cost 
FROM edge_table et
LEFT JOIN (SELECT id, -1 cost FROM vertex_table WHERE closed) vt ON vt.id = et.target

It simply joins all closed target vertices to edges and when there is closed target vertex then it sets edge cost to -1, thus efectively remove that edge from topology.


Or you can just join edges and target vertice and leve only those that are open...

SELECT et.id, et.source, et.target, et.cost, et.reverse_cost 
FROM edge_table et
JOIN vertex_table vt ON vt.id = et.target 
WHERE not vt.closed

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.