Simplifying the input of Culvert Inlet Parameters with SQL Prompts in InfoWorks ICM

The use of the SQL functionality is subject to a large number of blog posts within our blog pages.  This is because they allow the user to avoid repetitive tasks and automate processes.  One of the SQL functionality which is well described is the use of Prompts with the following blog posts providing more details and examples:-

These allow prompt dialogs to be displayed which prompt for user input as part of the SQL query.  As shown in the latter of the above links, these can be very useful when creating model networks.  One part of building models which can be quite tedious is adding culvert inlets, specifically determining the correct parameters (K,M,c,Y,Ki) for the specific culvert inlet type (as identified in the ‘Culvert Inlets’ help page) from the tables taking from the Culvert Design Manual (1997).  I find myself flicking backwards and forwards between the software and the help page.

Figure 1: Culvert Inlet Parameters

This is one situation where developing a series of prompts can be used to allow the user to select the barrel shape, the material and the inlet type in turn leads to the automation of the setting of culvert inlet parameters based on the selection.  This is done with a series of Prompt, IF and SET statements within an SQL query as shown at the bottom of this blog post.  

The first step is to select the culvert inlet that you wish to populate the culvert inlet parameters.  Then run the SQL (this could be associated with a custom action, see the following blog post for more information:- http://blog.innovyze.com/2016/06/14/sql-prompts-and-custom-actions-to-edit-river-reach-parameters-in-infoworks-icm/)

 The SQL query will firstly prompt for the conduit type, with a dropdown menu of:

  • Circular,
  • Rectangular,
  • Pipe Arch Conduit (ARMCO) 450mm circular radius,
  • Pipe Arch Conduit (ARMCO) – 790 mm corner radius,
  • Full Arch Conduit

Figure 2: Culvert Barrel Shape Prompt

Depending on the option selected, the user is then prompted to choose the material with the following options:-

  • Concrete,
  • Corrugated metal.

Figure 3: Culvert Inlet Material Prompt

Finally the user is prompted for the Inlet Edge type which will depend on the previous two options (see the tables in the ‘Culvert Inlet’ help page for the inlet edge types.

Figure 4: Culvert Inlet Edge Type Prompt

Once the various options have been selected, then the selected culvert inlet will have the inlet parameters specified accordingly.

The text for the SQL is below.  If you directly copy and paste from the below into the SQL editor, depending on your web browser you may get an error when running the SQL query. 

Figure 5: SQL Query Error Message

This can be resolved by using the steps in the following blog post:

http://blog.innovyze.com/2015/04/21/copying-sqls-from-an-internet-browser-into-infonet-error-message-error-parsing-query-invalid-character-at-start-of-token/

Also, when creating the SQL query ensure that you tick the option to ‘Apply filter to current selection’ otherwise the query will be applied to all culvert inlets in the model.  Although the SQL query and parameters have been checked, please review any parameters and let us know if there are any obvious errors.

Reference:

Culvert Design Manual (1997, Ramsbotom D., Day R., and Rickard C. Culvert Design Manual. Construction Industry Research and Information Association (CIRIA) report no. R168.

SQL Text

LIST $conduit_type = ‘Circular Conduit’, ‘Rectangular Conduit’, ‘Pipe Arch Conduit (ARMCO) 450mm circular radius’, ‘Pipe Arch Conduit (ARMCO) – 790 mm corner radius’, ‘Full Arch Conduit’;

LIST $Material= ‘Concrete’, ‘Corrugated Metal’;

PROMPT TITLE ‘Input Culvert Barrel Shape;

PROMPT LINE $ConduitSelected ‘Conduit Type: ‘ STRING LIST $conduit_Type;

 

PROMPT DISPLAY;

 

IF $conduitselected=’Circular Conduit’;

 

PROMPT TITLE ‘Input Material’;

PROMPT LINE $MaterialSelected ‘Material’ STRING LIST $material;

 

PROMPT DISPLAY;

 

IF $MaterialSelected=’Concrete’;

LIST $InletTypeCircularConcrete=’Headwall / square edge‘, ‘Headwall / socket end of pipe’, ‘Projecting / socket end of pipe’;

 

PROMPT TITLE ‘Inlet Type’;

PROMPT LINE $InletTypeselected ‘Inlet Type:’ STRING LIST $InletTypeCircularConcrete;

 

PROMPT DISPLAY;

 

SET equation=’A’, k=0.0098, m=2.0, c=0.0398, y=0.67, headloss_coeff=0.5 WHERE $InletTypeselected=’Headwall / square edge‘;

 

SET equation=’A’, k=0.0078, m=2.0, c=0.0292, y=0.74, headloss_coeff=0.3 WHERE $InletTypeselected=’Headwall / socket end of pipe‘;

 

SET equation=’A’, k=0.0045, m=2.0, c=0.0317, y=0.69, headloss_coeff=0.3 WHERE $InletTypeselected=’Projecting / socket end of pipe‘;

 

 

ELSEIF $MaterialSelected=’Corrugated Metal’;

LIST $InletTypeCircularmetal=’Headwall / square edge’, ‘Mitred to slope‘, ‘Projecting‘;

 

PROMPT TITLE ‘Inlet Type’;

PROMPT LINE $InletTypeselected2 ‘Inlet Type:’ STRING LIST $InletTypeCircularmetal;

PROMPT DISPLAY;

 

SET equation=’A’, k=0.034, m=1.5, c=0.0553, y=0.54, headloss_coeff=0.5 WHERE $InletTypeselected2=’Headwall / square edge’;

 

SET equation=’A’,  k=0.0018, m=2.5, c=0.03, y=0.74, headloss_coeff=0.7 WHERE $InletTypeselected2=’Mitred to slope‘;

 

SET equation=’A’,  k=0.0018, m=2.5, c=0.0243, y=0.83, headloss_coeff=0.9 WHERE $InletTypeselected2= ‘Projecting‘;

 

ENDIF;

 

ELSEIF $conduitselected=’Rectangular Conduit’;

 

PROMPT TITLE ‘Input Material’;

PROMPT LINE $MaterialSelected2 ‘Material’ STRING LIST $material;

PROMPT DISPLAY;

 

IF $MaterialSelected2=’Concrete’;

LIST $InletTyperectangularConcrete=’Headwall and wingwalls at 30° to 75° to barrel / square edge’, ‘Headwall or headwall and wingwalls at 15° to barrel/ square edge

‘, ‘Headwall and wingwalls at 0° to barrel/ square edge’, ‘Headwall / 20mm chamfers, Headwall / 45° bevels’;

 

PROMPT TITLE ‘Inlet Type’;

PROMPT LINE $InletTypeselected3 ‘Inlet Type:’ STRING LIST $InletTyperectangularConcrete;

PROMPT DISPLAY;

 

SET equation=’A’,  k=0.026, m=1.0, c=0.0385, y=0.81, headloss_coeff=0.3 WHERE $InletTypeselected3= ‘Headwall and wingwalls at 30° to 75° to barrel / square edge’;

 

SET equation=’A’,  k=0.061, m=0.75, c=0.04, y=0.8, headloss_coeff=0.5 WHERE $InletTypeselected3=  Headwall or headwall and wingwalls at 15° to barrel/ square edge‘;

 

SET equation=’A’, k=0.061, m=0.75, c=0.0423, y=0.82, headloss_coeff=0.7 WHERE $InletTypeselected3=’Headwall and wingwalls at 0° to barrel/ square edge’;

 

SET equation=’B’,  k=0.515, m=0.667, c=0.0375, y=0.79, headloss_coeff=0.5 WHERE $InletTypeselected3=’Headwall / 20mm chamfers’;

 

SET equation=’B’,  k=0.495, m=0.667, c=0.0314, y=0.82, headloss_coeff=0.5 WHERE $InletTypeselected3=’Headwall / 45° bevels’;

 

 

ELSEIF $MaterialSelected2=’Corrugated Metal’;

 

LIST $InletTyperectangularMetal=’Headwall / square edge’, ‘Thick wall projecting’, ‘Thin wall projecting’;

 

PROMPT TITLE ‘Inlet Type’;

PROMPT LINE $InletTypeselected4 ‘Inlet Type:’ STRING LIST $InletTyperectangularMetal;

PROMPT DISPLAY;

 

SET equation=’A’,  k=0.0083, m=2.0, c=0.0379, y=0.69, headloss_coeff=0.5 WHERE $InletTypeselected4= ‘Headwall / square edge’;

 

SET equation=’A’,  k=0.0145, m=1.75, c=0.0419, y=0.64, headloss_coeff=0.6 WHERE $InletTypeselected4= ‘Thick wall projecting’;

 

SET equation=’A’,  k=0.034, m=1.5, c=0.0496, y=0.57, headloss_coeff=0.6 WHERE $InletTypeselected4= ‘Thin wall projecting’;

 

ENDIF;

 

ELSEIF $conduitselected=’Pipe Arch Conduit (ARMCO) 450mm circular radius’;

 

LIST $InletTypePAC=’Headwall / square edge’, ‘Mitred to slope’, ‘Projecting’, ‘Headwall / 33.7° bevels’;

PROMPT TITLE ‘Inlet Type’;

PROMPT LINE $InletTypeselected5 ‘Inlet Type:’ STRING LIST $InletTypePAC;

PROMPT DISPLAY;

 

SET equation=’A’,  k=0.0083, m=2.0, c=0.0496, y=0.57, headloss_coeff=0.5 WHERE $InletTypeselected5= ‘Headwall / square edge’;

 

SET equation=’A’,  k=0.03, m=1.0, c=0.0463, y=0.75, headloss_coeff=0.7 WHERE $InletTypeselected5= ‘Mitred to slope‘;

 

SET equation=’A’,  k=0.034, m=1.5, c=0.0496, y=0.53, headloss_coeff=0.9 WHERE $InletTypeselected5= ‘Projecting‘;

 

SET equation=’A’,  k=0.003, m=2.0, c=0.0264, y=0.75, headloss_coeff=0.3 WHERE $InletTypeselected5= ‘Headwall / 33.7° bevels‘;

 

ELSEIF $conduitselected=’Pipe Arch Conduit (ARMCO) – 790 mm corner radius’;

 

LIST $InletTypePAC790=’Headwall / square edge’, ‘Projecting’, ‘Headwall / 33.7° bevels’;

PROMPT TITLE ‘Inlet Type’;

PROMPT LINE $InletTypeselected6 ‘Inlet Type:’ STRING LIST $InletTypePAC790;

PROMPT DISPLAY;

 

SET equation=’A’,  k=0.0087, m=2.0, c=0.0361, y=0.66, headloss_coeff=0.5 WHERE $InletTypeselected6= ‘Headwall / square edge’;

 

SET equation=’A’,  k=0.0296, m=1.5, c=0.0487, y=0.55, headloss_coeff=0.9 WHERE $InletTypeselected6= ‘Projecting‘;

 

SET equation=’A’,  k=0.003, m=2.0, c=0.0264, y=0.75, headloss_coeff=0.25 WHERE $InletTypeselected6= ‘Headwall / 33.7° bevels‘;

 

ELSEIF $conduitselected=’Full Arch Conduit’;

 

LIST $InletTypeFullArch=’Headwall / square edge’, ‘Mitred to slope’, ‘Thin wall projecting’;

PROMPT TITLE ‘Inlet Type’;

PROMPT LINE $InletTypeselected7 ‘Inlet Type:’ STRING LIST $InletTypeFullArch;

PROMPT DISPLAY;

SET equation=’A’,  k=0.0083, m=2.0, c=0.0379, y=0.69, headloss_coeff=0.5 WHERE $InletTypeselected7= ‘Headwall / square edge’;

 

SET equation=’A’,  k=0.03, m=2.0, c=0.0463, y=0.75, headloss_coeff=0.7 WHERE $InletTypeselected7= ‘Mitred to slope‘;

 

SET equation=’A’,  k=0.034, m=1.5, c=0.0496, y=0.57, headloss_coeff=0.6 WHERE $InletTypeselected7= ‘Thin wall projecting‘;

 

ENDIF;

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 ICMLive, InfoWorks CS, InfoWorks ICM, InfoWorks RS and tagged , , , , , , , . Bookmark the permalink.