I recently got contacted via email with the following problem:
I have 2 complex polygons, representing district boundaries. The polygons look correct, but I'm getting exceptions when I try to create the type.
I've attached the wkt polygons to the email.
These polygons are stored in a table, but I've tried it without the table and get the same result.
Here's the sql I'm trying to execute:
declare @g geography;
set @g = geography::STPolyFromText(@wkt, 4268)
where @wkt is replaced by the correct polygon string.
Now, I should point out that this is a very nice error report. They've told me what their data is, they included their data, and they told me very clearly what they're trying to do. (Perhaps knowing what version they're using would good, but I'm getting picky.)
But here's the kicker that saved me any debugging:
Here is the error message I receive:
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.
at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeometryData g)
at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeometryData g, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.STPolyFromText(SqlChars polygonTaggedText, Int32 srid)
I looked at all the nodes, and they are all in the same hemisphere, so I'm a little confused with what might be going on.
So, what's going on? Ring ordering.
I've posted on this before, but let's recap. On a round Earth, defining a polygon by a ring is ambiguous. Consider the polygon defined by a ring around the equator: does this describe the northern or southern hemisphere? A small ring could describe either, say, a small island in a large ocean, or the large ocean itself.
To get out of this pickle, we disambiguate with a standard trick: we take ring orientation into account. We simply define the left side of the ring as it is drawn to be inside the polygon.
If we put this together with our current (unfortunate) limitation that we do not deal with objects that exceed a hemisphere, and it becomes clear what's going on. While the polygon looks like it's nice and small, the rings are inverted, and so it's actually quite large. We can't handle it: cue the exception.
I describe this hemisphere limitation as unfortunate, and it remains one of the biggest items the spatial team would like to fix up, but in this case it has actually helped catch an error.
I'll talk a bit more about our hemisphere limitation in a future post. It's not quite as simple as we let on.