Splitting up your GeographyCollection's into seperate Polygons in Sql Server 2012 [Part 1 of 2]

Some spatial software doesn't like the use of a GeographyCollection. A GeographyCollection can contain mutliple geography types in the single collection. Personally, I tend to find this a bit confusing.

So to get around this, you can split your GeographyCollection up into separate polygons.

With my example, I have a large boundary from Australia. It happens to be a GeographyCollection consisting of 226 seperate Polygon's.

Image.

Sample data..

So to get this data, here's the Sql Code:

SET NOCOUNT ON

-- Create the numbers table.
DECLARE @counter INTEGER
DECLARE @numberTable TABLE(Number INTEGER PRIMARY KEY)
SELECT @counter = 1
WHILE @counter <= 10000
BEGIN
    INSERT INTO @numberTable(number) VALUES (@counter)

    SELECT @counter = @counter + 1
END


-- Now extract the data.
-- NOTE: My shape boundaries are in a table called GeographyBoundaries.
SELECT
 a.LocationId,
 a.OriginalBoundary.STGeometryN(b.number) AS Shape,
 a.OriginalBoundary.STGeometryN(b.number).ToString() AS WellKnownText
FROM GeographyBoundaries a 
    INNER JOIN @numberTable b ON b.number <= a.OriginalBoundary.STNumGeometries()
WHERE a.LocationId = 5709978

I'm sure this code can be cleaned up - but as a quick maintenance task, it serves our purposes, fine :)

Hat Tip: This idea was inspired from this blog post originally.