Page 1 of 7 123 ... LastLast
Results 1 to 10 of 65

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

  1. #1
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    115

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

    If so which ones do you use?

    SQL's or Structured Query language is a highly powerful and flexible way to select objects and edit data based on the object properties. They're particualrly useful for changing values and selecting nodes/links which have particular results.

    If you have got some useful ones, please post them in this thread so that others can use? Hopefully we can start an online repository so that modellers can see how SQLs are used and see some examples. That way we can all build and analyse our models more efficiently.

  2. #2
    If I have a selection list of nodes, can I select the immediate downstream links using SQL? (or any other method, really!)

  3. #3
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    18
    Yes, you can.

    Lets assume the selection is to be done on the model data (rather than a set of results).

    It can be done as a single operation, but requires two SQL's, so start by creating an SQL Group (you'll see why in a moment!).

    In the SQL Group, create your first SQL, which sets a User-Text value on the link attached to the selected nodes (Start with "All Nodes" as your object type). You must tick the box to apply the change to the currently selected nodes.

    SET ds_links.user_number_1=99

    Now create a second SQL which is based on "All links"

    SELECT WHERE user_number_1=99

    When saving your two SQL's, prefix the first with "1" and the second with "2" (again, you'll see why in a moment!).

    To automatically apply each of the two SQL's in sequence you just need to drag the SQL Group onto the GeoPlan. When you drag a Group onto the GeoPlan all of the SQL's in the group are applied in sequence (that's why I suggested you name each of the SQL's in alphabetical or numerical order).

    All links connected to the previously selected nodes are now selected.
    Andrew Walker
    Client Service Manager | Innovyze | Wallingford, UK
    Web: www.innovyze.com | Twitter: @innovyze

  4. #4
    Senior Member
    Join Date
    Feb 2013
    Posts
    119
    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.

  5. #5
    Thanks, Andrew: that works very well.

  6. #6
    We rely heavily on SET queries when doing model build, clean-up, and validation. It makes the process much faster being able to make changes quickly and have the data be flagged properly at the same time.

  7. #7
    Guest
    Join Date
    Mar 2013
    Posts
    2
    You can start with manhoels with no upstream link, and prune iteratively

  8. #8
    Senior Member
    Join Date
    Feb 2013
    Posts
    119
    That sounds like a manual job, or do you have a way of selecting any with no upstream connected area?

  9. #9
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    115
    Below is a useful SQL for exporting cross-section data (x,y,z and Mannings n) to a csv file:-

    select id, section_array.X, section_array.Y, section_array.Z, section_array.roughness_N DP 4 INTO FILE 'd:\mysections.csv'

    Note that the DP4 means that the Mannings n value is exported to 4 decimal places rather than the default 2. Useful for exporting and displaying the relevent decimal places.

  10. #10
    Senior Member

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    121
    Quote Originally Posted by Duncan Kitts View Post
    Below is a useful SQL for exporting cross-section data (x,y,z and Mannings n) to a csv file:-

    select id, section_array.X, section_array.Y, section_array.Z, section_array.roughness_N DP 4 INTO FILE 'd:\mysections.csv'

    Note that the DP4 means that the Mannings n value is exported to 4 decimal places rather than the default 2. Useful for exporting and displaying the relevent decimal places.
    Thanks, that is very useful, Duncan!

Posting Permissions

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