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

[Duncan Kitts]

A new one that I came accross today. If you are importing data into a table, the Table Name is identical to that specified in the 'Object Type' section of the SQL editor. If the Table name includes spaces then the table name should be contained within square brackets. The space is NOT represented using an underscore.

For instance to add data to the Runoff Surface table the SQL table reference is:-

[Runoff Surface] NOT Runoff_Surface

[Duncan Kitts]

Note, that the actual table name is case-insensitive.

[Duncan Kitts]

Set the node system type to be the same as that of the connecting subcatchment:

Set system_type=subcatchments.system_type

[Duncan Kitts]

The following will provide a table of a count of all the U/S Headloss values grouped into integer classes:

SELECT COUNT (*) GROUPBY INT(us_headloss_coeff)

Capture.JPG

[Duncan Kitts]

A way of working out the duration of time that a pump is flowing greater than 0 for:-

Select Duration (tsr.ds_flow>0)

Note, that this will be the total duration and may not be contiguous. It is possible to limit the time periods within which the query is run. This is only available in version 6 or later.

[Duncan Kitts]

A way of automatically values to a storage array for a storage node.

Insert Into [node.storage_array] (node_id, storage_array.level, Storage_array.area) SELECT node_id,user_number_1,1000;
Insert Into [node.storage_array] (node_id, storage_array.level, Storage_array.area) SELECT node_id,user_number_2,1000;

In this case the user number 1 and 2 were used to take invert/soffit levels from upstream and downstream conduits.

[Duncan Kitts]

Count number of flooding nodes and output to a csv table together with the total flood volume


SELECT COUNT (sim.max_floodvolume>0) AS "Flooded Manholes", SUM(sim.max_floodvolume) AS "Total Volume" INTO FILE 'D:\flood volumes.csv'

[Duncan Kitts]

A check for upstream link width greater than downstream link width. This is applied to 'All Nodes' object type

us_links.conduit_width>ds_links.conduit_width

[Duncan Kitts]

Capture.JPG

This query will update the node ground level with the mesh level zone assigned elevation in which the node resides.

[Duncan Kitts]

This query will take a selected node, trace upstream, refine the selection to include subcatchment, deslect all objects other than the subcatchments:-

UPDATE [ALL Links] SET $link_selected = 0;
UPDATE [ALL Nodes] SET $node_selected = 0;
UPDATE SELECTED SET $node_selected = 1;

LET $count = 0;
WHILE $count < 100;

SET us_links.$link_selected = 1 WHERE $node_selected = 1;
UPDATE [ALL Links] SET us_node.$node_selected = 1 WHERE $link_selected = 1;
LET $count = $count + 1;
WEND;

SELECT FROM [All Nodes] WHERE $node_selected = 1;

UPDATE Subcatchment SET $subcatchment_selected =0;
UPDATE SELECTED SET subcatchments.$subcatchment_selected =1;
SELECT FROM Subcatchment WHERE $subcatchment_selected =1;

DESELECT ALL

The while keyword will select all nodes upstream for a count of 100. If you need this to be larger you can edit the number as required.