Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Geography Data Type


By:   |   Updated: 2010-03-18   |   Comments (5)   |   Related: More > Spatial Data Storage

Problem

We are working on the migration to SQL Server 2008 and have geospatial data that we would like to move over as well. As part of our application we house information on locations across the globe. Which data type should we use?

Solution

In a previous tip we discussed the geometry data type. The geometry data type can be used to house planar, or flat-earth, data where the curvature of the earth has no effect on distance calculation between points. If your application houses data whose points are more than a mile or so apart then the geography data type is the data type to use.

The Geography Data Type
Like the geometry data type, the geography data type is housed as a binary representation of the coordinate, known as the Well-Known Binary Representation, a data type created through the .NET Common Language Runtime (CLR). But the geography data type, unlike the geometry data type, requires the specification of a Spatial Reference System. A Spatial Reference System is system used to identify a particular coordinate system and is specified by an integer. Information on available Spatial Reference Systems available in SQL Server 2008 can be found in the sys.spatial_reference_systems catalog view:

working on the migration to SQL Server 2008 and have geospatial data that we would like to move over

The specified Spatial Reference Identifier must be one that is supported and listed in the sys.spatial_reference_systems view. If one is specified that is not supported the following error is returned:

Spatial Reference Systems available in SQL Server 2008 can be found in the sys.spatial_reference_systems catalog view

Example Using the Geography Data Type

To demonstrate the use of the geography data type I have created a table in the MSSQLTIPS database called EarthquakeData. A newsletter entitled SpatialNews Daily is sent out daily and included in the newsletter is data on all earthquakes in the world for the previous days. I will be housing the information on the world's earthquakes in that table.

Included in the earthquake is the date and time (UTC), latitude, longitude, depth, and magnitude. In both the geometry and geography data types, there are additional values, or attributes, that can be housed in the data type. These are known as "m" and "z" ordinate values. What is housed in these values depends on the application in which the data is used. In this case, we can house the coordinates, depth, and magnitude of the earthquake in the field by making use of he m and z ordinate values. Below is the definition for the table:

CREATE TABLE dbo.EarthquakeData
(
     EarthquakeID INT IDENTITY(1,1)
          CONSTRAINT PK_EarthquakeData_EarthquakeID PRIMARY KEY
     , EarthquakeDateTime SMALLDATETIME NOT NULL
     , EarthquakeDescription VARCHAR(250) NOT NULL
     , EarthquakeInformation GEOGRAPHY NOT NULL
)

There are some interesting points to remember about using the geography data type. The first point is that since the geography data type is implemented as a .NET CLR data type, the methods are case sensitive and an error will be returned if a method is called otherwise. The second point is that the methods must be called via geography::method since it is a static method. Finally, and perhaps most important, is the order in which the coordinates are entered in the data type. The functions used for the geography data types view coordinates as {X,Y} coordinates. In order to properly document the locations of the earthquakes I will need to present the data as longitude first, then latitude, which is different from how most people think of earth-related coordinates. As for the Spatial Reference Identifier, the one that is used for the planet Earth is the World Geodetic System 1984 (WGS 84), represented in the sys.spatial_reference_systems table as 4326.

Because the data did not present itself initially in a useful form there were some modifications to be made. The main issue was that the latitude and longitude were represented as North, South, East, and West coordinates. The .NET CLR function for conversion to the Well-Known Binary Representation expects numeric values; latitudes south of the equator are expressed as a negative number and longitudes west of the Prime Meridian are also negative (according to NationalAtlas.gov). For example, north Atlanta is around the coordinate 33.9 Latitude and -84.4 Longitude; -23.4 Latitude by -57.43 Longitude is around Concepcion, Paraguay.

In my case I had to loop through each of the entries to update the table with the Well-Known Binary Representation. Here are samples of the script I used for the update:

I had to loop through each of the entries to update the table with the Well-Known Binary Representation

Once converted into the Well-Known Binary Representation I can see the textual representation using the CONVERT function:

Once converted into the Well-Known Binary Representation I can see the textual representation using the CONVERT function s

To extract the individual coordinates use the following functions:

Function Description
[Column Name].Lat The latitude coordinate
[Column Name].Long The longitude coordinate
[Column Name].M The M ordinate value, which in our case was the magnitude of the earthquake
[Column Name].Z The Z ordinate value, which in our case was the depth of the earthquake

o extract the individual coordinates use the following functions
Next Steps


Last Updated: 2010-03-18


get scripts

next tip button



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




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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, January 09, 2013 - 10:16:33 AM - Erik Back To Top

Hi Tim

I'm trying to concatenate some text to the hexadecimal value. Based in your table definition, it would be something like this:

SELECT 'The hexadecimal value of geography column is: ' + EarthquakeGeoCode FROM dbo.Earthquake

And the result will be something like this:

The hexadecimal value of geography column is: 0xE610000001148716D9CEF7D34740D7A3703D0A975EC08716D9CEF7D34740CBA145B6F3955EC0

Thanks,

Erik


Wednesday, January 09, 2013 - 6:45:44 AM - Tim Cullen Back To Top

What message are you getting when you try it?

What version of SQL are you connecting to when you try it?

You may be able to use STGeomFromWKB (http://msdn.microsoft.com/en-us/library/bb933897.aspx), although the example above is not valid when I try to convert it.


Tuesday, January 08, 2013 - 12:51:20 PM - Erik Back To Top

Hi

I'm importing geography data from c#. And I want to obtain the geography value that the quey editor gives you when you make a select. Example "0xE610000001148716D9CEF7D34740D7A3703D0A975EC08716D9CEF7D34740CBA145B6F3955EC0". I already try to cast, convert and other methods, but none of them work.

Thanks,

Erik


Sunday, September 09, 2012 - 5:57:41 PM - Tim Cullen Back To Top

The new geography data types were not available until 2008


Sunday, September 09, 2012 - 6:14:00 AM - UTS Back To Top

hi, is there any wat to use geography data type in sql server 2005?


Learn more about SQL Server tools