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?