## SQL Server 2008: Spatial Data, Part 7

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

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:

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

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

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:

*(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.

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** in the February 2008 CTP: 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.

More on the **Geography** type later!

SQL Server 2008: Spatial Data, Part 1

SQL Server 2008: Spatial Data, Part 2

SQL Server 2008: Spatial Data, Part 3

SQL Server 2008: Spatial Data, Part 4

SQL Server 2008: Spatial Data, Part 5

SQL Server 2008: Spatial Data, Part 6

Pingback from jasonfollas.com

A View Inside My Head | SQL Server 2008: Spatial Data, Part 6