New in SQL Server 2008 is the ability to store GeoSpatial data. Well, that and a whole host of other things but for this we are going to look at storing location data as GSP Latitude/Longitude points and determining some information from them.

To begin with, we need some data. Fortunately, the US Census Bureau has some we can use. This link has a list of all cities and their latitude and longitude. With that, we're ready to start loading data.

Note: I'm going to be focussing on the practical. If you want a really good explanation of GeoSpatial, I recommend Jason Follas' 8 part series, a good, quick read. Start here.

We'll begin by creating a table that can hold the data:

CREATE TABLE GeoSpatialTesting
( GSID int identity(1,1) primary key,
  GeogCol1 geography,
  GeogCol2 AS GeogCol1.STAsText(),
  City nvarchar(100),
  StateCode char(2)
)

The second column, GeogCol1, holds the actual data. The second column isn't really a column but displays the data as text. As you can see from the example below, SQL Server stores the Geography data type as a binary.

GeogCol1                                       GeogCol2
0xE6100000010CC0CB0C1B65913F4076A73B4F3C5055C0 POINT (-85.253681 31.567949)

The first gotcha that I discovered is that SQL Server is storing POINT data in Longitude, Latitude format, which is the reverse of the Latitude/Longitude that you find data generally stored. This caused me several problems and, if you start getting errors about Longitude must be in the range of -90 to 90, you probably have your Latitude/Longitude swapped.

You can download a zip file here. This contains both a script that will create a database and the table outlined above and load it with the census data so you won't have to. 

Now that we have the data, let's take a look at determining distance. One of the most common applications seems to be looking for places within a certain range. So this will simply return all cities with a certain number of miles of a latitude/longitude point.

-- =============================================
-- Author: Josef Finsel
-- Create date: 2008-10-16
-- Description: Sample to display cities within 
-- a radius of a point.
-- =============================================
CREATE PROCEDURE GetLocationsWithinADistance @Latitude varchar(20),
 @Longitude varchar(20),
 @DistanceInMiles numeric(10,3)
AS
DECLARE @pPoint geography,
 @pString nvarchar(100)
-- Create the string version of the point based on the latitude and longitude passed in
SET @pString = 'POINT(' + @Longitude + ' ' + @Latitude + ')'
-- Create the actual point
set @pPoint = geography::STGeomFromText(@pString,4326)
-- Used for changing to miles
declare @metres float
declare @MetreConversion float
set @MetreConversion = 0.000621371192
set @metres = @DistanceInMiles / @MetreConversion
SELECT GSID, City, StateCode,GeogCol1.STDistance(@pPoint)*@MetreConversion Miles
FROM geospatialtesting WHERE GeogCol1.STDistance(@pPoint)<=@metres
ORDER BY city
-- GetLocationsWithinADistance @Latitude='39.3311', @Longitude='-84.542897', @DistanceInMiles=20

The key thing to look at here is theGeogCol1.STDistance(@pPoint). This actually compares the two points and returns a value, in this case, the number of meters. This was another point that confused me at first, but once I discovered that's what it was, I could easily create the conversion for it.

And that's the short version. Posted mostly to get a reasonable database of points out there for use.