How to fill the gap in a multipolygon in SQL Server

Question

Using SQL Server 2014.

I have the following self-intersecting geometry that I make valid with .MakeValid() which gives a multipolygon.

declare @geomOrig geometry
select @geomOrig = geometry::STGeomFromText('POLYGON((705768.86 6193250.0725,705646.46 6193139.6725,705848.06 6193169.2725,705636.06 6193237.2725,705784.06 6193102.0725,705768.86 6193250.0725))', 25832).MakeValid()
select @geomOrig

The question is: How do I fill in the gap in the middle to create a single star-shaped polygon?

Answer 1

One way I know that introduces a bit of error (0.0000001 units based on projection) is shown below. It does produce the single polygon star you are looking for. I removed the decimal values in your original just so I could see how much error was introduced.

DECLARE @geomOrig GEOMETRY, @geomText VARCHAR(MAX), @geomOuterPoly GEOMETRY
SELECT @geomOrig = geometry::STGeomFromText('POLYGON((705768 6193250,705646 6193139,705848 6193169,705636 6193237,705784 6193102,705768 6193250))', 25832).MakeValid()

-- buffer the existing polygons by a small amount to join them into one polygon and get the exterior ring (LINESTRING)
SET @geomOuterPoly = @geomOrig.STBuffer(0.0000001).STExteriorRing()

-- get the WKT of the buffered polygon
SET @geomText = @geomOuterPoly.STAsText()

-- replace the word 'LINESTRING' with 'POLYGON' and add an extra parenthese
SET @geomText = REPLACE(@geomText, 'LINESTRING', 'POLYGON')
SET @geomText = REPLACE(@geomText, '(', '((')
SET @geomText = REPLACE(@geomText, ')', '))')

-- build a new geometry from the text
SET @geomOuterPoly = geometry::STGeomFromText(@geomText, 25832)

-- unbuffer the polygon to get back down to the previous shape (small amount of error introduced here)
SET @geomOuterPoly = @geomOuterPoly.STBuffer(-0.0000001)

-- select the final polygon
SELECT @geomOuterPoly