I have data on the location of water treatment facilities in the EU (green in the map) and of stations that control for water quality in rivers (blue in the map). I'd like to get a list of treatment facilities and water quality stations that are upstream and downstream for each treatment facility. Optimally the output would look something like the table below. Alternatively, listing all downstream stations (monitoring and treatment) for each monitoring station, would also be enough. The data on the stations and a river dataset is here.
It is of course possible to spatially join the stations and rivers. But this does not provide information of the order of stations along the river.
Using the elevation of stations in basin areas to get the order of stations also seems not to be a solution as different tributary rivers feed into larger streams.
Treatment fac | Upstr treatment fac | Downstr treatment fac | Upstr stations | Downstr stations | River ID |
---|---|---|---|---|---|
FR011013100000 | FR011049600000; FR010295000000 | ...? | AR01097000 | AR01094000 | AR13 |
FR011049600000 | FR011013100000; ... | AR01097000; AR01094000 | AR66 | ||
... |