• InfoWorks WS Pro

Analyzing simulation results using SQLs

Analyze simulation results using an SQL query.


Tutorial resources

These downloadable resources will be used to complete this tutorial:

iwsp2023M20U04_SQL_Rslt_Anlys.wspt

Step-by-step guide

SQLs may be used to aggregate the simulation results you are interested in. For example, you can produce a grid that illustrates the average flow and velocity across the network, then compare the results with those found in another scenario.

  1. From the Model Group, double click SQL Control to open the Baseline simulation results.

In the InfoWorks WS Pro Model Group, Run Group and Baseline are expanded and SQL Control is called out and being selected.

Note: If the run icon on the left of the simulation is greyed out, right-click the run and select Re-run.

To create a query illustrating average flow and velocity in the network:

  1. Right-click the Stored Query Group and select New > Stored Query.

In the Model Group, Stored Query shortcut menu, New is selected, and in the flyout, Stored Query is selected and called out.

  1. In the popup, name the stored query “Baseline- Average”.
  2. In the Model Group, double-click Baseline- Average to open the Stored Query dialog box.
  3. From the Object Type drop-down, select Pipe.
  4. In the text box, type the following query:

SELECT SUM(length) as “Length”, AVG(sim.flow) as “Average Flow”, AVG(sim._Velocity) as “Average Velocity”

  1. Click Test.

A notification appears, indicating valid syntax.

  1. Click OK.
  2. Click Save.
  3. Click Run.

In the SQL dialog box, the Baseline- Average SQL query is entered in the text box, and Run is selected.

The SQL window closes and a results grid opens, containing the total length of pipes in the network, the average flow, and the average velocity.

An SQL results grid showing the total length of pipes in the network, the average flow, and the average velocity.

  1. Close the grid window.

To compare these results with an alternative baseline scenario:

  1. From the Model Group, expand the Pipe Break Scenario.
  2. Right-click the [Pipe Break] SQL Control simulation and select Open as.
  3. In the Select Result dialog box, select As alternate results for comparison.
  4. Click OK.
  5. In the Model Group, double click Baseline- Average to open the SQL dialog box.

To extend the script to include the second scenario:

  1. In the text box, at the end of the last line, type a comma (,).
  2. After the comma, type the following, starting with a SPACE:

AVG(sim2.flow) as “Average Flow 2”, AVG(sim2._Velocity) as “Average Velocity 2”

  1. Click Test.

A notification appears, indicating valid syntax.

  1. Click OK.
  2. Click Save.
  3. Click Run.

In the SQL Baseline- Average dialog box, the extended script for the query is entered in the text box, and Run is selected.

The SQL window closes and a grid opens showing the average flow and average velocity for both simulations, and the total length of pipes in the network.

An SQL results grid window showing the total length of pipes in the network, the average flow, and the average velocity for both simulations.

  1. When you are finished comparing the results, Close the grid window.