SQL Server 2008几何结构不工作？

Question

我遇到了SQL Server2008和几何函数的问题。我正在尝试使用STDifference函数创建不重叠的形状...但它似乎并不是100%有效的。

以下是演示该问题的所有示例代码。有没有人知道如何制作这些不重叠的多边形？(除了“不要让多边形变得如此复杂”)

-- create our Geometry polygons.
    DECLARE @Geo1 Geometry, @Geo2 Geometry;
    SET @Geo1 = geometry::STGeomFromText('POLYGON ((-79725887.5234375 42951274.765625, -79699548.921875 42991827.84375, -79695546.375697568 42997990.498925969, -79695559.967852488 42998690.733179785, -79696152.0625 43029192.375, -79715247.75 43051708.75, -79802181.8984375 43020797.71875, -79806253.6640625 43018621.6953125, -79810436.03125 43014767.046875, -79825837.5 43000572.375, -79830640.625 42973672.03125, -79832936.7265625 42960812.4296875, -79725887.5234375 42951274.765625))',0);
    SET @Geo2 = geometry::STGeomFromText('POLYGON ((-79863430.6875 42937209.4375, -79850399.0625 42940068.75, -79850394.9375 42940069.625, -79845320.6875 42946082.625, -79843216.25 42948576.375, -79832949.125 42960743, -79814909.939453125 43061773.556640625, -79817218.30078125 43060262.947265625, -79823496.6875 43056154.3125, -79867800.5 43027161.5625, -79871834.50390625 43024521.70703125, -79875660.228515625 43022018.123046875, -79875666.8125 43022013.8125, -79875674.536313191 43022008.760254942, -79875676.408203125 43022007.53515625, -79875677.236328125 43022006.994140625, -79875718.458984375 43021980.017578125, -79875728.506891936 43021973.442301653, -79881723.017578125 43018050.58984375, -79882437.0625 43017583.3125, -79882585.375 43017486.25, -79884466.9375 43016254.9375, -79884550.875 43016200, -79886514.3125 43014915.125, -79887785.5 43014083.25, -79887790.4375 43014080, -79887793.125 43014078.26953125, -79887807.171875 43014069.076171875, -79887825.568359375 43014057.03515625, -79887831.322335 43014053.269705132, -79888029.5 43013923.5625, -79890094.5625 43012572.1875, -79934236.875 42983685.125, -79863430.6875 42937209.4375))',0);

    -- A quick test so you can see them
    SELECT @Geo1, 'Geo1' as MyName UNION ALL SELECT @Geo2, 'Geo2' as MyName;

    -- how do they compare initially?
    SELECT 
     @Geo1.STTouches(@Geo2) as 'Touches'
     ,@Geo1.STOverlaps(@Geo2) as 'Overlaps'
     ,@Geo1.STIntersects(@Geo2) as 'Intersects'

    -- try to make them NOT overlap or intersect
    SET @Geo1 = @Geo1.STDifference(@Geo2);
    SET @Geo2 = @Geo2.STDifference(@Geo1);

    -- Now, how do they compare?
    SELECT 
     @Geo1.STTouches(@Geo2) as 'Touches'
     ,@Geo1.STOverlaps(@Geo2) as 'Overlaps'
     ,@Geo1.STIntersects(@Geo2) as 'Intersects'

    -- what does the intersection look like?
    SELECT @Geo1.STIntersection(@Geo2).STAsText();

这是最终的选择结果：

MULTIPOLYGON (((-79831832.015625 42966999.5078125, -79830502.34765625 42974446.45703125, -79830181.430394545 42976243.79133676, -79830220.96484375 42976022.375, -79831832.015625 42966999.5078125)), ((-79832481.0538819 42963364.484146826, -79832328.75390625 42964217.45703125, -79831832.015625 42966999.5078125, -79832481.0538819 42963364.484146826)))

Answer 1

不是答案，只是观察...如果我为这两个差异引入两个新变量，我会得到不同的结果。

