SQL Server 2008 Geometry not working?_问答_开发者

I'm having an issue with SQL Server 2008 and the geometry functions. I'm trying to create non-overlapping shapes using the STDifference function... but it does not seem to be working 100% of the time.

Here is all the sample code to demonstrate the issue. Anybody have any idea to make these non-overlapping polygons? (except "don't make your polygons so complex")

-- 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 430开发者_JAVA技巧21980.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();

This is the final select result:

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

Not an answer, just an observation... I get different results if I introduce two new variables for the two differences.

-- 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();

Compare:

Touches     Overlaps        Intersects
1           0               1

Final Select:

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

I suspect this is due to the method/precision with which SQL Server performs spatial calculations. For operations like STIntersection() etc, SQL Server rounds the supplied float coordinate values to a 27 bit integer grid. This can introduce slight distortions to the coordinates returned as the result of spatial operations.

In your case, your coordinate values are large, which means that the fixed size integer grid must be made quite coarse to accommodate the full range of data. The relative difference between the intersecting coordinates that you're trying to remove, however, is very small. When snapped to this coarse grid, SQL Server is unable to correctly identify the area of intersection.

See here for a explanation, and also a suggestion that this situation has been improved in 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

As pointed out by Joe Stefanelli, your polygons don't technically overlap but they do intersect. The reason is that if you cut a hole out of one polygon using a smaller polygon, the smaller polygon will not overlap the new polygon but it will intersect it along the edge of the cut. It isn't like a table saw that removes some material along the edge of the cut. I could not include the code because SO is having a problem with certain SQL statements and blocks the post.

SEE REFERENCE A in image

If you would like it not to overlap OR intersect, you can create your own sawblade effect. Apply a very small buffer around the smaller of the geometries when using it in the STDifference() function.

SEE REFERENCE B in image

I know this post is old but it does seem pertinent still for those of us who will still be on 2008 for a while.

SQL Server 2008 Geometry not working?