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.
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.