Go to content Go to navigation and search


Current SQL Server Blog Articles



RSS / Atom

Email me


Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Tip #6: Correcting invalid geometries

Thursday November 29 2007 at 23:42

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: 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.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment [1]

Thanks! That stuff was very helpful!

— orzech · 10 January 2013, 21:44 · #