Answered by:
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
http://social.msdn.microsoft.com/Forums/enUS/sqlspatial/thread/c2096879ebf842458f59cbe853d00069
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?
Thanks in advanceThursday, July 9, 2009 2:15 PM
Answers

Hi there,
You can put the code in the previous post into a table valued function and then crossapply 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 tablevalued 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 Marked as answer by online_mapping Friday, July 10, 2009 3:57 PM
Thursday, July 9, 2009 2:59 PMAnswerer 
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 Marked as answer by online_mapping Friday, July 10, 2009 3:54 PM
Friday, July 10, 2009 1:36 PMAnswerer
All replies

Hi there,
You can put the code in the previous post into a table valued function and then crossapply 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 tablevalued 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 Marked as answer by online_mapping Friday, July 10, 2009 3:57 PM
Thursday, July 9, 2009 2:59 PMAnswerer 
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/1430218290Friday, July 10, 2009 1:34 PMAnswerer 
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 Marked as answer by online_mapping Friday, July 10, 2009 3:54 PM
Friday, July 10, 2009 1:36 PMAnswerer 
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, thanksThursday, July 9, 2015 12:39 PM