Tip of the Day : Differences Between the Geometry and Geography Data Types

Welcome to SQL Server Helper !!!

This site is intended for those who are beginning to use SQL Server as part of their day-to-day activities.  You will find in this site a collection of useful functions, triggers, stored procedures and tips and tricks related to SQL Server.

Should you have any comments or questions regarding this site or if you want to ask SQL Server-related questions, e-mail us here.

We hope we are able to help and we are glad to help!!!

SQL Server Tip of the Day - January 18, 2017

Differences Between the Geometry and Geography Data Types

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.

SQL Server 2012

SQL Server 2008

User-Defined Functions

Date Functions

A collection of useful user-defined functions that deal with dates.

String Functions

A collection of useful user-defined functions that deal with strings (varchar/char/nvarchar/nchar).

Tree Functions

A collection of useful user-defined functions that deal with tree or hierarchical design structures.

Table-Valued Functions

A collection of useful table-valued user-defined functions that can be used to join with other tables.

SQL Server Built-in Functions

A reference to all built-in functions available within SQL Server grouped into categories.

Tips and Tricks

A collection of useful SQL Server-related tips and tricks:

SQL Server Error Messages

A list of SQL Server error messages and for certain error messages, discusses ways on how to solve the error or work around them:

Frequently Asked Questions