Page 6 of 6 FirstFirst ... 456
Results 51 to 60 of 60

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

  1. #51
    This SQL sums all the population in US sucatchments of selected nodes and store it in the user_number_1. Set the object type as "All Nodes" and use the following code:

    LIST $nodeID STRING;
    SELECT SELECTED DISTINCT node_id INTO $nodeID FROM [All Nodes];


    LET $i=1;
    WHILE $i<=LEN($nodeID);
    DESELECT ALL FROM [All Nodes];
    SELECT FROM [All Nodes] WHERE node_id= AREF($i,$nodeID);

    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;


    DESELECT ALL From Subcatchment;


    SELECT FROM Subcatchment WHERE $subcatchment_selected =1;
    SELECT SELECTED Sum(population) INTO $summ FROM Subcatchment;

    SELECT FROM [All Nodes] WHERE node_id= AREF($i,$nodeID);
    UPDATE SELECTED SET user_number_1=$summ;


    LET $i=$i+1;
    WEND;


    DESELECT ALL FROM [All Nodes];
    DESELECT ALL From Subcatchment;

  2. #52

    Join Date
    Nov 2014
    Posts
    2
    Quote Originally Posted by Rui Daniel Pina View Post
    Please check the "Tracing tools" in the "Geoplan" menu. You can use the "Intermediate paths" to select all the links in paths between two selected nodes. I also recommend you to have a look to the tools "Connectivity" and "Proximity trace".

    Hello Rui, and thanks for your response. However, I tried using the tracing tools as you suggested and the operation never completed. The two selected manholes were approx. 75m apart, in a model of 33,000 nodes.

    Connectivity.JPG


    The dialogue "Intermediate Path Tracing ..." got to 15% and never went any further. I set the process going last night roughly 12 hours ago.
    Connectivity_Progress.JPG

    So, we're still where I started with my initial request. Is there anything else I can try?
    Thanks

    EDIT: as far as I can see, the suggested approach only works if you know the two nodes where there might be a more than a single direct link between two manholes - but I'm after an approach that finds all examples within a network.
    Last edited by Kieron; February 7, 2017 at 02:32 AM.

  3. #53
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    107
    If you want to add a SUDS Control table for all Subcatchments, the following will add a SUDS control ID of, specify a SUDS control feature of DRK and set the type to 'Bio-Retention Cell':-

    Insert Into [subcatchment].suds_controls (subcatchment_id,suds_controls.id, suds_controls.suds_structure, suds_controls.control_type) SELECT subcatchment_ID, '1', 'DRK', 'Bio-Retention Cell' from subcatchment

  4. #54

    Join Date
    Feb 2017
    Posts
    3
    I'm trying to select all nodes ending in "c2", using
    node_id LIKE '%c2'
    This is however not working. Any idea why? I've also used node_id LIKE '%c2%'.
    the Like operator in general doesnt seem to be working, at least not for node_id selection

  5. #55
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    107
    The % Operator is the modulus. I would recommend reviewing the help page particularly 'Section 1.4: String Operators' to see what is possible with the LIKE command.

  6. #56
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    107
    If you open a set of results, and then open another set of results as alternate for comparison, the following query will create a table of the link id, sim1 max surcharge state, sim2 max surcharge state, differences between the 2 surcharge states.

    Select oid As Link_ID,

    sim.max_Surcharge AS Base_Scenario, sim2.max_Surcharge As Post_Development_Scenario, sim.max_Surcharge-sim2.max_Surcharge AS Absolute_Difference;

    The table columns are presented as Link_ID, Base_Scenario, Post_Development_Scenario, Absolute_Difference. This can be done for other result parameters and network objects.




  7. #57
    Is there a way to search for values within a range?

    For example I want to find every manhole where the incoming invert level does not equal the outgoing invert level; however, the difference between the two is within 1mm. This seems to have occurred where the modeller has input values to 3dp rather than the all the decimal places calculated using standard inference tools in ICM.

  8. #58
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    107
    Yes, you can use the logic operator, 'AND' to do something similar to the following:-
    conduit_width>100 AND conduit_width<250

    to select conduit with a width between 100 and 250mm.

    However, I don't think you'll need to do that in your instance. I assume you want to select manholes where the difference between the US Link DS Invert level and the DS Link US Invert Level is greater than 0.001m, or:-


    ABS(us_links.ds_invert-ds_links.us_invert)>0.001






  9. #59
    Brilliant thanks, didn't think of that

  10. #60
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    107
    The following will add a node for each subcatchment at the subcatchment centroid (or wherever the X,Y co-ordinates are located if not default):-

    Insert Into (Node_ID, X,Y) SELECT (Subcatchment_ID), (X), (Y);

    The subcatchment object type needs selecting in the SQL editor.

Posting Permissions

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