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:
Continue…

Essential SQL Queries (To Me, At Least)

For a session of five-minute “lightning talks” at this week’s 2010 Investigative Reporters and Editors conference in Phoenix, I contributed “Five Essential Queries for SQL Server.” Aside from the basic SELECT statement, these are five techniques that, at least for me, either solved a tricky problem or made coding life more efficient. They came to me after some trial and error or from using the coder’s best friend, Google.

I realize that many journalists prefer the open source (free) MySQL to Microsoft’s product, so I’ve replicated the five queries below in MySQL syntax. You can download script files for either syntax here:

Five essential queries (MS SQL Server)
Five essential queries (MySQL)

Feedback and your ideas are welcome. Here they are:

1. Create a temporary table with identity column.
Temp tables are handy for storing and manipulating data when you need a table but don’t want to make it part of your actual schema. In SQL Server, the table variable is held in memory and disappears once the query finishes executing.
 

DECLARE @tmp TABLE (
   id INT IDENTITY(1,1), 
   FirstName VARCHAR(50)
   )
 
INSERT INTO @tmp (FirstName) VALUES ('Bob')
INSERT INTO @tmp (FirstName) VALUES ('Joe')
INSERT INTO @tmp (FirstName) VALUES ('Sally')
 
SELECT * FROM @tmp

Continue…

‘Trouble on the Tray’ Wins EWA Award

Good news for our USA TODAY team that researched, reported and wrote the “Trouble on the Tray” series on school lunch safety: The Education Writers Association yesterday named it a winner in the 2009 National Awards for Education Reporting. The series — reported by Blake Morrison, Peter Eisler and Elizabeth Weise with data analysis by yours truly — received first prize in the “Large Media — Investigative Reporting” category.

I’m giving a talk this week at the IRE Computer Assisted Reporting conference on how we acquired and analyzed the federal data that helped fuel the story.

Major stories in the series include:

Schools in the dark about tainted lunches
Why a recall of tainted beef didn’t include school lunches
Fast-food standards for meat top those for school lunches
26,500 school cafeterias lack required inspections

Our series spurred congressional calls for reforms to USDA policies, and in February the agency announced tighter requirements on companies that supply food to the National School Lunch Program, including stricter testing of meat.

Minkoff, Data Delvers and Yours Truly

Michelle Minkoff, perhaps the hardest-working journalism student I’ve ever encountered, for the last few months has been writing up a series of interviews with hacker-journalists and newsroom data nerds at her web site. Her subjects include include designers, coders and data lovers of all stripes. Among them are Pulitzer winner Matt Waite of PolitiFact fame, my Gannett colleagues Gregory Korte and Matt Wynn, and the St. Paul Pioneer Press’s Mary Jo Webster, whom I worked with for several years at USA TODAY.

Now add me to the list. Michelle interviewed me right after one of this winter’s east coast blizzards, and my cabin fever shows in the sheer verbosity of my responses. But it was fun reliving my early days — when I discovered the power of merging data and reporting. Here’s one quote:

A reporter in the newsroom came to me and said, “Hey, it would be really good if we could figure out what the most valuable properties are in the city of Poughkeepsie. And I thought to myself, “You know, this might be a good opportunity for me to go and make friends with the IT guy over in City Hall.” I went over and visited him, he was down in the basement of City Hall, in the computer room. Back in those days, they all had big mainframe computers in an air-conditioned room.

Actually, what I first did was I went to the tax assessor’s office, and I said, “I want a list of all the properties in the city of Poughkeepsie and how much they’ve been assessed for.” And they pointed me over to the corner where there were these big books filled with computer printouts, and they said, “Well, all the numbers are there, and you can just start copying them down.” And I thought to myself, “If they were printed on this piece of paper that looks like computer paper, then certainly they are in a computer somewhere in this building. And I can get that data on a disk that I can bring over and put into my computer.” And that’s how I really started figuring out that we can do computer-assisted reporting by going to the government and getting data.

That’s what I did. I went to visit that guy in City Hall, and I said, “Look, I know you’ve got a file on your computer. I’d love to have you put it on this floppy disk for me.” And he had to check with the local attorneys, and get their permission, and I called up a sunshine advocate in New York state and got him to weigh in, and they agreed that, “Yeah, the law says we can do this.” The next thing I know, I had that data on the computer and was going through it in Paradox. We wound up writing a couple of stories about different properties.

A hat tip to Michelle for a smart way to gain insight into our slice of journalism.