Tuesday, February 26, 2008
SQL 2008: SQL Geography & Single Hemisphere Requirement
Posted on Tuesday, February 26, 2008 7:13:31 PM (Mountain Standard Time, UTC-07:00)  Comments [2] | 
Categories: SQL 2008 | Virtual Earth

I've been working with Virtual Earth and SQL 2008 - mainly pulling points out of the database, clustering them, and throwing them on the map. All well and good, but I was getting some weirdness when zoomed out to the first of second zoom levels.

Specifically I was getting this error:

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.

The issue was that the bounding box of the map view port was more than a "hemisphere" in size, and so I was going to have to split up these queries into SQL Server. Now that sounds simple enough, but took some experimenting to work out exactly how to do this. Steve Kass (I believe of the SQL Server team @ Microsoft) has a post on the hemisphere requirement that talks about ring-direction. While we're on that sub-topic, ring-direction matters a lot here. The "left side of the line" is the "inside", and if you get it wrong you will also get this same exception.

So - my observations - it seems that your geometry has to be less than a hemisphere. The "exceeds" in the error message is a red herring.

For example the "western hemisphere" as defined below will throw an exception

declare @WKT varchar(max);
set @WKT = 'POLYGON((90 -180, -90 -180, -90 0, 90 0, 90 -180))'
SELECT count([PointLocationID]) FROM [dbo].[DemoPoints]
WHERE [Location].STIntersects(geography::STPolyFromText(@WKT, 4326)) = 1

However, if we tuck these values in a little bit...

declare @WKT varchar(max);
set @WKT = 'POLYGON((89.9 -179.9, -89.9 -179.9, -89.9 0, 89.9 0, 89.9 -179.9))'
SELECT count([PointLocationID]) FROM [dbo].[DemoPoints]
WHERE [Location].STIntersects(geography::STPolyFromText(@WKT, 4326)) = 1

All is well again.

Further experiments seem to indicate that which way you cut the globe matters as well. Despite having 1000's of points in the souther hemisphere, this query does not return any points

declare @WKT varchar(max);
set @WKT = 'POLYGON((0 -179, -89 -179, -89 179, -1 179 , 0 -179))'
SELECT count([PointLocationID]) FROM [dbo].[DemoPoints]
WHERE [Location].STIntersects(geography::STPolyFromText(@WKT, 4326)) = 1

If I then split the southern hemisphere into two parts - East and West, I get the points again...

declare @WKT varchar(max);
set @WKT ='POLYGON((0 0, -89 0, -89 179, -1 179 , 0 0))' --eastern half of southern hemisphere
SELECT count([PointLocationID]) FROM [dbo].[DemoPoints]
WHERE [Location].STIntersects(geography::STPolyFromText(@WKT, 4326)) = 1

and

declare @WKT varchar(max);
set @WKT ='POLYGON((0 -179, -89 -179, -89 0, -1 0 , 0 -179))'--western half of southern hemisphere
SELECT count([PointLocationID]) FROM [dbo].[DemoPoints]
WHERE [Location].STIntersects(geography::STPolyFromText(@WKT, 4326)) = 1

So - it would seem that the "hemisphere" they are referring to is really an EAST - WEST range. As long as your query geometry is less than 180 degrees wide, things are pretty good. Take this query, which is a patch that is ~180 wide and 90 high centered over 0,0.

declare @WKT varchar(max);
set @WKT = 'POLYGON((45 -89.9, -45 -89.9, -45 89.9, 45 89.9, 45 -89.9))'
SELECT count([PointLocationID]) FROM [dbo].[DemoPoints]
WHERE [Location].STIntersects(geography::STPolyFromText(@WKT, 4326)) = 1

This works just fine, so clearly we are not restricted by the actual Lat/Lon values just the range of values.

So as far as I can tell the rules for a valid geometry are:
1) Latitude (Y) Range must be less than or equal to 90 degrees

2) Longitude (X) Range must be less than 180 degrees

Hope this helps someone else avoid a few hours of head scratching and manual SQL querying.

Tuesday, February 26, 2008 8:49:10 PM (Mountain Standard Time, UTC-07:00)
If you want to create a hemisphere ring, the ring has to be along Equator. You are specifying points at the poles as well, and last time I checked they are not part of Equator :-) (remember we are using spherical geometry, not planar!)

You can define the northern hemisphere like this:
POLYGON((0.0001 -180, 0.0001 -60, 0.0001 60, 0.0001 -180))

Another thing... Your two ranges shouldn't be excluding the edges (poles/dateline). They are valid values as well.
Morten
Tuesday, February 26, 2008 9:01:01 PM (Mountain Standard Time, UTC-07:00)
forgot to add... the hemisphere thing is not an east/west thing. It can be oriented any way you like.
Morten
Comments are closed.