Results 1 to 7 of 7

Thread: Tracing Upstream and Downstream as an SQL

  1. #1

    Tracing Upstream and Downstream as an SQL

    Is it possible to write an SQL that will trace and select everything upstream of a given point in InfoNET or InfoWorks. I know you can obviously use the trace tool but I want to caputure some starts from the result and repeat for every sewer in a catchment.

    Thanks Barry

  2. #2
    Forum Moderator

    Innovyze Employee

    Join Date
    Feb 2013

    Our Support Team sent this reply to Barry in an e-mail, but the content may be of interest to others looking to do similar things in InfoWorks and InfoNet.

    Try the following which works on a selection of one or more nodes. It creates a prompt into which the user enters a number and the SQL will then select that number of pipes upstream. Entering a very large number would ensure that the SQL selects the entire network upstream. NB, the SQL doesn't work on lateral nodes that are on a pipe but don’t split that pipe.

    LET $n = 0;

    PROMPT TITLE 'Select Number of Pipes to go Upstream';
    PROMPT LINE $n 'Number of Links';


    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 < $n;
    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;

    SELECT FROM [All Links] WHERE $link_selected = 1; SELECT FROM [All Nodes] WHERE $node_selected = 1;
    Andrew Walker
    Client Service Manager | Innovyze | Wallingford, UK
    Web: | Twitter: @innovyze

  3. #3
    When I try this in IW CS 12.5 IW gives me an error: "Error in preparing WHERE subclause of clause 9: 'WHILE$count<$n': $count:expected operator"... any suggestions?

  4. #4
    Forum Moderator

    Innovyze Employee

    Join Date
    Feb 2013
    Hi Ian,

    The SQL sequence will only work in CS v13.5 or later. The WHILE function was one of a number of SQL enhancements added at the v13.5 release.

    Enhancements to SQL features

    • New date/time processing SQL functions are available - INYEAR(date,number), INMONTH(date,month,year), INYEARS(date,startyear,endyear) , INMONTHS(date,startmonth,startyear,endmonth,endyea r), ISDATE(putative_date), MONTHYEARPART(date), YEARMONTHPART(date), MONTHNAME(date), SHORTMONTHAME(date), DAYNAME(date) and SHORTDAYNAME(date).
    • Trigonometric functions, logs and exponents are now supported - LOG(x), LOGE(x), EXP(x), SIN(x), COS(x), TAN(x), ASIN(x), ACOS(x), ATAN(x), ATAN2(x,y) and GAMMALN(x).
    • A new list function is available - AREF(n,list).
    • The INSERT statement allowing objects to be inserted into the network and rows to be inserted into blobs is now supported.
    • Possibility of saving scalar and list variables to files using SAVE.
    • Possibility of reading scalar and list variables from files LOAD.
    • WHILE loops are now available.
    • The ORDER BY clause, allowing users to sort records in result sets, has been added. TOP and BOTTOM functions can now be used with the ORDER BY clause.
    • Object flags can be treated in the same way as array fields and as such, aggregate functions can now be used on them.
    • The syntax has slightly changed from INTO 'd:\temp\myfile.csv' to INTO FILE 'd:\temp\myfile.csv' as INTO can be used for storing values in scalars.

    See SQL syntax topic in the Help system of v13.5 for more information. You might also like to refer to the SQL in InfoWorks and InfoNet technical paper (available within the Help system) for comprehensive information on the use of SQL and all the functions supported.
    Last edited by Andrew Walker; July 24, 2013 at 08:34 AM.
    Andrew Walker
    Client Service Manager | Innovyze | Wallingford, UK
    Web: | Twitter: @innovyze

  5. #5
    Ah! that would explain it. Thanks.

  6. #6
    Great SQL Andrew... I didn't realise you could do traces via an SQL

    Is it possible to loop this trace to do it on every pipe in a model network? I am trying to pull out the Node ID of the node with the lowest cover level upstream of every pipe in a model and write the Node ID to a user text column i.e. work out the lowest point in a network upstream of every pipe within that network. Using the SQL posted by Andrew I can do this by selecting each pipe manually and running the SQL but can you automate the process to loop through the pipes table?

    I was going to write something in MapBasic to do this but to be able to do it within InfoWorks would be great!


  7. #7

    Join Date
    May 2017

    Sorry for dragging this thread back up but I believe it's relevant to my query. I have implemented the above SQL query successfully, however I was wondering if it was possible to introduce a condition whereas an upstream link is only selected if it has flow going through it.

    So for example, I would run a dry-weather simulation and use that to only select upstream links that have flwo above a certain threshold.


Posting Permissions

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