SQL example: Critical pipes

This example shows how you can use information from your network, combined with data from GIS layers, to identify critical pipes in the network. A series of SQL statements refines a selection until we have identified the pipes that meet all the criteria. We then use SQL to set values in the selected pipes.

The example is taken from the Innovyze product InfoAsset Manager but the basic principles are relevant to whichever Innovyze product you are using.

The network and GIS data

In this example we are looking for large damaged pipes in difficult ground. Specifically we are going to select any such pipes near a hospital, as a failure in such a pipe could be a major problem.

Figure 1 shows the area of interest.

The various roads and buildings of interest are described in the stages below.

The first SQL object selects a number of pipes based on the following criteria:

  • the pipe width field is greater than or equal to 300. In this case the units are millimetres
  • the pipe material is CO (concrete)
  • the pipe lies within two metres of an object in a GIS layer defined as type "B Road". In this example, a B Road is a minor road, shown in Figure 1 as pale green lines.
    • gislayer is the layer specified in the GIS Layer box
    • STRUCTURE is the field in gislayer. This could also have been defined in GIS Field
    • the GIS Search Type field specifies that we are looking for pipes within a specific distance. Other options allow you to search for objects that cross, contain, or are contained by, the GIS object
  • the pipe has a condition score >= 2. In this case the condition score uses a scale from 1 to 5, with a larger number representing poorer pipe condition. A pipe that has collapsed would have a score of 5. So we are selecting pipes that have known problems

After an SQL object is applied to the network, any network objects that meet the criteria in the SQL statements are selected.

The second SQL object refines the selection made by the first SQL object. Note that Apply Filter to Current Selection is checked. This limits the network objects to which the SQL is applied to those already selected. This is a powerful feature.

  • This query tests the selected objects against data in a different GIS layer. This layer represents soil types, shown in Figure 1 as areas with different shades of grey.
  • the field WRAPCLASS contains the soil type information. WRAP stands for Winter Rain Acceptance Potential
  • in this case we are looking for difficult soil types, such as rocky or potentially waterlogged soils, where special equipment may be required to dig down to the pipe

Next, we test our selected pipes to see if any of them is near a hospital. A pipe collapse near a hospital that blocked access or released wastewater in the area would probably be a major problem, and therefore repairing damaged pipes in this sort of situation will be more critical than elsewhere.

  • we are selecting any pipe that is within a certain distance of a hospital building
  • here we go one step further and actually set a data field and flag in the object so it can be easily identified in future. The Criticality field is set to AA (representing the highest value). We have also set the Criticality field flag. This could be a code for the method used to set the field, or even the initials of the person who ran the SQL.
  • in Figure 1 the hospital buildings are shown in pale green. Just one pipe meets all the conditions we have set in our series of SQL statements. That pipe is highlighted in red.

SQL Queries can be grouped together and executed automatically to make the process even more efficient.

Here, the three queries described above have been placed in the same Stored Query Group. If you drag this group onto a network, all the queries it contains will be executed in alphabetical order.

So, with a little care when naming the individual queries, you can create powerful, multi-step queries.

SQL examples

SQL and stored queries