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 hemisphereSELECT 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 hemisphereSELECT 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.
