SQL Server 2008 Geometry not working?

Question

Hi There. 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 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();

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

Answer 1

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