Cleaning up artefacts created by Reduce()

The Reduce() method simplifies geography or geometry instances by removing vertices. Depending on the situation, this can sometimes cause a Polygon instance to collapse in on itself, degenerating into a LineString, or even a single Point.

For example, in the following code listing I’ve simplified a MultiPolygon instance of the United Kingdom using the Reduce() methods with a tolerance of 2km:

SELECT
@UK.Reduce(2000) AS Shape;

This causes many of the Scottish islands and some other parts of coastline to degenerate into singular Point instances, which are shown as black specks in the SSMS spatial results tab, as follows:

Instead of being a MultiPolygon instance, the shape is now a GeometryCollection containing a variety of geometry types, but this is often not what you really want.

Assuming that what you really wanted was only those simplified Polygons returned by Reduce(), you can use the following T-SQL function to loop through a geometry and removes any Point or LineString elements, returning only the union of all polygonal elements (i.e. those elements where STDimension() = 2):

CREATE FUNCTION RemoveArtefacts(@g geography) RETURNS geography AS BEGIN
  DECLARE @h geography = geography::STGeomFromText('POINT EMPTY', @g.STSrid);
  DECLARE @i int = 1;
  WHILE @i <= @g.STNumGeometries() BEGIN
    IF(@g.STGeometryN(@i).STDimension() = 2) BEGIN
      SELECT @h = @h.STUnion(@g.STGeometryN(@i));
    END
    SET @i = @i + 1;
  END
  RETURN @h;
END;

Usage as follows:

SELECT
dbo.RemoveArtefacts(@UK.Reduce(2000)) AS Shape;

The map is now simplified as before, but does not contain the unwanted degenerate artifacts created as a result of vertex removal: