I have a geometric network that represents a combined sewer system. The network functions as expected - I can set flags and trace upstream and downstream.
My goal is to identify all of the ultimate sources (line-segments) that flow to each of several downstream weirs (also line segments). Because it is a complex branching network, there can be multiple source segments per weir and, theoretically, a source segment could terminate at multiple weirs. A final deliverable would be a table with two columns: Weirs and sources:
| Weir ID | Source ID | ------------------------ | 1 | A | | 1 | B | | 2 | C | | 3 | D | | 4 | A | | 1 | E | ------------------------
What I've figured out so far:
- I can create a layer of points to represent the 90 or so weirs in the network.
- I can use the point layer as flags to execute the "Trace Geometric Network" tool from ArcToolbox.
- I can save out the traced flowpaths from source to weir as a new layer.
If I only had to do this for a single weir, it would be easy. I'd look at the traced network and write down the IDs of any source line segments. Or, if it's a particularly complex trace, I'd run a query on the saved-out flowpath and select any segments that don't have a segment upstream. Something like:
SELECT * FROM saved_flowpath sf1 WHERE NOT EXISTS (SELECT 1 FROM saved_flowpath sf2 WHERE sf1.upstream_node_id = sf2.downstream_node_id)
However, with ~90 weirs in this particular network, and the likelihood of having to do this again for other networks, I want to find a way to automate it. But without being able to write recursive queries in the ArcGIS query builder, I'm at a loss for how to proceed. Anyone have any thoughts?