9

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?

  • 1
    +1 great question ... do you have a link where it says Sql server assumes left-handed shapes? – Kirk Kuykendall Jul 22 '13 at 14:24
  • @Kirk Thanks. I am having trouble trouble finding official documentation, but I can link to the MSDN where it says the "ring orientation" matters (although it doesn't say which way to use). I'll also put in the error one gets when it crashes. – Michael Jul 22 '13 at 15:24
11

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

  • 2
    On SQL Server 2008 r2, I had to do put .MakeValid() on the inside of the STUnion() as well to get this to work: .STUnion(@geom.MakeValid().STStartPoint()) – Chris Smith Jul 20 '16 at 4:26
  • @Smitty That makes sense for cases where SQL can't otherwise determine the starting point. Maybe if the shape doubles back over itself or other odd circumstances? – Michael Jul 20 '16 at 15:20
  • Yes in my circumstance the shape is whacky and overlapping itself. – Chris Smith Jul 20 '16 at 15:37
0

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, code below will extract the points and re-create a polygon with points (vertices) in reverse order:
(NOTE 1: works for simple polygons, not for multipolygons or polygons with rings/centroids)
(NOTE 2: using SRID 4326 (WGS 84) coordinate system)

--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); 
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.