By: Tim Cullen | Updated: 2010-03-18 | Comments (5) | Related: > 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:
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:
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:
(
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:
Once converted into the Well-Known Binary Representation I can see the textual representation using the CONVERT function:
sTo 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 |
Next Steps
- A special thanks to the GeoCommunity and its newsletters, through which the earthquake data used in this tip was acquired
- Review information on the Types of Spatial Data in SQL Server 2008
- Read the Simple Feature Access-Part 2: SQL Option as published by the Open Geospatial Consortium
- Read how to get started with the geometry data type
- Stay tuned for more SQL Server Spatial Data Storage Tips.
About the author
Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2010-03-18
