Try this version. It compares the direction of the middle segment of each line in [Network] (rounding towards the start) with the directions of overlaid lines in [Paths]. (The first version does the same, but for every segment of each line in [Network], aggregating the results. So if you hit a strangely formed line, with reversed segments, then the first version would be more robust, returning 2 for mixed directions.) UPDATE (SELECT [E].[ID], [E].[Direction], [V].[Path directions] FROM [Network] AS [E] LEFT JOIN (SELECT [ID], -- COUNT(DISTINCT [Segment direction]) AS [Check], CASE WHEN MAX([Segment direction]) IS NULL THEN -1 -- no match WHEN MIN([Segment direction]) = -2 THEN -2 -- some segment(s) with unknown direction WHEN COUNT(DISTINCT [Segment direction]) = 1 THEN FIRST([Segment direction]) -- the same WHEN COUNT(DISTINCT [Segment direction]) = 2 THEN 2 -- mixed ELSE -3 -- should not arise END AS [Path directions] FROM (SELECT [T].[ID], CASE WHEN Coord([Network segment], 0) = Coord([Path segment], 0) THEN 1 -- same WHEN Coord([Network segment], 0) = Coord([Path segment], CoordCount([Path segment]) - 1) THEN 0 -- opposite ELSE -2 -- unknown END AS [Segment direction] FROM (SELECT [ID], NewLine( Coord([ID], Floor(CoordCount([ID]) / 2) - 1), -- since one-based Coord([ID], Floor(CoordCount([ID]) / 2)) ) AS [Network segment] FROM [Network] ) AS [T] LEFT JOIN [Paths] AS [D] ON Touches([D].[ID], [T].[Network segment]) -- with e SPLIT BY Branches(IntersectLine([D].[ID], [T].[Network segment])) AS [Path segment] -- IntersectLine() does not trigger normalization (or reversal) LEAVING Touches([Path segment], Centroid([Network segment])) -- The section(s) of path within the network segment -- (more than one if the same path reverses on itself) ) AS [U] GROUP BY [ID] ) AS [V] ON [E].[ID] = [V].[ID] ) SET [Direction] = [Path directions] ; If you get any -2 or -3 results, please tell me. (If you get any -1 results, check you data.) Attachments: Path directions within line b (sample).txt
|