I am a newby when it comes to somewhat complexer sql queries so please bear with me.
I have a street network. My final goal is update segments of "class" 5 that are touching each other by the class value of the next segment that belongs to a different class than 5.
Imagine: there are 12 segments. One 'situation' (read highway exit situation) is made of 6 segments. The first segment is a highway segment, it has class '2'. The following 4 segments are exit segments, they have the class '5'. The last segment is a local road, it has class '4'. All exit segments need to be updated to class '2'. The second situation looks similar.
I managed to identify all transition segments by running this query:
CREATE TABLE touch AS
SELECT s2.geom, s2.linkid, s2.class, s1.linkid AS linkid_sr FROM
sr_buffer as s1, sr_buffer as s2
WHERE s1.class = 5 AND s2.class != 5 AND ST_EndPoint(s2.geom)=ST_StartPoint(s1.geom)
However, I need to identify all segments that lay in-between the transition segments. I came up with this recursive query.
WITH RECURSIVE walk_network(linkid, class, geom, path) AS (
SELECT linkid, class, geom, ARRAY[linkid] FROM test_exit WHERE class = 5
UNION ALL
SELECT n.linkid, n.class, n.geom, path || n.linkid
FROM test_exit n, walk_network w
WHERE ST_EndPoint(w.geom)=ST_StartPoint(n.geom) AND n.class = 5
)
SELECT *
FROM walk_network
My idea was to store all linkid's that belong to one 'cluster' into an array. But the query just returns the array with the current linked instead of adding them up. I don't really understand why?
I want to find out which exit segments belong together, so adding up means either putting them into an array or table. So I can use them afterwards to look up the highest class they are attached two.
3882600054 7 {3882600054}
3882600056 7 {3882600056}
3882600040 7 {3882600040}
How can I store all linkid's for one recursive loop in an array? What is the best way to loop through the array afterwards to look up the values in the other table?
Or is there a way better to do this anyway?
JOIN
operator) – a_horse_with_no_name Jun 11 at 11:47