Change polygon "handedness" for SQL 2008 (reverse polygon vertex order)

Question

I have a few hundred POLYGONs and MULTIPOLYGONs, each containing 10k+ points, that I am trying to bring into SQL Server 2008.

Unfortunately, these shapes are "right-handed." Namely, the perimeter of each one is wound clockwise around the points it contains. For GEOGRAPHY types, SQL Server assumes "left-handed" shapes, anti-clockwise around the interior. This means that SQL Server assumes that I'm trying to select the entire earth except for my shape. Some people describe this as "inside-out" shapes.

MSDN's Orientation of Spatial Data heading states that the ring winding direction is significant, without actually saying which orientation 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 pick the wrong ring orientation in SQL Server 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.

Fixing this problem appears fairly trivial on SQL Server 2012 by testing the envelope angle and using ReorientObject(), but I am tied to version 2008.

How should I convert the shapes?

Answer 1

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 [...].

Answer 2

In >=SQL Server 2012, the ReorientObject() method should accomplish this. For <SQL Server 2012, below is an alternative method.

For an existing SQL geography, @g, the code below will extract the points and re-create a polygon with points (vertices) in reverse order:

-- For existing geography @g
DECLARE @GeometryText varchar(max), @ReversedPolygon geography
DECLARE @GeometryType varchar(20) = 'POLYGON', @Count int
SET @Count = @g.STNumPoints()
WHILE @Count > 0
BEGIN
    SET @GeometryText = @GeometryText + CONVERT(varchar(30),CONVERT(decimal(12,8),@g.STPointN(@Count).Long)) + ' ' + CONVERT(varchar(30),CONVERT(decimal(12,8),@g.STPointN(@Count).Lat))
    SET @Count = @Count - 1
    IF @Count > 0 SET @GeometryText = @GeometryText + ','
END
SET @GeometryText = @GeometryType +'((' + @GeometryText + '))'
SET @ReversedPolygon = geography::STGeomFromText(@GeometryText, 4326);

• It only works for simple polygons, not for multipolygons or polygons with rings/centroids.
• It uses the SRID 4326 (WGS 84) coordinate system. (See last line.)

Answer 3

It looks like I can use some unholy hybrid of SQL and C# from SQL Server Spatial Tools, as suggested on Stack Overflow.

Note: At the time this answer was posted, there was not a lot of information out there. Please do not try this method unless you already need SQL Server Spatial Tools for something else. Instead, try one of the other answers here or on Stack Overflow.