Cleaning EPA's Dirty Sewers Data

By Wheeler, Larry; Smith, Grant | Investigative Reporters and Editors, Inc. The IRE Journal, November/December 2008 | Go to article overview

Cleaning EPA's Dirty Sewers Data


Wheeler, Larry, Smith, Grant, Investigative Reporters and Editors, Inc. The IRE Journal


You're reading a featured story from Uplink, IRE and NICAR's online publication devoted to computer-assisted reporting - available at http://data.nicar.org/uplink.

The Environmental Protection Agency estimates that the nation's aging and overburdened sanitary sewer systems overflow at least 23,000 times a year and discharge between 3 billion and 10 billion gallons of raw sewage into streams, rivers and lakes.

The problem is even bigger - 850 billion gallons bigger - when older sewer systems that discharge both storm water and sewage from the same pipe are included in the tally.

But there is no national database to track when, where and how much.

The best the EPA has to offer is its Enforcement and Compliance History Online database (ECHO; www.epa-echo.gov/echoj.The searchable database contains thousands of records depicting enforcement actions, including fines taken against hundreds of publicly owned sewage treatment plants. Many of those enforcement actions were related to illegal or chronic sewage discharges. The online ECHO database worked well for searching out a few facilities in one region, but it didn't answer the question journalists (and readers) typically want to know: Which facility got fined the most?

To begin answering that question, we downloaded the raw enforcement data from Integrated Data for Enforcement Analysis (IDEA; www.epa.gov/compliance/data/systems/multimedia/idea/ index.html), another EPA database.

IDEA is a sprawling collection of three datasets never designed to talk to each other. To construct a complete picture of sewer enforcement and compliance across the nation, we needed to turn the three IDEA datasets into one all-powerful, all-knowing database.

There was nothing in the EPA data dictionary that warned us this would be a mind-bending task, even with substantial help from our dear friend Microsoft Access database manager.

Using variables strewn across 1 8 tables and two EPA datasets, we first defined our universe as all publicly owned sewage treatment plants with a daily treatment capacity of at least I million gallons.

We came up with about 4,250 municipal sewage plants in 50 states and the District of Columbia.

Next, we threw a five-year time bracket around the data to make our results comparable to ECHO.

Eventually, we created a single database that included the name and location of each facility, plus federal, state and local penalties assessed against each facility since 2003. It was neatly organized with one record for each facility and used an EPA national permit number as a unique ID. This allowed us to sort, rank, and look for trends and extremes.

That's when we noticed Ridiculous Data Problem No. 1: State or local fines and federal fines were recorded in separate fields. In some cases, the two fields could be combined to get an accurate total. Other records recorded the same fine in multiple fields, and combining them would have resulted in inaccurate double counting. Ultimately, we devised a set of criteria to sum the penalties for each facility and compared our results to the ECHO database results to ensure accuracy.

We still had a third IDEA dataset to contend with, which became the root of most of our difficulties.

The rest of this article is only available to active members of Questia

Sign up now for a free, 1-day trial and receive full access to:

  • Questia's entire collection
  • Automatic bibliography creation
  • More helpful research tools like notes, citations, and highlights
  • Ad-free environment

Already a member? Log in now.

Notes for this article

Add a new note
If you are trying to select text to create highlights or citations, remember that you must now click or tap on the first word, and then click or tap on the last word.
One moment ...
Project items

Items saved from this article

This article has been saved
Highlights (0)
Some of your highlights are legacy items.

Highlights saved before July 30, 2012 will not be displayed on their respective source pages.

You can easily re-create the highlights by opening the book page or article, selecting the text, and clicking “Highlight.”

Citations (0)
Some of your citations are legacy items.

Any citation created before July 30, 2012 will labeled as a “Cited page.” New citations will be saved as cited passages, pages or articles.

We also added the ability to view new citations from your projects or the book or article where you created them.

Notes (0)
Bookmarks (0)

You have no saved items from this article

Project items include:
  • Saved book/article
  • Highlights
  • Quotes/citations
  • Notes
  • Bookmarks
Notes
Cite this article

Cited article

Style
Citations are available only to our active members.
Sign up now to cite pages or passages in MLA, APA and Chicago citation styles.

(Einhorn, 1992, p. 25)

(Einhorn 25)

1

1. Lois J. Einhorn, Abraham Lincoln, the Orator: Penetrating the Lincoln Legend (Westport, CT: Greenwood Press, 1992), 25, http://www.questia.com/read/27419298.

Cited article

Cleaning EPA's Dirty Sewers Data
Settings

Settings

Typeface
Text size Smaller Larger
Search within

Search within this article

Look up

Look up a word

  • Dictionary
  • Thesaurus
Please submit a word or phrase above.
Print this page

Print this page

Why can't I print more than one page at a time?

Full screen

matching results for page

Cited passage

Style
Citations are available only to our active members.
Sign up now to cite pages or passages in MLA, APA and Chicago citation styles.

"Portraying himself as an honest, ordinary person helped Lincoln identify with his audiences." (Einhorn, 1992, p. 25).

"Portraying himself as an honest, ordinary person helped Lincoln identify with his audiences." (Einhorn 25)

"Portraying himself as an honest, ordinary person helped Lincoln identify with his audiences."1

1. Lois J. Einhorn, Abraham Lincoln, the Orator: Penetrating the Lincoln Legend (Westport, CT: Greenwood Press, 1992), 25, http://www.questia.com/read/27419298.

Cited passage

Welcome to the new Questia Reader

The Questia Reader has been updated to provide you with an even better online reading experience.  It is now 100% Responsive, which means you can read our books and articles on any sized device you wish.  All of your favorite tools like notes, highlights, and citations are still here, but the way you select text has been updated to be easier to use, especially on touchscreen devices.  Here's how:

1. Click or tap the first word you want to select.
2. Click or tap the last word you want to select.

OK, got it!

Thanks for trying Questia!

Please continue trying out our research tools, but please note, full functionality is available only to our active members.

Your work will be lost once you leave this Web page.

For full access in an ad-free environment, sign up now for a FREE, 1-day trial.

Already a member? Log in now.