Interested in about “a billion” (approximately) Spatial Data elements for SQL Server 2008? A step by step getting U.S. Census TIGER/Line Shapefiles into SQL Server on the cheap (i.e. free)

Tuesday, July 07, 2009

Interested in about “a billion” (approximately) Spatial Data elements for SQL Server 2008? A step by step getting U.S. Census TIGER/Line Shapefiles into SQL Server on the cheap (i.e. free)

Adventures in .NET - SQL Server 2008: Importing TIGER/Line Spatial GIS Data

“If you’ve been itching to use the spatial data and query features of SQL Server 2008 but haven’t figured out a way to get data to use, you’re in the same boat I was. Until today.

I have a whole slew of address information, but no way to convert that into latitude/longitude values to do any meaningful spatial querying – I was pretty much limited to Zip Code statistics only. Then I remembered that the US Census Bureau puts out a freely available database of everything from roads to highways to railways to geological and political borders with their coordinates called TIGER/Line. …

It would be quite a bit of effort to try and parse the TIGER/Line data yourself, so the use of a utility greatly helps here. Download SQL Server 2008 Spatial Tools to follow along with this blog post, although Microsoft has a sponsored project on Codeplex which I haven’t used called SQL Server Spatial Tools as well.

…

Finally we can see what we’re working with! It’s not the prettiest thing in the world, but it helps you quickly visualize the spatial results of your query.

Using functions like STDistance, you could do things such as finding all customers within a range of a certain latitude/longitude point, which I hope to explore in a future blog post. But once you overcome getting data into your system, it’s just SQL queries from here on out.

Oh, and did I mention that this is all free? :-)

”

SharpGIS - SQL Server 2008 Spatial Tools

“The SQL Spatial Tools consists of two tools to make it easy to get experience with the new spatial capabilities of SQL Server 2008 (click for more info) :

Shape2SQL : Uploads ESRI Shapefiles to Microsoft SQL Server Spatial.

SqlSpatial Query Tool : Queries MSSQL Server 2008 and displays geometry output on a WPF-based interactive map.

Requirements:

A SQL Server 2008 to connect to (DUH!)

Microsoft .NET 3.5

Either "SQL Server 2008" or "Microsoft SQL Server System CLR Types" installed on client machine.

”

U.S. Census Bureau - 2008 TIGER/Line® Shapefiles

“The TIGER/Line Shapefiles are extracts containing selected geographic and cartographic information from the Census Bureau's MAF/TIGER^®(Master Address File / Topologically Integrated Geographic Encoding and Referencing) database. The MAF/TIGER database was developed at the Census Bureau to support a variety of geographic programs and operations including functions such as mapping, geocoding, and geographic reference files that are used in decennial and economic censuses and sample survey programs. Spatial data for geographic features such as roads, railroads, rivers, and lakes, as well as legal and statistical geographic areas are included in the product. Other information about these features, such as the name, the type of feature, address ranges, and the geographic relationship to other features, also are included. The TIGER/Line Shapefiles are made available to the public for no charge and are typically used to provide the digital map base for a Geographic Information System or for mapping software.

The 2008 TIGER/Line Shapefiles contain current geographic areas (boundaries of governmental units as of January 1, 2008), Census 2000 vintage geography, and Economic Census geography. The shapefiles also contain some additional feature updates reported in the Boundary and Annexation Survey (BAS). All counties have now been realigned as a result of the MAF/TIGER Accuracy Improvement Project (MTAIP) and are included in the 2008 TIGER/Line Shapefiles. For more information about the MTAIP please refer to the 2008 TIGER/Line Shapefile Technical Documentation available in [PDF] or TEXT.

The TIGER/Line Shapefiles contain attribute data only and do not include mapping software. They are designed for use with geographic information system (GIS) software. The TIGER/Line Shapefiles do not contain demographic data from any census or survey, but do include the geographic entity codes, which provide a link between the Census Bureau’s demographic data and the TIGER/Line Shapefiles.

…”