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:
(more…)
Filed under: Census by Anthony on Mar 27, 2010
No Comments »
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
(more…)
Filed under: Programming, SQL by Anthony on Mar 11, 2010
No Comments »
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.
Filed under: Analysis by Anthony on Mar 11, 2010
No Comments »
