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

In the first part of this protip collection, I explained how to split up a GeographyCollection into multiple polygon's.

That's all fine - but why would you want to do this?

Some spatial software can't handle GeographyCollections. A good example of this is RavenDb, which leverage's the SpatialN project for all spatial calculations.

When I create RavenDb Spatial Index and it tries to do this against some Well Known Text data that is a GeographyCollection, this is the error :

NtsGeometry does not support GeometryCollection but does support its subclasses.

Checking the SpatialN source code confirms that this is the defined logic.

So what can we do?

This might not be the ideal solution, but it's a nice work around.

Split you GeographyCollection into Polygons. Grab the polygon with the large area and use that value. Throw away all the other polygons.

The idea is pretty simple and has a massive assumption: The shape is generally 1 main shape with lots of tiny 'islands' that are off it. So ditch the islands.

Not fool proof but it might be fine for most scenario's.

So how can we do this? Lets improve on our previous 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 TOP 1
 a.LocationId,
 a.OriginalBoundary.STGeometryN(b.number) AS Shape,
 a.OriginalBoundary.STGeometryN(b.number).STArea() AS Area,
 a.OriginalBoundary.STGeometryN(b.number).STArea() / a.OriginalBoundary.STArea() * 100.0 AS '% Area',
 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
ORDER BY a.OriginalBoundary.STGeometryN(b.number).STArea() DESC

The main things I've added here was :

• Ordering by STArea() DESC (order by the largest areas, first) -TOP 1 - only return the first result, in this list.
• Show me the percentage this split area is, with respect to the full shape.

So now when I run this, here is my result:

• Area: 602026064425.97
• % Area: 99.8637901016668
• WKT: POLYGON ((124.6706....

Nice!

Another idea could be to join all the Polygons into a single MultiPolygon type. That's of course, assuming all the items in the GeographyCollection are Polygon's.

For me, I'm happy with the above code, for the data I am dealing with.

HTH :)