locked
MultiPolygon to Polygons RRS feed

  • Question

  • Hi All,

    Can anyone please let me know how to split a multipolygon into individual polygons.

    MULTIPOLYGON (((10 2.8573061398097446, 10 10, 2.8573293685913086 10, 6.000053882598877 8.000084400177002, 6.0000572204589844 8.0000820159912109, 6.00006103515625 8.00007963180542, 6.0000643730163574 8.00007677078247, 6.0000672340393066 8.0000739097595215, 6.0000705718994141 8.0000710487365723, 6.0000734329223633 8.000068187713623, 6.0000762939453125 8.0000648498535156, 6.0000791549682617 8.0000615119934082, 10 2.8573061398097446)), ((0 0, 10 0, 10 2.8569796717889782, 5.9999313354492188 7.9999251365661621, 2.8569564916626993 10, 0 10, 0 0))) 

    I need to split this multipolygon to individual polygons.


    Regards,
    Ravi.
    Tuesday, January 20, 2009 9:43 AM

Answers

  •  Try this:

    DECLARE @MultiPolygon geometry  
    SET @MultiPolygon = geometry::STMPolyFromText('
    MULTIPOLYGON (((10 2.8573061398097446, 10 10, 2.8573293685913086 10, 6.000053882598877 8.000084400177002, 6.0000572204589844 8.0000820159912109, 6.00006103515625 8.00007963180542, 6.0000643730163574 8.00007677078247, 6.0000672340393066 8.0000739097595215, 6.0000705718994141 8.0000710487365723, 6.0000734329223633 8.000068187713623, 6.0000762939453125 8.0000648498535156, 6.0000791549682617 8.0000615119934082, 10 2.8573061398097446)), ((0 0, 10 0, 10 2.8569796717889782, 5.9999313354492188 7.9999251365661621, 2.8569564916626993 10, 0 10, 0 0)))  
    ', 0)  
     
    DECLARE @i int = 1  
    DECLARE @Results TABLE ( result geometry )  
    WHILE @i <= @MultiPolygon.STNumGeometries()  
    BEGIN 
    INSERT INTO @Results VALUES (@MultiPolygon.STGeometryN(@i))  
    SET @i = @i + 1  
    END 
     
    SELECT * FROM @Results 
    Tuesday, January 20, 2009 9:54 AM
    Answerer

All replies

  •  Try this:

    DECLARE @MultiPolygon geometry  
    SET @MultiPolygon = geometry::STMPolyFromText('
    MULTIPOLYGON (((10 2.8573061398097446, 10 10, 2.8573293685913086 10, 6.000053882598877 8.000084400177002, 6.0000572204589844 8.0000820159912109, 6.00006103515625 8.00007963180542, 6.0000643730163574 8.00007677078247, 6.0000672340393066 8.0000739097595215, 6.0000705718994141 8.0000710487365723, 6.0000734329223633 8.000068187713623, 6.0000762939453125 8.0000648498535156, 6.0000791549682617 8.0000615119934082, 10 2.8573061398097446)), ((0 0, 10 0, 10 2.8569796717889782, 5.9999313354492188 7.9999251365661621, 2.8569564916626993 10, 0 10, 0 0)))  
    ', 0)  
     
    DECLARE @i int = 1  
    DECLARE @Results TABLE ( result geometry )  
    WHILE @i <= @MultiPolygon.STNumGeometries()  
    BEGIN 
    INSERT INTO @Results VALUES (@MultiPolygon.STGeometryN(@i))  
    SET @i = @i + 1  
    END 
     
    SELECT * FROM @Results 
    Tuesday, January 20, 2009 9:54 AM
    Answerer
  • Hi Ravi,

    Two more methods:

    1. If you have a numbers table:

      declare @g geography = ... 
       
      select @g.STGeometryN(numbers.n) 
      from numbers 
      where n >= 1 and n <= @g.STNumGeometries() 

    2. Use the builder/sink API.  This should be a pretty straightforward sink/TVF.

    Cheers,
    -Isaac

    Isaac Kunen, Microsoft SQL Server
    Tuesday, January 20, 2009 3:39 PM
  • Tanoshimi,

    Thank you for your scipt on converting from a Multipolygon to a polygon!  Do you have a script for converting from a results table of polygons to a multipolygon for for comparing a set a polygons to another set of polygons?

    Thursday, April 23, 2009 5:40 PM
  • Hi Rebecca,

    Let's see now.... first let's insert some polygons into a table:
    DECLARE @GeometryTable TABLE (
      geom geometry
    )
    
    INSERT INTO @GeometryTable VALUES 
    (geometry::STPolyFromText('POLYGON((0 0, 2 0, 1 1, 0 0))', 0)),
    (geometry::STPolyFromText('POLYGON((10 12, 12 5, 6 6, 10 12))', 0)),
    (geometry::STPolyFromText('POLYGON((0 -5, -2 -5, -10 1, 0 -5))', 0)),
    (geometry::STPolyFromText('POLYGON((0 5, 7 10, 1 8, 0 5))', 0))
    
    SELECT geom.STAsText() FROM @GeometryTable
    This gives:
    -------------
    POLYGON ((0 0, 2 0, 1 1, 0 0))
    POLYGON ((10 12, 12 5, 6 6, 10 12))
    POLYGON ((0 -5, -2 -5, -10 1, 0 -5))
    POLYGON ((0 5, 7 10, 1 8, 0 5))



    Now use a cursor to retrieve each row individually and STUnion() them together into a single MultiPolygon:
    DECLARE @Polygon geometry
    DECLARE @MultiPolygon geometry = geometry::STGeomFromText('GEOMETRYCOLLECTION EMPTY', 0)
    DECLARE GeomCursor CURSOR FOR SELECT geom FROM @GeometryTable
    OPEN GeomCursor
    FETCH NEXT FROM GeomCursor INTO @Polygon
    WHILE @@FETCH_STATUS = 0
    BEGIN
      SET @MultiPolygon = @MultiPolygon.STUnion(@Polygon)
      FETCH NEXT FROM GeomCursor INTO @Polygon
    END
    CLOSE GeomCursor
    DEALLOCATE GeomCursor
    
    SELECT @MultiPolygon.STAsText()
    which leads to:
    --------------
    MULTIPOLYGON (((12 5, 10 12, 6 6, 12 5)), ((0 5, 7 10, 1 8, 0 5)), ((0 0, 2 0, 1 1, 0 0)), ((-2 -5, 0 -5, -10 1, -2 -5)))

    Is that what you wanted? (from p321 of "Beginning Spatial with SQL Server 2008", by the way!)


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, April 23, 2009 6:13 PM
    Answerer
  • Hi Rebecca,

    Let's see now.... first let's insert some polygons into a table:
    DECLARE @GeometryTable TABLE ( geom geometry)
    INSERT INTO @GeometryTable VALUES (geometry::STPolyFromText('POLYGON((0 0, 2 0, 1 1, 0 0))', 0)),(geometry::STPolyFromText('POLYGON((10 12, 12 5, 6 6, 10 12))', 0)),(geometry::STPolyFromText('POLYGON((0 -5, -2 -5, -10 1, 0 -5))', 0)),(geometry::STPolyFromText('POLYGON((0 5, 7 10, 1 8, 0 5))', 0))
    SELECT geom.STAsText() FROM @GeometryTable
    This gives:
    -------------
    POLYGON ((0 0, 2 0, 1 1, 0 0))
    POLYGON ((10 12, 12 5, 6 6, 10 12))
    POLYGON ((0 -5, -2 -5, -10 1, 0 -5))
    POLYGON ((0 5, 7 10, 1 8, 0 5))



    Now use a cursor to retrieve each row individually and STUnion() them together into a single MultiPolygon:
    DECLARE @Polygon geometryDECLARE @MultiPolygon geometry = geometry::STGeomFromText('GEOMETRYCOLLECTION EMPTY', 0)DECLARE GeomCursor CURSOR FOR SELECT geom FROM @GeometryTableOPEN GeomCursorFETCH NEXT FROM GeomCursor INTO @PolygonWHILE @@FETCH_STATUS = 0BEGIN SET @MultiPolygon = @MultiPolygon.STUnion(@Polygon) FETCH NEXT FROM GeomCursor INTO @PolygonENDCLOSE GeomCursorDEALLOCATE GeomCursor
    SELECT @MultiPolygon.STAsText()
    which leads to:
    --------------
    MULTIPOLYGON (((12 5, 10 12, 6 6, 12 5)), ((0 5, 7 10, 1 8, 0 5)), ((0 0, 2 0, 1 1, 0 0)), ((-2 -5, 0 -5, -10 1, -2 -5)))

    Is that what you wanted? (from p321 of "Beginning Spatial with SQL Server 2008", by the way!)


    Beginning Spatial with SQL Serverhttp://www.apress.com/book/view/1430218290

    I'm new to this, Now I understand more about it.
    Saturday, February 19, 2011 10:53 PM