Tracking Census 2010 Participation

This week, my USA TODAY colleague Paul Overberg and I launched a simple database application to display the Census 2010 mail participation rates for states, counties and 27,000 cities and towns.

Through late April, the Census Bureau is updating the data each weekday. They’ve launched their own interactive map and offer the data in CSV or double-pipe-delimited format (a new one for us). We didn’t want to duplicate the bureau’s map, but we did want to offer something Census isn’t: the ability to quickly find and rank geographies.

Here’s more on how it came together:

Our database team is keen on moving more of our apps away from Flash, so I let this small-scale project serve as a learning exercise. While Paul focused on using SAS to parse the text file and convert geography names to AP style, I built a SQL Server stored procedure to extract the relevant geographies into a small table. (We keep our production-environment data sets as small as possible for best performance.)

The SQL was the easy part, as was a basic HTTP handler in ASP.NET/C# to pass and return calls from the page to the DB. Not much sweat.

Then the learning fun happened. I needed HTML/CSS for the table, search buttons, drop downs and wrapper. And I wanted to “appropriate” JavaScript my coworkers Brad Heath and Jack Gillum had written for similar pages.

The CSS was a bear. I thought I could just modify a presentation Jack had built around NCAA finances, but soon I realized the interactivity and display were different. And our design folks requested style changes. So, I ended up using about 50% of the code from Jack’s project and engineered the rest. The centerpiece is a table with Div elements for the bars displaying the percent of forms mailed back. When the handler draws the table HTML, the bar’s Div width in pixels gets set based on the value in the field. Jack suggested the idea, and it was a discovery for me.

While doing that, I also was enjoying new adventures in JavaScript. I’d never worked in it before, and the code looks a lot like C# but has enough syntax differences to keep me on my toes. After many visits to Google and sites like this, my buttons, drop downs and display worked fine. I ended up using a snippet of JQuery as well to set the default values in my drop downs.

In all, I spent about a day on the DB back end and handler plus a week on the HTML/CSS/JavaScript. It’s a small-scale presentation, but the learning benefits were more than worth the sweat.

Leave a Reply

Your email address will not be published. Required fields are marked *