时间戳

首页 > 解决方案 > SQL Multipolygon void 重叠的地方

sql - SQL Multipolygon void 重叠的地方

问题描述

我有一个多边形 WKT 并将其变成一个地理，并呈现有两个多边形，但它们重叠的地方有一个空白。

我正在尝试查找字符串中任何多边形中存在的标记（经纬度相交）。问题是，创建的这个空白没有被交付给我需要时存在的标记。

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)))'


    INSERT INTO #customPolygon
            SELECT geography::STPolyFromText(@customPolygon, 4326).MakeValid().ToString()
            UPDATE #customPolygon SET geog=case when geog.EnvelopeAngle() > 90 then geog.ReorientObject() else geog end
            SET @customPolygonJoin =  ' join #customPolygon poly on poly.geog.STIntersects(a.GeoLoc) = 1 '][1]][1]

我想避免在多面体字符串中逐项列出多边形，因为可能有无限数量。

我已经尝试过 ST 联合和 ST 差异，但似乎那些需要两个地理输入，其中 multipolgyon 仅为 1。

TYIA

在此处输入图像描述

标签： sqlgeospatial

解决方案

I think this "hole" is artifact of your drawing tool. Within SQL Server, it is two overlapping polygons - so there is no hole, it is instead covered by both of the polygons.

But if you want to dissolve this into a single polygon, I would try to StUnion it with empty geography (geography::STGeomFromText('POLYGON EMPTY', 4326)) or with itself.

推荐阅读