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:
Transcript
00:03
In its simplest form, SQL may be used to select objects for which an expression is true.
00:09
Making selections using SQL in InfoWorks WS Pro is easy.
00:15
By default, whenever an SQL Query is run,
00:19
any network objects that meet all the criteria defined in the query are automatically selected when the query finishes.
00:25
In this example, any nodes with a ground level above 35 meters will be selected using an SQL query.
00:32
To begin, open the network.
00:35
From the Model Group, expand the SQL Model group.
00:39
Open the SQL Network and SQL Control by double-clicking the SQL Network, or by dragging and dropping it into the main window.
00:48
Right-click the Stored Query Group and select New > Stored Query.
00:53
In the popup, name the stored query “Elevation > 35 m” and then click OK.
01:00
In the Model Group, double-click the Elevation > 35 m stored query object to open the Stored Query dialog.
01:09
Expand the Object Type drop-down and select Nodes.
01:13
Then, from the Field drop-down, select z (Elevation).
01:18
You will see in the query window that “z” has auto-populated.
01:23
Following the z, type “ > 35”.
01:28
With the expression complete, click Test.
01:32
A warning appears, indicating the number of links that meet the specified criteria.
01:37
Click OK.
01:39
Click Save to save the SQL before you run it.
01:43
Saving the SQL allows you to use it again on this model once it has been initially run.
01:49
Click Run.
01:50
Notice that the SQL window closed, but the nodes meeting the SQL criteria are selected in the Geoplan.
01:58
Now you can save this selection by right-clicking the Selection List Group.
02:02
Then select New > Selection List.
02:07
Name the selection list “Node elevation > 35 m”.
02:12
Now, all the currently selected objects will save into this selection list so that you can quickly access them while working on this model.
02:20
Next, try using a selection query that has more than one condition.
02:24
The next example outlines how to make a selection based on a pipe that meets two sets of criteria—
02:30
—the pipe is made of MDPE and is greater than 20 meters in length.
02:34
Right-click the Stored Query Group and select New > Stored Query.
02:39
In the popup, name the stored query “MDPE > 20 m”.
02:45
In the Model Group, double-click the MDPE > 20 m stored query object to open the Stored Query dialog.
02:54
From the Object Type drop-down, select Pipe.
02:60
In the query window, type “SELECT FROM Pipe WHERE material = "MDPE" AND length > 20”.
03:09
This expression selects all the pipes where the material is specified as MDPE in the network properties,
03:14
and then it refines the selection to the pipes with a length greater than 20 meters.
03:19
With the expression complete, click Test.
03:22
A warning appears, indicating the number of links that meet the specified criteria.
03:28
Click OK.
03:29
Click Save to save the SQL before you run it.
03:33
Then, click Run.
03:34
Notice that the SQL window closed, but the pipes meeting the SQL criteria are selected in the Geoplan.
03:42
Save this selection too.
03:44
Right-click the Selection List Group and then select New > Selection List.
03:49
Name the selection list “Pipes MDPE > 20 m”.
03:55
As in the first example, you can access this selection list at any time while working on this model.
04:01
Be aware that you can find additional SQL syntax explanations and statements,
04:06
as well as a list of SQL functions in the WS Pro help pages on the Autodesk website.
00:03
In its simplest form, SQL may be used to select objects for which an expression is true.
00:09
Making selections using SQL in InfoWorks WS Pro is easy.
00:15
By default, whenever an SQL Query is run,
00:19
any network objects that meet all the criteria defined in the query are automatically selected when the query finishes.
00:25
In this example, any nodes with a ground level above 35 meters will be selected using an SQL query.
00:32
To begin, open the network.
00:35
From the Model Group, expand the SQL Model group.
00:39
Open the SQL Network and SQL Control by double-clicking the SQL Network, or by dragging and dropping it into the main window.
00:48
Right-click the Stored Query Group and select New > Stored Query.
00:53
In the popup, name the stored query “Elevation > 35 m” and then click OK.
01:00
In the Model Group, double-click the Elevation > 35 m stored query object to open the Stored Query dialog.
01:09
Expand the Object Type drop-down and select Nodes.
01:13
Then, from the Field drop-down, select z (Elevation).
01:18
You will see in the query window that “z” has auto-populated.
01:23
Following the z, type “ > 35”.
01:28
With the expression complete, click Test.
01:32
A warning appears, indicating the number of links that meet the specified criteria.
01:37
Click OK.
01:39
Click Save to save the SQL before you run it.
01:43
Saving the SQL allows you to use it again on this model once it has been initially run.
01:49
Click Run.
01:50
Notice that the SQL window closed, but the nodes meeting the SQL criteria are selected in the Geoplan.
01:58
Now you can save this selection by right-clicking the Selection List Group.
02:02
Then select New > Selection List.
02:07
Name the selection list “Node elevation > 35 m”.
02:12
Now, all the currently selected objects will save into this selection list so that you can quickly access them while working on this model.
02:20
Next, try using a selection query that has more than one condition.
02:24
The next example outlines how to make a selection based on a pipe that meets two sets of criteria—
02:30
—the pipe is made of MDPE and is greater than 20 meters in length.
02:34
Right-click the Stored Query Group and select New > Stored Query.
02:39
In the popup, name the stored query “MDPE > 20 m”.
02:45
In the Model Group, double-click the MDPE > 20 m stored query object to open the Stored Query dialog.
02:54
From the Object Type drop-down, select Pipe.
02:60
In the query window, type “SELECT FROM Pipe WHERE material = "MDPE" AND length > 20”.
03:09
This expression selects all the pipes where the material is specified as MDPE in the network properties,
03:14
and then it refines the selection to the pipes with a length greater than 20 meters.
03:19
With the expression complete, click Test.
03:22
A warning appears, indicating the number of links that meet the specified criteria.
03:28
Click OK.
03:29
Click Save to save the SQL before you run it.
03:33
Then, click Run.
03:34
Notice that the SQL window closed, but the pipes meeting the SQL criteria are selected in the Geoplan.
03:42
Save this selection too.
03:44
Right-click the Selection List Group and then select New > Selection List.
03:49
Name the selection list “Pipes MDPE > 20 m”.
03:55
As in the first example, you can access this selection list at any time while working on this model.
04:01
Be aware that you can find additional SQL syntax explanations and statements,
04:06
as well as a list of SQL functions in the WS Pro help pages on the Autodesk website.
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.
In the text box, “z” has auto-populated:
A warning appears indicating the number of links that meet the specified criteria.
Notice that the SQL window closed, but the nodes meeting the SQL criteria are selected on the GeoPlan.
To save this selection:
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.
The pipes meeting the SQL criteria are now selected in the GeoPlan:
In the Model Group, this selection list can be accessed any time while working on this model.
