How to fill holes in MULTIPOLYGON in SQL Server 2008 spatial CLR

Question

I have multipolygon that looks like a star with an empty hole in a middle: image

How do I transform this multipolygon into a single polygon or multipolygon that also includes the hole in a middle, e.g. fill the hole (using SQL Server 2008 CLR)?

WKT of the empty star: MULTIPOLYGON (((-75.850724788384952 39.781027647924496, -75.847514688412119 39.777165541462658, -75.835440819564724 39.781232982437771, -75.850724788384952 39.781027647924496)), ((-75.861083592601616 39.772592386436379, -75.836642464570019 39.764081172000729, -75.847514688412119 39.777165541462658, -75.861083592601616 39.772592386436379, -75.861083592601616 39.772592386436379)), ((-75.866832035574916 39.780809081927849, -75.850724788384952 39.781027647924496, -75.857585145413083 39.78927966926625, -75.866832035574916 39.780809081927849)), ((-75.8843643235475 39.764740937261692, -75.861083592601616 39.772592386436379, -75.8717486771904 39.776304058191712, -75.8843643235475 39.764740937261692)), ((-75.884021002483152 39.780573380153484, -75.8717486771904 39.776304058191712, -75.866832035574916 39.780809081927849, -75.884021002483152 39.780573380153484)))

Thank you.

Douro

Answer 1

Maybe not the fastest way, but one way that you could do it:

1. Compute a geometry that contains your input: Use STConvexHull, which returns a single polygon that contains your entire input geometry, including both empty regions inside the geometry and concavities outside the geometry (such as the regions in between the points of your star).
2. Find the empty regions: Use STDifference, subtracting the input from the convex hull, which will return polygons for those empty regions previously mentioned.
3. Determine which empty regions are outside your input: Use STBoundary to get the bounds of the convex hull. A region is inside your input geometry if and only if it does not touch the bounds of the convex hull. Use STTouches to check.
4. Compute the union of your geometry and the inner empty regions: Use STUnion.

Here is an example script:

DECLARE @g geometry;
DECLARE @hull geometry;
DECLARE @regions geometry;
DECLARE @boundary geometry;

SET @g = geometry::STGeomFromText(...);
SET @hull = @g.STConvexHull();
SET @regions = @hull.STDifference(@g);
SET @boundary = @hull.STBoundary();

WITH Geoms AS
(
    SELECT 1 i

    UNION ALL 

    SELECT i + 1
    FROM Geoms
    WHERE i < @regions.STNumGeometries()
)
SELECT @g = @g.STUnion(@regions.STGeometryN(i))
FROM Geoms
WHERE @regions.STGeometryN(i).STTouches(@boundary) = 0

SELECT @g

Tautonym

@user1181846 Absolutely - please consider accepting it as the answer if you are satisfied. – Tautonym

Great solution. Imaginative use of existing functionality and cunning CTE. – Joellejoellen