Using SQL to narrow 1D simulation results

Step-by-step guide

In InfoWorks ICM, all objects or a selection of objects can be viewed in a results grid window. SQL queries can also be used to create selections to narrow simulation results, or to generate custom tables containing specific object and results fields. For example, a query can select only the nodes that are flooding into a table with the ground level and maximum water level.

To use a results grid to tabulate all the result information for individual objects:

1. Double-click the Design Run simulation to open it in a GeoPlan window.
2. In the toolbar, from the Window menu, select Grid windows, and then select New node results window.

In the node results grid, the green column headings represent time-varying results for the current timestep, while black column headings represent the maximum results. The layout can be customized, sorted, and the results filtered by right-clicking in the grid.

3. Right-click the Max Flood depth column header and select Sort Descending.

Now the nodes with the highest flood depths appear at the top of the grid.

It can be useful to narrow the results down to a custom selection. For example, to see a selection of only the nodes that are flooding, use an SQL query to make this selection with specific criteria:

4. In the Database, right-click the 1D Sewer Model group and select New InfoWorks > Stored query.
5. In the New Name popup, enter "Flooding Nodes".
6. Click OK.
7. In the Database, double-click Flooding Nodes to open the SQL window.

To set up a query to select only nodes that are flooding:

8. On the SQL tab, in the Object Type drop-down, select All Nodes.

9. Type the following query directly into the text box:

SELECT WHERE sim.max_floodvolume > 1 OR sim.max_flvol > 1;

This query identifies all nodes with a flood volume, stored or lost, greater than the tolerance of 1 m³.

10. Enable Open Selection in Grid View.
11. Click Test.

A notification appears to show if the query is valid and displays the number of items selected.

12. Click OK.
13. Click Save to save the query for later use, if needed.
14. Click Run to run the query.

A grid appears, containing both the object properties and the simulation results based on the query.

15. Scroll the grid to view the various properties for the selected nodes.
16. Select all the rows in the grid.
17. Switch to the model view to see the locations where flooding is occurring highlighted.
18. Right-click anywhere in the GeoPlan and select Zoom to selection.

Further narrow the results with another query to select exactly what information appears in the grid. For this example, the query will select the nodes where the maximum flood depth is between 0 and -0.200m, and display only the node_id, ground_level, and max_flooddepth information.

19. In the database, right-click the 1D Sewer Model group and select New InfoWorks > Stored query.
20. In the New Name popup, enter the name "Risk of Flooding".
21. Click OK.
22. Double-click Risk of Flooding to open the SQL window.
23. On the SQL tab, in the Object Type drop-down, select All Nodes.
24. Use the Builder to help construct the query, or type the query directly into the text box:

SELECT node_id, ground_level, sim.max_flooddepth WHERE sim.max_flooddepth <= 0 AND sim.max_flooddepth >-0.200 ORDER BY sim.max_flooddepth DESC ;

25. Enable Open Selection in Grid View.
26. Click Test to validate the query.
27. Click OK.
28. Click Save.
29. Click Run.

The grid appears with the results and displays only the columns built into the query.

30. Select all the rows in the grid.
31. Switch to the model view to see the locations where there is a risk of flooding highlighted.
32. Right-click the GeoPlan and select Zoom to selection.