Page 7 of 8 FirstFirst ... 5678 LastLast
Results 61 to 70 of 71

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

  1. #61

    Join Date
    Aug 2017
    Posts
    3
    Hi,

    I'm having trouble using matches with spatial querys. I have a number of similar SQLs that update the notes field where I am querying against polygons within a certain distance. There are instances where some polygons have the same name and each is returned where I would like to only include 1 result for each unique name. I have come up with the following query using the 'matches' operator which works for everything except where the result contains brackets and it is then returning all of those results again.

    SET notes = IIF(notes matches '.*Interest: ' + spatial.SSSI_NAME + '.*',notes,notes +'&CHAR(10)&"Sites of Special Scientific Interest: ' +spatial.SSSI_NAME + ';"');

    I have looked into using [ ] to escape the brackets and for a set string (ie 'test(test)') I can use matches '.*test[(]test[)].*' and it works but as the brackets will be in various places I'd need to escape the entire result and I'm not sure how to do this.

    I've also looked at storing the result as a variable and changing the brackets to { } using SUBST which does work but with my limited SQL knowledge I can only get it to return one result per query.

    Any thoughts or suggestion on the easiest way to achieve this?

    Cheers

  2. #62
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    115
    The following query will take a selection of conduits and for each selected conduit, create a dummy node 2m downstream of the conduit downstream node (witht he suffix _COL) and connect via an orifice link with the orifice invert level set to the conduit downstream invert .

    LIST $ConduitID STRING;

    SELECT SELECTED DISTINCTus_node_id INTO $ConduitID;

    DESELECT ALL;

    LET $i=1;
    WHILE $i<=LEN($ConduitID);
    SELECT FROM [Conduit] WHERE us_node.node_id=AREF($i,$conduitID);

    SELECT SELECTED ds_node.node_idINTO $US;
    SELECT SELECTED ds_node.node_id+'_COL'INTO $A;
    SELECT SELECTED ds_node.X +1.41INTO $X;
    SELECT SELECTED ds_node.y +1.41INTO $Y;

    SELECT SELECTED ds_invert INTO$Invert;
    INSERT INTO node (node_id, x, y)values ($A, $X, $Y);
    INSERT INTO orifice (us_node_id,ds_node_id, link_suffix, invert) values ($US, $A, 2, $invert);
    DESELECT ALL;

    LET $i=$i+1;
    WEND;

  3. #63
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    115
    The following will calculate the floodable area for nodes within a polygon by divided the polygon area by the number of nodes within that polygon. There's a couple of optional steps in here which provide output tables to check the output.SQL Query.jpg

    SET $ID=
    spatial.polygon_id;
    SET $Area=spatial.area;
    SELECT COUNT(*), $Area,$Area/Count(*) AS Flood_Area group by $ID;

    LIST $polygonIDs STRING;
    SELECT DISTINCT polygon_id INTO$polygonIDs FROM [Polygon];

    LET $i=1;
    WHILE $i<=LEN($polygonIDs);

    SELECT WHERE spatial.polygon_ID=AREF($i,$polygonIDs);

    SET $Area=spatial.area;
    SELECT SELECTED COUNT(*) INTO$Test;
    Update SELECTED SETuser_number_1=$Area/$Test;
    Update SELECTED SETuser_number_2=$Area;
    Update SELECTED SETuser_number_3=$Test;

    DESELECT ALL;

    LET $i=$i+1;
    WEND;

    SET floodable_area=user_number_1 WHERE flood_type='Stored';

  4. #64
    Senior Member
    Join Date
    Feb 2013
    Posts
    120
    That's so that it ends up looking like a WASSP model? Nostalgia trip!

    Quote Originally Posted by Duncan Kitts View Post
    The following query will take a selection of conduits and for each selected conduit, create a dummy node 2m downstream of the conduit downstream node (witht he suffix _COL) and connect via an orifice link with the orifice invert level set to the conduit downstream invert .


    WEND;

  5. #65

    find pipes with bends

    I was just random searching the forum. Suppose you just found the answer a year ago, but just in case.The answer is use trigonometry:

    clear selection;Set x$ = ((us_node.x-ds_node.x)^2 + (us_node_u-ds_node
    .y)^2)^1/2; x$<length



    Note: the "length" field name will change depending on the specific software you are using.


    Quote Originally Posted by Eva Choudalaki View Post
    Hi all,

    Is it possible to select links with length larger than the actual distance between the u/s and d/s node? So simply links with bends?

    Thanks
    Last edited by Guillermo; October 20, 2017 at 12:50 AM.

  6. #66

    Join Date
    Nov 2017
    Posts
    2
    Hi,

    I can't manage to run a very basic query. Would anyone know how to select a list of asset? I tried this kind of syntax but it doesn't work:

    SELECT FROM [All Nodes] WHERE node_id in ('id1','id2',...)

    Thanks

    Tom

  7. #67
    Hi Tom,
    This should work:
    list x$= 1,2,3; select from [all nodes] where member(node_id,x$)
    Regards,

    Quote Originally Posted by Tom L View Post
    Hi,

    I can't manage to run a very basic query. Would anyone know how to select a list of asset? I tried this kind of syntax but it doesn't work:

    SELECT FROM [All Nodes] WHERE node_id in ('id1','id2',...)

    Thanks

    Tom

  8. #68

    Join Date
    Aug 2017
    Posts
    3
    Quote Originally Posted by lee View Post
    Hi,

    I'm having trouble using matches with spatial querys. I have a number of similar SQLs that update the notes field where I am querying against polygons within a certain distance. There are instances where some polygons have the same name and each is returned where I would like to only include 1 result for each unique name. I have come up with the following query using the 'matches' operator which works for everything except where the result contains brackets and it is then returning all of those results again.

    SET notes = IIF(notes matches '.*Interest: ' + spatial.SSSI_NAME + '.*',notes,notes +'&CHAR(10)&"Sites of Special Scientific Interest: ' +spatial.SSSI_NAME + ';"');

    I have looked into using [ ] to escape the brackets and for a set string (ie 'test(test)') I can use matches '.*test[(]test[)].*' and it works but as the brackets will be in various places I'd need to escape the entire result and I'm not sure how to do this.

    I've also looked at storing the result as a variable and changing the brackets to { } using SUBST which does work but with my limited SQL knowledge I can only get it to return one result per query.

    Any thoughts or suggestion on the easiest way to achieve this?

    Cheers
    Sorry to bump this but I'm still looking for a solution to this if anyone has any suggestions?

    Thanks
    Lee

  9. #69
    Hi Lee,

    You could try using the subst function directly over the query as follows, I think it might work:

    SET notes = IIF(notes matches '.*Interest: ' + subst(subst(spatial.SSSI_NAME, "(","{"),")","}") + '.*',notes,notes +'&CHAR(10)&"Sites of Special Scientific Interest: ' +subst(subst(spatial.SSSI_NAME, "(","{"),")","}") + ';"');




    Quote Originally Posted by lee View Post
    Sorry to bump this but I'm still looking for a solution to this if anyone has any suggestions?

    Thanks
    Lee

  10. #70

    Join Date
    Nov 2017
    Posts
    2
    Thank you Guillermo, that works perfect!

Posting Permissions

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