Posted by

Posted on

December 21, 2011

Posted under

Bing Maps, C#, Code, Microsoft, Programming, SQL 2008r2, Technology, Windows 8

Comments

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:

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.

Posted by

DanielHeth

Posted on

December 17, 2011

Posted under

Access 2010, Microsoft, SQL 2008r2, Technology

Comments

5 Comments

Migrating Access Data to SQL 2008r2

I recently spent some time building an Access database file full of 2010 US Census data… As an Access database, it’s not very useful since I want to use the data within as a feed for my Windows 8 Metro application. I’ll need to move this data into my SQL 2008r2 server to make it available to my application.

To do this I’ll be using the Microsoft SQL Server Migration Assistant (SSMA) toolkit I recently installed.

Upon first use, I’ll need to acquire a license for the tool as instructed by the included Readme file.

After filling out the form, they provide me a file which I will through onto a network file share and give to the tool…

ok, in the above pic, I didn’t need to specify the filename… just the directory it sat in… then the license imported correctly.

After specifying the database, I had to give it a minute or so to completely read in and load my database file… the 2010 Census file is right at 100mbs.

Then specify my SQL server, destination Database name, and my authentication method… I’m in a domain so I’ll simply use Windows Authentication since my Domain account has the appropriate level of access to the SQL Server.

Yes Please…

I do not have any pre-existing applications which connected to this Access file… thus no need to link anything.

Let the Migration begin!

At this point I could see the connection and the data migration was occurring…

back on the server, my tables are now created…

On the Migration Wizard, the data is slowly being copied over…

This is where my SQL server is running a tad slow… a lot of data being input into the database…

Wizard complete, and I can see the newly arrived data on the server.

According to the Migration report… there were Warnings… but no Errors. The Warnings mostly related to database quality… IE: some tables did not have primary keys, others had their primary key nullable value configured.

Those tables with nullable primary keys were corrected to not allow null values.

Those without primary keys were ignored since SQL server doesn’t consider that a serious problem…

That and I’m not worried about those two tables considering they were used during the “Import” process from the Census .dp files and won’t be used again.

It’s time to integrate the new found data into my Windows 8 application!

If you have any questions or comments on this, please leave them below!

Posted by

DanielHeth

Posted on

December 16, 2011

Posted under

Access 2010, Microsoft, SQL 2008r2, Technology

Comments

1 Comment

Microsoft SQL Server Migration Assistant for Access

Microsoft SQL Server Migration Assistant (SSMA) is a toolkit for quickly and easily converting Access Databases to full fledged SQL Server Databases. Exactly what I was looking for to move my newly created 2010 Census Access Database to someplace more usable for my purposes.

Download the toolkit at: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=959

And thanks goes out to the Access Team over at the Access Blog for pointing me to the right place thanks to a wonderful Bing search.

Here’s the installation of that tool…

I have plenty of space and do not like to go back and reinstall things I need… so I’ll do a complete installation.

That’s it… checkout my next article where I describe using it to actually migrate Access data to my SQL 2008r2 server.

If you have any questions or comments, please leave them below!

Posted by

DanielHeth

Posted on

December 15, 2011

Posted under

Access 2010, HACK-The-Planet, Microsoft, Technology

Comments

11 Comments

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…

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

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.

The specs will automatically change to the correct format.

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

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.

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

danielheth

Tag Archives: database

Congressional District Polygons

Migrating Access Data to SQL 2008r2

Microsoft SQL Server Migration Assistant for Access

Creating a 2010 Census Database