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





Comments For This Article




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

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 (21339)

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 (21322)

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 (19444)

The new geography data types were not available until 2008


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

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



download





Recommended Reading

SQL Server 2008 Geography and Geometry Data Types

SQL Server 2008 Spatial Index Performance

Calculate the Geographical Distance Between Two Cities in SQL Server

Calculate Geographical Coding and Time Zone for an Address in SQL Server with PowerShell, T-SQL and Google Maps

SQL Server Stored Procedure to Calculate Area of a Polygon and Draw its Shape














get free sql tips
agree to terms