Do you use SQL queries to build your model or analyse your simulation results?

is anyone using sql to analyze the result to get hourly peak flow, 24 hour peak flow out of link result table?

The following will provide a list of pipe names with the pipe downstream average velocity for the simulation. It can be easily modified for upstream velocity or flow.

SELECT us_node_id, link_suffix,AVG(tsr.ds_vel)

LET $n = 0;
PROMPT TITLE "Manning's n for River Reach Sections";
PROMPT LINE $n "Manning's n value" DP 3;
PROMPT DISPLAY;
SET sections.roughness_N=$n

The above query will provide a prompt dialogue where a user can specify a Manning's n value which is then used to update the river reach section roughness. You could use this in conjunction with the custom actions (http://blog.innovyze.com/2015/10/16/custom-actions/) functionality to speed up building river models. A similar thing could be done with Bank coefficients

Is there SQL query to select manholes without upstream link?

Duncan

Will Innovyze be publishing the SQLs/tips that have emerged? (the post is rather long to look through)

Quote:

---

Originally Posted by DS Wessex

Duncan

Will Innovyze be publishing the SQLs/tips that have emerged? (the post is rather long to look through)

---

No plans to. This post was intended to be a single location for queries.

Mr. Abhi, this should work
count(us_links.*)=0

A query to determine which subcatchments are using a particular runoff surface:-

“
CLEAR SELECTION;
SET $runoffSurfaceID="111";

SELECT FROM [land use]WHERE runoff_index_1= $runoffSurfaceID or runoff_index_2= $runoffSurfaceID or runoff_index_3= $runoffSurfaceID or runoff_index_4= $runoffSurfaceID or runoff_index_5= $runoffSurfaceID or runoff_index_6= $runoffSurfaceID or runoff_index_7= $runoffSurfaceID or runoff_index_8=$runoffSurfaceID or runoff_index_9= $runoffSurfaceID or runoff_index_10= $runoffSurfaceID or runoff_index_11= $runoffSurfaceID or runoff_index_12= $runoffSurfaceID;

LIST $landUseID STRING;
SELECT SELECTED DISTINCT land_use_id INTO $landUseID;
LET $i=1;
WHILE$i<=LEN($landUseID);

SELECT FROM subcatchment WHERE land_use_id=AREF($i,$landUseID);

LET $i=$i+1;
WEND;

SCALARS
“

Justchange the line SET $runoffSurfaceID ="111"; with the runoff surface you area looking for.

The following will allow create a table of subcatchments, the rainfall profile and the simulated NAPI value at a particular point in time.

SELECT subcatchment_id AS 'ID', rainfall_profile AS 'Rain gauge', MAX(tsr.napi) AS 'NAPI' WHEN tsr.timestep_start = #01/01/2016 00:00#

This SQL selects upstream links of selected nodes:

LIST $nodeID STRING;
SELECT SELECTED DISTINCT node_id INTO $nodeID;

DESELECT ALL;

LET $i=1;
WHILE $i<=LEN($nodeID);
SELECT FROM [All Links] WHERE ds_node.node_id=AREF($i,$nodeID);
LET $i=$i+1;
WEND;