Covering the early stages of the federal stimulus package was frustrating. Much of the data available at the state level were just multimillion-dollar allocations going to existing federal programs or to state agencies to divvy up through competitive grants.

Back in March, there wasn’t much detail provided on how and where the money was being spent.

In mid-March, Journal Sentinel transportation reporter Tom Held received a spreadsheet from the Wisconsin Department of Transportation that showed 37 bridge projects were approved to receive the first round of federal stimulus dollars. The estimated cost: nearly $16 million.

We quickly saw an opportunity for a story.

I had prior experience working with the National Bridge Inventory, available from the IRE and NICAR Database Library, after the Minneapolis I-35 bridge collapsed in 2007. I had written several stories analyzing the safety of Wisconsin’s bridges, which rank fourth-best in the country.

I knew that I could query the database to get more information on the 37 bridges to determine if they met the federal requirements to receive stimulus dollars. To meet federal eligibility, bridges must be deemed structurally deficient or functionally obsolete by state inspectors. Structurally deficient bridges are those that are deteriorating and limited to lighter vehicles. Functionally obsolete bridges are older structures that are narrow, have low clearances or do not meet current design criteria.

I first looked for a minimum story and quickly realized that all of the bridges met this basic requirement.

But after analyzing the bridge data, I noticed the approved bridges were almost all located in rural areas of Wisconsin and most carried very little traffic, some as few as 10 cars a day. A dozen of the selected bridges carried fewer than 100 vehicles a day.

From there, Held and I spent a week making calls to state Department of Transportation engineers and local government officials to figure out why these low-traffic bridges were first in line for federal money.

The answer: The cash flowing to the chosen projects was based largely on timing.

Those 37 bridges met all of the requirements to start the flow of cash from federal coffers and were ready to go to right away, whereas other bridges weren’t quite shovel-ready. Higher-traffic-volume bridges often require additional engineering and environmental work, disqualifying them from the first round of stimulus funds, according to the state DOT.

To illustrate the situation, I drove out to a small bridge in rural Iowa County about 30 miles west of Madison. I spent the morning observing the mostly nonexistent traffic crossing the bridge. It resulted in this anecdote that ran in a story titled “Rural bridges targeted,” on March 29:

It took three seconds for the teal GMC truck to rumble over the River Road Bridge in northeast Iowa County. About 15 minutes later, the same truck crossed again.

Friday morning, three other vehicles traveled the concrete bridge, which handles 10 cars a day on average.

Despite the low traffic volume, the Town of Arena will get about $426,000 to replace the little-used bridge this summer. It's one of 37 that will be paid for with federal stimulus money approved this month by Wisconsin lawmakers.

A vast majority of the Wisconsin bridges awarded construction money in the first wave of federal stimulus funding carry fewer than 1,000 vehicles a day, a Journal Sentinel analysis of federal bridge inspections shows. And many bridges carry significantly fewer than that. The 37 bridges average 568 vehicles a day.

These mostly small, rural bridges will receive $15.8 million, while high priority larger bridges with daily traffic counts approaching 60,000 vehicles were ineligible for funding in the first round.

The 40-foot River Road span serves as the only link over Blue Mounds Creek to a state Department of Natural Resources boat landing on the south bank of the Wisconsin River. Traffic over the bridge is all for recreational use - canoeing, fishing or lodging at a private campground, Town Chairman Dave Lucey said.

"I was surprised as anyone when I got the call that the bridge was going to be fixed," Lucey said at town hall, housed in a sheet metal building. "I can tell you that bridge is a low priority for us."

Data and mapping

The database work for this story was straightforward. I first imported the National Bridge Inventory data into a Microsoft Access database. From there, I joined the master NBI table to the table of approved projects provided by our state Department of Transportation.

One challenge was that a few of the unique IDs the state provided were incorrect, so I had to do some massaging to isolate the right structures. Once I was confident I had identified the correct 37 bridges, I began analyzing the quality ratings and average daily traffic counts.

After seeing the low traffic counts, I began to compare them to the other 1,200 bridges that were technically eligible for federal stimulus money but weren’t shovel-ready. I found the average traffic count for the other eligible bridges was about 2,550 cars a day, or more than four times as many vehicles as the projects approved by the state.

Using ESRI’s ArcView geographic information system software, I geocoded the bridges and color-coded them based on average daily traffic count, which became the basis for a graphic that was published in print and online.

On the Web, we launched a searchable database of the 2,020 structurally deficient and functionally obsolete bridges in Wisconsin. Readers can search the database to see the location, age, average daily traffic and sufficiency rating for each bridge.

After the story ran, U.S. Rep Paul Ryan, R-Janesville, referenced our story during a budget meeting on the House floor in trying to make a point that the stimulus money was being squandered.

The story was featured on IRE’s Extra! Extra! and on ProPublica.org. This article is a good example of how reporters can get a high-impact story from creative thinking and basic data analysis.

Ben Poston is the computer-assisted reporting specialist at the Milwaukee Journal Sentinel. He joined the newspaper in 2007 after earning his master’s degree at the Missouri School of Journalism, where he worked as a data analyst at the National Institute of Computer-Assisted Reporting.