Update network data using an SQL query.
Tutorial resources
These downloadable resources will be used to complete this tutorial:
Transcript
00:03
SQLs may be used to update network data regarding selected objects and can be carried out easily by using SQL in InfoWorks WS Pro.
00:13
In this unit we will undertake two examples,
00:16
the first is to update the friction factor of pipes made of MDPE and the second is to populate a User Text Field.
00:24
To begin, open the network.
00:27
From the Model Group window, expand the SQL Model group.
00:32
Open the SQL Network and SQL Control by double-clicking the SQL Network, or by dragging and dropping it into the main window.
00:41
Right-click on the Stored Query Group, select New, and from the flyout select Stored Query.
00:49
Call the stored query “MDPE FF”.
00:54
Double-click on the “MDPE FF” stored query object to open the Stored Query window.
01:01
From the Object Type drop down menu, select Pipe.
01:06
In the query window, in the first line, type SELECT FROM Pipe WHERE material = "MDPE";.
01:16
In the second line type SET k = 0.1
01:22
Click the Test button.
01:24
A pop-up will appear indicating valid syntax.
01:28
Click OK.
01:31
Click Save to save the SQL before you run it, then click Run.
01:36
You will notice that the SQL window will close but the MDPE pipes are selected in the Geoplan.
01:43
Using the Properties tool, select any of the highlighted pipes.
01:49
In the Properties Window, the CW - k (mm) value changes to 0.1.
01:55
Commit the changes to the database.
01:60
Create a new Stored Query by right-clicking the Stored Query Group, and select New > Stored Query.
02:08
Name the stored query “User Text 1”.
02:12
Double-click the “User Text 1” stored query object to open the Stored Query window.
02:18
From the Object Type drop-down, select Customer Point.
02:23
In the query window type “SET user_text_1 = "Significant Dom User" WHERE spec_consumption > 500”.
02:37
Click Test.
02:39
A notification will indicate valid syntax.
02:44
Click OK.
02:46
Click Save and then Run.
02:49
Once the SQL has completed, from the Grid Windows icon, select New Customer Points window from the drop-down.
02:57
Scroll to the User Text 1 column and note that some customers have now been specified as Significant Users.
03:06
Commit the changes to the database.
00:03
SQLs may be used to update network data regarding selected objects and can be carried out easily by using SQL in InfoWorks WS Pro.
00:13
In this unit we will undertake two examples,
00:16
the first is to update the friction factor of pipes made of MDPE and the second is to populate a User Text Field.
00:24
To begin, open the network.
00:27
From the Model Group window, expand the SQL Model group.
00:32
Open the SQL Network and SQL Control by double-clicking the SQL Network, or by dragging and dropping it into the main window.
00:41
Right-click on the Stored Query Group, select New, and from the flyout select Stored Query.
00:49
Call the stored query “MDPE FF”.
00:54
Double-click on the “MDPE FF” stored query object to open the Stored Query window.
01:01
From the Object Type drop down menu, select Pipe.
01:06
In the query window, in the first line, type SELECT FROM Pipe WHERE material = "MDPE";.
01:16
In the second line type SET k = 0.1
01:22
Click the Test button.
01:24
A pop-up will appear indicating valid syntax.
01:28
Click OK.
01:31
Click Save to save the SQL before you run it, then click Run.
01:36
You will notice that the SQL window will close but the MDPE pipes are selected in the Geoplan.
01:43
Using the Properties tool, select any of the highlighted pipes.
01:49
In the Properties Window, the CW - k (mm) value changes to 0.1.
01:55
Commit the changes to the database.
01:60
Create a new Stored Query by right-clicking the Stored Query Group, and select New > Stored Query.
02:08
Name the stored query “User Text 1”.
02:12
Double-click the “User Text 1” stored query object to open the Stored Query window.
02:18
From the Object Type drop-down, select Customer Point.
02:23
In the query window type “SET user_text_1 = "Significant Dom User" WHERE spec_consumption > 500”.
02:37
Click Test.
02:39
A notification will indicate valid syntax.
02:44
Click OK.
02:46
Click Save and then Run.
02:49
Once the SQL has completed, from the Grid Windows icon, select New Customer Points window from the drop-down.
02:57
Scroll to the User Text 1 column and note that some customers have now been specified as Significant Users.
03:06
Commit the changes to the database.
In InfoWorks WS Pro, SQLs can be used to update network data of selected objects. In this exercise, you use SQL to first update the friction factor of pipes made of MDPE, and then a second time to populate a user text field.
A pop-up appears indicating valid syntax.
The SQL window closes, and the MDPE pipes are now selected in the GeoPlan.
In the Properties window, notice the CW - k (mm) value changes to 0.1.
To create the second stored query:
Note that some customers are now specified as Significant Users.
