Creating Selections with SQL

Various methods of selecting network objects have been described previously in this tutorial. However, selections can also be made using SQL queries, which allow you to create lists of objects that satisfy specified conditions. The conditions can be as simple or as complex as you like. For example, you can create a list of nodes of a particular type or links for which particular fields lie within specified ranges.

Lists generated with SQL queries can be used to simplify the network.

This part of the tutorial describes how to use SQL to create selection lists containing:

• All Subcatchments in the network
• All nodes that are not manholes
• All pipes that are less than 250 mm wide
• All pipes that are less than 250 mm wide and less than 10 m long

It also describes how to view the results of a query as a grid.

Subcatchments

To create a selection list of all subcatchments in the currently selected network:

1. Click the (SQL select) tool on the Selection toolbar. The SQL Dialog is displayed.

Show me



2. Ensure that the SQL tab is opened (default).
3. Select the Subcatchment option from the drop-down list in the Object Type field. A simple query can select objects of one type only but remember that you can combine lists if more than one type is needed. More complex queries can also be used to select objects of more than one type.
4. Select the subcatchment_id option from the Field drop-down list.
5. Click the Builder button. The dialog is expanded to show the operations that can be included when building the SQL query.

Show me



6. Click the > button.
7. Type 0 into the Constant field.
8. Click the OK button which appears under the Constant field. The SQL query is now complete. If you make a mistake, click the large text box on the left, containing the query, and edit it. You can type any expression directly into the text box; you do not have to use the Builder options if you are familiar with the format required for SQL expressions.

Show me



9. Click the Save As... button. A Save as dialog appears with a default name 'SQL' displayed at the top of the window.

Show me



1. Change the default name from 'SQL' to 'Subcatchments'.
2. Open the 'Tutorial' and 'Langley Catchment Study' items from the tree.
3. Click the 'Stored Query Group'. The Save button is then enabled.
4. Click Save to add the new 'Subcatchments' query to the group. The Save As dialog is closed, and the 'Subcatchments' query is now displayed in the master database tree in the Explorer window.
10. In the SQL dialog, click Run. The dialog is closed and all objects matching the query condition are selected (i.e. all those that have a Subcatchment ID). If required, right-click an empty area of the GeoPlan, and, from the pop-up menu, select Zoom to network to maximise the display.

    Show me

    

11. Right-click the 'Selection List Group' in the Explorer window, and choose New InfoWorks | Selection List from the pop-up menu, and a New Name dialog is displayed. Up to this point, the SQL that generates the list has been created; the list of objects that results from running the SQL needs to be specifically saved. If you do not save the list, you will have to recreate it by running the SQL again whenever you need to select the items
12. Name the list 'Subcatchments (SQL)' and click OK. The window closes and the 'Subcatchments (SQL)' selection list is now displayed in the master database tree in the Explorer window.

Nodes that are not manholes

To create a selection list, in the currently selected network, for all nodes that are not manholes:

1. Clear the current selection by clicking on the Selection toolbar.
2. Click the (SQL select) tool. The SQL Dialog is displayed. If you run an SQL query from the SQL dialog, any matching objects are added to the current selection. However, if you run an SQL query by dragging its icon onto the GeoPlan, the new selection replaces the current selection.
3. Ensure that the SQL tab is opened (default).
4. Select the All Nodes option from the drop-down list in the Object Type field.
5. Select the node_type option from the Field drop-down list.
6. Click the Builder button. The SQL dialog is expanded to show the operations that can be included when building the SQL query.
7. Click the <> (not equal) button.
8. Type 'manhole' (including the single quotes) into the Constant field.
9. Click the OK button which appears under the Constant field. When comparing text values in SQL expressions, no distinction is made between upper and lower case letters.

   Show me

   

10. Click Test. A window showing the number of objects which match the criteria is displayed.

    Show me

    

11. Click OK.
12. Click the Save As... button. A Save as dialog appears.
13. Save the query in the 'Nodes - Not Manholes' item in the Stored Query Group. Change the default name 'SQL'' to 'Nodes - Not Manholes'. Open the 'Tutorial' and 'Langley Catchment Study'' items from the tree. Click the 'Stored Query Group'. Click Save to add the query to the group.
14. Click Run and the selected nodes are highlighted in the network. You can also run an existing SQL query, without using the SQL dialog, by dragging the query onto the GeoPlan.
15. Right-click an empty area of the GeoPlan and, from the pop-up menu, select Zoom to selection to maximise the display.

Show me



