Now that SQL Server supports geometry and geography data types natively in the SQL Server 2008 (and later releases) relational engine, developers and DBAs are putting those data types to good use. There are many useful applications for spatial data types, and if you aren’t currently using them, you probably will in the near future.
If you’ve never seen a well-crafted application using spatial data, I encourage you to take a look at toxicrisk.com, a website that shows EPA-registered toxic waste sites across the United States. (I was surprised to find that there are two such sites in my suburban home town.)
We’ve been able to store longitude and latitude data since the inception of relational databases. But the beauty of spatial data types comes from their ease of use. Just because spatial data types are easier to use and more functional than older methods of storing geometric and geographic data, doesn’t mean it’s all strawberries and cream. For example, loading shape files (under the ESRI standard), can be time intensive and require a lot of experimentation to get it right. That’s where SQL Server 2008 Spatial Tools (SQLSpatialTools) comes in.
Written by geographic information systems (GIS) coding enthusiast Morten Nielsen, SQLSpatialTools is made up of two simple executables that handle SQL Server spatial data: Shape2SQL, which enables you to load shape files into a SQL Server 2008 and later, and the SQLSpatial Query Tool, which pulls spatial data out of SQL Server. Some key features of Shape2SQL include
- Support for single point, multipoint, polygon, and linestring shape types
- Geometry and geography data types
- Schema object handling, such as replacing existing tables and creating spatial indexes
Some key features of the SQLSpatial Query Tool include
- Using SQL queries to display the spatial results on a map
- Displaying spatial data type attributes when hovering over geometry features on the map projection, as shown in Figure 1
- Flexible renderings allowing thematic and custom background maps
These tools don’t cover every imaginable use case for working with spatial data types. However, they’ll get you up and running quickly and make this newer SQL Server feature much more accessible and useful.
The SQLSpatialTools have rather modest requirements. Because spatial data types became available in SQL Server 2008, you’ll naturally need to connect to SQL Server 2008 and later. The tools also require the .NET Framework 3.5. And because SQLSpatialTools use SQL Server CLR, you’ll need either a full install of the SQL Server 2008 client or the Microsoft SQL Server System CLR Types on the client machine on which SQLSpatialTools run. Download SQLSpatialTools.
Additional Free Resources
There are other free tools you can grab when doing spatial and mapping applications. Take a look at SharpMap on CodePlex for an easy-to-use mapping library written in C#. And if you want data, shape files, maps, and other geographic information, this is one area in which the United States and Canadian governments excel. For example, you can get almost all US public data in shape formats, such as the cartographic boundary files from each US census. I find that the MapCruzin website is a great clearinghouse for vast amounts of free data.
| Summary |
Benefits: SQLSpatialTools makes the difficult job of loading and extracting spatial data quick and painless.
System Requirements: SQL Server 2008 or later; .NET Framework 3.5; SQL CLR via the SQL Server 2008 client or Microsoft SQL Server System CLR Types
How to Get It: Download SQLSpatialTools.