T-SQL Tuesday #006: Tiger/Line Spatial Data

Mike C — Tue, 11 May 2010 23:11:00 GMT

This month’s T-SQL Tuesday post is about LOB data http://sqlblog.com/blogs/michael_coles/archive/2010/05/03/t-sql-tuesday-006-what-about-blob.aspx.

For this one I decided to post a sample Tiger/Line SQL database I use all the time in live demos. For those who aren't familiar with it, Tiger/Line data is a dataset published by the U.S. Census Bureau. Tiger/Line has a lot of nice detailed geospatial data down to a very detailed level. It actually goes from the U.S. state level all the way down to street, feature and landmark level. Tiger/Line data is very complete and detailed--but the best part is it's FREE. There are lots of applications for Tiger/Line, like national [U.S.] and local mapping and geocoding applications [applications that convert street addresses to (latitude, longitude) coordinates]. All this great data is distributed in the form of a ton of ESRI shapefiles.

A shapefile is basically a file format that contains shape objects like points, lines and polygons. SQL Server doesn’t natively understand ESRI shapefiles, but it also stores geospatial objects like lines, points and polygons. There some handy utilities out there for loading these files into SQL Server. Morten Nielsen has a great utility for loading shapefiles into Geometry and Geography data types at http://www.sharpgis.net/page/Shape2SQL.aspx. Because of the volume of data involved in this project (I loaded hundreds of shapefiles) I decided to roll my own small set of SSIS custom components that read ESRI shapefiles and convert them to SQL Server spatial data types [keep an eye out -- these components are scheduled to be published with source code by SQL Server Standard magazine in the near future].

The sample database can be downloaded from http://www.sqlkings.com/downloads/Tiger_Sample.zip

Download the ZIP file from http://www.sqlkings.com/downloads/Tiger_Sample.zip.
Extract the database backup file and restore it to a SQL Server 2008 instance.

In the future I’ll be sharing some code samples on the blog to demonstrate Tiger/Line data (as well as spatial data from other sources) based on this database.

Here are a couple of quick queries you can run against this sample database to view the spatial data in SSMS 2008.

SELECT *

FROM Tiger.State;

SELECT c.*

FROM Tiger.State s

INNER JOIN Tiger.County c

ON s.STATEFP = c.STATEFP

WHERE s.STUSPS = 'TX';

SELECT *

FROM Tiger.ZCTA z

WHERE z.ZCTA5CE00 LIKE '0[7-8]%';

This last one works because all of the ZIP Code tabulation areas for the state of New Jersey start with '07' and '08'. There are similar relationships between other ZCTA prefixes and their states.

Next time we'll look at using SQL Server-based spatial data with online mapping programs like Bing maps.

*For more information about Tiger/Line data visit http://www.census.gov/geo/www/tiger/tgrshp2009/tgrshp2009.html.

Search results matching tags 'SQL Server 2008', 'BLOB', 'SQL 2008', 'SQL Server', 'sample database', and 'programming'

T-SQL Tuesday #006: Tiger/Line Spatial Data