Validating Geometries with SQL Server 2008

Introduction

When you want to store spatial data in SQL Server 2008 you have to decide which data type to choose: The GEOMETRY data type supports planar data and conforms to the Open Geospatial Consortium (OGC) Simple Features for SQL Specification version 1.1.0. The GEOGRAPHY data type stores ellipsoidal data such as latitudes and longitudes in a WGS84 coordinate system. Obviously you can store latitudes and longitudes in a GEOMETRY data type as well and in fact it might make a lot of sense for:

  • certain Spatial-Relationship queries which are available for the GEOMETRY data type but not for the GEOGRAPHY or
  • validating data.

The GEOGRAPHY data type is particularly useful if you want to measure distances or areas and your coordinates are in the WGS84 and therefore it might be useful to convert between GEOMETRY and GEOGRAPHY data types. Let’s assume your data are stored with WGS84 coordinates in GEOMETRY columns and you want to calculate the area in square-meter. You could do something like this:

select (geography::STGeomFromWKB(geom.STAsBinary(),4326)).STArea() from Table

Now, this only works if the spatial data which are stored in the GEOMETRY column are valid. Otherwise you will receive an error message. Fortunately SQL Server 2008 has a function that allows us to query if a GEOMETRY is valid: STIsValid() and another one that even creates a valid GEOMETRY: MakeValid().

MakeValid

The MakeValid() helps when you’re GEOMETRY is invalid because of self-intersections or overlaps. The following example shows an self-intersecting POLYGON:

DECLARE @g geometry;
SET @g = geometry::STPolyFromText('POLYGON((0 0, 2 2, 0 2, 2 0, 0 0))', 4326);
SELECT @g;

image

The MakeValid() statement will split it into a MULTIPOLYGON

SELECT @g.MakeValid().ToString();

Result: MULTIPOLYGON (((1 1, 2 2, 0 2, 1 1)), ((0 0, 2 0, 1 1, 0 0)))

The Orientation Problem

What the MakeValid() method does not resolve is the orientation problem. For a GEOMETRY the polygons

POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))
and
POLYGON((0 0, 0 4, 4 4, 4 0, 0 0))

are the same. For a GEOGRAPHY however the orientation does matter a lot. For a simple polygon the orientation of the points needs to be counter-clockwise e.g. POLYGON((0 0, 4 0, 4 4, 0 4, 0 0)). For a polygon with holes the orientation of the outer ring needs to be counter-clockwise and the orientation of the interior rings needs to be clockwise, e.g. POLYGON((0 0, 4 0, 4 4, 0 4, 0 0),(1 1, 2 3, 3 1, 1 1)).

image

Some of the data that I received are not following this rule so I could not just use the MakeValid() method and then convert them into a GEOGRAPHY.

I had written a little TSQL-script which changed the orientation in a way that it followed the required orientation but although it worked that script was pretty slow and too complex. Fortunately "Spatial" Ed Katibah has provided a simple and very efficient way to work around this issue and he has described it on his blog.

Technorati Tags: ,
Advertisements
This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s