确定LineString是否为多边形内圆的可靠方法

I have written this basic code, which firstly extracts polygons from a multipolygon and then the particular LineStrings corresponding to inner and outer circles:

我已经编写了这个基本代码,它首先从多边形中提取多边形,然后是与内圆和外圆相对应的特定LineStrings:

DECLARE @SomeMultiPolygon GEOGRAPHY = 'MULTIPOLYGON (
((-2 -2, 2 -2, 2 2, -2 2, -2 -2), (-1 -0.5, 0 -0.5, 0 -1.5, -1 -1.5, -1 -0.5), (0 1, 1 1, 1 0, 0 0, 0 1))
, ((-4 -3, -4 -5, 0 -5, 0 -3, -4 -3)))';
SET @SomeMultiPolygon.STSrid = 4326;

DECLARE @i int = 1  
DECLARE @Results TABLE ( Id INT IDENTITY(1,1), PolygonData GEOGRAPHY )  
WHILE @i <= @SomeMultiPolygon.STNumGeometries()  
    BEGIN 
        INSERT INTO @Results VALUES (@SomeMultiPolygon.STGeometryN(@i))
        SET @i = @i + 1  
    END 

SELECT 
    Id,
    PolygonData.STGeometryType(), 
    PolygonData, 
    PolygonData.STAsText() AS PolygonWkt,
    PolygonData.NumRings() AS NumberOfRings
FROM @Results

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
    DROP TABLE #Temp

CREATE TABLE #Temp
(
    ParentId INT,
    SubPolygon GEOGRAPHY
)

DECLARE @Id INT, @InnerLoop INT, @SubPolygon GEOGRAPHY;  
SELECT @Id = MAX(Id) FROM @Results    
WHILE @Id >= 1
    BEGIN   
        SELECT @InnerLoop = PolygonData.NumRings() FROM @Results WHERE Id = @Id
        WHILE @InnerLoop > 0
            BEGIN
                SELECT @SubPolygon = PolygonData.RingN(@InnerLoop) FROM @Results WHERE Id = @Id

                INSERT INTO #Temp
                        ( ParentId, SubPolygon )
                VALUES  ( @Id, 
                          @SubPolygon
                          )

                SET @InnerLoop = @InnerLoop - 1;
            END

        SET @Id = @Id - 1                              
    END

SELECT 
    *, 
    SubPolygon.STAsText() AS SubPolygonText,
    SubPolygon.EnvelopeAngle() AS EnvelopeAngle
FROM #Temp

Am I right to assume that, if the EnvelopeAngle is less, than 1 degree the LineString corresponds to an inner circle?

我是否正确地假设,如果EnvelopeAngle小于1度,则LineString对应于内圈?

Btw, is there a set based approach to achieve the above - I am not really a fan of loops in TSql.

顺便说一下,有没有一套基于集合的方法来实现上述目标 - 我并不是TSql循环的粉丝。

PS:

This is a visualization of the above example:

这是上面例子的可视化:

1 个解决方案

#1

Answering your questions in order:

按顺序回答您的问题:

Am I right to assume that, if the EnvelopeAngle is less than 1 degree the LineString corresponds to an inner circle?

我是否正确地假设,如果EnvelopeAngle小于1度,LineString对应于一个内圈?

No. That is an artifact of your test data, but you cannot make that assertion in general. Your question comes at a good time though as I just learned about EnvelopeAngle the other day. I like to think of it as "how wide (in degrees) is this object?". If you re-scaled all of your objects either up or down, your EnvelopeAngle heuristic no longer works.

不。这是您的测试数据的工件,但一般情况下您无法做出该断言。你的问题来得很愉快,因为前几天我刚学会了EnvelopeAngle。我喜欢把它想象成“这个物体有多宽(以度为单位)?”。如果您向上或向下重新缩放所有对象,则EnvelopeAngle启发式不再有效。

But, a heuristic that I might try is for a given ID in your results table, the largest (by area) is the one that contains the others. You could play around with STDifference as well, but area seems the most straightforward.

但是,我可能尝试的启发式是针对结果表中的给定ID,最大(按区域)是包含其他ID的那个。你也可以玩STDifference,但区域似乎最直接。

[I]s there a set based approach to achieve the above? - I am not really a fan of loops in TSql.

[我]有一套基于集合的方法来实现上述目标吗? - 我真的不喜欢TSql中的循环。

Yes! Here it is:

是!这里是:

DECLARE @SomeMultiPolygon GEOGRAPHY = 'MULTIPOLYGON (
    (
        (-2 -2, 2 -2, 2 2, -2 2, -2 -2), 
        (-1 -0.5, 0 -0.5, 0 -1.5, -1 -1.5, -1 -0.5), 
        (0 1, 1 1, 1 0, 0 0, 0 1)
    )
    , (
        (-4 -3, -4 -5, 0 -5, 0 -3, -4 -3)
    )
)';
SET @SomeMultiPolygon.STSrid = 4326;

DECLARE @Results TABLE ( 
    Id INT IDENTITY(1,1), 
    PolygonData GEOGRAPHY 
);

INSERT INTO @Results
        ( [PolygonData] )
SELECT @SomeMultiPolygon.STGeometryN([n].[n])
FROM Util.dbo.[Numbers] AS [n]
WHERE n <= @SomeMultiPolygon.STNumGeometries();

SELECT 
    Id,
    PolygonData.STGeometryType(), 
    PolygonData, 
    PolygonData.STAsText() AS PolygonWkt,
    PolygonData.NumRings() AS NumberOfRings
FROM @Results;

CREATE TABLE #Temp (
    ParentId INT,
    SubPolygon GEOGRAPHY
);

INSERT INTO [#Temp]
        ( [ParentId], [SubPolygon] )
SELECT r.ID, rings.[ring]
FROM @Results AS [r]
CROSS APPLY (
   SELECT r.[PolygonData].RingN(n) AS ring
   FROM [Util].[dbo].[Numbers] AS [n]
   WHERE n <= r.[PolygonData].NumRings() 
) AS rings;

SELECT 
    *, 
    SubPolygon.STAsText() AS SubPolygonText,
    SubPolygon.EnvelopeAngle() AS EnvelopeAngle
FROM #Temp