Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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?

share|improve this question
2  
Should work just fine. Can you setup a SQLFiddle example? (Btw: you should stop using the outdated implicit joins in the where clause and start using a proper JOIN operator) –  a_horse_with_no_name Jun 11 at 11:47
    
As I said, I'm a newbie... –  LarsVegas Jun 11 at 11:49
    
What exactly is the problem, avoiding loops in the graph (and infinite recursion ...) ? –  wildplasser Jun 11 at 11:52
    
Problem is: I want to add up all id's belonging to one 'situation'. The array just adds up the current id, not all id's of the current cycle. –  LarsVegas Jun 11 at 11:59
    
@a_horse_with_no_name, I guess because of the geometry part I can't put my data into SQLFiddle... –  LarsVegas Jun 11 at 12:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.