For the last several months, we’ve been working with EarthFix to report on the Clean Water Act in the Pacific Northwest. The Act turns 40 on Oct. 18, and our goal with the collaboration was to explain what the Act has — and has not — accomplished in four decades.
I got to tackle the data.
Clean Water Act data is tricky. It’s a federal law, but it’s administered (largely) by the states. It’s up to states to set water standards, enforce those standards, and send a comprehensive report back to the EPA every two years on their progress. The federal database of record, called ECHO, is shot through with errors. Apples-to-apples comparisons across states are nearly impossible.
Nevertheless, we wanted to show what water pollution looks like across the Northwest. Causes and probable sources, primarily. Here’s how we wrangled it.
Collecting the GIS data
Our first step was to get the most current waterbody shapefiles for Idaho, Washington and Oregon.
- 2008 Washington Water Quality Assessment 303(d) (Source: Washington State Department of Ecology)
- Oregon’s 2004/2006 Integrated Report (Source: Oregon Department of Environmental Quality)
- 2008 Final Integrated Report 305B/303D for Idaho (Source: Idaho Geospatial Office)
To figure out impaired waterbodies, we had to go to a different source: the EPA’s state water quality assessment reports, which aggregates the total size of assessed waters by causes of impairment (WA, OR, ID).
Click into the miles (or acres, in the case of lakes) for a particular cause of impairment, and the EPA gives you a nice table with a list of all the waterbodies, with unique IDs, locations, size and cause(s) of impairment. Here’s a list of Washington State rivers impaired by Ammonia.
If you wanted to do that for all 50 states, they’d be easy enough to scrape. For our purposes, it was quicker to copy/paste into Excel (via a text editor to delete the formatting), standardize the data and export it back to CSV.
Putting it in QGIS
Because of differences in record names, I did this next step separately for each state.
Open a new project and add the waterbodies shapefile(s). Add the CSV of impaired waters by choosing Add Vector Layer in the Layers menu. Then open up the properties menu for the waterbodies and, in the Joins tab, add a vector join. Now the waterbodies attribute table includes the additional fields we made in the CSV file, matched to the proper records using the unique waterbody ID field.
Open that attribute table. Use advanced search to find impaired waters. Here’s what the query looked like for me:
IS_IMPAIRED = ‘YES’
Using the controls at the bottom of the attribute table, invert your selection. (So all NON-IMPAIRED waters are selected.) Toggle editing ON. And delete the selected rows. Be warned: This took a couple of hours to process on my MacBook Pro for Idaho and its 70,000+ records.
Adding your map to Fusion Tables
Right click on the waterbodies layer and choose “Export to KML.” Upload that KML file to Fusion Tables. At this point you should have the following columns:
Unique_ID | geometry | Waterbody_Name | Impairment(s) | Color_Style
The last style is to style the map using that last column.
Switch from table view to map view and choose Change Styles. Color by buckets, with a range of 1-5 — or n+1 of however many categories you’re showing on the map.
The Final Product
Here’s the final map we published of nutrient-impaired waters in the Pacific Northwest, as part of a piece on how agriculture is the nation’s biggest polluter but usually goes unpunished. Each state — and the EPA in Idaho — has a different system for assessing its waterways, which partially explains why Washington, Oregon and Idaho appear to have variations in the waters they have listed as impaired.