Multies to singleparts in mssql

This writeup serves to purposes:

it’s the umpteenth time I need to look up how to dump a multipart geometry to singleparts in MS SQLServer, because you know, memory and the verbosity needed to do it …
and given a polygon’s outer boundary as a closed LineString how are you supposed to turn it back into a polygon.

Dumping a multipart

As a reference, in PostGIS you can achieve this by

select
    id, (st_dump(geom)).*
from
    foo.bar
;

st_dump in PostGIS is a set-returning function and this last query will return records in the form of path, geom.

In SQLServer there is no built-in way but this can be achieved by generating a set of integers corresponding to the number of component geometries and then pulling the respective geometry. So the above query would be something in the line of:

select
    id, n.n, geom.STGeometryN(n.n) as geom
from
    foo.bar
        join (
            select
                row_number() over (order by object_id) as n
            from
                sys.all_objects
        ) n on
            n.n <= geom.STNumGeometries()
;

Build a polygon from a closed LineString

Now this is a bit tricky and I’ve not really dug into the implications on this but at least it seemed to work for my current use case. Again, in PostGIS you’d dump your multipart polygons to singleparts, get the exterior ring with st_exteriorring of the polygon and then call st_buildarea to get the polygon.

select
    id, st_buildarea(st_exteriorring((st_dump(geom)).geom))
from
    foo.bar
;

I ended up finding a solution to this in this StackOverflow question with carefully hand-crafting WKB in a select statement. Meaning:

select
    id,
    geometry::STGeomFromWKB(
        0x01 + 0x03000000 + 0x01000000 +
        substring(
            geom.STAsBinary(), 6, datalength(geom.STAsBinary())
        ), geom.STSrid
    ) as geom
from (
    select
        id, geom.STGeometryN(n.n).STExteriorRing() as geom
    from
        foo.bar
            join (
                select
                    row_number() over (order by object_id) as n
                from
                    sys.all_objects
            ) n on
                n.n <= geom.STNumGeometries()
) f
;

So, yes …