Files
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 is listed in FME's Quick Facts documentation.
Microsoft SQL Server follows OGC Common Architecture specifications. An overview of the spatial data types supported by SQL Server 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 verify the validity of their data using the STIsValid() method, which is documented for both Geometry (planar units) and Geography (ellipsoidal units), as well as the IsValidDetailed() method.
Alternatively, users can opt to use the GeometryValidator and correct the geometry before loading the database.
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 as 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 an 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 an OGC Simple test. A MultiLineString with edges that overlap will fail an 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 that demonstrate the need and the process for fixing invalid geometry. See below that 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 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 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.
Both passed and repaired ports are connected to the Writer. Alternatively, you could store repaired features in a different output than the passed features.