Jaa

The Geography Hemisphere Limitation

  • Artikkeli

Hi Folks,

It occurs to me that I haven’t posted anything about the hemisphere limitation for the SQL Server geography type.  This limitation is a little confusing, and could probably use some clarification.

It also occurs to me that I haven’t posted anything in a while—let’s rectify both of these.

We usually express SQL Server’s limitation by saying that no object can exceed a hemisphere.  But which hemisphere?  Does this mean that objects cannot cross the equator?  The international date line?  The truth: neither.  We can, in fact, generate objects that cross the equator:

POLYGON ((-145 -45, -55 -45, -55 45, -145 45, -145 -45))

image

Or the date line:

POLYGON ((135 0, -135 0, -135 90, 135 90, 135 0))

image

Or the poles:

POLYGON ((0 45, 90 45, 180 45, 270 45, 0 45))

image

This illustrates that it is not objects in certain positions that are disallowed.  Rather, large objects are disallowed: if an object spans more than half the globe, SQL Server will complain.  So while a polygon that sits slightly inside of a hemisphere is fine:

POLYGON ((0 1, 90 1, 180 1, 270 1, 0 1))

image

One that is slightly larger causes problems:

POLYGON ((0 -1, 90 -1, 180 -1, 270 -1, 0 -1))

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.
Microsoft.SqlServer.Types.GLArgumentException:
at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData g)
at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive()
at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData g, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.STGeomFromText(SqlChars geometryTaggedText, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)
.

We can dig a little deeper.  When SQL Server sees a geography instance, it computes a bounding cap for it.  This is very much like a bounding box on the plane, but is defined by a center point and an angle.  We can actually find the bounding cap for an object using the EnvelopeCenter() and EnvelopeAngle() methods on geography.  Using these, we can plot the cap.  For example:

POLYGON ((-50 -10, 50 -10, 50 10, -50 10, -50 -10))

image

If the cap angle exceeds 90 degrees, then the object exceeds a hemisphere.  The cap computed above has an angle of 50.7 degrees.

But there is some subtlety in how SQL Server determines this cap.  The angle is relatively simple: it is the maximum angle from the center to any of the points in the figure, yielding a minimal cap with that center.  The center, however, is determined by summing the vectors from the center of the globe to each vertex in the figure, essentially averaging the vertices.  This means that if the points in our figure are lopsided—e.g., if add more points on the eastern edge—we will compute a cap that no longer looks very minimal:

POLYGON ((-50 -10, 50 -10, 50 -5, 50 0, 50 5, 50 10, -50 10, -50 -10))

image

Now the cap center is skewed, so the angle has to increase—to 77.4 degrees—to contain the object.  If we add more points on the eastern edge we get into trouble:

POLYGON ((-50 -10, 50 -10, 50 -8, 50 -6, 50 -5 50 -4, 50 -2, 50 0, 50 2, 50 4, 50 5, 50 6, 50 8, 50 10, -50 10, -50 -10))

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.
Microsoft.SqlServer.Types.GLArgumentException:
at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData g)
at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive()
at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData g, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.STGeomFromText(SqlChars geometryTaggedText, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)
.

So although our object hasn’t changed—the extra vertices we added were chosen so they don’t change the shape—SQL Server now thinks we’ve exceeded a hemisphere.

Let me answer two more obvious questions.  First: why do we compute caps this way instead of computing a true minimal bounding cap?  It turns out that finding a minimal cap is difficult computationally, and because this operation happens a lot, we need something cheap.

Second: why does SQL Server have the hemisphere limitation at all?  A complete answer will require another post.  Suffice to say, it’s an implementation artifact that the SQL Server team absolutely abhors.  That SQL Server isn’t the only system with such a limitation doesn’t make it any less annoying, and removing the limit is high on everyone’s priorities.

Cheers,
-Isaac

Comments

  • Anonymous
    April 13, 2009
    The comment has been removed