none
Separate a sqlgeometry(POLYGON) ,sometimes failed!

    Question

  • I want to separate a Polygon by a thin polygon,but when the thin polygon is too thin, fail to separate the big polygon.

    why?

    example:

    --right examply: return multiPolygon,  separate right

    select geometry::STGeomFromText('POLYGON ((0 0, 0 10000000, 10000000 10000000, 10000000 0,0 0))', 0).STDifference(
    geometry::STGeomFromText('POLYGON ((0 0, 0 1, 10000000 10000000, 10000001 10000000,0 0))', 0)
    ).ToString()

    --separate error
    select geometry::STGeomFromText('POLYGON ((0 0, 0 10000000, 10000000 10000000, 10000000 0,0 0))', 0).STDifference(
    geometry::STGeomFromText('LINESTRING  (-1 -1,1000000 11001000)', 0).STBuffer(0.02)
    ).ToString()

    --return Polygon but not multiPolygon.

    -- the thin polygon

    select geometry::STGeomFromText('LINESTRING  (-1 -1,1000000 11001000)', 0).STBuffer(0.02).ToString()


    look:
    select geometry::STGeomFromText('POLYGON ((0 0, 0 10000000, 10000000 10000000, 10000000 0,0 0))', 0)
    union all
    select geometry::STGeomFromText('LINESTRING  (-1 -1,1000000 11001000)', 0).STBuffer(0.02)


    (for authority,there is no image)


    • Edited by Shawn Meng Tuesday, July 16, 2013 5:31 AM
    Tuesday, July 16, 2013 3:22 AM

Answers

  • Hello,

    Yes, the issue may be caused by the precision. I got the result above in SQL Server 2012.  I can reproduce the issue in SQL Server 2008 and SQL Server 2008 R2. 

    SELECT   @Polygon.STDifference(@Linestring.STBuffer(0.2)).STAsText() 

    Result:
    MULTIPOLYGON (((0 12.751237498596311, 909007.939254778 10000000, 0 10000000, 0 12.751237498596311)), ((0 0, 10000000 0, 10000000 10000000, 909008.43925477751 10000000, 0 7.2507424987852573, 0 0)))

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click  here.


    Fanny Liu
    TechNet Community Support


    Tuesday, July 23, 2013 5:02 AM
    Moderator

All replies

  • Hi Shawn,

    I test the query based on the data your post, however, I can get the exact result:

    DECLARE @Polygon geometry  
    SET @Polygon = geometry::STPolyFromText('POLYGON((0 0, 0 10000000, 10000000 10000000, 10000000 0,0 0))', 0)  
    DECLARE @Linestring geometry  
    SET @Linestring = geometry::STLineFromText('LINESTRING(-1 -1,1000000 11001000)', 0)  
    SELECT   
    @Polygon.STDifference(@Linestring.STBuffer(0.02)).STAsText() 

    Result:
    MULTIPOLYGON (((3.5390257835388184E-08 10.221916957758367, 909008.169172362 10000000, 0 10000000, 3.5390257835388184E-08 10.221916957758367)), ((0 0, 10000000 0, 10000000 10000000, 909008.20933728712 10000000, 3.5390257835388184E-08 9.7800629865378141, 0 0)))

    If I have any misunderstanding, please let me know.

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click  here.


    Fanny Liu
    TechNet Community Support


    Monday, July 22, 2013 9:07 AM
    Moderator
  • Hi,Fanny

    My reluslt is:POLYGON ((0 0, 10000000 0, 10000000 10000000, 909008.18925477751 10000000, 0 10000000, 0 10.000989999622107, 0 0))

    The  result is different,maybe the platform's problem,

    i used it on the  SQL SERVER 2008,

    And the STDifference's result is not my wanted,the reason is the precision,

    The solution is that make the STBuffer() 's parameter to bigger.

    Tuesday, July 23, 2013 1:51 AM
  • Hello,

    Yes, the issue may be caused by the precision. I got the result above in SQL Server 2012.  I can reproduce the issue in SQL Server 2008 and SQL Server 2008 R2. 

    SELECT   @Polygon.STDifference(@Linestring.STBuffer(0.2)).STAsText() 

    Result:
    MULTIPOLYGON (((0 12.751237498596311, 909007.939254778 10000000, 0 10000000, 0 12.751237498596311)), ((0 0, 10000000 0, 10000000 10000000, 909008.43925477751 10000000, 0 7.2507424987852573, 0 0)))

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click  here.


    Fanny Liu
    TechNet Community Support


    Tuesday, July 23, 2013 5:02 AM
    Moderator