0

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?

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
  • I feel as though I may have missed a class qualifier in there somewhere, but I have no way to test against your data. – ike Jun 6 '14 at 16:34
  • i can't test right now either but i'll get back to it as soon as possible. thanks already for looking into this. – LarsVegas Jun 6 '14 at 19:05
  • You may also need to examine the possibility of iterative loops a procedure like this may create. An edge that connects to more than one class 5 object at different points, having either connecting class lower than the object itself, may require multiple iterations of this procedure. With a pervasive network of class 5 objects, you may end up with every single edge being set to the lowest overall class. Before you swim in that possibility, it may be wise to consider if the directions of the edges is not also a factor. – ike Jun 6 '14 at 19:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.