SQL Server 2008 Geography and Geometry Data Types

By: Tim Cullen | Read Comments | Print Tim has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer. Related Tips: More

Problem
So, I've heard about the new geospatial data types in SQL Server 2008 and my company would like to take advantage of them when we migrate; however, I'm relatively new to geodatabases and geospatial data. What can you tell me about it?

Solution
Microsoft did indeed introduce geospatial data types, as well as a host of functions to support those data types, in SQL Server 2008.  To me this is one of the most exciting enhancements in that release. The data types and functions used to support geospatial data are based on standards set forth by the Open Geospatial Consortium. But the main question is-what exactly is "geospatial data"? The Environmental Protection Agency defines geospatial data as "...data that identifies, depicts or describes geographic locations, boundaries or characteristics of Earth's inhabitants or natural or human-constructed features...". Geospatial data can be categorized in one of two types:

Euclidean data

Euclidian data, also called flat-earth data, is mostly a two-dimensional representation of an object. It is useful in cases where the curvature of an object, like the Earth, has little impact on the measurement between different objects. Before the use of computers flat paper maps were used, which provided some challenges when calculating and otherwise tracking data, requiring government entities to develop systems like the State Plane Coordinate System.

Geodetic data

Ellipsoidal data, or round-earth (geodetic) data, is designed to take the Earth's curative into account when measuring distances. This is important when dealing with earth points that are long distances from one another. Examples of making use of geodetic data include flight planning and missile defense systems. Flat maps offer the disadvantage of limiting the route taken by planes or missiles since the map has a limited number of options for routing (essentially an X:Y coordinate system). Geodetic data can take advantage of the Earth's curvature to find the shortest route regardless of the locations.

The two data types used in SQL Server for housing geospatial data are Geometry and Geography. Both data types are employed using the .NET Common Language Runtime . But before we discuss the data types, we should discuss Spatial Reference Identifiers, since they are very important to geospatial data.

Spatial Reference Identifiers
Spatial Reference Identifiers (SRID's), are collections of information about a particular spatial instance, including unit of measure, geodetic datum, and projection or mathematical model. Although SRID's must be specified in the text representations of both geometry and geographic data types, an SRID OF 0 is always used with flat-earth data, since an SRID is not required during calculation. Geodetic data, on the other hand, must be accompanied by a valid SRID from the sys.spatial_reference_systems catalog view. To view the Spatial Reference Identifiers available in SQL Server 2008 you can query the sys.spatial_reference_systems catalog view:

If you attempt to use an invalid Spatial Reference Identifier the following error will be thrown:

Geometry Data Type
The Geometry data type is used to house information on any of the recognized objects (called instances), like points, lines, and polygons. One example of using geometric data would be mapping product locations in a warehouse that can be used to provide the most efficient routing for order fulfillment.

When inserting or updating geometric data types in SQL Server 2008 a text representation of an object, known as the Well-Known Text (WKT) representation, is passed and CLR functions transform the representation into a binary value. For example, if you wanted to find out the binary value of a point with the coordinates x=0 and y=0, you would execute the following query:

DECLARE @Point GEOMETRY SET @Point = geometry::STGeomFromText('POINT (0 0)',0) SELECT @Point

There are two additional values you could specify with the point above: the first is height or elevation associated with the point. This value is commonly known as the "z" attribute. The second value is the "measure", or "m" attribute. This value can store any additional value that can be expressed as a float, like the time the point was measured. You can specify the point in the following ways: (x y), (x y z), (x y m), or (x y z m). The application specifications in which the points will be used should indicate the format used to store the point values.

The number of coordinates expected by the CLR function depends on the shape you specify in the text representation. For instance, if I change the query above from "POINT" to "LINESTRING" and execute it I get the following error:

 

Geography Data Type
The Geography data type houses the latitude and longitude of an instance, again, like a point, string, or polygon. Many of the characteristics are the same as the geometry data type, like the z and m value specifications for point values. With the geography data type, however, a valid Spatial Reference Identifier must be specified with each object, since calculations accounting for the curvature of the Earth or other object is performed.

The following query shows the binary representation of a line string:

DECLARE @Location GEOGRAPHY SET @Location = geography::STGeomFromText ('LINESTRING(47.653 -89.358, 48.1 -89.320, 49.0 -88.28)', 4326) SELECT @Location

 

As with the geometry data type, an error will be thrown if the well-known text representation (WKT) is not valid. In the case below a linestring is specified, but the WKT is missing commas to delineate the points on the line:

Next Steps

• In the next tip on geospatial data in SQL Server 2008 we'll discuss data storage, indexing, and working with geospatial data
• Read about Types of Spatial Data
• Read the Simple Feature Access: SQL Option standards as set forth by the Open Geospatial Consortium
• Read about the Geometry data type in SQL Server 2008
• Read about the Geography data type in SQL Server 2008
• Stay tuned for more geospatial data tips...

Related Tips: More | Become a paid author

Last Update: 9/29/2009

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL doctor is the best SQL product on the market, by far. All of Idera's tools are great, but this is the icing on the cake!" Quickly and accurately deploy database changes with Red Gate's SQL Compare - the industry standard comparison and deployment tool. What grade do you think your SQL Servers get? Find out with Edgewood's SQL Server Health Check consulting services. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. The SQL Server Security THREAT - It’s Closer Than You Think