postgis finding lines that touch and update

Question

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.

Class ID

1 14

1 9

Now it should be possible to find all lines that make up the exit starting at ID 14.

Any ideas on how to proceed?

Answer 1

You should probably be using nodes to communicate the connections between your edges. ST_Touches may bring about matches that you did not intend. The following query should give you the results you are looking for using ST_Touches though:

SELECT  A1.linkid,
        A1.class AS FromClass,
        B1.MinClass AS ToClass,
        B1.linkid AS ExitID
FROM    sr_buffer AS A1
        INNER JOIN
        ( 
          SELECT  B2.linkid,
                  B2.geom,
                  A2.MinClass
          FROM    sr_buffer AS B2
                  INNER JOIN
                  (  
                    SELECT  B.linkid, 
                            MIN(A.class) AS MinClass
                    FROM    sr_buffer AS A
                            INNER JOIN
                            sr_buffer AS B
                            ON  A.linkid <> B.linkid
                                AND
                                ST_Touches(A.geom, B.geom)
                                AND
                                B.class = 5
                                AND
                                A.class <> 5
                     GROUP BY B.linkid
                   ) AS A2
                   ON  A2.linkid =  B2.linkid
       ) AS B1
        ON  A1.linkid <> B1.linkid
            AND
            ST_Touches(A1.geom, B1.geom)
            AND 
            A1.class <> 5