Adventures in SQL Server 2008 - Geospatial Time Travel

Well....  Maybe not time travel;)

The second in my series of SQL Server adventures is the geospatial data type.  All I have to say about this is, WOW!  For someone who is responsible for returning geocoded data this is a godsend.  

Although I knew about geospatial datatypes, I'd never really messed with them and have no experience with them.  Pinal Dave has an excellent write up on how to get started with shape files.  I wanted my learning exercise to be relevant to me so I downloaded the states by county shape files from VDS Technologies.  

Shape files are not like delimited text files that DBA's are used to.  These files required a method of uploading them.  For this task I used SharpGIS's Shape2SQL.  Loading the shape file data to a database is extremely easy with this tool.  Point to the file, select some parameters.  Here's what mine looked like below.

 I ran a simple select statement to see what geospatial data looks like.  Turns out, it looks like a binary data type.

 select *
from travisCountyEdges

 The data I uploaded is a representation of streets and neighborhoods.  At this point I wasn't really impressed.  That is...  Until I noticed an extra tab in the results pane.  The one called 'Spatial Results'.  This now gave me an image representation of the shape file we uploaded.  In my case, it's a view of Travis County in Texas.  Now we're getting somewhere.

A quick search on the MSDN library led me to all of the available methods I can use with geospatial data types.  To keep things simple I gave my self a business problem to solve.  "Give me a list of points within 1 mile of the Texas State Capitol."  If you're trying to find the geocodes for your specific area you'll need something to reverse geocode a given address.  I used this and it worked perfected for what I needed.

Use this code below and plug in the appropriate lat and long codes for your specific needs.

declare @radius float
set @radius = 1.00
declare @long float
declare @lat float
declare @multiplier float
set @long = -97.741132
set @lat = 30.272745
set @multiplier = 1609.344 -

-Gets points with in a radius
select * from travisCountyEdges 
where geom.STDistance('POINT( ' + cast(@long as varchar(10)) + ' ' + cast(@lat as varchar(10)) + ')') < (@radius * @multiplier)

There are a myriad of methods you can used with a geometry or geography datatype.  I used the 'STDistance' method and passed in a point.  But it could very well be a line, a polygon, or any other type of geometry.  

A few things to note

1. Case matters in your syntax when using geospatial methods.  .STDistance is not the same as .STdistance.  Ask me how I know that one;)
2. The distance returned from the method is in meters so you'll need to convert the @radius (which was in miles) to meters as I did above.  1609.344 meters per mile.  Google is a great thing.

The wheels in my head are spinning away looking for ways I can implement this.  I'm extremely excited about this.  I hope you are too.