I've tidied the query up a bit to make it easier to reuse. Add lists of Data and Overlay attributes where indicated (see placeholders a, b, c and x, y, z). OPTIONS CoordSys("Data" AS COMPONENT), Precision("Data" AS COMPONENT) ; SELECT [ID], -- Data attributes -- [a], [b], [c], ... -- Overlay attributes -- [x], [y], [z], ... -- [op], -- For checking: for each ID -- there may be many parts of type "intersection", -- but only one for type "exclusion" [Part] FROM (SELECT [ID], -- Data attributes -- [a], [b], [c], ... -- Overlay attributes -- [x], [y], [z], ... [op], CASE [op] WHEN "intersection" THEN CASE WHEN [Mask A] IS NULL THEN NULL -- no intersection between this data area and any overlay mask ELSE ClipIntersect([ID], [Mask A]) -- with epsilon END WHEN "exclusion" THEN CASE WHEN MAX([Mask B]) IS NULL THEN Geom([ID]) -- no intersection between this data area and any overlay mask ELSE ClipSubtract([ID], UnionAll([Mask B])) -- with epsilon -- NULL if this data area is completely covered by overlay masks END END AS [Part] FROM (SELECT [D].[ID], --[D].[Geom (I)]), -- Data attributes a, b, c,... -- [D].[a], [D].[b], [D].[c], ... [P].[op], -- Overlay attributes x, y, z,... -- filtered by operation type -- (included for intersection, -- for exclusion replaced by NULL, to be ignored in grouping) -- IIf([P].[op] = "intersection", [O].[x], NULL) AS [x], -- IIf([P].[op] = "intersection", [O].[y], NULL) AS [y], -- IIf([P].[op] = "intersection", [O].[z], NULL) AS [z], -- ... -- First reference to this overlay mask, for the intersection operation -- Used individually, for ClipIntersect -- For exclusion substitute NULL (ignored in grouping) CASE [P].[op] WHEN "intersection" THEN [O].[ID] --[Geom (I)] WHEN "exclusion" THEN NULL END AS [Mask A], -- Second reference to this overlay mask, for the exclusion operation -- Used after grouping, for ClipSubtract -- For intersection substitute NULL (ignored in grouping) CASE [P].[op] WHEN "exclusion" THEN [O].[ID] --[Geom (I)] WHEN "intersection" THEN NULL END AS [Mask B] FROM [Data] AS [D] LEFT JOIN [Overlay] AS [O] ON Touches([D].[ID], [O].[ID]) -- with epsilon AND NOT Adjacent([D].[ID], [O].[ID]) -- with epsilon CROSS JOIN (VALUES ("intersection"), ("exclusion") NAMES ([op]) ) AS [P] ) GROUP BY [ID], -- Data attributes -- [a], [b], [c], ... [op], -- Overlay attributes -- [x], [y], [z], ... [Mask A] -- Group by Mask A to give a separate row for each intersection -- Not grouping by Mask B because we combine these copies of the masks for exclusion ) AS [T] WHERE [Part] IS NOT NULL ; Attachments: Identity template.txt
|