Inside Microsoft SQL Server 2008: T-SQL Programming – Source Code and Spatial Data for Chapter 14

  • Article

In the previous post, the content for Chapter 14, Spatial Data, was presented.  This post will discuss the sample code and the spatial data associated with the chapter.

SOURCE CODE.  The Source Code for Chapter 14 - Spatial Data is located in the zip file:

TSQLProgramming20090901.zip (457,765 B)

Within the zip file the source code is contained in the file:

Chapter 14 – Spatial Data.sql (156,256 B)

SPATIAL DATA. The spatial data for the chapter contains a selection of US-based data and provides the basis for many of the code examples used in the chapter.

For downloading ease, the spatial data is archived as a single zip file and which is subsequently split into 7 segments.

This is the view of the download site at Solid Quality Mentors:

    Tuesday, December 08, 2009  5:50 PM        45456 ReadMeZip.rtf
  Thursday, November 05, 2009  3:37 AM    100431872 Sample_Data.z01
  Thursday, November 05, 2009  6:43 PM    100431872 Sample_Data.z02
  Thursday, November 05, 2009  8:02 PM    100431872 Sample_Data.z03
  Thursday, November 05, 2009  9:10 PM    100431872 Sample_Data.z04
  Thursday, November 05, 2009 10:17 PM    100431872 Sample_Data.z05
    Friday, November 06, 2009  8:26 AM    100431872 Sample_Data.z06
    Friday, November 06, 2009  1:20 PM     89577422 Sample_Data.zip
 Wednesday, October 21,  2009 10:35 AM       457765 TSQLProgramming20090901.zip

After downloading all 7 of the Sample_Data.xxx files, you open the split zip files by opening the file with the .zip extension (Sample_Data.zip). Don't try to open any of the files with the numbered extensions - WinZip (and other "zip" programs) won't recognize them as zip files.

After restoring the Sample_data.zip archive, the resulting Sample Data folder contains the companion data for Chapter 14, Spatial Data. This folder is comprised of the following files:

  • BTS_shapefile.zip
  • Data_Dictionary.rtf
  • Readme_First.rtf
  • Sample_Data_Content_Attribution.rtf
  • SAMPLE_TEXT.txt
  • Sample_USA.zip

BTS_shapefile.zip. [44,382 MB compressed] This the zip archive contains the source shapefile for Highways table in the Sample_USA database.

Data_Dictionary.rtf. This document contains the metadata describing the tables contained in the Sample_USA database.

Sample_Data_Content_Attribution.rtf. This document describes the sources of data provided with this distribution and the license agreements, where applicable.

SAMPLE_TEXT.txt. This file contains tab-delimited data, including latitude and longitude, points-of-interest data, and is used as the basis for an example in Chapter 14. This data is derived from the GeoNames database.

Sample_USA.zip. [631,582 KB compressed, 2.33GB uncompressed] Contains the SQL Server 2008 backup file, Sample_USA.bak, containing the tables and registered assemblies used as the basis for coding examples in Chapter 14. The backup file is intended to be used to restore the database, Sample_USA to a SQL Server 2008 instance.

  Tables:

  • dbo.CensusBlockGroups – [polygon] US Census Block Groups (212,942 rows)
  • dbo.Counties – [polygon] US Counties (3,146 rows)
  • dbo.CountiesFIPSCodes – [tabular] US Federal Information Processing Codes for States, Counties (3,141 rows)
  • dbo.GeoNames – [point] GeoNames points-of-interest for US (1,892,290 rows)
  • dbo.GeoNamesFeatures – [tabular] – Codes for GeoNames features (664 rows)
  • dbo.Highways – [linestring] US Highways (8,362 rows)
  • dbo.Nums – [tabular] Numbers table (20 rows)
  • dbo.States – [polygon] – US States (51 rows)
  • dbo.StatesFIPSCodes – [tabular] US Federal Information Processing Codes for States (51 rows)
  • dbo.ZIPCodes – [polygon] – US Census-based ZIPCodes (49,146 rows)

  Programmability:

  Assemblies registered to the database:

  • SQLSpatialTools – CodePlex SQL Server Spatial Tools project assembly
  • transformer – Assembly created in the Chapter 14 code examples
  • UnionAgg – Assembly created in the Chapter 14 code examples

The following images illustrate the spatial content for the tables with spatial columns in the Sample_USA database.  The State of Nevada is used for the examples, but all data extends to the full USA.

CensusBlockGroups table       Counties table                       Geonames table*

 image image image

Highways table*                    States table                          ZIPCodes table

image image image

* the data in these images are clipped to less than 5000 objects, the limit of Management Studio’s spatial display capabilities and is unioned with the outline of the State of Nevada for context.