-- create our Geometry polygons.
    DECLARE @Geo1 Geometry, @Geo2 Geometry, @Geo3 Geometry, @Geo4 Geometry;
    SET @Geo1 = geometry::STGeomFromText('POLYGON ((-79725887.5234375 42951274.765625, -79699548.921875 42991827.84375, -79695546.375697568 42997990.498925969, -79695559.967852488 42998690.733179785, -79696152.0625 43029192.375, -79715247.75 43051708.75, -79802181.8984375 43020797.71875, -79806253.6640625 43018621.6953125, -79810436.03125 43014767.046875, -79825837.5 43000572.375, -79830640.625 42973672.03125, -79832936.7265625 42960812.4296875, -79725887.5234375 42951274.765625))',0);
    SET @Geo2 = geometry::STGeomFromText('POLYGON ((-79863430.6875 42937209.4375, -79850399.0625 42940068.75, -79850394.9375 42940069.625, -79845320.6875 42946082.625, -79843216.25 42948576.375, -79832949.125 42960743, -79814909.939453125 43061773.556640625, -79817218.30078125 43060262.947265625, -79823496.6875 43056154.3125, -79867800.5 43027161.5625, -79871834.50390625 43024521.70703125, -79875660.228515625 43022018.123046875, -79875666.8125 43022013.8125, -79875674.536313191 43022008.760254942, -79875676.408203125 43022007.53515625, -79875677.236328125 43022006.994140625, -79875718.458984375 43021980.017578125, -79875728.506891936 43021973.442301653, -79881723.017578125 43018050.58984375, -79882437.0625 43017583.3125, -79882585.375 43017486.25, -79884466.9375 43016254.9375, -79884550.875 43016200, -79886514.3125 43014915.125, -79887785.5 43014083.25, -79887790.4375 43014080, -79887793.125 43014078.26953125, -79887807.171875 43014069.076171875, -79887825.568359375 43014057.03515625, -79887831.322335 43014053.269705132, -79888029.5 43013923.5625, -79890094.5625 43012572.1875, -79934236.875 42983685.125, -79863430.6875 42937209.4375))',0);

    -- A quick test so you can see them
    SELECT @Geo1, 'Geo1' as MyName UNION ALL SELECT @Geo2, 'Geo2' as MyName;

    -- how do they compare initially?
    SELECT 
     @Geo1.STTouches(@Geo2) as 'Touches'
     ,@Geo1.STOverlaps(@Geo2) as 'Overlaps'
     ,@Geo1.STIntersects(@Geo2) as 'Intersects'

    -- try to make them NOT overlap or intersect
    SET @Geo3 = @Geo1.STDifference(@Geo2);
    SET @Geo4 = @Geo2.STDifference(@Geo1);

    -- Now, how do they compare?
    SELECT 
     @Geo3.STTouches(@Geo4) as 'Touches'
     ,@Geo3.STOverlaps(@Geo4) as 'Overlaps'
     ,@Geo3.STIntersects(@Geo4) as 'Intersects'

    -- what does the intersection look like?
    SELECT @Geo3.STIntersection(@Geo4).STAsText();

比较：

Touches     Overlaps        Intersects
1           0               1

最终选择：

MULTIPOINT ((-79830220.96484375 42976022.375), (-79831832.015625 42966999.5078125))

Answer 2

我怀疑这是由于SQL Server执行空间计算的方法/精度所致。对于STIntersection()等操作，SQL Server会将提供的浮点坐标值舍入为27位整数网格。这可能会对作为空间操作的结果返回的坐标产生轻微的扭曲。

在您的例子中，您的坐标值很大，这意味着固定大小的整数网格必须非常粗糙，以适应整个数据范围。但是，您尝试删除的相交坐标之间的相对差异非常小。捕捉到此粗略网格时，SQL Server无法正确识别交叉点区域。

请参阅此处获得解释，以及在SQL Server Denali：https://connect.microsoft.com/SQLServer/feedback/details/580254/spatial-operations-are-done-with-a-low-precision-causing-troubles-in-the-returned-data中对此情况进行了改进的建议

Answer 3

正如Joe Stefanelli所指出的，从技术上讲，您的多边形并不重叠，但它们确实相交。原因是，如果使用较小的多边形从一个多边形中切割出一个孔，则较小的多边形将不会与新多边形重叠，但会沿切割的边与其相交。它不像台锯那样沿着切割的边缘去除一些材料。我不能包含代码，因为SO在某些SQL语句上有问题，并且阻塞了post。

请参见图像中的引用A

如果您不希望它重叠或交集，您可以创建自己的锯片效果。在STDifference()函数中使用时，在较小的几何体周围应用一个非常小的缓冲区。

请参见图像中的引用B

我知道这篇文章很老了，但它似乎仍然适用于我们这些还会在2008上一段时间的人。

​

​