Monday, March 29, 2010

SQL Server Geospatial Part 1 – A Brief Introduction To SQL Server 2008 Geospatial

One of the coolest features of Microsoft SQL Server 2008 is its native handling of geospatial data. Spatial data can be stored, indexed, and manipulated using an array of useful functions. There are also a number of third party and open source tools that work together to help build incredibly powerful mapping solutions. In this series of posts I’ll explore some of that functionality and talk about some of the tools at your disposal.

SQL Server implements two spatial data types: geometry and geography. They are quite similar and both store points, lines, polygons or multiples of these shapes. The important difference however is that where the geometry type is a planar type and represents shapes on a flat earth using an x,y coordinate system, the geography type represents shapes projected onto a spheric earth according to a spatial reference system. A great primer on the two data types is here.

If you want to start experimenting with spatial data in SQL Server, there are a number of excellent (free) resources out there to help you get started. Shape2SQL is the first of these tools that you’ll need to download, and allows you to import shapefiles into SQL Server. An ESRI Shapefile or just ‘shapefile’ is one of the most common file formats for geospatial data. There is a wealth of free data available on the Interwebs, for this example we’ll use the 2000 census ZIP code boundaries file for Illinois.

Open Shape2SQL and select the shapefile you just downloaded, a target database, and a target table. Leave the rest of the options default and click ‘Upload to Database’.

image

You should now have a table in your database named ‘zt17_d00.’ Open SQL Server Management Studio and run the following query against your target database.

select * from dbo.zt17_d00 where name in ('60606','60601','60607','60603','60602')

image

This selects a few of the zip codes in downtown Chicago. The shapes themselves are stored in the column ‘geom,’ the zip code is in ‘name.’ In your results pane, you should see a ‘Spatial Results’ tab. The results should look something like this.

image

Hey presto, you’re now working with spatial data in Microsoft SQL Server. Experiment with some of the functions available for the geometry data type. One of the functions available is STIsValid(), which determines whether a polygon is a valid shape. For example, if you run the following query you’ll notice that zip code 62313 is not a valid geometry shape.

select geom.STIsValid() as ValidGeom, * from dbo.zt17_d00 order by 1

image

Thankfully, there’s a built-in function that helps you repair invalid geometry instances, STMakeValid(). If we wanted to sort the zip codes by area, we can first make the shapes valid and then use STArea() to determine their size. The following query shows both how to accomplish that and how to combine multiple geospatial function calls.

select geom.MakeValid().STArea(), * from dbo.zt17_d00 order by 1 desc

image

As I mentioned earlier, geometry is only one of two SQL Server data types - the other being geography. In the next post we will talk a little about converting between these data types, potential pitfalls, and share some handy tips.