IRE

In this free-read article from Uplink, Gavin Off of the Tulsa World shows how he used local school crime report data to uncover a rise in incidents at local elementary schools. The article provides useful tips for journalists who’d like to cover school crime in their own areas.

After several years of negotiations, the NICAR Database Library has updated its copy of the FAA Enforcement Information System. This useful database documents cases where airlines, airports and pilots are accused of breaking FAA Regulations — examples include drug-test failures and alcohol abuse on the job.

Only two months into 2009, there have already been two major airplane crashes that have attracted heavy media attention. When covering such events, reporters have the opportunity to get at the “how” and “why” of the story by using some of the Federal Aviation Administration’s public databases.  While many of these databases are freely available on the World Wide Web, the Database Library has recently updated a key dataset that, quite frankly, was very difficult to get. The FAA’s Web site — normally a good source of government information — does not serve the enforcement database.

It’s important to note that the newest copy of this dataset has plenty of information redacted — almost six in 10 records have the accused individual’s name expunged. However, most of the records contain information about the airline involved, the location of the incident, what rule was allegedly broken and what the FAA ultimately did with the case.

Despite the data’s limitations, this is the sort of thing that makes the Database Library a useful resource for IRE members. Independently seeking this database through the Freedom of Information Act, and cleaning it up so it can be used in a modern database manager, would take up an enormous amount of time. We are proud to be able to serve our members by making data much, much easier to obtain.

– Jeremy Milarsky

As IRE has grown and evolved, so have the services offered to our members. Just a few years ago, one of the most common requests of our Database Library was a conversion of electronic information from tape to disc. Nowadays, Database Library staffers are working with open-source database technology, Web scraping and dynamic mapping.

Recently  we reached yet another milestone in the services we are offering our members: the Database Library can now create interactive maps for news Web sites connected to investigative projects. In our first venture, we worked with reporter and IRE board member Phil Williams and created a Google maps mashup for WTVF in Nashville, allowing site visitors to explore how gas station ownership in the Nashville metro area has become more homogenous.

Shortly after finishing the project for Williams, Database Library staff members completed an interactive map of daycare inspections in metropolitan Southeast Florida for  WFOR, Miami’s CBS 4. IRE member and WFOR I-Team reporter Stephen Stock worked on the story.  Stock and his co-workers painstakingly entered more than 7,000 inspection records into Microsoft Excel for analysis.

Projects like this are a continuation of IRE’s work with mapping. Maps provide journalists with a powerful tool to help their audience see and feel the story. By giving audiences for WTVF and WFOR a way to work with the information that provided a foundation for their story, the stations definitely “showed” rather than “told” their stories.

“The work of everyone at NICAR was wonderful in helping me to visualize the issues and to provide a means for our viewers to do their own research,” Williams wrote in an email to IRE staff.

“I showed off the map to our morning editorial meeting and the room was ABUZZ…they were knocked off their seats,” Stock wrote in a message to IRE staff. “This is a hard crowd and they were wowed! Way to go!”

Since both projects launched Nov. 18, IRE’s mapping server has logged more than 348,600 hits (as of Dec. 1).

In addition to making available a collection of more than 40 federal databases to IRE members, we can assist with:

1) SQL queries and general database analysis;

2) Web scraping of some public information;

3) Mapping out data with GIS tools to explore the “where” of your story;

4) Parsing those stubborn text files into a database-friendly format.

For more information, contact the Database Library at 573-884-7711 or visit data.nicar.org.

– Jeremy Milarsky, Database Library director

NICAR’s copy of the Housing Mortgage Disclosure Act dataset for 2007 has been updated. This dataset, maintained by the Federal Financial Institutions Examination Council, provides information about property loans in the United States, including, for each loan application:

• the race, ethnicity and gender of the applicant
• how much money was requested in the loan
• the annual income of the applicant
• if the loan was considered “subprime” — defined in this dataset by being three points higher than the prime rate — how much higher its interest rate was
• The U.S. Census tract for the property location — highly useful for mapping

Reporters have used HMDA data for years to report housing trends with authority; in the current economic climate, that effort has become all the more important. Did lending institutions in your state grant fewer subprime loans than last year? By how much? This dataset can help answer those questions. Please contact the Database Library with any questions.

A great deal of the news coverage surrounding this week’s Democratic National Convention touched on whether the party could recover from a close primary election between Sens. Hillary Clinton and Barack Obama.

Articles focused on the desire from the Obama campaign to reach out to Clinton supporters, and whether a rift remained in the party.

