Page 8 of 10 FirstFirst ... 678910 LastLast
Results 71 to 80 of 98

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

  1. #71

    Join Date
    Aug 2017
    Posts
    3
    Thanks Guillermo,

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

    Quote Originally Posted by Guillermo View Post
    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, "(","{"),")","}") + ';"');

  2. #72
    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;
    Attached Images Attached Images
    • File Type: jpg 1.JPG (48.0 KB, 59 views)

  3. #73
    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;

  4. #74
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    133
    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

  5. #75
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    133
    This will remove SUDs controls from all subcatchments. You can apply to a selection if required.

    'DELETE FROM [Subcatchment].suds_controls

  6. #76
    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;

  7. #77
    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

  8. #78
    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)

  9. #79

    Join Date
    Aug 2013
    Posts
    22
    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.


  10. #80

    Join Date
    Mar 2018
    Posts
    5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •