Separating distinct lines in PostGIS

Question

I have a table of linestring segments in Postgis. These segments make up distinct non-intersecting lines as shown by the diagram. To add confusion the direction of the linestrings are not uniform.

I want to create a simple set of linestrings that are uniformly joined in a single direction, this will enable me to shorten them if required using ST_LineInterpolate().

So, looking at the diagram you can see three lines which is what I want to get out of the query. I tried a recursive query but this ran forever on a table of only 10 linestrings:-

WITH RECURSIVE line_join(ogc_fid,wkb_geometry) AS (

 SELECT ogc_fid, wkb_geometry FROM TABLE.tll_lines_selected WHERE ogc_fid=47
UNION ALL
     SELECT n.ogc_fid, n.wkb_geometry FROM TABLE.tll_lines_selected n, line_join lj
     WHERE ST_Intersects(n.wkb_geometry,lj.wkb_geometry)

) SELECT ogc_fid FROM line_join;

Therefore I think something is not right. Is there a better strategy?

Answer 1

I made progress on this last night thanks to the answer here Select the lines that form a ring in postgis

WITH RECURSIVE merge_lines(ogc_fid, wkb_geometry, path, merged) AS 
(
    SELECT ls.ogc_fid, ls.wkb_geometry, array[ls.ogc_fid],ST_LineMerge(ls.wkb_geometry) FROM TABLE.tll_lines_selected AS ls

UNION ALL

    SELECT ls.ogc_fid, ls.wkb_geometry, path || ls.ogc_fid,(ST_Dump(ST_LineMerge(ST_Collect(ls.wkb_geometry, merged)))).geom
    FROM merge_lines, TABLE.tll_lines_selected AS ls
    WHERE ls.ogc_fid != ALL(path) AND ST_Touches(ls.wkb_geometry, merged)


) 
SELECT DISTINCT (array_sort(path))[1] as first, array_length(path,1) as plen, array_sort(path) as members FROM merge_lines GROUP BY first,plen,members ORDER BY plen DESC

They key was passing the feature id back into the recursion in an array to prevent an infinite loop. The next step is to choose the longest array for a given starting ID