How to Split a MultiPolygon Column Into the Corresponding Multiple Polygons in SQL Server

If you're looking for a way to decompose SQL Server geometry (or geography) columns containing multiple polygons (MULTIPOLYGON) into their corresponding polygons, then the following method will come for sure in handy.

First of all, we create a custom dataset (a table variable) containing our sample multipolygons:

declare
    @tv_MultiPolygonsAndPolygons
table
(
    MultiPolygon_ID int identity
    , MultiPolygon_Shape geometry
)
;

insert into
    @tv_MultiPolygonsAndPolygons (MultiPolygon_Shape)
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)
);

By querying this table variable:

select
    g.*
from
    @tv_MultiPolygonsAndPolygons g
;

we get the following 4 multipolygons, as expected, each of one with its own distinctive color:

The easiest and cleaniest way to split our multipolygons into their separate polygons is by creating a table-valued function, and then cross-apply (or outer-apply) our table variable containing the multipolygons to it. In order to do so, we make use of a recursive CTE:

create function [dbo].[ft_Geographie_SplitMultiPolygon](@pr_MultiPolygon geometry)
returns table
as
return
(
    with
        NS(Num) as
    (
        select
            Num = 1
   
        union all
   
        select
            Num = Num + 1
        from
            NS
        where
            Num < @pr_MultiPolygon.STNumGeometries()
    )
    select
        MultiPolygon_ID     =  NS.Num
        , Polygon           =  @pr_MultiPolygon.STGeometryN(NS.Num)
    from
        NS option (MaxRecursion 1000)
)

The CTE contains the STNumGeometries()function, a function returning the number of geometries that comprise a geometry instance, in our case the individual polygons.

We're now ready to test our brand new table-value splitting function:

select
    g.MultiPolygon_ID
    , Polygon_ID        =    sp.ID
    , Polygon_Shape        =    sp.Polygon
from
    @tv_MultiPolygonsAndPolygons g
    cross apply usw.ft_Geographie_SplitMultiPolygon(g.MultiPolygon_Shape) sp
order by
    MultiPolygon_ID
    , Polygon_ID

And, as expected, we're now getting a different row for each polygon: