MultiPolygon to Polygons

_{Tuesday, January 20, 2009 9:43 AM}

Hi All,

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

I need to split this multipolygon to individual polygons.

Regards,
Ravi.

_{Tuesday, January 20, 2009 9:54 AM ✅Answered | 1 vote}

 Try this:

_{Tuesday, January 20, 2009 3:39 PM | 1 vote}

Hi Ravi,

Two more methods:

1. If you have a numbers table:

   Expand 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

_{Thursday, April 23, 2009 5:40 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 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 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

_{Saturday, February 19, 2011 10:53 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()

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.