I am not much of a postgis guy so please bear with me. I have a subset of lines representing exits and roads around those exists. Main roads and exists can be distinguished by their class attribute (exits = class 5).
My final goal is to update each single line that together make up an exit with the class attribute of the main road with the smallest value (say you leave the highway (class 1) and continue to a local road (class 4), the exit lines need to be updated to class 1).
Sounds pretty straight forward but I'm struggling.
I tried to break the problem apart so I came up with the following queries to close in on the problem.
# finds all lines of exits that together form the exit road SELECT ST_LineMerge (ST_Collect(a.geom)) FROM sr_buffer as a, sr_buffer as b WHERE ST_Touches(a.geom, b.geom) AND a.class = 5 AND b.class = 5
This isn't really helping in the end I'm afraid as I'm losing the ids along the way.
# finds all transition points from main road to slip road 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_TOUCHES(s2.geom, s1.geom)
This is better already. Now we know that the exists starting with ID 14 and 9 for example should become class 1.
Now it should be possible to find all lines that make up the exit starting at ID 14.
Any ideas on how to proceed?