Spatial data in SQL Server 2008

Geography type in SQL Server

The Open Geospatial Consortium's Simple Features specification, which SQL Server 2008's Geometry data type is based upon, defines standards for working with spatial data using a flat-earth (projected planar) model. Ironically, these standards don't exactly cover the intricacies of using an ellipsoidal model, which is needed to "accurately" represent the world that we live in. In other words, the OGC standards define how to work with paper maps of the world, but not globes.

Fortunately, the SQL Server team recognized that that the Geometry type is inadequate for a lot of scenarios, and implemented a second data type just for representing geospatial data using a true ellipsoidal model: Geography.

In this, the seventh part of a series about the SQL Server Spatial Data Type, I'll examine some of the key differences between the Geometry and the Geography type that developers should be aware of.

Latitude and Longitude

Locations on a flat model are defined in terms of X and Y. There exists some point known as the Origin where X and Y are both zero. From there, it is defined that values of X will increase (or decrease, in the case of negatve numbers) if you move horizontally away from the Origin. Likewise, the values of Y will increase if you move vertically away from the Origin.

By convention, both X and Y will grow to infinity, so flat models do not "wrap around" and start approaching the Origin again if you go too far in one direction. Usually, a coordinate system will be based on some underlying representation of the real-world, so coordinates that are beyond the defined boundaries of that map are logically undefined.

By contrast, though, an ellipsoidal model does wrap around. If you started at a point in the middle and kept traveling in a straight line to the right, you will eventually return to that starting point.

So, it turns out not to be very practical to define points on a ball using X and Y. Instead, points are defined using angles. Longitude is the horizontal angle (how far East or West from a Prime Meridian) and ranges from -180 degrees to 180 degrees (with -180 and 180 being the same). Latitude is the vertical angle (how far North or South from the Equator) and ranges from -90 degrees to 90 degrees (with -90 representing the South Pole and 90 representing the North Pole).

LonLat

In terms of the Geography data type, just be aware that there is no X and Y. Instead, you work with Long and Lat.

Note: All of the SQL Server 2008 CTPs to date that include Spatial support, including the most recent February 2008 version, use Lat-Long ordering within WKT. This was a design decision based on the fact that the OGC standard did not already define parameter ordering for angular coordinates. Starting with the first Release Candidate, however, these parameters will be swapped to use Long-Lat ordering. Doing so will align SQL Server's spatial support with other platforms that have already implemented Long-Lat ordering. Note also that that this is aligned with the concepts of X and Y, which by convention lists the X value first.

Straight Lines

The shortest distance between two points is a straight line. But, a straight line on a flat-earth model is far different than a straight line on an ellipsoidal model. To demonstrate, consider the shortest path from Redmond, WA, USA to Cambridge, England, UK:

planar projection

On this planar projection, it certainly looks like the shortest path. Even when examined on a 3D model, it looks correct:

planar projection from north pole

But, if the camera is moved towards the North Pole, then the error becomes apparent:

ellipsoidal model

In the ellipsoidal model, the shortest path between the points is not the red line, which roughly parallels the lines of Latitude, but rather the black arrow! Converted back to a planar projection, this actual shortest path appears curved:

planar projection flattened

(in this view, the black curve was [hastily] plotted by hand)

Instance Methods

The following slide shows in all of the instance methods that have been implemented for the Geography type as of the February 2008 CTP. For comparison, instance methods from the Geometry type that do not exist in the Geography type are shown in gray.

GeographyMethods

It may be impossible to define some of these equivalent methods for Geography, simply because the rules are different. For instance, if you define a set of points that make up a Polygon, what is considered to be the interior and exterior of that shape? Since the world coordinates wrap around in an ellipsoidal model, you might be intending to represent a shape whose interior is the entire world except for the small portion. There is simply no way to convey your intent using the methods as described by the OGC standard.

In an attempt to prevent this particular scenario, the SQL Server team has imposed a limit on the size of a Geography : you cannot define a Geography that is larger than a hemisphere.

There may very well be logical solutions for working around some of the issues that prevented the SQL team from implementing all of Geometry's methods in the Geography type. However, in this case, Microsoft appears to be waiting for the OGC to define certain rules as part of a standard rather than coming up with their own assumptions, which could be invalidated later by the standards group going in a different direction.

You might also like...

Comments

About the author

Jason Follas United States

Jason Follas is the President of the Northwest Ohio .NET User Group (NWNUG) and is a Technical Architect for Perficient...

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“God could create the world in six days because he didn't have to make it compatible with the previous version.”