The following article looks at adding Spatial Data Support to SQL Server 2005 using the free 3rd party open-source project MsSqlSpatial from the Codeplex site. By adding spatial extensions to SQL Server, you’ll easily be able to do geographical style queries such as "Which walking tracks fall within my suburb or defined radius areas?". With the latest trends set by Google Maps & Virtual Earth, managing more than simple x/y pushpins is becoming important. MsSqlSpatial is an open-source set of CLR extensions that add basic spatial support to SQL Server. The following article is based on the Getting Started Guide from the project site.

To get started, download the latest binaries for the project and save it wherever you like, I used: C:\Program Files\Microsoft SQL Server\MsSqlSpatial.

As I’m testing this with SQL Server 2005, I went ahead and downloaded the SQL Server Management Studio Express to make management of the database a little easier.

As the MsSqlServer project uses CLR extensions, and SQL Server 2005 doesn’t enable them by default, we’ll need to do this now. Load the Management Studio, connect to your database and choose the option to run a query. You’ll want to run the following query which is sourced from MSDN:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

The following screenshot shows the successful run of the above query. Just a quick aside that my computer is called MELON, so any places below where you see MELON, substitute with your computer name.

Next create a new database which we can deploy the extensions to. There is the potential to add the extensions to an existing database, but we’ll start with a fresh one in this example which I’ve named “GisDB”.

We now want to deploy the MsSqlSpatial extensions into our database within SQL Server 2005. To do this we’ll open a command window and cd into C:\Program Files\Microsoft SQL Server\MsSqlSpatial such that we can run the msscmd.exe utility. Run the following (substitute MELON with your computer name):

msscmd -deploy -server=MELON\\SQLEXPRESS -db=GisDB

If everything went well, you’ll have output like:

'C:\\Program Files\\Microsoft SQL Server\\MsSqlSpatial\\MsSqlSpatialLibrary.dll' deployed.

Congratulations, you now have a spatially enabled database! Now to do something useful with it. The "shapefile" format is the de-facto format for distributing GIS data. There are a number of free shapefiles already available which we can import into our spatially enabled SQL Server database to do some sample queries. I recommend you download a program like MapBrowser which lets you visually review the contents of a shapefile as a map. To get started with some sample data you might like to use something like US Cities - it’s really big at 2GB!. Australian’s will want to look at the Geoscience Australia site which provides a number of data sources in the shapefile format. The sample I’ll be demonstrating below is the "Sydney Special" set from the GEODATA TOPO 250K Series 3, it’s really small at around 8MB. Having downloaded the zip file from above, extract it to somewhere convenient like c:\temp. You’ll have a folder that looks something like below.

Inside the various folders there’s information from major roads to airfields, and even swamp lands! Specifically in this example we’ll look at the placenames listing within the Habitation folder, titled: i5605_placenames.shp. Open it up in a tool like MapBrowser and you’ll see a listing like below. Note that you’ll want to click on Properties > Displayed Labels > NAME in-order to get a similar screenshot to below.

We’re now going to import this shapefile into SQL Server. It turns out this is really easy, there’s a couple of ways of doing this, but we’ll use the command line tool to keep it simple. Using the utility before, we’ll create a new table called gPlaceNames from the shapefile. Note that the command will create the table for us, we should not create the table first. The command to be issued is as follows:

msscmd -server=MELON\\SQLEXPRESS -db=GisDB -table=gPlaceNames -import=shp
  -shp_filename="C:\\temp\\sydney-special\\Habitation\\i5605_placenames.shp"

If all is well, you’ll receive output similar to:

740 rows imported in 5 seconds from shapefile 'i5605_placenames.shp'.

Open SQL Server Management Studio, and connect to the GisDB database. At this point I’m going to go through a couple of queries to give you an idea of what you could potentially use. Firstly, to test that the data is all there, a simple select query will yield the 740 rows of data. You’ll note that MsSqlSpatial adds a geom column for holding the geographic data, as well as 4 columns representing a bounding rectangle for the geometry. The the_geom column is a byte representation, you could run select NAME, ST.AsText(the_geom) from gPlaceNames if you wanted to see the point as a lat/long.

You can now run some sample queries on the dataset, I’ve outlined some below.

1. Find all the places within 0.05 decimal degrees of CRONULLA

select t.NAME, ST.AsText(t.the_geom)
from ST.IsWithinDistanceQuery('gPlaceNames', 'the_geom', (
	select the_geom from gPlaceNames where NAME='CRONULLA'
), 0.05) AS q
inner join gPlaceNames as t on q.oid = t.oid

Result:

2. Find all the places contained within the bounding box of PYMBLE and MARTIN PLACE (I’ve fixed the lat/long).

select t.NAME, ST.AsText(t.the_geom)
from ST.FilterQuery('gPlaceNames', 'the_geom',
    ST.MakeEnvelope(151.14539,-33.74366,151.21144,-33.86764,-1)) AS q
inner join gPlaceNames AS t ON q.oid = t.oid

Result:

Those 2 queries will get you started, keep in mind that MsSqlSpatial conforms to the OGC Simple Features Specification for SQL Revision 1.1, which means all the Open Spatial Consortium standards for SQL are followed, you might want to look into the Simple Feature Access page for more advanced spatial sql.

Hope this write up has been useful, if you have any tips or tricks you have for dealing with Spatial data in SQL Server 2005 that you’d like to share, please comment below!