Spatial Data - What Is It?

Spatial data is data related to geographic locations or geometric coordinates. Support for spatial data was built into SQL Server beginning with SQL Server 2008. How might we use this feature?

Have you ever clicked on a link for "branch office / ATM locations" on your bank's web site? Doing so may have presented you with a list of locations near you, ranked in order from the location nearest you to the location farthest away. This is one example of making use of spatial data. This is not to say that you cannot accomplish this type of functionality without database support for spatial data. But having support for spatial data built into SQL Server can make these kinds of applications much easier to code.

Beginning with SQL Server 2008, spatial data is supported with the "geography" and "geometry" data types, as well as with "spatial reference identifiers" or SRIDs. Let's explore some of the spatial data features available in SQL Server to see how we might put them to use.

Let's say we work for "Unreal Office Supply", an office supply retail chain located in San Diego, California. (This is not an actual company.) We would like to add a feature to the company's website that allows customers to locate the nearest Unreal Office Supply store. We decide to make use of the Spatial Data features of SQL Server 2008 to accomplish this.

We'll begin by adding spatial data to an existing table containing our retail store locations. We'll add a column called "GeoLocation" to our retail store location table named "Store". We'll define this column as a "geography" data type:

Next, we will need to obtain latitude and longitude coordinate data for each of our four San Diego locations, and load this into our GeoLocation column. We'll use Google Maps to obtain the data. There are various ways to do this. In Google Maps, you can select Google Maps Labs, and there you will have the option to enable the "LatLng Tooltip" or the "LatLng Marker" features. These features will provide latitude and longitude data when viewing a map.

Another option involves entering javascript into the address of the browser. An advantage of this last method is that you can then copy and paste the latitude and longitude coordinates displayed. Here's how it works: When you first enter a street address into Google Maps, the map is centered on that location. Entering the following javascript will cause a prompt to display showing the latitude and longitude of this centered location:

javascript:void(prompt('',gApplication.getMap().getCenter()));

Our four store locations, along with the latitude and longitude data obtained from Google Maps are as follows:

Now we can enter these latitude and longitude coordinates into our store location table. We will do so with the following SQL UPDATE statements:

You may be wondering about the "4326" argument in the above statements. This is the "Spatial Reference Identifier" (SRID). SRIDs refer to standard schemes of coordinate data. SRID 4326 corresponds to the World Geodetic System scheme known as WGS 84. This is the default SRID used by SQL Server when using methods related to geography data types.

Now, back to the central task we started out to accomplish: Calculating the Unreal Office Supply store nearest to our customer.

Let's assume that on Unreal's website, we are going to prompt the customer to enter their zip code to determine the nearest store. So, we are going to need some data that provides latitude and longitude coordinates for given zip codes. One place we can obtain this data is from www.geonames.org. At download.geonames.org/export/zip/ we find US.zip, a file containing exactly the data we are after.

We'll create a new table in our database named PostalCodeGeo, and import this data into the table. In our table, we will have PostalCode, Latitude, and Longitude columns which will be filled by our import. To this we will add a GeoLocation column, similar to the one we created for our Store table above. We can set the GeoLocation column based on the imported data in our Latitude and Longitude columns. To do so, we will execute the following SQL statement:

At this point, we should have all the data we need to accomplish our task. SQL Server can now easily produce a list of stores for us sorted in order of those nearest our customer. After prompting the customer to enter his zip code, will pass that zip code in parameter @CustPostalCode to the following SQL code:

Notice that we are multiplying the result of the STDistance method by 0.000621371192. This is because STDistance gives the distance in meters, and we want to convert this to miles.

We will test our code by passing a @CustPostalCode value of '92120' to the above SQL. This gives us the following result set:

Not bad, huh? Of course, this was a very simplistic demonstration. But it introduces you to some of the basic spatial data functionality we can make use of beginning with SQL Server 2008.

Below are some links for more information:

Designing and Implementing Spatial Storage (Database Engine) - SQL Server "Denali"
msdn.microsoft.com/en-us/library/bb933790(v=SQL.110).aspx

Working with Spatial Data (Database Engine)
technet.microsoft.com/en-us/library/bb933876(SQL.100).aspx

Spatial Method Reference for geography Data Type
msdn.microsoft.com/en-us/library/bb933802.aspx

World Geodetic System
en.wikipedia.org/wiki/World_Geodetic_System