Geometry and Geography in SQL Server 2008 Spatial Queries
SQL Server 2008 has great spatial capabilities, notably spatial querying. But performing a Geographic spatial query is a different question and will return different results than a Geometric spatial query. Neither method is wrong, but it is important to know the differences, and as such what you are asking, in order to understand the results. Both options are valid, depending on what you are looking for.
|Geography takes into account our relatively spherical earth, and so uses spherical geometry. If you draw a straight line in geography, it will use the great circle line, which is just an awesome way of saying the shortest distance between two points –like pulling a string tight between two points on a globe.
Why Not Geography?
|Geometry imagines that the world is actually a flat rectangle (much like how we see it every day in our Mercator maps) and uses good old planar geometry. Since most online map applications live in a Mercator world, planar geometry is a fine fit. A straight East-West line will just follow the line of latitude, and as a result look straight on an equirectangular map.
Why Not Geometry
A rectangular spatial query using the Geographic (spherical geometry) method is illustrated here. My delicately manicured fingers are holding a rectangular shape over a globe. It simply pulls a tight line around my four corner points, and returns anything within. But look how hosed it appears in a planar system (Mercator)…
A bounding box query is illustrated here in a Mercator map. Looks fine –notice how it respects lines of latitude and longitude. Now check out how that bounding box looks in on a real globe…
However, sometimes these results are confusing. For example, Winnipeg is north of Vancouver and Anticosti Island (see awesome image above), but it clearly does not fall within my Geographic rectangle (because on a sphere, North is relative).
If you are performing a Geographic query, but are anticipating the results of a Geometric query, you might, for example, be confused as to why items falling within the highlighted area above are omitted. That red dashed line follows what would be the bottom edge of a Geometric query. It’s all in the expectations of the question you are asking.
By the way, you’ll notice a bigger discrepancy between Geometry and Geography query results the farther you get from the equator.
What the Deuce IS a Great Circle?
Here is a really high-tech illustration of a great circle. I’ve pulled a string tight over the globe between Vancouver and Anticosti Island. This is the shortest distance between these two places. If you were to fly an airplane from Vancouver directly to Anticosti Island, you’d follow this string (the straight line between the two locations). So check out how this perfectly straight line appears in Mercator! This is the exact same image, warped onto a Mercator projection. You’ll also notice that in the Mercator image, the globes lines of latitude now appear straight.
An actual straight line (as illustrated by that string on the globe up there), and that same straight line in planar geometry (in this case, Mercator).
- Potential workarounds: http://social.msdn.microsoft.com/forums/en-US/sqlspatial/thread/006785d4-8201-4765-b74c-aa4a053bdd61/
- Great little article: http://www.sharpgis.net/post/2008/01/Straight-lines-on-a-sphere.aspx