Invalid Geometries In SQL Server 2008

By GGP Systems

Posted in Technology Blog on December 7th 2010 23:42

We have already covered the issue of invalid geometries in overlays stored in SQL Server 2008 in our Release Notes for GGP 2009 and in the technical document “Planning Your GGP 2009 Installation“.

To summarise the problem, SQL Server 2008 is notoriously intolerant of invalid geometries (e.g. polygons with self-intersecting linear rings, bow-ties, spikes). Whenever a spatial query or spatial operator is used on a table or view that contains even one single invalid geometry, SQL Server raises a .NET error. This error is something that prevents the GGP applications from drawing any geometries in the viewport. This occurs when performing operations as simple as calculating the extents of an overlay, or determining how many features are contained in a rectangle.

This problem has led our development team to create a workaround: the use of a “safe spatial query mode”, which is the default setting for overlays stored in SQL Server. Unfortunately, although this safe mode computes all spatial calculations correctly, it is much slower than the ordinary “unsafe” query strategy. The safe mode can be disabled for a layer for better performance, but that requires an absolute certainty that it does not contain any invalid geometries.

So how would a GIS administrator find out whether any invalid geometries exist in an overlay? And how to spot them, in order to re-capture them? Allow me to introduce the OGC-compliant SQL Server 2008 operator geometry::STIsValid(). This operator will return integer value zero or one depending on whether a geometry is invalid or valid.

Let’s suppose that we have a layer called PLANNING stored in SQL Server, and that its geometry column is called GEOMETRY. Using the SQL Server administration console, we could obtain the number of invalid geometries running the following SQL query:

select count(*) from PLANNING where GEOMETRY.STIsValid() = 0;

If the resulting number is zero, the PLANNING overlay contains no invalid geometries and the safe query mode can be disabled. However, if any invalid geometries exist within PLANNING, we can plot those on a map very easily. Opening the overlay in safe mode, all we need to do is create a display query or a database query with the condition

GEOMETRY.STIsValid() = 0

If we run this query, and zoom to the complete overlay extents, we’ll see the complete set of records that have invalid geometries.

Read more:

You must be logged in to post a comment.