Using SQL Server Spatial Data with .NET Applications

In SQL Server 2008, Microsoft introduced two new data types, Geography and Geometry, that support the use of spatial data. Spatial data is useful for any kind of location-based data, including locations on earth identified with latitude and longitude angles and locations identified with XY coordinates in a Cartesian grid. The new data types are implemented as SQL CLR types, which means that they go well beyond being simple buckets for storing data. In fact, these new data types have properties and methods that support a pleasantly surprising number of sophisticated location and geometric features.

Related: "Get to Know SQL Server 2012's SQL Server Data Tools"

One of the benefits for application developers when implementing spatial data types with SQL CLR code is that the objects are easily usable in .NET applications. The Microsoft.SqlServer.Types.dll file implements those objects, so all you need to do is add a reference to that DLL file and you're set. Then you can create a new SqlGeography or SqlGeometry object and use its properties and methods to create points and shapes on the globe. Creating a location on the earth is as easy as running the following line of C# code:

SqlGeography location = SqlGeography.Point(latitude,   longitude, 4236);

And of course the same features are available in Visual Basic.

In this command, 4236 is the Spatial Reference Identifier (SRID) that's part of a large group of spatial reference systems for identifying locations. This designator is also used for the commonly used World Geodectic System 1984.

There are plenty of other classes that support full use of the spatial types from .NET client applications. Two useful classes are the SqlGeographyBuilder and SqlGeometryBuilder, which support the ability to easily create different shapes that are supported by spatial data types, including LineString, CircularString, Polygon, and CurvePolygon.

SQL Server's spatial data types can be intimidating at first because of all their features that are built on centuries of human mapmaking. I've hinted at one interesting complication, SRIDs, but there are many other complex issues that you'll need to understand to use the types beyond creating nice triangles that link to different locations on earth. It's almost a requirement for general-purpose SQL Server 2012 books to include a chapter on spatial data, and those resources are fine for getting a general introduction. A few of those resources have some nice samples that integrate the spatial types with client applications and Bing Maps.

Related: "SQL Server 2012: Top New Features for .NET Developers"

However, I needed a deeper reference for a recent project that I was working on and found Alastair Aitchison's Pro Spatial with SQL Server 2012 (Apress, 2012). Aitchison has some pretty intense Geographic Information Systems (GIS) credentials and seems to have a gift for clearly explaining complex technical topics. The book provides a logical progression through spatial data topics. The first chapter is almost worth the price of admission by providing a solid background on spatial reference systems, which leads into the second chapter's introduction to SQL Server's spatial features. The third chapter provides a good foundation for the exploration of the spatial data types. After the third chapter, the book explores every nook and cranny related to spatial data in SQL Server. I was able to carefully read the first few chapters for a solid refresher and foundation, then pick and choose from the rest of the book for the things I needed for my project.

Spatial data types are crucial features for today's increasingly location-aware applications, and SQL Server has the tools to provide safe storage for the underlying data. The hooks into the .NET Framework make it easy to build client applications with the data types and are worth a look if you work with any kind of location data, including street addresses. There's a bit of a learning curve to go beyond the basics, but the good news is that Microsoft has built in a lot of the common functionality that you're likely to need.