As part of our learning and development assignment this year, I was interested in attempting to find a way to effectively create a process for analysing client data at a sub-DMA level. As we expand our client base as well as our number of products, it can become difficult to get a high-level idea of what each DMA contains; tables of data can provide lots of information but do not bring an area to life and spatial data can get very busy and complex when trying to overlay property/pipe/pressure data etc. onto a map (fig.i).
For this assignment I decided to try and create a way of giving a simple spatial overview of what is going on inside each DMA to see if it could bring any interesting insights that would complement our existing analysis outputs from Paradigm, Sonar and Dynamo. As well as creating an output that would give clarity to client attributes at a glance, I was also focused on making the process well documented and relatively modular so that outputs could be created for a variety of datasets. I planned to develop existing knowledge of QGIS and PostGIS that I had used in previous assignments to help with this project.
Figure i: DMA property data showing congested spatial data
Before diving into how I would tackle the problem, I created a list of aims for my assignment which would help structure the analysis and allow me to document each step for future use. My initial aims were:
- To create a spatial output that shows sub-DMA classifications clearly and simply for internal and external stakeholders
- Structure the analysis and outputs in a way that can be linked to existing products (using database structure and formatting from Cadence)
- Develop a process that is repeatable and modular
- Focus on outputs showing property type and pipe material as a proof of concept for this analysis
- Create outputs that show distinct attribute classifications within a single selected DMA boundary
I was conscious that the outputs needed to give insight whilst paring down the complexity that data such as single property feeds or pipe networks have, and that classifications needed to be kept within each DMA boundary so that stakeholders could easily navigate to an area of their choice. This would again give a clear way of diving into a single area to better understand if sections of the DMA may behave differently to others, whilst removing the noise of surrounding DMAs.
The first question that needed answering was how the DMAs would be separated into distinct and equal parts that would be suitable for any size DMA, with varying attributes situated anywhere within the polygon. After some trial and error, I settled on using hexagons with 50-meter diameter as a base grid that could be used to divide up DMA areas. I felt this would be effective as they tesselate, are more aesthetically pleasing than a square, and a 50m diameter is a size that is tangible for stakeholders but also contain a maximum of 8-10 properties in high domestic areas and was less likely to contain a complex amount of network (Fig ii.).
Figure ii: 50m hex grid with property and pipe data
QGIS also has a handy function in ‘Research Tools’ that will render a grid layer for a given area using your desired dimensions (i.e 50m hexagons). Once created I was able to import this layer into Postgres as a reference table, giving each hexagon a unique ID for later use in the analysis. The next step was to add client data to the database structure; for this proof of concept I required:
- DMA polygons
- Property geometry data with DMA assignment
- Property type (domestic/commercial)
- Mains geometry data with DMA assignment
- Mains length (km)
- Mains material (metallic/plastic etc.)
Each dataset was added to the database with the DMA assignment as their foreign key, linking them to the polygons ready for spatial analysis.
The most important step of the analysis was to link the hexagon base grid layer to the DMA polygons. This would enable each hexagon to have its own ID and a DMA reference in which it overlaps (partially or wholly). Using PostGIS functions ST_INTERSECTS, ST_OVERLAPS and ST_CONTAINS I was able to assign each hexagon in the grid to the DMA/s that they sit within.
For the the property geometry I used PostGIS to match where properties sit within each individual hexagon and aggregate the numbers of domestic and commercial properties in each. Once these property counts had been created for the hexagon layer, I was then able to classify each hexagon based on the most prevalent property type. By utilising the PostGIS function ST_UNION, I created a separate dataset of combined hexagons based on their property classification and DMA assignment, in essence making sub-DMA polygons that show where property types can be found within a DMA boundary.
Now that the general process for assigning, aggregating and combining the hexagons had been created, I shifted focus onto creating classifications for the client mains data. As the base grid and DMA assignments were already complete, the main variation to the process was to understand how best to aggregate mains data by material. I had already decided to simplify the data from the client to give broader material types (plastic/metallic/concrete/other) and settled on using the material of greatest combined length within each hexagon as the overall classification. Again, once this had been achieved, I was able to combine all cells with matching classifications to create a dataset of sub-DMA polygons by client mains material (fig iii.)
Figure iii: Process for Project Capillary
Considering this analysis would rely heavily on the spatial nature of sub-DMA characteristics, I was keen to use QGIS as a tool to demonstrate the outputs and how client DMAs can vary. One of my core aims was to bring
clarity to what is going on inside a DMA using clear classification outputs that would be easily understood by stakeholders. To achieve effective outputs,
I chose to use the same colours for property types as we use for HH and NHH component within Paradigm and variations on company branding colours for pipe materials that maintained a ‘clean water’/blue theme (Fig iv.). I also used a greyscale Open Street map as the background to the QGIS output to give geographical familiarity and a tangible base for any hexes that had not been assigned a classification (i.e. where no properties or mains were situated).
Using a Mask plugin on QGIS I have also been able to ‘highlight’ the selected DMA and sub-DMA polygons whilst keeping the surrounding data in more muted tones for reference; something that I think adds focus to
Figure iv: Combined sub-DMA polygons by prevalent property type
the DMA being analysed but removes some of the clutter from the map. This allows the user to see which attributes they are interested in for a given DMA but doesn’t hide all other polygons, so that a wider understanding of the area can be appreciated.
On the whole I feel that this year’s assignment has been successful, I was able to achieve all of my initial core aims for the project and I believe that this analysis is something that could be readily used to complement some of our existing products to help clients better understand their DMAs.
Having created the process in a modular way, it will be easily repeatable and can be altered to suit other potential client attributes without having to overhaul the approach. This has already been the case when elevation data in client DMAs was requested to help with the Dynamo Interface proof of concept. As elevation is continuous data rather than discrete data like property type the whole process was not required, however it was relatively simple to assign elevations to each hex cell and apply this to DMAs as required without having to create distinct sub-DMA polygons. Elsewhere in SME-Water this analysis could help with DMA/zonal understanding in Paradigm where particular areas are not behaving as expected, or in Sonar there is potential that the mains material analysis could be used for high level DMA suitability reports ahead of deployment planning.
Future improvements and developments for this analysis should probably focus on streamlining the process using Python to create datasets and outputs, as well as the initial hex-grid, as the QGIS function is very useful but struggles to render large geospatial extents. I would also be interested to explore further classification metrics such as expected consumption values for each given property group within a hex cell or a burst density matrix to align with Paradigm ratings.
