June 18, 2015 / by John
Recently the Commons team has been tasked with designing software to bring efficiency to data management workflows for environmental non profits. Our latest round of completed work on AshTracker, has enabled our team to design, iterate, and optimize the existing platform for use in heavy metal exceedance tracking. While this new technology and approach works extremely well for coal ash, we believe that it could easily be applied to tracking compliance across a variety of other major and minor point source facilities.
When monitoring for heavy metal exceedances at coal ash disposal sites, NGOs, state, and federal agencies are faced with an exponential amount of hurdles in their workflow. These road blocks at minimum, range from hand entering groundwater readings that are submitted on paper by industry to keeping track of permit requirements that differ between many monitoring wells, heavy metals and even facilities. We’ve spun up a quick visualization below that gets at the complex nature of successfully evaluating coal ash sites for heavy metal exceedances. The example represents a small subset of the AshTracker data model and a fraction of it’s possible permutations. If one clicks the blue circle labeled facility, 4 facilities will be revealed. Diving deeper into the hierarchy one can open a facility node revealing the monitoring wells. At this stage in the model, wells are the organizing unit for which heavy metal exceedances are tabulated. Clicking on a well will reveal 30 possible parameters that a facility can track however, each well typically monitors for a differing suite of heavy metals, making it difficult to even understand which metals are being tracked by the facility. Our model only shows a subset of information at 4 coal ash sites let alone the 300+ identified by EPA.
AshTracker’s data management system goes beyond simply feeding a public web visualization. Instead we’ve designed and built the software in a way that enables Environmental Integrity Project (EIP) analysts to take raw groundwater readings from the state, upload them to the cloud, and have them instantly analyzed for heavy metal exceedances. This can be done for thousands of readings across hundreds of wells at given facility saving EIP time and money in compliance sweep efforts. By bringing these data online faster, agencies can take action and make informed decisions when spills, leaks, and failures occur. Further media outlets can view comprehensive exceedance histories for a given coal ash site. With appropriately formatted data, EIP is leveraging the system to bring actionable facility information online in a matter of hours compared to weeks.
How it Works
Logging into the system, an authorized user has full control to edit an existing facility or bring a new one online. To find an existing facility to edit, we’ve incorporated a search box that filters the list of facilities based on their names and update status. At the facility view users have functionality that shows and hides the site from the forward facing application as well as add, edit or delete any of the text associated with the coal ash impoundment.
To add wells, EIP staff can simply upload a .CSV with the appropriate column names and the system will commit the records into the database ensuring they are associated with the proper coal ash site. More wells can always be appended and the subset can always be deleted from the system all together. With wells established, the last step is adding readings. Similarly to wells, AshTracker will take a batch upload of readings with the specified column headers. Once a readings table is cued for upload, the app takes over and associates all readings with the correct facility monitoring wells, checks and tabulates heavy metal exceedances, and updates all statistics in the forward facing site. In most cases the processing time required will take less than a minute and users interacting with AshTracker.org can access facility exceedance data by downloading it in the form of a .CSV or leveraging the API.
The code libraries we’ve used are not mysterious and our approach is far from disruptive. Our team spent a significant number of hours learning from EIP and studying the existing workflow for tracking heavy metal exceedances at coal ash disposal sites. Understanding the core requirements of our stakeholders’ efforts informed the design and tools to be leveraged for the project. In this case we decided that it was important to normalize the data into a PostgreSQL database. We then used Flask, an open source python micro framework, to put together a functional application programming interface (API). Lastly, we built the UI with AngularJS, an extensible toolset designed specifically for application development projects. These building blocks make up the bulk of the AshTracker platform and can be applied to other workflows that involve complex data management fueling analysis and real time visualization. In the future we hope to apply what we’ve learned to local efforts focused around volunteer water quality monitoring programs. If you’d like to view or adapt the AshTracker code for your own project, check out our Github page.