New Functions Added To The SQL Server Spatial Tools CodePlex Project

Two new functions have been added to the SQL Server Spatial Tools CodePlex Project:

• FilterArtifactsGeography
• FilterArtifactsGeometry

These new functions, as the names imply, are designed to remove unwanted components from spatial objects.  For instance, consider the output from the Reduce() method on a Geography object which results in a GeometryCollection consisting of a MultiPolygon and many Point components.  The FilterArtifactsGeography function can be used to remove the Point components and return only the MultiPolygon. 

As one would imagine, these functions also allow the removal of LineStrings and Empty geometries. Arguably, however, the most interesting feature of the new functions is their ability to remove Polygons – specifically very thin polygons (often called “slivers”).  Slivers can creep into spatial objects from a variety of ways, most commonly from union operations on polygons which do not precisely match each others adjoining edges.  The polygon filtering feature of these new functions employs a simple but very effective algorithm which allows a fair degree of control over which Polygon components are removed from the input object.  Here is the formula:

ring.STArea < ringTolerance * ring.STLength

where: ringTolerance is a value supplied by the user
ring.STLength is calculated by the function
and is the circumference of the ring

In practice, this allows very thin polygon rings (slivers) to be detected and removed while leaving more typically shaped polygon rings alone.  The presumption is, of course, that slivers are undesirable but non-sliver rings are desirable.

Here is the more formal description of the FilterArtifactsGeography() function:

SqlGeography FilterArtifactsGeography(SqlGeography geography, bool filterEmptyShapes, bool filterPoints, double lineStringTolerance, double ringTolerance)

Selectively filter unwanted shapes from input geography, with the following arguments:

• geography SQLGeography – Input geography object.
• filterEmptyShapes boolean – A value of 1 will remove all empty shapes while a value of 0 will not remove any empty shapes.
• filterPoints Boolean – A value of 1 will remove all point/multipoint shapes while a value of 0 will not remove any.
• lineSTringTolerance double – Remove all linestring/multilinestring shapes shorter than the provided tolerance. A value of 0 will not remove any linestring/multilinestring shapes.
• ringTolerance double – Remove all polygon rings thinner than provided tolerance (e.g. ring.STArea< ringTolerance * ring.STLength). A value of 0 will not remove any rings.

The FilterArtifactsGeometry function is identical in description with the substitution of geometry for geography. Both functions will always return the simplest OGC construction possible.

Here is a pseudo-code example of the FilterArtifactsGeography function in use:

FilterArtifactsGeography(@g, 1, 1, 4, 2)

Here is an example using the FilterArtifactsGeometry function:

    1: DECLARE @g GEOMETRY
    2: SET @g = GEOMETRY::STGeomFromText('GEOMETRYCOLLECTION (
    3:                                      LINESTRING (51 51, 99 99), 
    4:                                      MULTILINESTRING ((52 50, 70 50),(80 50, 100 50)),
    5:                                      POINT (50 50),
    6:                                      POINT EMPTY,
    7:                                      MULTIPOINT ((30 30),(31 31), (32 32)),
    8:                                      POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0),
    9:                                               (10 80, 20 80, 20 90, 10 90, 10 80),
   10:                                               (10 10, 90 10, 90 11, 10 11, 10 10)))'
   11:                                    ,4326)
   12: SELECT @g

Here is the resulting object after using the FilterArtifactsGeometry() function to remove empty components, Points/MultiPoints, LineStrings/MultiLineStrings and thin polygon rings:

    1: DECLARE @g GEOMETRY
    2: SET @g = GEOMETRY::STGeomFromText('GEOMETRYCOLLECTION (
    3:                                      LINESTRING (51 51, 99 99), 
    4:                                      MULTILINESTRING ((52 50, 70 50),(80 50, 100 50)),
    5:                                      POINT (50 50),
    6:                                      POINT EMPTY,
    7:                                      MULTIPOINT ((30 30),(31 31), (32 32)),
    8:                                      POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0),
    9:                                               (10 80, 20 80, 20 90, 10 90, 10 80),
   10:                                               (10 10, 90 10, 90 11, 10 11, 10 10)))'
   11:                                    ,4326)
   12: SELECT dbo.FilterArtifactsGeometry(@g,1,1,200,1.1)

POLYGON ((0 0, 100 0, 100 100, 0 100, 0 0), (10 80, 20 80, 20 90, 10 90, 10 80))

Note: The current SQL Server Spatial Tools CodePlex Project is designed for use with SQL Server 2008 (including R2). Some functions in this project will not perform correctly in Denali CTP1, including the two functions described in this post. A new version of this project will be created which will be Denali-specific.

Technorati Tags: SQL Server,spatial,CodePlex,Spatial Tools,artifacts

Comments

• Anonymous
  January 20, 2011
  Excellent sir! FilterArtifacts is a great addition to the SQL Spatial tool belt