16. Open the Nodes grid view by selecting the Grid windows | New nodes window option from the Windows menu, and sort the records in descending order of Node Type. Right-cilck the Node type column heading and select Sort on Selected Columns Descending from the pop-up menu.

    The selected nodes are listed at the top, which confirms that the list is accurate and provides an alternative method for generating the selection.

17. Click to close the grid view.

Pipes less than 250 mm wide

You can use any of the data fields in a query. For example, to create a list of pipes that are less than 250 mm wide:

1. Clear the current selection by clicking on the Selection toolbar.
2. Click the (SQL select) tool. The SQL Dialog is displayed.
3. Ensure that the SQL tab is opened (default).
4. Select the Conduit option from the drop-down list in the Object Type field.
5. Select the Conduit_width (Width) option from the Field drop-down list.
6. Enter the condition: 'conduit_width < 250'.

Show me



7. Click Test. A window showing the number of objects that match the criteria is displayed.
8. Click OK.
9. Click the Save As... button. A Save as dialog appears.
10. Save the query as 'Pipes < 250 mm'. Change the default name 'SQL'' to 'Pipes < 250 m'. Open the 'Tutorial' and 'Langley Catchment Study'' items from the tree. Click the 'Stored Query Group'. Click Save to to add the query to the group.
11. Click Run to run the query, and the pipes which match the '< 250 mm' query are displayed in the GeoPlan.

    Show me

    

12. Save the selection in the Selection List Group as 'Pipes < 250 mm'. Up to this point, the SQL that generates the list has been created but the list of objects that results from running the SQL needs to be specifically saved. If you do not save the list, you will have to recreate it by running the SQL again whenever you need to select the items. To save the selection, right-click the 'Selection List Group' in the Explorer window, and choose New InfoWorks | Selection List from the pop-up menu, and a New Name dialog is displayed. Name the list 'Pipes < 250 mm'' and click OK. The window closes and the 'Pipes < 250 mm'' selection list is now displayed in the master database tree in the Explorer window.

Pipes less than 250 mm wide and 10 m long

The GeoPlan tools and grids can achieve the same effects as simple SQL queries. However, more complex SQL queries can be designed to select objects according to a combination of criteria. For example, to extend the previous query to limit the search to pipes less than 250 mm wide and 10 m long:

1. Make a copy of the last query:
1. Right-click the 'Pipes < 250 mm' query (in the Stored Query Group) and choose Copy from the pop-up menu.
2. Right-click the 'Stored Query Group' and select Paste.
3. Right-click the new query ('Pipes < 250 mm!') and choose Rename. A Rename dialog appears.
4. Change the name to 'Minor Pipes' and click OK.
2. Clear the current selection by clicking on the Selection toolbar.
3. Drag the 'Minor Pipes' query onto the InfoWorks ICM background (not the GeoPlan). The query is reloaded in the SQL Dialog. Dragging the query onto the GeoPlan runs the query but does not load it for editing.
4. Click the Builder button.
5. Extend the condition, either by using the buttons on the dialog or by typing the condition directly: conduit_width < 250 OR conduit_length < 10.

Show me

6. Click Test. A window showing that there are now more matching objects, which have either a width less than 250 mm or length less than 10 metres, is displayed.
7. Click OK.
8. Click Save to re-save the query.
9. Click Run to run the query, and the pipes that match the query are displayed in the GeoPlan.
10. Click Save to save the query.
11. Save the selection in the Selection List Group as 'Minor Pipes'. Up to this point, the SQL that generates the list has been created but the list of objects that results from running the SQL needs to be specifically saved. If you do not save the list, you will have to recreate it by running the SQL again whenever you need to select the items. To save the selection, right-click the 'Selection List Group' in the Explorer window, and choose New InfoWorks| Selection List from the pop-up menu, and a New Name dialog is displayed. Name the list 'Minor Pipes' and click OK. The window closes and the 'Minor Pipes' selection list is now displayed in the master database tree in the Explorer window.
12. To view the results of a query as a grid:
1. Click to clear the current selection.
2. Drag the' Minor Pipes' query onto the InfoWorks ICM background.
3. Click the Grid tab.
4. Hold down Ctrl and click the 'us_node_id (US node ID)', 'link_suffix (Link suffix)', 'conduit_length Llength)' and 'conduit_width (Width)' fields.

   Show me

   

5. Click Include.

   Show me

   

6. Click Test Grid. The query is run and the Conduit grid view is loaded.
7. Close the SQL dialog. The grid shows only the matching records and the selected columns. If you don't select any fields in the Grid tab, all columns are included.

   Show me

   

8. Close all the windows, including the GeoPlan window.

Lists generated with SQL queries can be used to simplify the network.

 

Back to flowchart

 

---