• Jason
• Jul 13,2008
• In: Code, Design, News

Spatial Databases

This morning I discovered the concept of Spatial Databases while researching a few things for my GPS tracking side project using a new Utah based service. I’m not sure how I missed this until now because it seems to be a hot topic among those involved with any sort of GIS (geographic information system) application.

Recently there has been a lot of discussion and focus on various location based services such as map API’s and GeoRSS but I never heard much about how people were actually storing their geographical data. I always assumed that their databases had columns for latitude and longitude using some sort of decimal datatype. Well, it seems that might not be the case because things look to be heading in a very different direction.

SQL Server 2008 Spatial Support

The announcement that SQL Server 2008 would natively support spatial data is how I learned about spatial databases. They aren’t the first to offer this kind of support but I like SQL Server so I’ll cover it more.

Basically, Microsoft will be adding two new datatypes and a handful of built in functions to SQL Server which will help developers create fast and effecient spatial databases. I took the follow descriptions of the two new types from a blog of someone who was involved with this release.

The first type, “geography”, will store points, lines, polygons, and collections of these in latitude/longitude coordinates using a round-Earth model. Most commonly-available data is given in latitude/longitude coordinates, so we expect that most people will want to use this type. Furthermore, this type will give correct computations on a true ellipsoidal model of the planet. What is the area of Indonesia? Will my flight from Seattle to Beijing take me over North Korea? Where can I store my GPS readings? This is the type for you.

A “geometry” type to support flat-earth data. For those of you who are familiar with such things, this type is our OGC-compliant offering. In some ways, this is a more specialty offering for people who need to work in projected map coordinates either for legacy or legal reasons, but this type can be used for things like interior spaces as well, e.g., “Where in this warehouse is my book?”

This is pretty exciting. One of the blogs also mentioned that indexing location data is now possible. Normally that’s a difficult thing to do with just latitude and longitude values.

As I mentioned before, there will also be a handful of functions added to perform various geographic calculations and queries. I can’t say what each one does but you can guess by some of their names.

• STRelate
• STDisjoint
• STIntersects
• STTouches
• STCrosses
• STWithin
• STContains
• STOverlaps
• STBuffer
• STConvexHull
• STIntersection
• STUnion
• STGeomFromText
• STPointFromText
• STLineFromText
• STPolyFromText
• STGeomFromWKB
• STPointFromWKB
• STLineFromWKB
• STPolyFromWKB

Examples

If you’re curious to know what your queries would look like then check out these example snippets and explanations.

Full Example

We want to be able to store all of the data on this map—the roads, the parks, and user-generated polygon—in SQL Server. Being a database, we want to be able to ask questions about the data. For example, “What are the roads that intersect Microsoft’s main campus?” A more complex example would be “What is the area of all parks within 1 kilometer of Microsoft’s main campus?”

If we take our roads data above—perhaps all of the roads for the United States—we could store them in a table Roads:

Roads(name varchar(30), location geography)

I.e., geography is a column type just like any other. We expose a pretty comprehensive set of operations on these type through a method-based interface. For example, if we have a geometry variable @microsoft that represents Microsoft’s main campus, we can find out which roads intersect it with the query:

SELECT name
FROM Roads
WHERE location.STIntersects(@microsoft) = 1

Given a similar Parks table containing all US parks, we can ask our parks question from above:

SELECT SUM(location.STArea())
FROM Parks
WHERE location.STDistance(@microsoft) < 1.0

Here’s another example that was shown at a demonstration when the announcement was made. The following query was used to produce the visualization.

-Return Census Block regions with the count of restaurants
-contained in each region for each block group in the zipcode the user clicked onDECLARE @clickedPoint dbo.Geometry;
SET @clickedPoint = dbo.Geometry::STPoint(@lat, @lon, 0);
SELECT c.id,
c.shape,
c.pop as [Population],
c.shape.STArea() as [Area],
(select count(*) from dbo.business b where c.shape.STIntersects(b.shape)=1
AND substring(sic,1,2)=’58′) as [Restaurant Count]
FROM dbo.census c, dbo.zipcodes z
WHERE c.shape.STIntersects(z.shape)=1 and z.shape.STIntersects(@clickedPoint)=1;

Other Databases

While I was digging around for more information I also found some MySQL information. It seems they implement something called OpenGIS but I haven’t investigated it much. Check out the link below for more information on this.

Conclusion

I can’t wait for SQL Server 2008 to be released. I got a preview version at a Visual Studio conference but I never installed it. I’m thinking I’ll set it up to see if the spatial features were included or not. The built in functions seem like they are going to be very useful. Hopefully there will also be huge performance gains now that location data has its own data type.

Sources

http://en.wikipedia.org/wiki/Spatial_database
http://www.directionsmag.com/editorials.php?article_id=2477&trv=1 - SQlServer 2008
http://blogs.msdn.com/isaac/archive/2007/05/16/sql-server-spatial-support-an-introduction.aspx
http://dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html - MySQL