SQL Server Reference Guide

Data Types in a database system allow not only a convenient method of defining the field in a table, or to enforce integrity in the system. Sometimes data types carry a meaning by their structure — and that's the case with SQL Server spatial data.

The spatial data type deals with the location of something in three dimensional space, from a point to a particular noun. Other database systems have a geographical data type, which deals with the "round earth" problem I'll describe later. In SQL Server, the spatial data type deals not only with geography, but also geometry. With it you can record points in space, even for small objects. You work with both of these data types slightly differently, and knowing about this data type can be more important than you think.

Most editions of SQL Server 2008 and higher, including SQL Azure, include the spatial data type. I'll cover only the basics in this article - for a full treatment, check out the official documentation here: http://msdn.microsoft.com/en-us/library/bb933876.aspx You'll especially want to check that documentation if you are a true spatial "geek" with lots of knowledge of geometry and/or geography computing. Those two areas are quite detailed, so in this overview I'll show you the highlights so that you can decide if you want to read more later.

How You Can Use It

When I first heard about this data type, I wasn't certain that is was actually that important. I have worked with geographic data before, and it was just a series of numbers that I kept in separate fields. I figured that the same thing could be accomplished with geometric data - simply make three INT fields with columns X, Y and Z, and you can manipulate the numbers they hold just like any other data set. Or perhaps another, although poorer design might be to store all of the numbers in a single field as a fixed length and then parse that in the client code.

But once I began to examine this position a little closer, I began to understand the need for this data type. First, trying to actually write code that can do something useful with three separate fields is actually pretty difficult and error-prone. It also doesn't allow for good performance, and can't be indexed efficiently. So clearly a data type for these two problems would alone be enough justification for you to learn and use it.

But you might not think you're faced with these issues. You might not be storing any spatial data now, so having a type for it isn't that useful. But perhaps you should be...

There are a multitude of places where spatial data makes sense. Location-based applications abound in smartphones, tablets and other devices. Adding a simple location field to your current data sets can give them new uses, vectors and purposes.

Assume for a moment that you can now tell when a particular client orders a part, which service center that has that part is closest to that client. Pair that with your routing system and you can find out which truck can get the part to the client the cheapest, fastest, or both. That's adding value to what you already have stored, with the addition of one field. You could also locate the "best path" in that delivery route, saving time, money, and even gas (and therefore the environment). That's all goodness.

And it's not just locations that are interesting - with the geometric type of data you can also store and calculate dimensions of objects, sizes, widths and so on, which you can then combine with some clever Transact-SQL (T-SQL) to show things like whether or not a package will actually fit in that truck you're about to send to the customer's house.

Working with Geographic Data

If you're convinced you want to work with spatial data, you'll need to learn a little bit about the format the field contains. You can create a variable or column in a table that will hold the data quite simply - it's just a definition like INT or VARCHAR. I've created a sample database on my testing system called USA - which I'll fill later with some tables that represent data points for the geography for the United States. I'll explain more on that in a moment. For now, I'll create a simple table to hold some geographic data:

USE USA;
GO
CREATE TABLE GeographicTable 
    ( id int IDENTITY (1,1),
    GeogCol1 geography, 
    GeogCol2 AS GeogCol1.STAsText() );
GO

I have three columns there - one is the primary key for the table, and the second, GeogCol1 holds the geographic data. Let me stop here a moment and explain a little more about that data.

Geographic data has multiple "standards" (there's a logic-twister right there) and SQL Server supports the Open Geospatial Consortium (OGC) type of data, so that's what you should look for when you want to import maps. I'll show you how to do that in a moment - for now, make sure you keep in mind that you'll want to check the sys.spatial_reference_systems system table to see which standards you want to use as you input or import the data.

OK, with that out of the way, I'll move on to that third column. I've actually included the second  column in the definition of the third (it's OK to do that) and applied a function - the .STAsText() part to it. That's an important point - and advantage - to working with this data type.  In fact, under the covers, the spatial data type is actually a .NET, or more correctly, a Common-Language Runtime (CLR) data type. It's implemented with code. That means it can be acted on by functions - but for data to actually be placed in the field, it's not a simple insert. There are some functions that need to act on the data as well.

Most often when a data type is implemented this way, you append a "dot" or period to the end of it to work with it, but you add a colon to the front of it if you want to transform it another way. As an example, assume now that the table is created, I want to add some data to it. I'll enter in the data in the proper way, using lines to indicate some simple boundaries of a fictional place, and add the "type" of geography (the one from the supported formats I mentioned a moment ago) not as binary or numeric but as text. To do that I need to transform the text into the proper type. I can do that with the following statement:

INSERT INTO GeographicTable (GeogCol1)
VALUES (geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656 )'
, 4326));
GO

