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

[lee]

Thanks Guillermo,

Appears to work! Just need to incorporate it into all my queries now.

Hi Lee,

You could try using the subst function directly over the query as follows, I think it might work:

SET notes = IIF(notes matches '.*Interest: ' + subst(subst(spatial.SSSI_NAME, "(","{"),")","}") + '.*',notes,notes +'&CHAR(10)&"Sites of Special Scientific Interest: ' +subst(subst(spatial.SSSI_NAME, "(","{"),")","}") + ';"');

[Rui Daniel Pina]

This is to add multiple lateral links to subcatchments:

1. Run this spatial SQL to find the links inside subcatchments:
1.JPG
code:
set user_text_1= spatial.subcatchment_id


2. Run this SQL to add lateral links to subcatchments, with Object Type "Subcatchment" use this code:

LIST $clist STRING;
SELECT DISTINCT oid INTO $clist FROM [All Links];


let $node_id="";
let $link_suffix = "";
let $subcatchment_id="" ;


LET $i=1;
WHILE $i <= LEN($clist);
DESELECT ALL;


select user_text_1 into $subcatchment_id FROM [All Links] where oid=AREF($i,$clist) ;
select us_node_id into $node_id FROM [All Links] where oid=AREF($i,$clist);
select link_suffix into $link_suffix FROM [All Links] where oid=AREF($i,$clist) ;


IF $subcatchment_id <> "" and $subcatchment_id <> 0.00;
INSERT INTO subcatchment.lateral_links(subcatchment_id, lateral_links.node_id, lateral_links.link_suffix) VALUES ($subcatchment_id, $node_id, $link_suffix);
ENDIF;


LET $i=$i+1;
WEND;

[Rui Daniel Pina]

This SQL renames cross sections in river reaches. Just create a new stored query with Object type "River reach" with the following code:

LIST $listRivers string;
SELECT DISTINCT oid INTO $listRivers;


LET $j=1;
WHILE $j<=LEN($listRivers);
SELECT WHERE oid = AREF($j,$listRivers);


LIST $listXS string;
SELECT SELECTED DISTINCT sections.key INTO $listXS;


LET $i=1;
WHILE $i<=LEN($listXS);
set sections.key = oid + ':' + $i WHERE sections.key = AREF($i,$listXS) and oid = AREF($j,$listRivers);
LET $i=$i+1;
WEND;


DESELECT ALL;


LET $j=$j+1;
WEND;

[Duncan Kitts]

A simple query to set nodes to outfalls where they have no downstream links. Particularly useful if you're importing from asset data which doesn't specify the outfall node type explicitly.

SET node_type='outfall' where count(ds_links.*)=0

[Duncan Kitts]

This will remove SUDs controls from all subcatchments. You can apply to a selection if required.

'DELETE FROM [Subcatchment].suds_controls

[Rui Daniel Pina]

The following SQL select nodes with higher chamber_floor level in the current scenario than in the base scenario:
1.JPG

The code is:
DESELECT ALL;
UPDATE IN BASE SCENARIO SET $x = chamber_floor;
SELECT WHERE chamber_floor>$x;

[Oli Moravszky]

Is there a way to use SQLs to select nodes where the combined capacity of the incoming pipes exceeds the combined capacity of the outgoing pipes by a given percentage? This is useful for finding incorrect pipe diameters

Thanks
Dave Searby, Wessex Water

[Guillermo]

clear selection; (sum(us_links.capacity)-sum(ds_links.capacity))/sum(ds_links.capacity)*100>xx

where xx is the desired percentage.This is the fields names for ICS, for ICM don't rememeber if the field capacity has the same name. Remember that if you are changing geometry data you should validate in order to update the capacity and that certain elements differents form pipes might not have this field (such as Weirs)

[DCJ]

Statistics Template is a good application for model pump runtimes too.


We use a similar query for dual pumpstation starts...

SELECT us_node_id, Duration (tsr.pmpstate>0) WHERE link_suffix = '2'

We also use a script to report when "minimum_flow < nominal_flow". This is useful when changes to the system reduce pumping capacity of the existing stations.

[James_F]

does anyone know how to present the maximum depth from two set of results in the 2D domain? (assuming the meshes are identical)

I was hoping this would work:

(sim.max_depth2d+sim2.max_depth2d)-min(sim.max_depth2d,sim2.max_depth2d);

the fisrt bit works but min isn't recognised