Using SQL Queries to add Scenarios and Update Object Parameters

There are many blog posts on here highlighting the usefulness and effectiveness of the SQL functionality.  This blog post provides another way to use SQL queries to automatically add scenarios to a network and then to change object parameters within these scenarios.  There are a number of ways these can be used but probably the most common example if to undertake sensitivity analysis, for instance take an existing 2D model and add 2 scenarios, one to increase roughness values by 10% and one to reduce roughness values by 10%.

Assuming there is an existing network with a 2D zone, the first step is to write an SQL query to create the Scenarios.  This can be done simply by using the ADD SCENARIO keywords and then providing the name of the scenarios.  The example below creates 2 scenarios called ‘Mannings n +10%’ and ‘Mannings n -10%’

Once created you can then use another query to change the roughness values in each scenario. It is not possible to add this step to the above SQL query as the scenarios don’t exist. Therefore we need a separate query. In this example I will only change the roughness in the 2D zone but it would be easy to also change the roughness values in river sections, conduits, roughness zones and bridges.

If you apply the 2 SQLs sequentially it will create the scenarios and then update the 2D zone roughness within the 2 scenarios.  This has the inconvenience of dragging 2 SQLs out onto the network geoplan.

There is a neat trick to get around this, and this is to add the 2 SQL queries to a model group and number them 1. and 2. as below.

Once this has been setup, the user can drag the SQL model group onto the network geoplan and the 2 queries will be applied sequentially in numeric order.  This could be used in conjunction with the custom actions described I the following blog post:-

In this instance the SQL queries have been used to create scenarios and then update the roughness values.  However, these queries could also be used to change population values in subcatchments, change pipe widths or add Subcatchment SUD/LID features for example.


Share this post!

    About Duncan Kitts

    Duncan Kitts is a Senior Support Engineer with Innovyze in the United Kingdom, specializing in River modelling and 2D modelling. He has over 9 years experience of modeling the key hydraulic processes involved in both fluvial systems and urban drainage environments. Duncan is responsible for providing support of both infoworks ICM and Infoworks RS.
    This entry was posted in InfoWorks ICM and tagged , , , . Bookmark the permalink.