What Is SQL Server 2008?

Microsoft SQL Server 2008 is the first version of SQL Server to have built-in functionality for doing geographic spatial queries.

This tutorial is similar to our Part 1: Getting Started with PostGIS: An almost Idiot's Guide but written to provide a similar quick primer for SQL Server 2008 users and also just as a parallel exercise in mirroring the camps. Think of it as a big welcome to the new kid on the block who is an old family friend.

We will assume a windows environment for this tutorial since SQL Server only runs on Windows and preferably Windows XP and above (not sure if it works on Windows 2000). All our examples will be using Microsoft SQL Server 2008 Express which is a free version of SQL Server 2008. SQL Server 2008 Express is allowed for both non-hoster commercial and private use. Please note that the spatial functionality in the SQL Server 2008 Express family is just as good as in the Standard and Enterprise versions with the limitation on database size, mirroring, partitioning and some other minor things which are not spatial specific. SQL Server 2008 Standard, Web and Enterprise work on only servers while SQL Server Express 2008 works on both Servers and Workstations.

Installing SQL Server 2008 Express

SQL Server 2008 Express comes in 3 flavors:

• SQL Server 2008 Express - which is just the engine (~60-80 MB download)
• SQL Server 2008 Express with Tools - which is the engine plus the management studio express. If you don't have 2008 Studio or Express Studio already, we highly suggest using at a minimum this one. - approximately 250 MB
• SQL Server 2008 Express with Advanced Services - this is a much bigger install which includes Full-text engine and Reporting Services. ~500 MB

For the below exercise, we will assume you have downloaded at a minimum SQL Server 2008 express with Tools, or that you already have 2008 Express Studio already installed.

Some gotchas before we start:

1. You need to install .NET Framework 3.5 with SP1
2. If you have prior VS 2008 Pre SP1 you'll need to uninstall them or upgrade them to SP1 which you can get from http://www.microsoft.com/downloads/details.aspx?FamilyId=FBEE1648-7106-44A7-9649-6D9F6D58056E&displaylang=en if you are running professional or above. If you are running express family item, you need to reinstall the VS 2008 Express family item with SP1 http://www.microsoft.com/Express/Download/ and backup your IDE settings if you care about them.
3. You also need Windows Powershell installed http://www.microsoft.com/windowsserver2003/technologies/management/powershell/download.mspx

1. Download and install .NET Framework 3.5 with SP1 if you don't have it installed from http://www.microsoft.com/downloads/details.aspx?FamilyId=AB99342F-5D1A-413D-8319-81DA479AB0D7&displaylang=en. and then restart your computer.
2. If you don't have windows powershell - download and install from http://www.microsoft.com/windowsserver2003/technologies/management/powershell/download.mspx
3. Download one of the above from: http://www.microsoft.com/express/sql/download/
4. Run the executable for SQL Server Express.
5. Click on Installation link and Choose first option - New SQL Server Stand-alone installation and once its done with checks click OK. You may be forced to restart after the support file install step.
6. Under install options - choose everything. Replication is optional.
7. for SQL Server Instance - choose named instance and call it - Spatial or whatever you want. Note you can use default instance if you have no other SQL Server installs on your pc.
8. For service account - you can just run under NT AUTHORITY\SYSTEM, though for production installs, that need to interact with network, you may want to create a domain account with run as service rights and use that account.
9. Then click use same account for all SQL Server Services and pick NT AUTHORITY\SYSTEM
10. For account provisioning - we often use mixed mode which is useful if you will have non-domain access such as from a stand-alone web server.
11. In section specify SQL Server adminstrators, click add Current User.
12. At this point you may get an error if you have installed prior Visual Studio 2008 things. You will need to uninstall those or upgrade them to SP1. If you get to this this point it should be smooth sailing.
13. click click click install - go get a large cup of coffee.
14. Next - hopefully you'll get a message that says SQL Server 2008 completed successfully.

Creating a database

Once SQL Server 2008 express is installed with management tools do the following

1. On windows Start->Programs->Microsoft SQL Server 2008->SQL Server Managment Studio. If by chance you can't find it in your Programs because you have so many - its installed in C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe
2. You should see something like computernamehere\SPATIAL and now login with sa (Standard mode) or just the windows account assuming you gave current user admin rights.
3. Select Databases -> Right mouse click -> New Database
4. Give database a name and click the OK button.