That enters a line on the fictional map. Now I'll use yet another conversion - this one using multiple lines, or a polygon - to create another shape on the fictional map:

INSERT INTO GeographicTable (GeogCol1)
VALUES (geography::STGeomFromText('POLYGON((-122.358 47.653 , -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326));
GO

OK, with that data entered, what can I do with it? Well, I can SELECT it, just like any other data:

SELECT GeogCol1 FROM GeographicTable;
GO

Interesting — I get back some numeric values. But in SQL Server 2008 (and later) SQL Server Management Studio (SSMS) I get back a "Spatial Results" tab, which is a graphical representation of the data in the table:

Figure 1

Now that's interesting. Of course, most of the time your applications are the ones that receive the data, and there are lots of libraries for .NET languages, Java, C++ and even applications that can handle showing these pictures like Excel and some web browsers.

But this is just data that I made up. To get some real data, you have a lot of options. Here in the U.S., the government provides geographic data for free. I went to this location: http://www.data.gov/catalog/geodata and downloaded a set of data for the entire United States, including states, counties and even cities.

But the download isn't in a format I could import directly into SQL Server. Remember, I have to convert the data with its supported type to get it into the database. Since the data is quite large, that's a lot of work, so I found this utility http://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools.aspx to automate the process.

When I was done, I had the entire U.S. represented in my USA database. Now I can query things like asking for State number 53 — Washington, where I work:

-- Needs the USA Database
SELECT State.StateName, State.geom
FROM State
WHERE State.StateID = 53;
GO

And I get this result:

Figure 2

And I can UNION more data to show the county and even the cities:

SELECT State.StateName, State.geom
FROM State
WHERE State.StateID = 53

UNION ALL
SELECT County.Name, County.geom
FROM County 
WHERE County.StateID = 53
AND County.Name = 'King'

UNION ALL
SELECT City.Name, City.geom
FROM City
WHERE StateID = 53

UNION ALL
SELECT
Name, geom
FROM ZipCode
WHERE Name LIKE '980%'

This shows the counties and their zip codes.

Figure 3

There are many functions you can perform to see if a certain city has a particular surface area, a line that crosses it and more. You can find a list of those functions here: http://msdn.microsoft.com/en-us/library/bb933917.aspx

Working with Geometric Data

Geometric data is interesting because it is not flat, or even perfect round. This can wreak havoc with the calculations you try to perform, called the "round earth problem", but with those functions I mentioned you can get good results from your data.

But you can also work with geometric data like points, polygons, curves and so on. While you can also look at these graphically, it's often more interesting to perform calculations on them using even more functions. I'll create another test table in my USA database:

CREATE TABLE GeometricTable 
    ( id int IDENTITY (1,1),
    GeomCol1 geometry, 
    GeomCol2 AS GeomCol1.STAsText() );
GO

This looks similar to the geographic example from a moment ago, but this time the type is geometry. I'll use the same type of insert statements as before, but with a type of "0" (more on that insert format here in Books Online, which I based these examples from):

 INSERT INTO GeometricTable (GeomCol1)
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));

INSERT INTO GeometricTable (GeomCol1)
VALUES (geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0));
GO

Now I can simply SELECT all the data to look at it graphically:

SELECT * FROM GeometricTable;
GO

Or I can do the real work as I have here - once again, adapted from the examples in Books Online:

DECLARE @geom1 geometry;
DECLARE @geom2 geometry;
DECLARE @result geometry;

SELECT @geom1 = GeomCol1 FROM GeometricTable WHERE id = 1;
SELECT @geom2 = GeomCol1 FROM GeometricTable WHERE id = 2;
SELECT @result = @geom1.STIntersection(@geom2);
SELECT @result.STAsText();

This gives me the exact coordinates for the intersection data in these objects - which can be really difficult without these functions. Just like the geographic functions, there are lots of geometric functions, listed here: http://msdn.microsoft.com/en-us/library/bb933973.aspx

Indexing spatial data

Since spatial data is its own type, it has to be indexed carefully. Not only do you have to know the way your data is laid out and queried (your data "shape") just like any other data type, but in this case you need to carefully understand how granular the data is queried. The indexes for this type of data are standard B-Tree indexes, but they break down along "tessellations" instead of alphabetical or simple numeric lines.

My friend Bob Beauchemin has one of the best series I've seen on choosing these indexes, and indeed on the spatial data type itself. You can read more from him about this here: http://www.sqlskills.com/BLOGS/BOBB/category/SQL-Server-Spatial.aspx