[GIS] Change polygon “handedness” for SQL 2008 (reverse polygon vertex order)

geography-data-typesqlsql-server-spatial

I have a few hundred shapes (polygons and multipolygons) each consisting of tens of thousands of points that I am trying to get into SQL 2008.

Unfortunately, the shapes I have tried to import are "right-handed" (the perimeter of each one is drawn clockwise around the points it contains). SQL server assumes "left-handed" shapes (anti-clockwise around the interior), at least for geography types. This means that SQL assumes that I'm trying to select the entire earth except for my shape. Some people describe this as "inside-out" shapes.

From MSDN, which frustratingly doesn't say which ring orientation one is to use:

If we use the geography data type to store the spatial instance, we must specify the orientation of the ring and accurately describe the location of the instance.

If you use the wrong ring orientation in SQL 2008, it crashes with the following error (emphasis mine):

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.

Importing the shapes as geometry instead of geography works fine, but I'd like to use geography if I can.

In SQL 2012, it appears fairly trivial to fix this problem, but I am tied to 2008.

How should I convert the shapes?

Best Answer

Spatial Ed's blog had a concise solution. Here is some SQL demonstrating the transform:

DECLARE @geom GEOMETRY = 'POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))';
DECLARE @geog GEOGRAPHY = @geom.MakeValid().STUnion(@geom.STStartPoint()).STAsText()

And an excerpt from Ed's post:

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 [...]. This method illustrated is quite efficient, keeping overhead small [...].

Related Question