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:

27-01-2012 12-05-32

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 km2Really? 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:

27-01-2012 12-07-18

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:

    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:

27-01-2012 12-13-22

(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?)

This entry was posted in Spatial, SQL Server and tagged , , , . Bookmark the permalink.

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

  1. Hung says:

    thank you very much! like this post

  2. Paul says:

    Great article – saved my life. And such an entertaining writing style to boot…

  3. Russell Vassar says:

    I’m getting this error when importing shapefiles to SQL Server 2008 using ogr2ogr . Is there any way to get them imported, or do I have to fix the shapefiles before attempting to import them?

  4. Gay Spencer says:

    Thanks much for your posts here and your Spatial 2012 book! In working through the book examples, the CA ZCTA’s import produced the dreaded “rectangle-of-everythingness” in the spatial results tab. I thought maybe I’d done something wrong in the installation ogr2ogr, or in importing… and did the whole process several times. Adding a CASE statement that checks and fixes both validity (.MakeValid()) and orientation (ReorientObject()) was the answer. Thanks!

  5. SorrCorp says:

    This is perfect.

    Enjoying the book. I was guessing about a lot of things, and was getting lucky. Good to understand the ‘why’. The SQLCLR calls are timesavers!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s