• InfoWorks WS Pro

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:

iwsp2023M20U02_SQL_Smpl_Slctns.wspt

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.

  1. From the Model Group, expand SQL Model.
  2. Double-click the SQL Network to open the SQL Network and SQL Control on the GeoPlan.
  3. Right-click the Stored Query Group.
  4. Select New > Stored Query.

In the InfoWorks WS Pro Model Group, the Stored Query Group shortcut menu with New selected, and in the flyout, Stored Query selected.

  1. In the popup, name the stored query “Elevation > 35 m”.
  2. Click OK.
  3. In the Model Group, double-click the Elevation > 35 m stored query object.
  4. In the SQL dialog box, expand the Object Type drop-down and select Node.
  5. From the Field drop-down, select z (Elevation).

In the text box, “z” has auto-populated:

The SQL dialog with z Elevation selected in the Field drop-down, and in the text box, “z” auto-populated and highlighted.

  1. Following the z, type “ > 35”, noting the spaces before and after the greater than (>) symbol.
  2. Click Test.

A warning appears indicating the number of links that meet the specified criteria.

  1. Click OK.
  2. Click Save to save the SQL.
  3. Click Run.

Notice that the SQL window closed, but the nodes meeting the SQL criteria are selected on the GeoPlan.

In the GeoPlan, the nodes meeting the SQL criteria are selected.

To save this selection:

  1. In the Model Group, right-click the Selection List Group.
  2. Select New > Selection List.
  3. 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.

  1. Right-click the Stored Query Group.
  2. Select New > Stored Query.
  3. In the popup, name the query “MDPE > 20 m”.
  4. In the Model Group, double-click MDPE > 20 m.
  5. In the SQL dialog box, in the Object Type drop-down, select Pipe.
  6. In the text box, type: SELECT FROM Pipe WHERE material = "MDPE" AND length > 20

In the SQL dialog box, the text box with the typed SQL query.

  1. Click Test.
  2. Click OK to close the notification.
  3. Click Save.
  4. Click Run.

The SQL dialog box with the MDPE > 20 selection query entered and Run selected.

The pipes meeting the SQL criteria are now selected in the GeoPlan:

The GeoPlan with the pipes meeting the SQL criteria selected in red.

  1. In the Model Group, right-click the Selection List Group.
  2. Select New > Selection List.
  3. 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.

In the Model Group, the Pipes MDPE > 20 selection list appears under the Selection List Group.