# Multipolygon results split into Polygons • ### Question

• Hi all,

I'm looking for a way of spliting the 'multipolygon' spatial results (from an STIntersection query between two polygon datasets) into individual 'polygon' results only. I've found this similar post on the forum which describes methods for splitting a single multipolygon entry into seperate polygons

However my results typically have several rows of multipolygon and polygon results. Does anyone know a way to split all of the multipolygons into seperate polygon rows?

Thursday, July 9, 2009 2:15 PM

• Hi there,

You can put the code in the previous post into a table valued function and then cross-apply that to your table that has multipolygons in it. To demonstrate, first create some sample data:
```CREATE TABLE MultiPolygonsAndPolygons (
id int IDENTITY,
geom geometry
);
GO

INSERT INTO MultiPolygonsAndPolygons (geom) VALUES
(geometry::STPolyFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0)),
(geometry::STMPolyFromText('MULTIPOLYGON(((6 20, 8 20, 8 22, 6 22, 6 20)),((3 6, 6 7, 5 9, 3 6)))', 0)),
(geometry::STPolyFromText('POLYGON((10 0, 12 0, 12 2, 10 2, 10 0))', 0)),
(geometry::STMPolyFromText('MULTIPOLYGON(((0 10, 2 10, 2 12, 0 12, 0 10)),((15 10, 25 12, 23 20, 15 20, 15 10)))', 0));
GO
```

This table contains 4 rows - two polygons and two multipolygons (each containing two polygons). They look like this (notice that the large irregular purple polygon and the small orange triangle are the same colour as the purple square and the orange square respectively, because they are part of the same multipolygon): Now create a table-valued function that splits each multipolygon as follows:
```CREATE FUNCTION dbo.SplitMultiPolygons (@MultiPolygon geometry)
RETURNS @Results TABLE (result geometry) AS
BEGIN
DECLARE @i int = 1;
WHILE @i <= @MultiPolygon.STNumGeometries()
BEGIN
INSERT INTO @Results VALUES (@MultiPolygon.STGeometryN(@i))
SET @i = @i + 1
END
RETURN
END;
GO```

You call the function like this:
```SELECT
mp.id,
p.result
FROM
MultiPolygonsAndPolygons mp
CROSS APPLY
dbo.SplitMultiPolygons (geom) AS p
GO
```

The results give one row for each polygon, together with the id of the multipolygon of which they were originally part:
id    result
1    0x0000000001040...
2    0x0000000001040...
2    0x00000000010404...
3    0x00000000010405...
4    0x00000000010405...
4    0x00000000010405...

And they look like this (notice now all different colours): Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
Thursday, July 9, 2009 2:59 PM
• At the moment, this query splits the source data set into individual polygons first, then calculates the intersection with the @intersect polygon. Seeing as the result of STIntersection() gives the simplest geometry type that is capable of representing all of the points contained in the intersection, this is perfectly possible to return a multipolygon.

If what you want to do is to ensure that, in the final output, you only have polygon instances, you need to do the intersection first, and then do the multipolygon split. It's probably easiest to demonstrate this explicitly by doing the query in two steps:

```DECLARE @intersect geometry
SET @intersect = geometry::STPolyFromText('POLYGON((-5 40, 20 40, 20 50, -5 50, -5 40))',4326)

-- Work out the intersection first
DECLARE @Intermediate TABLE (geom geometry, geomtype varchar(32));
INSERT INTO @Intermediate
SELECT
@intersect.STIntersection(geom),
@intersect.STIntersection(geom).STGeometryType()
FROM country

-- At this point, @Intermediate may contain a mixture of polygons and multipolygons, so now split it into individual rows
SELECT
@intersect.STIntersection(p.result) As data,
@intersect.STIntersection(p.result).STGeometryType() AS geometrytype
FROM @Intermediate c
CROSS APPLY
dbo.SplitMultiPolygons (c.geom) AS p
WHERE (@intersect.STIntersects(c.geom)=1)

-- Results are now all polygons```

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
Friday, July 10, 2009 1:36 PM

### All replies

• Hi there,

You can put the code in the previous post into a table valued function and then cross-apply that to your table that has multipolygons in it. To demonstrate, first create some sample data:
```CREATE TABLE MultiPolygonsAndPolygons (
id int IDENTITY,
geom geometry
);
GO

INSERT INTO MultiPolygonsAndPolygons (geom) VALUES
(geometry::STPolyFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0)),
(geometry::STMPolyFromText('MULTIPOLYGON(((6 20, 8 20, 8 22, 6 22, 6 20)),((3 6, 6 7, 5 9, 3 6)))', 0)),
(geometry::STPolyFromText('POLYGON((10 0, 12 0, 12 2, 10 2, 10 0))', 0)),
(geometry::STMPolyFromText('MULTIPOLYGON(((0 10, 2 10, 2 12, 0 12, 0 10)),((15 10, 25 12, 23 20, 15 20, 15 10)))', 0));
GO
```

