Data and Methodology
VPR's Lake Champlain Cleanup Plan data project was initially informed by the report “Phosphorus TMDLs for Vermont Segments of Lake Champlain,” published Aug. 14, 2015, by the Region 1 office of the Environmental Protection Agency. Through EPA and Vermont's Department of Environmental Conservation, we obtained the “spreadsheet model” (Microsoft Excel File Download) referenced in the report as a primary data source. The spreadsheet model itself is informed by other environmental models, which also are explained in the report.
Note: Cumberland Bay, which is on the western side of Lake Champlain near Plattsburgh, New York, was removed from most of this project's segment-by-segment breakdowns of phosphorus loading because none of its phosphorus load is attributed to Vermont.
The key to the spreadsheet model is its interactive “scenario tool,” accessible on the “dashboard” sheet of the downloadable workbook. To inform their assessment of various potential approaches to reducing phosphorus runoff in the Lake Champlain basin, the EPA and Vermont DEC projected the phosphorus runoff from different different “scenarios” – hypothetical reductions achievable from a range of limits on wastewater, developed land, back roads, future growth, forests, streams, farmsteads and non-point agricultural sources.
The scenario tool allows a user to select a scenario for each phosphorus source. Phosphorus concentrations in each lake segment change (as shown in a separate table on the same "dashboard sheet) based on the scenario inputs chosen. As scenarios are chosen, certain fields on other sheets throughout the workbook also are adjusted to reflect the hypothetical phosphorus loads from the chosen set of scenarios.
Data behind the visualizations were copied from the spreadsheet model, restructured as needed to accommodate the publication platform (Silk), and pasted into the platform in order to generate the graphs.
The listings below provide the corresponding source material from the spreadsheet for each published chart.
Who feeds phosphorus to Lake Champlain? Summary Tables sheet, “Lakewide Totals” table; or Base Loads sheet, “2001-2010 Vermont Base Loads” table
2002 vs. 2015 TMDL plans: Summary Tables sheet, “Lakewide Totals” table
Phosphorus concentrations with or without pollution controls: Dashboard, using the scenario tool
“No reduction” data were sourced from the following set of scenarios: wastewater (currently permitted loads), developed land (no reduction), backroads (no reduction), future stormwater growth (20 years of new impervious surfaces), farmstead (no reduction), forest (no reduction), stream (no reduction).
“TMDL” data were sourced from the following set of scenarios: wastewater (0.2/0.8 Targeted Watersheds), developed land (TMDL scenario), backroads (TMDL scenario), future stormwater growth (20 years of new impervious surfaces), farmstead (TMDL scenario), forest (TMDL scenario), stream: TMDL scenario.
Segment-by-segment breakdown of phosphorus sources: Base Loads sheet, “2001-2010 Vermont Base Loads” table
NOTE: CSO value for Burlington Bay is added to the “developed land” category for consistency with CSO data treatment for other lake segments.
NOTE: “Agr. Nonpoint” from the source data is re-named “farms” in VPR's data project. This input category is defined in the project glossary.
Lake-wide breakdown of phosphorus sources: Base Loads sheet, “2001-2010 Vermont Base Loads” table
NOTE: As above, the CSO value is added to the “developed land” category.
Reductions by lake segment: Summary Tables sheet, “Vermont TMDL Percent Reductions Required” table
Reductions by source in each lake segment: Summary Tables sheet, “Vermont TMDL Allocations (mt/yr)” table
Compare wastewater scenarios: Wastewater sheet: see pivot table to the right of the primary table
Facilities within targeted watersheds: Wastewater sheet, primary table filtered by the field “targeted watersheds” described as “targeted”
Facilities outside targeted watersheds: Wastewater sheet, primary table filtered by the field “targeted watersheds” described as blank (i.e.: not targeted)