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.

Figure 1 below shows the GIS and network 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 above shows the area of interest.

• Soil types are contained in a GIS Layer and shown in different shades of grey.
• Roads, rivers, and buildings are contained in a different GIS Layer and show up using various colours.
• The underlying pipe network is shown in dark blue.

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

Step	Step Description	Query	Example Image
A	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 deep red lines. gislayer is the layer specified in the GIS Layer box. STRUCTURE is the field in gislayer. This could also have been defined in Field. the 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 value in the user number 7 field that's >= 2, where this value represents the condition score of the pipe that's been imported from an Asset Management system. 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.	SQL query context: Object Type: Pipe [Spatial] Search Type: Distance [Spatial] Layer Type: GIS Layer [Spatial] Layer: [SHP] Infrastructure [Spatial] Distance: 2.0 width >= 300 AND material = "CO" AND gislayer.STRUCTURE = "B Road" AND user_number_7 >= 2
B	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.	SQL query context: Object Type: Pipe [Spatial] Search Type: Distance [Spatial] Layer Type: GIS Layer [Spatial] Layer: [SHP] Infrastructure Apply Filter To Current Selection = TRUE gislayer.WRAPCLASS = 1 OR gislayer.WRAPCLASS = 3
C	The third SQL object tests our selected pipes to see if any of them are 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 5 (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 query context: Object Type: Pipe [Spatial] Search Type: Distance [Spatial] Layer Type: GIS Layer [Spatial] Layer: [SHP] Infrastructure [Spatial] Distance: 30.0 SET criticality = 5, criticality_flag = "AA" WHERE gislayer.STRUCTURE = "Hospital"
D	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.