Building simple SQL queries for selection sets
Build a simple SQL query, and then use it to find and select objects and create a selection set.
Tutorial resources
These downloadable resources will be used to complete this tutorial:
Step-by-step guide
In InfoWorks WS Pro, you can use a simple SQL query to select objects for which an expression is true. By default, when the query is run, network objects that meet all of its defined criteria are automatically selected.
This example uses an SQL query to select any nodes with a ground level above 35 meters.
- From the Model Group, expand SQL Model.
- Double-click the SQL Network to open the SQL Network and SQL Control on the GeoPlan.
- Right-click the Stored Query Group.
- Select New > Stored Query.
- In the popup, name the stored query “Elevation > 35 m”.
- Click OK.
- In the Model Group, double-click the Elevation > 35 m stored query object.
- In the SQL dialog box, expand the Object Type drop-down and select Node.
- From the Field drop-down, select z (Elevation).
In the text box, “z” has auto-populated:
- Following the z, type “ > 35”, noting the spaces before and after the greater than (>) symbol.
- Click Test.
A warning appears indicating the number of links that meet the specified criteria.
- Click OK.
- Click Save to save the SQL.
- Click Run.
Notice that the SQL window closed, but the nodes meeting the SQL criteria are selected on the GeoPlan.
To save this selection:
- In the Model Group, right-click the Selection List Group.
- Select New > Selection List.
- Name the selection list “Node elevation > 35 m”.
Next, make a selection query for a pipe that meets two sets of criteria—the pipe is made of MDPE and is greater than 20 meters in length.
- Right-click the Stored Query Group.
- Select New > Stored Query.
- In the popup, name the query “MDPE > 20 m”.
- In the Model Group, double-click MDPE > 20 m.
- In the SQL dialog box, in the Object Type drop-down, select Pipe.
- In the text box, type: SELECT FROM Pipe WHERE material = "MDPE" AND length > 20
- Click Test.
- Click OK to close the notification.
- Click Save.
- Click Run.
The pipes meeting the SQL criteria are now selected in the GeoPlan:
- In the Model Group, right-click the Selection List Group.
- Select New > Selection List.
- Name the selection list “Pipes MDPE > 20 m”.
In the Model Group, this selection list can be accessed any time while working on this model.