This table contains 4 rows - two polygons and two multipolygons (each containing two polygons). They look like this (notice that the large irregular purple polygon and the small orange triangle are the same colour as the purple square and the orange square respectively, because they are part of the same multipolygon): Now create a table-valued function that splits each multipolygon as follows:
```CREATE FUNCTION dbo.SplitMultiPolygons (@MultiPolygon geometry)
RETURNS @Results TABLE (result geometry) AS
BEGIN
DECLARE @i int = 1;
WHILE @i <= @MultiPolygon.STNumGeometries()
BEGIN
INSERT INTO @Results VALUES (@MultiPolygon.STGeometryN(@i))
SET @i = @i + 1
END
RETURN
END;
GO```

You call the function like this:
```SELECT
mp.id,
p.result
FROM
MultiPolygonsAndPolygons mp
CROSS APPLY
dbo.SplitMultiPolygons (geom) AS p
GO
```

The results give one row for each polygon, together with the id of the multipolygon of which they were originally part:
id    result
1    0x0000000001040...
2    0x0000000001040...
2    0x00000000010404...
3    0x00000000010405...
4    0x00000000010405...
4    0x00000000010405...

And they look like this (notice now all different colours): Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
Thursday, July 9, 2009 2:59 PM
• Thanks v.much for the help and quick reply to my question!

Now im trying to understand how to use the geometry returned from this function in combination with other spatial options. To test this, i am trying to write a query to return the intersection between one polygon dataset (world country borders) with another polygon shape. I want to return single polygons for areas where the two areas overlap

I've set up the dbo.SplitMultiPolygons function (as above but as a geography rather than geometry type), and my SQL looks like this ...

DECLARE @intersect geography
SET
@intersect = geography::STPolyFromText('POLYGON((-5 40, 20 40, 20 50, -5 50, -5 40))',4326
)
SELECT c.name AS country
,
@intersect.STIntersection(p.result) As data
,
@intersect.STIntersection(p.result).STGeometryType() As shapetype
FROM countries c
CROSS
APPLY
dbo.SplitMultiPolygons (c.geom) AS p
WHERE (@intersect.STIntersects(c.geom)=1
)

The result seems to be splitting most of the multipolygons into single polygons as i wanted, but there are still several multipolygons left over, as well as several empty Geometrycollection items. I think i'm placing the 'p.result' in the wrong part of the SQL but im not sure how to correct this. Are you able to suggest improvements to this query?

Thanks!

Friday, July 10, 2009 12:12 PM
• At the moment, this query splits the source data set into individual polygons first, then calculates the intersection with the @intersect polygon. Seeing as the result of STIntersection() gives the simplest geometry type that is capable of representing all of the points contained in the intersection, this is perfectly possible to return a multipolygon.

If what you want to do is to ensure that, in the final output, you only have polygon instances, you need to do the intersection first, and then do the multipolygon split. It's probably easiest to demonstrate this explicitly by doing the query in two steps:

```DECLARE @intersect geometry
SET @intersect = geometry::STPolyFromText('POLYGON((-5 40, 20 40, 20 50, -5 50, -5 40))',4326)

-- Work out the intersection first
DECLARE @Intermediate TABLE (geom geometry, geomtype varchar(32));
INSERT INTO @Intermediate
SELECT
@intersect.STIntersection(geom),
@intersect.STIntersection(geom).STGeometryType()
FROM country

-- At this point, @Intermediate may contain a mixture of polygons and multipolygons, so now split it into individual rows
SELECT
@intersect.STIntersection(p.result) As data,
@intersect.STIntersection(p.result).STGeometryType() AS geometrytype
FROM @Intermediate c
CROSS APPLY
dbo.SplitMultiPolygons (c.geom) AS p
WHERE (@intersect.STIntersects(c.geom)=1)

-- Results are now all polygons```

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
Friday, July 10, 2009 1:34 PM
• At the moment, this query splits the source data set into individual polygons first, then calculates the intersection with the @intersect polygon. Seeing as the result of STIntersection() gives the simplest geometry type that is capable of representing all of the points contained in the intersection, this is perfectly possible to return a multipolygon.

If what you want to do is to ensure that, in the final output, you only have polygon instances, you need to do the intersection first, and then do the multipolygon split. It's probably easiest to demonstrate this explicitly by doing the query in two steps:

```DECLARE @intersect geometry
SET @intersect = geometry::STPolyFromText('POLYGON((-5 40, 20 40, 20 50, -5 50, -5 40))',4326)

-- Work out the intersection first
DECLARE @Intermediate TABLE (geom geometry, geomtype varchar(32));
INSERT INTO @Intermediate
SELECT
@intersect.STIntersection(geom),
@intersect.STIntersection(geom).STGeometryType()
FROM country

-- At this point, @Intermediate may contain a mixture of polygons and multipolygons, so now split it into individual rows
SELECT
@intersect.STIntersection(p.result) As data,
@intersect.STIntersection(p.result).STGeometryType() AS geometrytype
FROM @Intermediate c
CROSS APPLY
dbo.SplitMultiPolygons (c.geom) AS p
WHERE (@intersect.STIntersects(c.geom)=1)

-- Results are now all polygons```

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
Friday, July 10, 2009 1:36 PM
• Thanks Tanoshimi, its working perfectly now!
Friday, July 10, 2009 4:08 PM
• Brilliant answer by Tanoshimi, saved me lots of time and made my app awesome, thanks
Thursday, July 9, 2015 12:39 PM