The problem with America - spatial data
Ok, it's not really a problem with America, just a problem with the perception of straight lines like the USA-Canada border. It's roughly the latitude line 49 degrees north. A line that goes East-West, on a straight line.
Except that latitude lines aren't straight - except the equator. Just look on a globe and you'll see what I mean.The curvature of the earth just gets in the way a bit. If you're ten miles from the North Pole and travel east until you come back on yourself, you've just gone around in a circle that's 20 miles across. The smaller the circle, the more obvious the effect. The bigger the circle (like when you're near the equator), the less obvious the effect. But it's still there.
Pilots understand the principle. When I flew from London to Seattle earlier this year, we went across the top of Greenland, because that's the quickest way of getting there. Pick up a globe of the world, and you'll see exactly why we should fly that way.
When someone wants to fly from one point on the 49th parallel to another, or to simply draw a line, the shortest route actually goes a little further north. The 49th parallel is like that 20-mile circle - it bends to match the curvature of the Earth.
So if you're mapping the world using spatial data for SQL Server 2008, you may want to consider this when looking at the American border. It's not a straight line along the 49th parallel, because the 49th parallel isn't a straight line.
To demonstrate this, my friend Greg Larsen (who runs the Olympic Area SQL Server User Group) has sent me a query. You'll notice that there are some locations north of the 49th parallel that are with the polygon which seems to be bound by the 49th parallel.
DECLARE @region geography;
SET @region = geography::STGeomFromText('POLYGON((-67 49, -125 49, -125 24, -67 24, -67 49))', 4326);
,[SpatialLocation].Long AS Long
,[SpatialLocation].Lat AS Lat
WHERE [SpatialLocation].STIntersects(@region) = 1
and [SpatialLocation].Lat > 49;
This query actually gives us 632 rows. That's 632 addresses in the sample database which are more than 49 degrees North, and yet are within a polygon which has corners at -67 49 and -125 49. Hopefully you can look at the picture of the US border and see where those 632 rows much be.
SQL Server spatial stuff is really clever. But perhaps we need a way of telling it to draw lines east-west, instead of as the crow flies.