Thursday, March 4, 2010

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

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 Geography?
You probably want to use this if you need very precise measurements over relatively long distances. Since it is more of a “real world” option, Geography will return results that are truer to life (but will look squirrelly on flattened maps like Google Maps or Bing).

Why Not Geography?
There is a lot of overhead in setting up spatial data as Geography. Since it has to accommodate the roundness of earth, you have to provide more parameters than you would with Geometry.
Also, when you see the results of straight lines drawn using Geography, they will appear as arched in a typical map application.  This means that if you are drawing a rectangular query in VFX (which assumes Geometry), Geography will return a different set of features that the query rectangle implies.
Sometimes the confusion can be more trouble than the “accuracy” is worth. Your call.

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 Geometry?
Geometry is a more straightforward mode to manage. You can get your application up and running faster and have, generally, good enough spatial results like distance. Also, geometry will look better on an equirectangular projection (again, Google Maps and Bing) because your lines will be “straight” as far as the map is concerned.
Often times Lat/Long bounding box spatial queries are what folks are after, if that is the case, Geometry is the answer.
Oh, and another good reason to use Geometry is because that is how VFX is set up to draw queries.

Why Not Geometry
If you need very high precision or perhaps have geometry that passes the international date line, or your query areas span very large areas, then you should consider Geography. After all…the Earth isn’t actually flat.

Spatial Queries

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 Geographic rectangle query in its native spherical environment...

...and stretched over a planar system.

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…
A Geometric bounding box query in its native planar environment...

...and warped onto a spherical system.
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).
A Geographic rectangle query, with an indication of the Geometric version of the same bottom boundary.
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).

More Reading

No comments:

Post a Comment