Mapping Risk in a Stormwater Network using SAP HANA Spatial and Graph

In this blog post I will show how to estimate the water load in a stormwater network and calculate the risk of an overload. Sure, I’m not a water network expert, but here is the idea: a stormwater network is a directed, (in our case mostly acyclic) graph made up of a set of edges/pipes and vertices/junctions. For each vertex we can calculate the catchment area, i.e. the area from which rain water most likely will enter the network via that vertex. Each vertex then propagates the load to its downstream network. By looking at the ratio of the pipe’s load and cross-section, we can estimate the overload risk for each pipe.

The scenario extends the analysis described in the blog post “Augment your Python Analysis with Multi-Model data in SAP HANA Cloud“. The data for this scenario is taken from the Wellington Water Open Data Portal. The code and data is available on github.

The Stormwater Network

The network we will analyze can be represented as a directed graph. Each edge/pipe has a source and a target vertex indicating the direction of flow. Besides the shape or geometry, the data also contains information about the pipe’s length and diameter.

If we take a look at the network, one might already guess that it is not connected. There are rather individual sub-networks in which the water from a specific area is flowing downstream and finally into the sea. The size of the lines in the below image indicates the pipe’s diameter. We can see some of the network’s backbones.

The Network’s Components

We can identify the individual sub-networks of the water network by using a graph procedure in SAP HANA Cloud. In graph terminology, we will calculate the weakly connected components, i.e. the set of vertices which are reachable traversing the edges as if they were undirected. In the weakly connected components procedure, we simply loop over all vertices and assign a number to all reachable vertices.

CREATE PROCEDURE "STORMWATER"."GS_CONNECTED_COMPONENTS" (...)
LANGUAGE GRAPH READS SQL DATA AS
BEGIN
  GRAPH g = Graph("STORMWATER", "STORMWATER");
-- add a temporary attribute to store the component number
  ALTER g ADD TEMPORARY VERTEX ATTRIBUTE (BIGINT "$COMPONENT" = 0L);
  BIGINT i = 0L;
-- For each vertes we'll check if it is alerady assigned to a component.
-- If not, we'll assign the vertex and all of it's neighbors/reachable vertices.
  FOREACH v_start IN Vertices(:g){
    IF(:v_start."$COMPONENT" == 0L) {
      i = :i + 1L;
-- all reachable neighbors are assigned the same component number > 0
      FOREACH v_reachable IN REACHABLE_VERTICES(:g, :v_start, 'ANY') {
        v_reachable."$COMPONENT" = :i;
      }
    }
  }
...
END;

With the result we can identify the individual components. The image below depicts 3 of the largest components.

Calculating Catchment Areas

Now, we want to estimate the amount of water that enters the network when it rains. We simply assume that the water amount is proportional to the area that “is covered” by the network. So how can we estimate the size of the catchment area for each node in the network? For this, we will use a built-in function of SAP HANA spatial: ST_VoronoiCell. The Voronoi Cell for a vertex indicates the set of points/area which is closest to this point. The Voronoi cells are the brown polygons. There is one for each of the graph’s vertex.

As an additional step, we intersect the Voronoi cells with the components Alpha Shape. This helps us to cut the cells at the “border” of each component. What we see below are the catchment areas for the vertices in some of the components, bounded by the components’ Alpha Shapes.

Estimating Water Load

So, now we have the size of each vertex’ catchment area. We assume the area is proportional to the amount of water entering the network via this vertex. Next, we need to propagate this load to the downstream part of the network. For this we again use a simple Graph procedure which loops over each vertex, identifies all upstream vertices and sums up their area/load. The core of the procedure is again the built-in function REACHABLE_VERTICES. For each vertex v in our graph g we identify all reachable vertices traversing the edges in INCOMING direction. Then we calculate the sum of the upstream vertices’ areas.

FOREACH v_upstream IN REACHABLE_VERTICES(:g, :v, 'INCOMING'){
  sum_area_m2 = :sum_area_m2 + :v_upstream."AREA_M2";
}

Mapping Load and Risk

As a result, we have calculated the load for each vertex.

And now we can also calculate the load of the edges/pipes. In case multiple pipes are connected to the same source vertex, we distribute the load proportional to the pipe’s cross-section. At first glance, the amount of water flowing through each pipe doesn’t look too strange.

If we calculate the load-to-cross section ratio for each pipe segment, we see a few pipe standing out. These are the ones with a high load, but a small diameter. If the network is under high load, these pipes would probably the first to run over.

Summary

We have seen how to use some core spatial and graph features of SAP HANA Cloud to simulate the flow in a water network and analyze risk. We used a built-in graph function for reachability to identify connected components. Next, we made use of Alpha Shapes and Voronoi cells to calculate catchment areas and estimate load. Finally, a graph procedure calculated the load for each vertex/edge by aggregating the load of all upstream vertices.

The complete script and data is available on github and may serve as a template which you can easily adapt to your domain. To explore the details just create a free SAP HANA Cloud Trial instance (..or use any other existing instance). Using the SAP HANA Cloud Database Explorer, you can easily upload the sample data and run the script yourself.

References

[1] source code and data on github
[2] Blog post “Augment your Python Analysis with Multi-Model data in SAP HANA Cloud”
[3] SAP HANA Graph Resources
[4] SAP HANA Cloud trial