Page 4 of 11 FirstFirst ... 23456 ... LastLast
Results 31 to 40 of 101

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

  1. #31
    This is an example of spatial analysis with SQL. The following code selects all links and nodes inside a polygon:

    LIST $PolygonID STRING;
    SELECT DISTINCT polygon_id INTO $PolygonID;

    PROMPT TITLE"Dialog";
    PROMPT LINE $sel 'Polygon' LIST $PolygonID;
    PROMPT DISPLAY;

    SELECT FROM [All Nodes] WHERE spatial.polygon_id = $sel;
    SELECT FROM [All Links] WHERE spatial.polygon_id = $sel;


    sql.png

  2. #32
    SQL can also be a powerful for themes in the Geoplan. In the example below, the SQL compares the 2D water depth of two simulations and shows the difference if higher than a treshold (0.1):

    IIF(sim.depth2d-sim2.depth2d > 0.1, sim.depth2d-sim2.depth2d , 0.0)

    You can add this simple expression in the "GeoPlan Properties and Themes > 2D Zone Theme" as shown in the next figures.

    sql2.pngsql1.png

  3. #33

    Join Date
    Apr 2016
    Posts
    7
    Quote Originally Posted by Duncan Kitts View Post
    This query will take a selected node, trace upstream, refine the selection to include subcatchment, deslect all objects other than the subcatchments
    Smart, this is often useful and with a few tweaks you can get it to do other equally frequent selections. Thanks
    Last edited by DanMor; August 29, 2016 at 06:25 PM.

  4. #34
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    133
    Quote Originally Posted by Kristian Ravnkilde View Post
    Clever. How about manholes/ links with no upstream subcatchments? I have never managed to automate that, and they can cause major instabilities, especially in quality models.
    The following should work for all nodes, you can refine further for manholes, it sets user text 1 to 1 for all nodes that a subcatchment drain to, then selects those nodes with no upstream links and where user text 1 is not equal to 1:-

    UPDATE [Subcatchment] SET node.user_text_1=1;
    count(us_links.*)=0 AND user_text_1<>1

  5. #35
    A section of this query is also very useful in expanding from a selection of nodes to any subcatchment(s) that are associated to that node.

    1.PNG

    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;

  6. #36
    This is an SQL to update Head Discharge Curves.
    You can add the curve to a txt file and run the script to update the curve automatically. The SQL will prompt what curve ID to update and do all the job!

    The SQL script:

    LIST $QDIDS STRING;
    SELECT ALL DISTINCT head_discharge_id INTO $QDIDS;

    LIST $CurveQ STRING;
    LOAD $CurveQ FROM FILE 'C:\Curve.dat';
    LIST $CurveH STRING;
    LOAD $CurveH FROM FILE 'C:\Curve.dat';

    PROMPT TITLE 'H-Q Name';
    PROMPT LINE $UpdateID 'HQ ID' LIST $QDIDS;
    PROMPT DISPLAY;

    DELETE From [Head discharge].HDP_table WHERE head_discharge_id = $UpdateID;

    LET $n = 1;
    WHILE $n<=LEN($CurveQ);
    LET $UpdateQ = AREF($n,$CurveQ);
    LET $UpdateH = AREF($n, $CurveH);
    INSERT INTO [Head discharge].HDP_table (head_discharge_id,HDP_table.discharge,HDP_table.h ead) VALUES ($UpdateID,$UpdateQ,$UpdateH);
    LET $n=$n+1;
    WEND


    An example of a txt in the 'C:\Curve.dat' file:
    $CurveQ,90,80,70
    $CurveH,0.0,0.3,0.7

  7. #37

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    13
    This query goes from a selected pipe(s) a number (you enter) of pipe lengths upstream.

    PROMPT TITLE 'US Trace';
    PROMPT LINE $USCount 'How many pipes segments upstream to select?' DP 0;
    PROMPT DISPLAY;

    LET $z=1;
    WHILE $z <= $USCount;
    LIST $nodeID STRING;
    SELECT SELECTED DISTINCT us_node_id INTO $nodeID;
    LET $i=1;
    WHILE $i<=LEN($nodeID);
    SELECT FROM [All Links] WHERE ds_node.node_id = AREF($i,$nodeID);
    LET $i=$i+1;
    WEND;
    LET $z=$z+1;
    WEND

  8. #38
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    133
    Quote Originally Posted by Duncan Kitts View Post
    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.
    Add the following line to sum the populations in the selected subcatchments, useful for determining populations upstream of a pump catchment.

    SELECT SELECTED Sum(population) FROM Subcatchment;

  9. #39
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    133
    INSERT INTO [Node].hyperlinks (node_id, hyperlinks.description,hyperlinks.url) SELECT node_id, 'Sheet_name', 'D:\FileName_Texttobereplaed.pdf' from node;

    SET hyperlinks.url=GSUBST('D:\thisisafilename_Texttobe replaced.pdf', 'Texttobereplaced', user_text_1)

    The first part of the SQL sets up a Hyperlink with the description ‘Sheet Name’ and the File Location (D:\FileName_Texttobereplaced.pdf) for all nodes.

    The second part then takes the file location string and replaces the ‘Texttobereplaced’ with the User_Text_1 value (which might be a drawing number for instance). Similar can be done for links/subcatchments etc...

  10. #40
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    133
    Export node hyperlinks to a csv file:-

    SELECT node_id, hyperlinks.description, hyperlinks.url into File 'D:/HyperlinkSpreadsheet.csv';

    Similar can be done for links/subcatchments.

    The following can also be used to change the url address should the folders be renamed in Windows Explorer:-

    Set hyperlinks.url=SUBST (hyperlinks.url, '\OldFileName\, '\NewFileName\');

    This uses the SUBST key word.

    SUBST
    SUBST(string,thing to replace, thing to replace it with)
    Replaces the first instance of the second parameter in the first with the third e.g. SUBST('01880132','01','ND') returns 'ND880132'

Posting Permissions

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