I Can See Clearly Now: An Automated Tool to Analyze and Report Data from TMDL Compliance Monitoring Programs
Municipal stormwater monitoring programs are becoming increasingly complex. For example, the Coordinated Monitoring Programs (CIMPs) recently developed for the Los Angeles MS4 Permit include 192 pollutants and several types of monitoring including receiving water, stormwater outfall and non-stormwater screening. The City of Los Angeles recently led development of four CIMPs in coordination with 25+ copermittees – Upper Los Angeles River, Ballona Creek, Santa Monica Bay and Dominguez Channel. These CIMPs cover hundreds of square miles of urbanized watersheds and their total implementation costs approach $5 million per year. The amount of data generated by these programs is unprecedented, to the point where there are concerns about the capacity of local laboratories to handle the number of samples that will be generated during storm events. The array of pollutants, analytical methods, water quality objectives, action levels and potential statistical approaches make it challenging to manage these monitoring programs and understand the generated data. Compliance monitoring for TMDLs can be especially challenging, as there are added “triggers” for flow rate and rainfall, and MS4 wasteload allocations are often expressed in terms of mathematical formulas (rather than simple concentration-based limits). At the same time, the requirements to report the results of these monitoring efforts on an annual basis has created a need for analytical tools to support data analysis and interpretation.
To support annual reporting efforts, the City of LA has spearheaded development of a Coordinated Data Analysis Tool (CDAT). The CDAT is a tool that automates core elements of monitoring data analysis and reporting, including generating summary statistics, flagging water quality objective / TMDL exceedances, producing trends analyses, and highlighting stations and samples that are outliers. The CDAT accepts data in a widely-accepted format (CEDEN) and accepts user inputs through an interface customized to the CIMP programs. Users can specify time periods to be analyzed including the historical period for comparison and trends analysis. Flagging of water quality objective exceedances is able to handle single sample limits, monthly limits, rolling limits, and hardness-based limits (for metals). The CDAT generates summary statistics based on regression-on-order statistics and accommodates datasets with multiple detection limits. The graphical outputs from CDAT are intuitive and visually appealing, which encourages users to review and interpret the generated datasets. The CDAT can output tables and figures into Microsoft Word which simplifies annual reporting efforts. While the algorithms that comprise CDAT are relatively sophisticated, the tool is Microsoft Excel-based with a simple linkage to Microsoft Access, which avoids additional licensing requirements for City staff.
This presentation will describe the details of the CDAT prototype and provide context of the importance of water quality data analysis tools as stormwater monitoring programs become even more demanding. These types of analysis tools are fundamental to efforts to estimate the effectiveness of MS4 programs and evaluate the progress being made toward water quality improvement.