Manage and format raw data

When our client the Fondation Rivières approached us in 2020 to create an interactive map of the problems of spillage (overflows) of wastewater in the various municipalities of Quebec, it would have been difficult to be further from the cut at the lip. An exhaustive inventory of spill data for the years 2011 to 2016 was carried out by a team of journalism students from UQAM. Additional data from the water portal of the Ministry of the Environment and the Fight against Climate Change (MDDELCC) had also been collected for the years 2017 to 2019, but data standardization and structuring work remained to do in order to make the data usable.

The first step of this mandate therefore consisted in modeling a relational database which would guarantee a uniform and optimized structuring of the data. A first iteration of the database was carried out on the database management system (SGDB) MySQL, but for performance reasons as well as in order to have access to more spatial functionalities, we quickly transferred to PostgreSQL/Postgis.

In the process of structuring the data, one of the major problems that we quickly came up against was the imprecision of the positioning of the processing units. Indeed, in order to link each wastewater treatment plant to the municipality it served, we conducted an intersection analysis between the positioning data of the wastewater treatment plants (points) and the data of the municipal territories ( polygons). However, the positioning of the treatment plants referring to the outlet of these, several of the stations were found in the wrong municipality or in an unorganized aquatic territory. A manual validation therefore had to be carried out to validate the data binding.

Data analysis

Once the data was structured, we were able to begin analyzing it in order to draw a summary portrait of the situation of wastewater spills in Quebec. Any good data analysis requires, first of all, the formulation of the right questions. At this stage, we took advantage of the expertise of our client Fondation Rivières. Indeed, although data analysis is our expertise, it is the client who has the understanding of the field in which he evolves.

Thus, we worked to develop an indicator of the intensity of discharges in order to obtain the best possible approximation of the volume of wastewater discharged into the environment. Indeed, not having access to the actual volume, we had to work with the available data to calculate an approximate volume. We therefore analyzed the evolution of the number of overflows per year, the duration of the overflows per year and the intensity of the overflows per year.

Creation of the data consultation interface on the web

Once the data analysis was done, we proceeded to create wireframe models in collaboration with Thibault Landel UX/UI Designer. The objective at this stage is to present the information as simply as possible in order to allow ordinary citizens to consult this map without them needing to be an expert in the field. To be done. we created an interactive map and created broken line graphs as well as bar graphs that can be viewed by clicking on the spatial entity of a municipality.

Results

Thanks to this interactive cartography, our client was able not only to learn about the state of the situation, but also to communicate this problem to the public in a simple and concise way and thus mobilize the actors of the environment in order to undertake a project of reduction of wastewater discharges in the various municipalities.