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:

Artefacts

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:

Artefacts2

This entry was posted in Spatial, SQL Server and tagged , . Bookmark the permalink.

3 Responses to Cleaning up artefacts created by Reduce()

  1. rbrundritt says:

    Awesome. This is much cleaner than the method I use for removing unwanted artifacts. For use with online maps a good idea would be to have two geometry/geography tables in your database, one for the original shape and one for the reduced version. You can setup a trigger to run this function you created if the original shape changes. This would then save you the overhead of reducing the data on the fly when using it with your map.

  2. Charles Martyn says:

    When reducing something as complex as this I generally break it into rings and reduce them individually by a factor based on the size of the ring. You get a better outcome without the artifacts. This is nice and clean code though. I have an immediate use for it. All of my unioned zip codes are full of line artifacts that need to go.

  3. Awesome. Thank you. Worked perfectly for me!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s