Skip Navigation Links
Home
Functions
Tips & Tricks
SQL Server 2005
SQL Server 2008
SQL Server 2012
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Differences Between the Geometry and Geography Data Types
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

Spatial data represents information about the physical location and shape of geometric objects. These objects can be point locations or more complex objects such as countries, roads or lakes. SQL Server supports two spatial data types, introduced in SQL Server 2008, namely the geometry data type and the geography data type.

The geometry data type supports planar, or Euclidean (flat-earch) data. The geometry data type both conforms to the Open Geospatial Consortium (OGC). The geography data type, on the other hand, stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.


Geometry Geography
The connecting edge between two vertices in a geometry type is a straight line. The connecting edge between two vertices in a geography type is a short great elliptic arc between the two vertices. A great ellipse is the intersection of the ellipsoid with a plane through its center and a great elliptic arc is an arc segment on the great ellipse.
Circular arc segments for geometry types are defined in the XY Cartesian coordinate plane (Z values are ignored). Circular arc segments for geography types are defined by curve segments on a reference sphere. Any parallel on the reference sphere can be defined by two complementary circular arcs where the points for both arcs have a constant latitude angle.
In the planar, or flat-earth, system, measurements of distances and areas are given in the same unit of measurement as coordinates. Using the geometry data type, the distance between (1, 4) and (4, 8) is 5 units, regardless of the units used. In the ellipsoidal, or round-earth, system, coordinates are given in degrees of latitude and longitude. However, the lengths and areas are usually measured in meters and square meters, though the measurement may depend on the spatial reference identifier (SRID) of the geography instance. The most common unit of measurement for the geography data type is meters.
In the planar system, the ring orientation of a polygon is not an important factor. For example, a polygon described b ((0, 0), (5, 5), (0, 10), (-5, 5), (0, 0)) is the same as a polygon described by ((0, 0), (-5, 5), (0, 10), (5, 5), (0, 0)). The OGC Simple Features for SQL Specification does not dictate a ring ordering, and SQL Server does not enforce ring ordering. In an ellipsoidal system, a polygon has no meaning, or is ambiguous, without an orientation. As an example, does a ring around the equator describe the northern or southern hemisphere? If the geography data type is used to store the spatial instance, the orientation of the ring must be specified and accurately describe the location of the instance. The interior of the polygon in an ellipsoidal system is defined by the left-hand rule.

Back to Tip of the Day List Next Tip