Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2008 Geography and Geometry Data Types


By:   |   Read Comments (5)   |   Related Tips: More > Spatial Data Storage

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:

Sample resultset when querying the sys.spatial_reference_systems catalog view

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

Eror generated when specifying an invalid Spatial Reference Identifier

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:

Query result showing the binary representation of a geographical instance

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 

Error encountered when not supplying enough coordinates for an object

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:

Error thrown when a well-known text representation is not formatted correctly
Next Steps


Last Update:






About the author
MSSQLTips author Tim Cullen Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Saturday, April 19, 2014 - 9:37:53 AM - Tim Cullen Back To Top

Hey Gerald:

 

Sorry, I haven't had a chance to work on this. Did you find an answer?


Monday, April 07, 2014 - 2:24:55 AM - mehrzad Back To Top

Hello Tim Cullen

tanks alot, very usefull for me

mehrzad (from iran)


Tuesday, February 04, 2014 - 9:37:00 PM - Gerald Back To Top

Sure...I have been researching this issue for two weeks, so another two days is fine.  By the way, I stated the mapping systems backwards -- I really need to convert coordinates from Lat & Long to USNG.

 

Thanks


Tuesday, February 04, 2014 - 11:11:28 AM - Tim Cullen Back To Top

Good morning, Gerald:

 

The short answer to your question is "No" to both questions; however, if you will give me a day or so to experiment with it I can provide an answer afterward. Is your situation one that requires an immediate solution or can we work with it for a couple of days? Thanks for the scenario-I'm looking forward to working with it and, hopefully, providing an answer.

 

Tim


Monday, February 03, 2014 - 8:58:42 PM - Gerald Back To Top
Hi Tim. I have been searching the Internet for the answer to my dilemma.  I am writing to you because you have a lot of knowledge regarding
SQL Server's geography data type.
 
Do you know if SQL Server 2008+ has the ability to convert geographic data from United States National Grid (USNG) format into Latitude-Longitude format?  If it does, an example would be very helpful.  If not, can you recommend any third party software to do the conversion?  Thanks.
 
 
 
 

Learn more about SQL Server tools