locked
Weird results with STBuffer and small negative distance (SQL 2012 and SQL 2016) RRS feed

  • Question

  • Hi there,

    We've come up with some weird results with trimming a shape with STBuffer and I'm wondering if anyone has any thoughts on it.

    With 2008 we can add a buffer around a geometry and then remove it fairly cleanly with STBuffer with small negative distances (in the example below when distances are less than about 1.4). I've had almost no contact with SQL geometry myself, but the dev explained this is to remove "holes".

    In 2012 and 2016 doing the same comes up with weird corner artifacts where STBuffer doesn't seem to remove the buffer properly. The resulting geometry is an unreasonably large MULTIPOLYGON.

    I've suggested BufferWithCurves (which doesn't seem to cause issues in our example) but he's trying to maintain as similar a codebase as possible with the old one during transition.

    I'm stumped - I can't imagine that if it's a genuine bug that noone's come across it in five years, but my and the dev's searching has come up blank.

    Any suggestions or pointers in the right direction appreciated.

    DECLARE @shape geography = geography::STGeomFromText('POLYGON ((152.84337865624502 -27.589915856227474, 152.8437007374792 -27.588117629681797, 152.84169920467127 -27.587835474993007, 152.8413776375 -27.5896305, 152.84337865624502 -27.589915856227474))', 4283);
    DECLARE @positiveBufferedShape geography = @shape.STBuffer(1);
    DECLARE @negativeBufferedShape geography = @positiveBufferedShape.STBuffer(-1);
    --DECLARE @negativeBufferedShape geography = @positiveBufferedShape.BufferWithTolerance(-0.25,abs(-0.25)*0.001,0);
    
    -- Display buffered and then trimmed geometries
    select @positiveBufferedShape
    UNION ALL
    select @negativeBufferedShape
    
    SELECT @negativeBufferedShape.STAsText()

    Thanks, Robert

    Thursday, October 19, 2017 6:56 AM

All replies

  • Hi Robert,

    We are currently looking into this issue and will give you an update as soon as possible.

    Thank you for your understanding and support.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 20, 2017 7:31 AM
  • Any news on this? I ran into the same issue. My example:

    DECLARE @wktstring nvarchar(max) = 'POLYGON((-93.62851917743677 41.587229471132915,-93.62829253077501 41.58721525356966,-93.62830594182014 41.58724316510857,-93.62832697183342 41.58725702262451,-93.6283634045497 41.58727254462733,-93.62839447284796 41.58729609120918,-93.62844016845804 41.587313065652545,-93.62849720840927 41.58730191088178,-93.6285354728825 41.58729978376523,-93.62855672836304 41.58726949978762,-93.62856477499002 41.587220395274734,-93.62855672836292 41.58715323537689,-93.62851917743677 41.587229471132915))'
    DECLARE @mySpatial geography = geography::Parse(@wktstring);
    SELECT @mySpatial.STBuffer(0.5)
    UNION ALL
    SELECT @mySpatial.STBuffer(0.5).STBuffer(-0.5)
    
    SELECT @mySpatial.STBuffer(0.5).STBuffer(-0.5).STAsText()

    Interestingly, in addition to the extra polygons that are added to certain corners, other corners have spurious POINTs added nearby.

    I also have this other example that appears to be related, in that it spawns a spurious POINT when it shouldn't:

    DECLARE @wktstring nvarchar(max) = 'POLYGON ((-93.627784252166677 41.587153237477352, -93.62778425216672 41.587273257309867, -93.627828080956661 41.58727307179683, -93.627892360547719 41.587272799740951, -93.627893173914771 41.587272792179938, -93.627939578457813 41.587272132195082, -93.628050262597824 41.587270557998771, -93.628088701020189 41.587270011311425, -93.628159877616014 41.587269933519579, -93.6282705617475 41.587269812547483, -93.628381245864176 41.587269691576282, -93.62849192998145 41.587269570604313, -93.628556728363023 41.587269499787617, -93.628556728362923 41.587153235376888, -93.627784252166677 41.587153237477352))'
    DECLARE @mySpatial geography = geography::Parse(@wktstring);
    SELECT @mySpatial.STBuffer(5.0)
    UNION ALL
    SELECT @mySpatial.STBuffer(5.0).STBuffer(-5.0)
    
    SELECT @mySpatial.STBuffer(5.0).STBuffer(-5.0).STAsText()


    • Edited by mful Friday, January 5, 2018 7:41 PM
    Friday, January 5, 2018 7:39 PM