Working with Invalid Data and the SQL Server 2008 Geography Data Type, Part 1b

"Fixing" Polygon Ring Orientation in SQL Server 2008 using T-SQL

In the last installment, I promised to try and correct invalid Geography data using FME.  While I am making progress on that front, I decided to take a small detour and discuss an interesting but little known behavior of the SQL Server Geometry type, polygons and data validation.

One of the most common data errors when attempting to import spatial data into the Geography data type involves the ring orientation of polygons:

A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
Microsoft.SqlServer.Types.GLArgumentException: 24205: The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation.

Consequently, we often see inquiries about changing ring orientation in SQL Server, using T-SQL.  Since SQL Server does not allow polygons with the "wrong" ring orientation into the Geography data type, any solution to this dilemma must use the Geometry data type. At this point, I should note that FME 2008, when loading data into a column of type Geography, attempts to correct polygon ring orientation.  I say attempts, since there are edge conditions (+- 180 degree meridian and the Poles) which can derail this process.  The same goes for the technique in T-SQL that I am about to describe.

Here is the aforementioned behavior - When executing an OGC-based method (which works on the entire geometry) on valid polygon features in the Geometry data type, SQL Server will check ring orientation.  If SQL Server finds outer rings oriented in a clockwise direction, it will re-orient such rings to counter-clockwise - the direction required for outer rings in the Geography data type. The same goes for inner rings (holes), which SQL Server will orient to clockwise.

So, how might you take advantage of this behavior?  I'll use a very simple test case:

--
-- Create table with Geometry column
--
CREATE TABLE foo (
  id   INTEGER,
  geom GEOMETRY);
--
-- Create two similar polygons with opposite ring orders
--
DECLARE @g GEOMETRY = 'POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))';
DECLARE @h GEOMETRY = 'POLYGON ((0 0, 0 10, 10 10, 10 0, 0 0))';
--
-- Persist polygons to the table
--
INSERT INTO foo (id, geom) VALUES(1,@g);
INSERT INTO foo (id, geom) VALUES(2,@h);
--
-- Update rows, forcing validation of spatial objects
--
UPDATE foo
  SET geom = geom.STUnion(geom.STStartPoint());
--
-- Verify new, consistent, ring order after forced validation
--
SELECT id,geom.ToString() FROM foo;
--
-- Results:
-- 1    POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))
-- 2    POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))

The key to this behavior is the the STUnion() method.  Since this is an OGC-based method, working on the entire geometry for a given feature, it forces polygons into the orientation required for the method - which just happens to be the one used for the Geography type (quite a coincidence, eh? ;-).  This method illustrated is quite efficient, keeping overhead small in the quest to achieve our goal of re-orienting the polygon rings.

Since the Geometry must be valid for this to work, you might want to add the MakeValid() method to the processing stream. 

UPDATE foo
  SET geom = geom.MakeValid().STUnion(geom.STStartPoint());

It is important to note that a MakeValid() on an already valid polygon geometry will not change ring orientation, hence the need to "force" a complete validation pass with the OGC-based method (STUnion, in this case).

To finish up this exercise, let's move the Geometry data to Geography data in a sister table.  Before doing this, please bear in mind that this approach is naive in that it does not accommodate several potential edge conditions.  Never-the-less, this approach should work in many cases.

--
-- Create table with Geography column
--
CREATE TABLE gfoo (
  id   INTEGER,
  geog GEOGRAPHY);
--
-- Convert from Geometry to Geography using Well Known Text
--
INSERT INTO gfoo
  SELECT foo.id, GEOGRAPHY::STGeomFromText(foo.geom.STAsText(),4326)
  FROM foo;
--
-- Convert from Geometry to Geography using Well Known Binary
--
INSERT INTO gfoo
  SELECT foo.id, GEOGRAPHY::STGeomFromWKB(foo.geom.STAsBinary(),4326)
  FROM foo;

A special thanks to IsaacK and BobB for suggestions and assistance with the SQL syntax...