Congressional District Polygons

Here I am again working on my House of Congress/Windows 8/Metro app contest when I’m wanting to show the individual districts each Congress person represents.  Using a Bing search I found myself back at the Census website.  This time looking at the congressional boundary files:  http://www.census.gov/geo/www/cob/cd110.html

I don’t know anything about the e00 or shp files, so I’ll be working with the available ascii files.

Each state’s zip file contains two dat files.  One dat contains the Long/Lat coordinates for various map-polygons which represent districts that are defined in the other dat file.  What I wanted was a way to tie together the dat files with the Districts table I’d already defined at the start of my development.

I need this information to be in the most covenant format for my application, therefore, I’ll be importing the information for each state into my SQL database.  I created a new table defined as follows:image

SNAGHTML6ef6126

Like many of my tables, I’ve setup an Id which auto increments and is the primary key for each row.  Next we have the linked DistrictID, the PolygonID identified within the dat file as well as the Latitude and Longitude values.

First thing was to download each individual ascii file, unblock (windows 7 “feature”), extract and rename each file… only took 20min.

Then using my import program, I follow the following sudo code to get into my database:

foreach DAT file
    read in dat and def files
    extract state name //will use this later to get district reference
    
    open database
    find state
   
    foreach dat file line
        parse Long/Lat and cur Polygon # if available
        if cur Polygon # found, then
            foreach def file line
                if cur polygon # then
                    district id = line
        if have district id and current polygon
            insert new coordinates into database

In the end we have a database called Boundaries that looks something like this:

image

If you have any questions or would like to see the source code for my importer mention it in the comments section below.

Creating a 2010 Census Database

So you’re interested in the 2010 Census results.  Well they’re publicly available at http://www2.census.gov/census_2010/03-Demographic_Profile/ and with a little bit of work, you can have yourself a fully stocked Access Database with complete 2010 Census data.

Here’s a simple step-by-step using Microsoft Office – Access 2010 to create a database and import the data for the state( s ) you want to collect.  Later I’ll show you how to turn the resulting Access 2010 file into a SQL Database for SQL 2008r2.

Download the “DPSF2010_Access.accdb” file from the url above.  Also download the various .zip files under the states your interested in.  Then open up the Access file and perform the following steps as described in their documentation…

SNAGHTML1c746f7

SNAGHTML1c7c20f

We’ll first import the demographics data… so select the non-“geo” file.

SNAGHTML1c80833

SNAGHTML1c84a61

SNAGHTML1c88b95

SNAGHTML1c8bcd2

Select the “Data Part…” specification since this relates to the Demographics file.  When you repeat this wizard for the Population (geo) file, you’ll need to choose the other “GeoHeader…” option on this screen.

SNAGHTML1c9071a

The specs will automatically change to the correct format.

SNAGHTML1c931e2

SNAGHTML1c95e10

SNAGHTML1c98b37

SNAGHTML1c9adf3

This step is important… so specify the correct primary key.

SNAGHTML1ca1fa9

Name your table… I’m importing everything, so I’ll name it the state and attach Demo onto the end and Pop on the end of the geo file.  This will give me tons of data in two different tables for each state.

SNAGHTML1ca62b1

 

Have fun with data… if you have any questions or comments, please add it below!