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:

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:

If you have any questions or would like to see the source code for my importer mention it in the comments section below.
Share this:
Twitter
Facebook
Reddit
Email
Print
Tumblr
LinkedIn
Like this:
Like Loading...


	
Related
				
Bing
Census
database
Metro
SQL
Visual Studio
Windows 8