Tip #6: Correcting invalid geometries

In Oracle one can gest for invalid geometries via the SDO_GEOM package’s VALIDATE_GEOMETRY (or VALIDATE_GEOMETRY_WITH_CONTEXT) function. To use it one does the following:

SELECT sdo_geom.validate_geometry(a.geom,0.5)
  FROM projpoly2d;

Where 0.5 is the minimum distance between two vertices (I have deliberately not described the other overloaded version of this function that uses an DIMINFO structure).

In SQL Server, if you execute a SELECT statement and that statement processes invalid geometry objects you will get an error message like this:

PDBA(SPDBA\Simon): Msg 6522, Level 16, State 1, Line 4 A .NET Framework error occurred during execution of user defined routine or aggregate 'geometry': 
System.ArgumentException: 24144:
This operation cannot be completed because the instance is not valid.
Use MakeValid to convert the instance to a valid instance.
Note that MakeValid may cause the points of a geometry instance to shift slightly.
System.ArgumentException:
at Microsoft.SqlServer.Types.SqlGeometry.ThrowIfInvalid()
at Microsoft.SqlServer.Types.SqlGeometry.STBuffer(Double distance)

SQL Server Katmai includes the OGC function STIsValid() which can be used to find invalid geometries. Katmai also includes an “extended” (non-OGC 1.1) function called MakeValid() which can be uses to correct any geometries are STIsValid() report as invalid. To correct invalid geometries one can do this:

UPDATE projpoly2d
  SET geom = geom.MakeValid()
 WHERE geom.STIsValid() = 0;

Note that the MakValid function has no parameters so one cannot supply SQL Server with coordinate precision data to control the “shift slightly” warning in the above exception.