PostGIS Spatial Database Engine UMN Mapserver Boston Geographic Information Systems    Checkout our PostGIS in Action book.  First chapter is a free download   PostGreSQL Object Relational Database Management System
GIS Books  Home   Consulting Services  About Boston GIS   Boston GIS Blog  Postgres OnLine Journal
PostGIS in Action is out in hard-copy,
download the first chapter
and SQL Primer for free. Tips and Tricks for PostGIS
  GIS Article comments Comments Rss
Part 1: Getting Started With SQL Server 2008 Spatial: An almost Idiot's Guide

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


Lets get on to Installing
  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 New database on SQL Server 2008
  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 Shp2SQL connection dialog SQL Server 2008
  3. Point at the towns file you downloaded - Your screen should look something like this when you are done: Shp2SQL Load Towns
  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: SQL 2008 Planar View
  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:
    SQL 2008 Buffer intersection map
    SQL 2008 Buffer intersection table

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.



Post Comments About Part 1: Getting Started With SQL Server 2008 Spatial: An almost Idiot's Guide




 CommenterComment
12/26/2011 3:40:49 AMSanyushaThank you very much for this post !
11/15/2011 7:47:40 PMJude AgbroHi I was unable to download the Town data for do the exercise.

My browser didn't return anything.

This was a real shame as the website does look like just what I need
9/22/2011 5:10:55 AMDANIELIt has been helpful going through the tutorials, grat stuff!
7/28/2011 8:35:20 AMTalakisewreally helpful and interesting example!
11/20/2010 2:01:55 AM no comment
10/25/2010 3:43:04 PMLuis SanchezRequiero de este manual
10/22/2010 4:53:23 AMTTTDstudy
10/15/2010 10:53:16 AMReginaBecky,

Are your floor plans in real world coordinates? If they are not, you'll want to use the geometry datatype and set the spatial reference id to 0. You can choose between the two. just depends on what other stuff you are interfacing with.

If using SQL Server is not a requirement, you may also want to consider PostGIS as an option. It has more spatial functions built in for doing transformations -- moving geometries etc.

Take a look at our compare. The upcoming PostGIS 2.0 will also support more 3D geometry types targeting CityGML, BIM and other similar work.

http://www.bostongis.com/PrinterFriendly.aspx?content_name=sqlserver2008r2_oracle11gr2_postgis15_compare
10/7/2010 12:14:35 PMBecky FinleyLooking to see if this is the technology I'm looking for to map data points to a Floorplan.
9/23/2010 1:44:27 AM agd
8/24/2010 4:53:12 PMJohn BendixThe shape to sql loader is great and works like a dream, but as far as using the spatial tables back within product like mapguide or autocad map3d 2011 it has some issues. It may be the OSGEO FDO component with Autodesk.
7/28/2010 5:30:23 AMVenkatHi,

Where i can download SQL Server 2008 Spatial Tools .Please let me know.

Thanks and regards,

Venkat


7/19/2010 2:02:20 AMTahirI want to explore sql server 2008 Spatial features
6/15/2010 11:13:41 AMMarknevermind I see what you mean about it not loading in the database. It does load if I uncheck the CREATE Spatial index box, so I suspect its a bug introduced in the latest shape2slq.

Though I haven't upgrade to SQL Server 2008 R2, so I suppose the shape2sql.exe might have been revised to support that and in doing so not work with creating index in lower version.

Anyrate -- give it a try again, but uncheck the create spatial index box.
6/15/2010 10:57:31 AMReginaMark,

Yes I see they have changed the names of the files. We'll have to update this time permitting.

I'm guessing what was called TOWNSSURVEY_POLY when we wrote this article is now called TOWNS_POLY.

Did you run into any other issues besides that?
6/15/2010 5:27:57 AMMarkThis article seems to be somewhat out of date. The Towns.exe doesn't yield the file indicated, and having tried an alternative shp file Shape2Sql.exe, it appears to run, etc. but no new table is added to the database. Shame really, it seems like a really well written article!
4/5/2010 11:56:49 PMakhamdoThank you for all tutorials.
1/27/2010 5:45:33 PMnbentleyQuestion - I use ESRI arcmap ArcView and I want to store all my data in SQL express 2008 Spatial I have not been successful in marring the two, any help or direction would be wonderful.
Nathan
1/21/2010 2:02:36 PMAbdul WahidNeed Guide
12/26/2009 5:50:37 PMyaI need info about spatial datatypes

This Document is available under the GNU Free Documentation License 1.2 http://www.gnu.org/copyleft/fdl.html & for download at the BostonGIS site http://www.bostongis.com

Boston GIS      Copyright 2014      Paragon Corporation