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().
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;
The MakeValid() statement will split it into a MULTIPOLYGON
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))
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)).
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.