Page 2 of 10 FirstFirst 1234 ... LastLast
Results 11 to 20 of 92

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

  1. #11
    Forum Moderator

    Innovyze Employee



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

  2. #12
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    133
    Note, that the actual table name is case-insensitive.

  3. #13
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    133
    Set the node system type to be the same as that of the connecting subcatchment:

    Set system_type=subcatchments.system_type

  4. #14
    Forum Moderator

    Innovyze Employee



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

  5. #15
    Forum Moderator

    Innovyze Employee



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

  6. #16
    Forum Moderator

    Innovyze Employee



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

  7. #17
    Forum Moderator

    Innovyze Employee



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







  8. #18
    Forum Moderator

    Innovyze Employee



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

  9. #19
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    133
    Capture.JPG

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

  10. #20
    Forum Moderator

    Innovyze Employee



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

Posting Permissions

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