Page 7 of 7 FirstFirst ... 567
Results 61 to 64 of 64

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

  1. #61

    Join Date
    Aug 2017
    Posts
    1
    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
    118
    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;

Posting Permissions

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