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.
Example: Calculating Distance
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:
Unreal Office Supply Store #1
7610 Hazard Center Drive
San Diego, CA 92108
(32.770370205902836, -117.15813875198364)
Unreal Office Supply Store #2
324 Horton Plaza
San Diego, CA 92101
(32.71366227336692, -117.16243028640747)
Unreal Office Supply Store #3
415 Parkway Plaza
El Cajon, CA 92020
(32.804749, -116.967685)
Unreal Office Supply Store #4
4545 La Jolla Village Dr
San Diego, CA 92122
(32.869802, -117.212298)
Updating our retail store location table with Spatial Data
Now we can enter these latitude and longitude coordinates into our store location
table. We will do so with the following SQL UPDATE statements:
UPDATE Store
SET GeoLocation = geography::Point(32.770370205902836, -117.15813875198364, 4326)
WHERE StoreID = 1
UPDATE Store
SET GeoLocation = geography::Point(32.71366227336692, -117.16243028640747, 4326)
WHERE StoreID = 2
UPDATE Store
SET GeoLocation = geography::Point(32.804749, -116.967685, 4326)
WHERE StoreID = 3
UPDATE Store
SET GeoLocation = geography::Point(32.869802, -117.212298, 4326)
WHERE StoreID = 4
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.
Setting up Zip Code Spatial Data
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:
UPDATE PostalCodeGeo
SET GeoLocation = geography::Point(Latitude, Longitude, 4326);
SQL Distance Query
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:
DECLARE @CustGeoLocation geography
SET @CustGeoLocation = GeoLocation FROM PostalCodeGeo WHERE PostalCode = @CustPostalCode
SELECT StreetAddress1, City, State, PostalCode, Phone, GeoLocation.STDistance(@CustGeoLocation)
* 0.000621371192 AS Distance
FROM Store
ORDER BY GeoLocation.STDistance(@CustGeoLocation)
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