Data Analysis Tools for Complex Water Quality Monitoring Programs
Empowering our Client with Tools to Streamline Reporting for Monitoring Programs and Reduce Turnaround of Data Analysis Requests
End Client: City of Los Angeles Watershed Protection Division (in partnership with Larry Walker Associates)
Paradigm developed and maintains a data analysis tool for the City of Los Angeles water quality monitoring programs. Under its Phase I MS4 Permit, the City of Los Angeles has requirements to routinely monitor approximately 50 receiving water stations and 27 outfall stations under four Coordinated Integrated Monitoring Programs (CIMPs) for its major watersheds. Up to 156 pollutants are analyzed in the collected water samples (including bacteria, nutrients, metals and toxics) and a majority of these pollutants are subject to water quality criteria. To support the City with routine data analysis and exceedance flagging, Paradigm developed a CIMP Data Analysis Tool. The CDAT generates four types of outputs: detection summary report, detailed exceedance report, summary statistics report, and graphical box plots (see figure below). The summary statistics report employs regression-on-order statistics that handle non-detects in an unbiased manner and includes calculation of average and median values that can be used to estimate EMCs. CDAT is a networked desktop-based solution with an Excel® front-end, Access® back-end and VBA coding language.
The desktop-based framework was selected by the City of LA because its staff were highly trained in Microsoft® software, reducing the amount of training necessary. The CDAT was developed to run within a network-based workflow, where administrators can deploy tool updates to the network (and avoid manual releases to users). The easy-to-use front-end authenticates users based on login information provided by the network administrator. The front-end includes custom VBA dialogue windows for a Report Wizard where the user can create queries for data analysis using dynamic dropdowns based on data that have been loaded into the tool. Paradigm conducted a tool release workshop and training for approximately 20 city staff. Since the original deployment, Paradigm has continued to support the City of LA with additional enhancements. The CDAT has become an integral tool for the City’s annual reporting, and will continue to evolve over time.