Data Visualisation for Improved Understanding of Large Water Quality Datasets

Christchurch City Council (CCC) and Environment Canterbury had a large amount of groundwater data but found that it was difficult to track changes in groundwater quality in the underground aquifers when data was stored and analysed in spreadsheets. Standard graphing techniques could be used to identify trends in a single bore, however, each bore was not monitored consistently over the entire data set. In many cases, one bore was monitored for a number of years and then monitoring swapped to another nearby bore. This meant that the trend in water quality for the area could not be easily identified.

CCC commissioned Beca to provide a data visualisation tool. The aim of this project was to compile and present available groundwater quality data to aid in early warning of any contamination risks to the drinking-water supply that may gradually develop over time. Microsoft Power BI was used to present two data bases (CCC and Environment Canterbury data) with a total of 12,474 data points dating from 1954 to 2017. Six parameters that are key to drinking water quality were selected for the visualisation tool: E. coli, total coliforms, nitrate, ammonia, chloride and dissolved reactive phosphorous.

A tool was developed that presents the data geospatially to clearly illustrate what is happening with the water quality in and around Christchurch over time. Maximum acceptable values and guideline values from the Drinking Water Standards for New Zealand were used to define the warning colour system so that significant data points could be identified. It is also possible to interrogate individual data points through the Microsoft Power BI platform. This project resulted in a tool that is valuable in informing an understanding of long terms trends in water quality, alerting CCC to groundwater quality changes that could have impacts on the water supply, informing future investment considerations including location of new wells, and whether treatment or other mitigation measures may be required in the future.