# MultiPolygon to Polygons • ### 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

• 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

### 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
• 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()```
--------------
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
• 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()```