Sorry for the delay. This uses a small trick which I'll explain first. GeomMergeLines aggregates source lines, but leaves each source line as a separate branch. Here, if we split each line into segments, reverse each segment, then recombine, we end up with each reversed segment as a new branch. That's not what we want. Riivo used GeomNormalize to dissolve away these internal branches, but as we all know, GeomNormalize disrupts line direction. (As Klaus says, that is something we need to discuss separately.) The trick is to convert the merged line to a (multi)point, specifying <keepBranches> = FALSE to join all coordinates into one branch, then convert back to a line. The double conversion does not affect coordinate order (line direction). GeomConvertToLine( GeomConvertToPoint( GeomMergeLines([Segment reversed]), FALSE) ) AS [Branch reversed] The first query is not very good, because it uses ORDER within subqueries to reverse the order of line segments, and also to reverse the order of branches (if more than one). This is not good since in SQL9, ORDER using is not necessarily maintained from an inner query to an outer query. In particular, it is not thread safe. If an outer query uses mutliple threads, the order in which each thread takes rows from the inner query is undefined. If we limit the outer query to a single thread, then this works OK (for now). But that's not guaranteed, so this is bad design. INSERT INTO [D2 Table] ([Geom]) SELECT GeomMergeLines([Branch reversed]) -- in listed order FROM ( SELECT [mfd_id], GeomConvertToLine( GeomConvertToPoint( GeomMergeLines([Segment reversed]), -- in listed order FALSE) ) AS [Branch reversed] FROM ( SELECT [mfd_id], [Branch], GeomMakeSegment([XYNext], [XY]) AS [Segment reversed] FROM ( SELECT [mfd_id], SPLIT CALL GeomToSegments([Geom]) FROM [D1] ) ORDER BY --[mfd_id] ASC, --[Branch] DESC, [CoordInBranch] DESC ) GROUP BY [mfd_id], [Branch] THREADS 1 ORDER BY --[mfd_id] ASC, [Branch] DESC ) GROUP BY [mfd_id] THREADS 1 ; Here is a better version, performing the ORDER inside a COLLECT expression, where it is thread-safe, and passing the reversed geometry for each branch to a custom aggregate function. The function: FUNCTION JoinLines(t TABLE) GEOM -- rebuild line, combining segments or sections per branch -- in listed order -- [Branch] index -- [result] segments or sections AS ( SELECT GeomMergeLines([Joined]) -- all branches FROM (SELECT -- combine segments or sections per branch -- passing via (multi)point -- to join internal branches GeomConvertToLine( GeomConvertToPoint( GeomMergeLines([result]), FALSE) -- join internal branches ) AS [Joined] FROM t GROUP BY [Branch] ) ) END ; The query calling it: INSERT INTO [D2 Table] ([Geom]) SELECT JoinLines( (COLLECT [Branch], -- index GeomMakeSegment([XYNext], [XY]) -- [result] -- each segment reversed ORDER BY [Branch] DESC, [CoordInBranch] DESC -- for recombination ) ) FROM ( SELECT [mfd_id], SPLIT CALL GeomToSegments([Geom]) FROM [D1] ) GROUP BY [mfd_id] ;
|