Loading GIS Data Into the Database

Now we have a nice fully functional GIS database with no spatial data. So to do some neat stuff, we need to get some data to play with.

First off we have to install a loader. You can use the freely available http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx Which comes with both an ESRI shape loader and SQL Server spatial viewer. To use simply download and extract.

WARNING: One of our friends noted that the SharpGIS Loader comes up with a very suboptimal spatial grid index that will throw of queries relying on a spatial index. This is particularly an issue for data loaded into the geometry planar data type. As a result, SQL Server queries may be unusually slow and you may get a bad impression of SQL Server's performance. This wil be fixed in a later version of the loader.

Get the Data

Download data from the MassGIS site.
For this simple exercise just download Towns with Coast

Extract the file into some folder. We will only be using the _POLY files for these exercises.

Bringing in Towns As Planar

First of all, what is Planar - Planar is a class of spatial reference systems that projects a round earth onto a flat model. SQL Server 2008 supports both a Geometry (Planar) and a Geography (round-earth model). For data brought in as Planar, SQL Server 2008 does not do any validation to ensure it is in the sys.spatial_reference_systems table, and in fact SQL Server 2008 only contains spherical spatial reference systems in that meta table. So if you want to bring in as planar, as long as all your data is in the same planar projection, you should be fine. SQL Server 2008 has no mechanism of transforming data from one planar projection to another.

Those who are familiar with the PostGIS equivalent exercise of this know that MassGIS data is in Massachusetts state plane Meters (Spatial_Reference_ID = 26986 which is a planar projection) so bringing it in as Geometry works fine, but trying to push it into Geodetic we shall find is a little trickier.

Now lets move some data:

1. Launch the Shape2Sql.exe packaged in the SharpGIS tools zip file
2. Your screen should look something like this
3. Point at the towns file you downloaded - Your screen should look something like this when you are done:
4. Now click the Upload to Database

Querying the data and visualizing it

What good is spatial data if you can't drop your jaws at its fantastic beauty. So lets look at what this animal looks like:

1. Launch the SQLSpatial.exe which is also packaged in the SharpGIS tools.
2. Type in the following SQL statement:
   SELECT * FROM towns_planar WHERE town = 'BOSTON'
3. Click the !Execute button, and mouse over a geometry and you should see something like this:
4. File New Query and type this: SELECT TOP 1 geom.STCentroid().STAsText() FROM towns_planar WHERE town = 'BOSTON'
   Should toggle to the table view and give you this - POINT (230137.48055381927 888512.01928805024)
5. Now lets pick the first geometry in Boston, find the centroid, buffer the centroid 1000 meters and find all fragments of towns in the buffer. People familiar with spatial queries will recognize this as clipping geometries to a buffer.
   File-> New Query and do this: - evidentally there are some Massachusetts towns that SQL Server doesn't like thus the need for the IsValid check.

   SELECT town, geom.STIntersection(buf.aBuffer) As newgeom
   FROM towns_planar INNER JOIN
   (SELECT TOP 1 geom.STCentroid().STBuffer(1000) As aBuffer 
   FROM towns_planar WHERE town = 'BOSTON') As buf
   ON (towns_planar.geom.STIntersects(buf.aBuffer) = 1)
   WHERE geom.STIsValid() = 1

   
   Map and table views of the above query are shown below:
   
   

Bringing in Towns As Geodetic -- To Be continued

If you have data measured in degrees e.g. WGS84 longlat (4326) or NAD 83 LongLat (4269 standard TIGER US Census format), bringing in your data as geodetic is simple since 4326 and 4269 are already listed in the sys.spatial_reference_systems. A simple query confirms that -
SELECT * FROM sys.spatial_reference_systems WHERE spatial_reference_id IN(4269,4326);

To do so - you simply follow the prior steps but choose Geography (Spheric) instead.

But what if we want to bring planar data in such as MassGIS towns as Geodetic. Then you need to first transform the data which SQL Server has no mechanism for and then bring it in. We shall go over this in part 2.