SQL Server MULTIPOLYGON重叠区空洞导致STIntersects匹配标记点失败

SQL Server的GEOGRAPHY类型完全遵循OGC空间标准，MULTIPOLYGON集合内的所有多边形默认语义为互不重叠，如果子多边形存在重叠区域，该重叠区域会被判定为内部空洞，STIntersects判定时会认为空洞内的点不属于该几何对象，和业务需求冲突。

不需要手动拆分处理子多边形，通过「拆分MULTIPOLYGON为独立多边形 + 聚合合并所有多边形」两步即可自动消除重叠空洞，适配任意数量的子多边形。

IF OBJECT_ID('tempdb..#customPolygon') IS NOT NULL DROP TABLE #customPolygon
CREATE TABLE #customPolygon (geog GEOGRAPHY)

DECLARE @customPolygon  geography = 'MULTIPOLYGON (((-122.31260682058497 41.7828672412252, -122.66803538233832 41.74189213171878, -123.0157152218382 41.67409631073075, -123.35191487435746 41.58021595697532, -123.67306979164175 41.461266240272025, -123.97583066087832 41.31852640120816, -124.25710598001243 41.15352132907401, -124.51409805934621 40.96800014522892, -124.74433194261869 40.763912348675035, -124.94567706168226 40.54338210199239, -125.11636173536279 40.30868123071627, -125.25498087780294 40.06220148128715, -125.3604974831004 39.80642653665781, -125.4322385960062 39.543904229883005, -125.46988656305209 39.277219329961135, -125.4734663894492 39.008967205820475, -125.44333001234564 38.74172860785035, -125.38013825025583 38.47804574501649, -125.28484111194828 38.22039978159673, -125.1586570556347 37.971189832187214, -125.00305168962673 37.73271349727281, -124.8197163058454 37.50714895406215, -124.61054654308778 37.296538597688055, -124.37762139148631 37.102774215181654, -124.12318267532315 36.9275836675835, -123.8496150891857 36.772519052867715, -123.55942681226277 36.638946322795874, -123.25523068653888 36.52803632929597, -122.93972591543192 36.44075727956827, -122.61568021845359 36.377868583149755, -122.28591236310895 36.339916078129036, -121.9532749859139 36.327228627297, -121.62063760871888 36.339916078129036, -121.2908697533742 36.377868583149755, -120.96682405639588 36.44075727956827, -120.65131928528895 36.52803632929597, -120.34712315956506 36.638946322795874, -120.0569348826421 36.772519052867715, -119.78336729650465 36.9275836675835, -119.52892858034149 37.102774215181654, -119.29600342874002 37.29653859768806, -119.08683366598238 37.50714895406215, -118.90349828220107 37.73271349727281, -118.7478929161931 37.971189832187214, -118.62170885987955 38.22039978159673, -118.52641172157198 38.47804574501649, -118.46321995948216 38.74172860785035, -118.43308358237861 39.008967205820475, -118.43666340877571 39.277219329961135, -118.4743113758216 39.543904229883005, -118.54605248872741 39.80642653665781, -118.65156909402486 40.06220148128715, -118.79018823646503 40.30868123071627, -118.96087291014557 40.54338210199239, -119.16221802920913 40.763912348675035, -119.3924519124816 40.96800014522892, -119.6494439918154 41.15352132907401, -119.93071931094948 41.31852640120816, -120.23348018018605 41.461266240272025, -120.55463509747037 41.58021595697532, -120.89083474998958 41.67409631073075, -121.2385145894895 41.74189213171878, -121.59394315124284 41.7828672412252, -122.31260682058497 41.7828672412252)), ((-119.1795222467484 42.50521242789702, -120.70099431758788 39.07636493660857, -115.32512633395542 38.91870544294517, -119.1795222467484 42.50521242789702)))'

-- 修正1：使用STMPolyFromText解析MULTIPOLYGON类型，不要用STPolyFromText
SET @customPolygon = geography::STMPolyFromText(@customPolygon.ToString(), 4326).MakeValid()

-- 递归CTE拆分MULTIPOLYGON为独立子多边形，自动适配任意子多边形数量
;WITH PolygonSpliter AS (
    SELECT 1 AS n, @customPolygon.STGeometryN(1) AS geog
    UNION ALL
    SELECT n + 1, @customPolygon.STGeometryN(n + 1)
    FROM PolygonSpliter
    WHERE n < @customPolygon.STNumGeometries()
)
-- 聚合合并所有子多边形，自动消除重叠空洞
INSERT INTO #customPolygon(geog)
SELECT geography::UnionAggregate(geog).MakeValid()
FROM PolygonSpliter

-- 处理多边形朝向问题
UPDATE #customPolygon 
SET geog = CASE WHEN geog.EnvelopeAngle() > 90 THEN geog.ReorientObject() ELSE geog END

-- 关联逻辑保持不变
DECLARE @customPolygonJoin NVARCHAR(MAX) =  ' JOIN #customPolygon poly ON poly.geog.STIntersects(a.GeoLoc) = 1 '

• 原有代码误用STPolyFromText解析MULTIPOLYGON类型，会导致几何对象解析异常，是空洞问题的诱因之一，修正为STMPolyFromText适配多面类型。
• 递归CTE自动拆分多面为独立子多边形，不需要提前知道子多边形数量，适配任意数量的多面集合。
• UnionAggregate是SQL Server提供的空间聚合函数，可自动合并所有输入多边形的重叠区域，最终得到的几何对象没有内部空洞，符合「点只要落在任意子多边形范围内就判定为相交」的业务需求。

内容的提问来源于stack exchange，提问作者Dan Peter