Ring Orientation, Bigger-than-a-Hemisphere Polygons, and the ReorientObject method in SQL Server 2012

If you’ve ever tried to import spatial data (from an ESRI shapefile, say) into SQL Server, you’ve almost certainly encountered the dreaded “bigger than a hemisphere” error, as follows:

Msg 6522, Level 16, State 1, Line 1 

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.

This error arises out of a widely documented limitation in SQL Server 2008/R2 that geography instances cannot span more than a single hemisphere (i.e. they cannot extend over more than half the surface of the earth).

While, strictly speaking, this is a technical limitation, the “smaller-than-a-hemisphere” requirement actually has a beneficial side-effect of providing a useful sanity check of your data: in practice, it is very unusual to ever need a geography instance that spans more than a single hemisphere. Unless you are modelling the Pacific Ocean, or the entire continent of Asia as a single instance, the chances are that you didn’t really want a geography bigger than a hemisphere anyway. Instead, as the error message above states, what probably happened is that the coordinate data of your imported shape had the incorrect ring orientation – the coordinate points were listed in reverse order, which has the effect of inverting the polygon. Therefore, you ended up including everything that was meant to be excluded, and excluding the area that you meant to include. Instead of defining the county of Norfolk, for example, your polygon actually represents the entire surface of the earth except for Norfolk…

Assuming that what you really wanted was to define a relatively small polygon area – a country, state, or sales region, say, then the “within a hemisphere rule” ensures the quality of your data by rejecting any Polygon instances with the incorrect ring orientation.

Along Comes SQL Server 2012

Well, SQL Server 2012 now allows geography Polygons to be bigger than a hemisphere, and that brings a whole new interesting problem… because now, you no longer get an error when you try to import data with incorrect ring orientation – it loads just fine. And you might not even realise that there’s anything wrong with your data until you try to perform some calculations on it.

For example, I just downloaded some Australian river basins data from the Geoscience Australia website, and imported it into SQL Server using OGR2OGR. Everything seemed to go successfully, and here’s a glance at what the table looks like with the geography data inserted into the geog4202 column:

Seems ok, right? But if you calculate the area of any of the river basins in this table using STArea(), you’ll get a result like this:

SELECT TOP 1 geog4202.STArea();

-- 510,069,272,680,889 (m2)

I mean, I know Australia is a big place, but… a river basin with an area of 510,069,272 km²… Really? That seems awfully close to the entire surface area of the earth. And, if you look on the spatial results tab, you’ll see this:

Sure enough – it looks pretty much like those polygons do cover the entire surface area of the earth.

The problem here is that the source data does not follow the same “left-hand rule” convention as SQL Server, and some (the majority, in fact) of the imported river basin polygons have been defined inside-out. But, because SQL Server now allows large geographies, no error or warning has been created.

The Solution

Fortunately, SQL Server 2012, whilst inadvertently causing this problem, also provides the solution, in the form of the ReorientObject() method, which reverses the ring orientation of any polygon to flip its interior and exterior.

We only want to use ReorientObject() to flip those polygons that have been defined inside-out. Unfortunately, there’s no fool-proof way to check for this, but it’s reasonable to assume that, in a dataset of Australia’s river basins, any polygon that covers more than half the earth’s surface must be incorrect. So, we can identify those polygons that need flipping because their EnvelopeAngle will be equal to or more than 90°, and put this in a CASE statement as follows:

SELECT
  CASE
    WHEN geog4202.EnvelopeAngle() >= 90 THEN geog4202.ReorientObject()
    ELSE geog4202
  END AS geog4202
FROM rbasin_polygon;

And now, the results look much more like those expected:

(If you really want, you could even put a CHECK constraint on a geography column that prevented any polygons greater than a hemisphere being inserted into the table in the first place. Effectively, you’d be manually reintroducing a technical limitation from previous versions of SQL Server – ironic, huh?)