FME is loading features with invalid geometries into my Database

Symptom

FME is loading features into my database such as Microsoft SQL Server Spatial, Oracle Spatial, Postgis or DB2 which the GeometryOGCValidator is passing as valid but the database tools are tagging as invalid.

Cause

FME implements the OGC definitions found at http://www.opengeospatial.org/standards/sfa/. In particular, see section 6.1 for the Geometry Object Model, and what it means for various geometries to be valid or simple.

However each spatial database implementation of these rules differs slightly and this is why sometimes a feature will pass through the GeometryOGCValidator but the database still flags it as invalid.

It most often happens with self-intersecting features, so running the features through the SelfIntersector can sometimes be useful.

Resolution

Microsoft SQL Server spatial

It is possible to find these invalid features by using the following SQL statement (GEOM is the name of the geometry column in the data). In SQL Server 2012 it is also possible to get a more detailed message explaining the error.

 SELECT *, GEOM.IsValidDetailed() FROM [user].[schema].[tablename] WHERE GEOM.STIsValid()=0

It is possible to fix these invalid features by using the writer parameter - SQL Statement to execute after translation - with the following SQL statement

 UPDATE [user].[schema].[tablename] SET GEOM=GEOM.MakeValid() WHERE GEOM.STIsValid()=0

Oracle

As with SQL Server spatial, it is possible to find these invalid features using the sdo_geom.validate_geometry_with_context function. For example

 SELECT sdo_geom.validate_geometry_with_context ([tablename].GEOM, [tolerance]) FROM [tablename] WHERE sdo_geom.validate_geometry(GEOM,0.5) <>'TRUE'

Fixing these invalid features can be done using the SDO_UTIL functions such as SDO_UTIL.REMOVE_DUPLICATE_VERTICES or SDO_UTIL.RECTIFY_GEOMETRY

See the Oracle spatial documentation for the correct syntax

PostGIS

As with SQL Server spatial it is possible to find these invalid features using the ST_isvalid function. For example

 SELECT * FROM [schema].[tablename] WHERE ST_isvalid(GEOM) <>'TRUE'

DB2

DB2 Spatial Extender also offers an ST_isValid function which will return invalid geometries.