But ABC News decided to dig a little deeper. They reasoned: campaign finance records are, of course, public — can we use them to take a look at Clinton supporters who may not be so enthused with Obama’s nomination?

ABC Reporter Marcus Baram knew that both Clinton’s donor list and the most recent electronic filings from the Obama and McCain campaigns could be cross-referenced to get an idea of how many donors were either sticking with the new Democratic candidate or shifting over to support McCain.

Although on the surface that may be straightforward, the data for processed FEC filings is kept in a slightly different format than the more raw electronic filings which are posted on the FEC’s site shortly after they are submitted by the campaigns.

Needing to save time, Baram contacted the NICAR Database Library for assistance. The result is an insightful look at how a reporter following the money showed, rather than simply told, how some of Clinton’s donors were switching sides.

- Jeremy Milarsky

By Julie Karceski
NICAR Data Analyst

One year later, and we’re back where we started.

The one-year anniversary of the Minnesota bridge collapse, Aug. 1, sparked a flurry of articles — more than 100 in the anniversary week — reflecting on the event.   Many revealed that bridges in certain states are in worse condition now than one year ago.

Newspapers across the country published stories with updates on the victims and investigations into what caused the collapse.  Many journalists also put a local angle on the anniversary by investigating bridges in their own communities and bringing public safety into question.

Data on bridges, including details such as age and inspection dates, were widely used in these stories.

Pam Sohn from the Chattanooga Times Free Press used a combination of data from the Tennessee Department of Transportation and NICAR for her story published Aug. 1. This was not the first time she had written about bridge safety. Two years ago, her paper investigated bridge safety in Tennessee with data from NICAR.

“About one year after our story, the collapse occurred,” she said. “We looked back and localized it using the data we had before.”

This year, Sohn wrote about the status of bridges in Chattanooga and the planning for repairs. Her story revealed that five out of Tennessee’s twenty – or one in four – highly-trafficked, “structurally-deficient” bridges are located in Chattanooga.

In preparation for increased attention on bridge safety, the Tennessee Department of Transportation released PDF documents on bridge inspections in the state.

“This time the Tennessee DOT did a little homework of their own- a preemptive strike,” Sohn said. “It showed where the worst bridges in the state were.”

Ariel Hart of the Atlanta Journal-Constitution also emphasized the importance of data in writing a story about bridge safety.

“You can’t just say it’s been a year since that bridge fell down,” Hart said. She published a story detailing how bridge ratings in Georgia have gotten worse in the past year, despite heightened attention on bridge safety.

Hart got the data for her story from the Georgia Department of Transportation. The Atlanta Journal-Constitution’s computer-assisted reporting specialist, Megan Clarke, calculated the bridge numbers for the state and gave Hart a few story angles to work from.

“Data made it a news story,” she said. “A big part of the picture has gotten worse. We have more structurally deficient bridges this year. That led us into the background about the budget crunch.”

Darryl Isherwood from The Morning Call in Allentown, Pa., scrolled through the 25,000 bridges listed in the Pennsylvania DOT Web site to provide context to his bridge anniversary story. He, like Hart, was struck by the fact that the number of structurally deficient bridges in the state had risen over the past year.

“One hundred more bridges are listed,” he said. “It made for an obvious story. It was just a matter of calling a few people.”

Isherwood used a spreadsheet do the calculations and spoke with state officials about the budget problems responsible for the increased number of structurally-deficient bridges.

“Having that data there, I was able to find a couple of bridges to focus on,” he said. “The data just made the numbers pop.”

I am proud to announce that purchases of datasets completely online, without the use of a phone or fax machine, is now available to members for most of the data we sell at the NICAR Database Library.

The new online store can be found via links on our main site at www.ire.org, or you can go directly to data.nicar.org.

To enforce the long-standing policy of only allowing journalists who are IRE Members to purchase data from the library, the online store requires visitors to register for the site. (It’s worth the effort; you can save it for accessing new features as we continue to improve our site.)

Within 24-48 hours, IRE staff members will verify whether a registrant is, in fact, an IRE Member.  Members in good standing will then be able to make purchases with a credit card using our new shopping cart.

On a personal note, it is my hope that this new Web application will enable us to better serve our members, who work daily in an ever-challenging environment where every minute counts.

Of course, you’ll still be able to purchase data the old fashioned way — and we certainly encourage people to call the library for our analysis and consultation services. But this new feature will hopefully be a boon to those members interested in purchasing datasets quickly.

Questions? Please feel free to email (datalib@nicar.org) or call us
573-884-7711.

- Jeremy Milarsky