Cleaning Geometry for SQL Server with the GeometryValidator

Introduction

SQL Server has specific restrictions on the geometry that can be stored in the database. If the geometry does not meet SQL Server standards, an error is likely to occur. SQL Server 2012 and onward will accept invalid geometry for validation later. For this reason, it is sometimes necessary and easier to clean and repair the geometry before loading it into the SQL Server database.

Supported Geometry

The supported geometry for Microsoft SQL Server Spatial readers and writers are listed in FME's Quick Facts documentation.

Microsoft SQL Server follows OGC Common Architecture specifications. An overview of the spatial data types SQL Server supports is provided here. Not all geometries are accepted by the database: certain geometric instances are invalid and are listed in the IsValidDetailed() method.

Handling Geometry

Validating geometry is important to ensure successful translations and successful transfers into the database. Loading invalid geometry into a database may cause unexpected results that require attention.

Invalid Geometry

SQL Server 2012 and forward will load invalid geometry and provide the user the ability to validate and repair the geometry later. Therefore, FME will not fail the translation. Instead it will write WARN messages in the log that the user should be aware of in case of invalid geometry.

Users can check the validity of their data using the STIsValid() method documented for both Geometry (planar units) and Geography (ellipsoidal units) in addition to the IsValidDetailed() method. For additional information about using these methods, please see FME is Loading Features with Invalid Geometries.

Alternatively, users can opt to use the GeometryValidator and fix the geometry prior to database loading.

The GeometryValidator

The GeometryValidator can be used to check and repair features that have invalid geometry. There are various issues a user can select such a OGC standards or Basic Geometry Integrity.

If the dataset being read into the database is likely to have such issues, the transformer should be used to check for any features that may cause errors or produce unwanted results.

It is worth noting that SQL Server allows for non-simple geometry. Should a feature fail a OGC Simple test, SQL Server may still accept the feature.

For example, a non-simple LineString (self intersecting) will be accepted by SQL Server yet fail a OGC Simple test. A MultiLineString with edges that overlap will fail a OGC Simple test and fail to load into the database since the lines do not intersect but instead overlap for an interval (IsValidDetailed() return value of 24413).

Example Workspace

In the example workspace, the Reader has a single feature with invalid geometry. There are two data flows demonstrating the need and the process to fix invalid geometry.

The input feature has invalid geometry: the explicit end point locations are too far from the parameter-defined arc and there is a lone point at the end of the linestring.

1. Writing with Invalid Geometry

Enable the first bookmark and run the workspace. The Reader's single feature is successfully written to the InvalidGeometry Microsoft SQL Server Spatial writer.

However warnings are logged, starting with the WARN message signifying invalid geometry: Failed to get the SQL Server native binary form for column "GEOM".

Inspect the writer using the Data Inspector: there is no geometry. Opening the the table in Microsoft SQL Server Management Studio results in a null table.

2. Use the GeometryValidator

The GeometryValidator in this example workspace has been set to detect Basic Geometry Integrity issues. By using this detection method, features with corrupt or badly formed geometry will be be flagged.

Enable the second bookmark and disable the first, now FME writes to the database with zero warnings. Inspect the data, you can now see the repaired geometry of the feature.

Note: Both passed and repaired ports are connected to the Writer. Alternatively, you could store repaired features in a different output than the passed features.