IRE and NICAR
Des Moines Register reporters Chase Davis and Perry Beeman spent months compiling and making sense of data for a series on air pollution in Iowa. But, with more than 1,600 polluting facilities across the state, there simply wasn’t space in the stories to mention any but the most noteworthy.
That’s where data editor James Wilkerson and digital projects editor Michael Corey came in. They developed an interactive map that allowed users to see information about the facilities near them.
"It localized the story to basically every community in Iowa," Davis said of the map.
It also gave Corey a chance to use the Google Maps application programming interface (API) for Adobe Flash. He layered a Flash movie clip with information about Iowa’s counties and polluting facilities over a Google Map, reducing loading time and allowing the use of Flash functions.
Most of the data came from the Iowa Department of Natural Resources. The most important were tables showing all Iowa facilities with permits to pollute and the emissions reports for these facilities.
The department readily provided the data, which was generally clean and came at little or no cost, Davis said. Some minor adjustments were necessary, he said, such as standardizing emissions totals that were reported in different units Davis said he used Microsoft Excel spreadsheet and MySQL database manager to make these adjustments. He also used ESRI ArcView geographic information system (GIS) to get a broad sense of the data.
As they were analyzing the data, Davis and Beeman met with department officials. "If something looked funny in the data, we could run it by them, and they would usually deliver a pretty good reason for that," Davis said.
Davis and Beeman also built their own database in Excel. They entered information from compliance files — "some of which are as thick as a phonebook," Davis said — for more than 100 facilities into what Davis called "really crudely related" tables that they created.
Most Iowa emissions data comes from facilities' self-reports, which can be inaccurate. Davis and Beeman hoped the compliance files resulting from state monitoring would provide a better picture, perhaps allowing them to quantify emissions permit violations. But they soon found that these files, too, were plagued by inconsistent reporting.
As a result, the database created from these compliance files became more of a tip sheet. "It really framed the rest of our reporting," Davis said. "Those files were the reason that we went out to the places that we went to and directed us to the people who were affected by all this."
Thanks in large part to their self-made database, Davis and Beeman were able to hone in on some of the worst facilities and locate people who could share their stories — from discerning the direction of the wind by the variety of chemical stench to breathing polluted air some believe contributed to serious health problems The Register’s stories alerted readers to some of the state’s biggest polluters and pointed out the often inadequate system for monitoring air pollution.
After cleaning the data, Davis gave the distilled Excel spreadsheets containing the most important information to Wilkerson, who created an XML application so the map could query the data. Starting with a MySQL database, he used PHP open-source scripting language to query the database, then used a PHP function to output XML.
Wilkerson said this process usually takes a couple of days “unless we decide to get fancy.” About a year ago, for example, it took more time to develop an address locator, which the pollution map uses. The Register team developed a spatial query that determined the county in which a point entered in an address box fell.
With Wilkerson’s XML in place, it took Corey between one and two weeks to develop the map using the Google Maps API for Flash.
Within the Google Maps framework, Corey said, he had a couple of choices when it came to building the map. Option one: layer polygons over the map. Each county would be a polygon, with the map calling on the Google server to geocode each point composing each polygon. "It would take literally two minutes to load," Corey said.
That’s why he chose option two: layer a Flash movie clip over the map. The map has to geocode just two points — diagonal corners – and there can be loads of information contained in the movie clip.
The Register’s graphics team already had detailed vector images in Adobe Illustrator for Iowa’s 99 counties. Corey imported these images directly from Illustrator to Flash, and, after minor adjustments to make the images fit with the Mercator projection used by Google Maps, they became the foundation of the Flash movie clip.
Using Flash gave Corey more tools to spruce up the map, such as animations and fade-ins. JavaScript could do some of the same things, Corey acknowledged, "but Flash makes it very easy to do a lot of the more polished Web presentation."
In the finished map, clicking on a county or facility triggers a slick animation and a chemical-by-chemical breakout showing the number of tons released of each of six pollutants regulated under the Clean Air Act and considered public health threats. Links provide more information about each chemical. A table below the map shows similar information for the selected area by using AJAX (Asynchronous JavaScript and XML) to retrieve data without reloading the page.
Only a small portion of the emissions sites — the ones releasing high amounts of pollution — are visible when viewing the map at its full extent. But after selecting an individual county, more pop up. "That was mostly to keep it from being too overwhelming," Corey said.
The mid-June series began: "The air across Iowa is so polluted that the state is perilously close to violating new federal limits aimed at protecting human health. Yet Iowans have no way of knowing what chemicals they are breathing because of a limited — and often inaccurate — system of monitoring pollution statewide, a Des Moines Register investigation found."
Over the next few days, stories by Davis and Beeman outlined Iowa’s pollution landscape and its flawed monitoring system, also offering possible solutions. Meanwhile, the interactive map gave readers the chance to see if they might be at risk.
Davis, Corey and Wilkerson all said they were pleased with how the map helped tell what Corey called an "inherently geographical story."
"We wouldn’t have been able to do this if we didn’t have this map," Davis said. "We sort of presented the general conclusions, and then people could use the map to drill down and see how those apply to their local areas. I think that really gave a sense of perspective that would have been very hard to get otherwise."
Chris Hamby is a master’s student at the Missouri School of Journalism and data analyst in the IRE and NICAR database library.
