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

This is an example of spatial analysis with SQL. The following code selects all links and nodes inside a polygon:

LIST $PolygonID STRING;
SELECT DISTINCT polygon_id INTO $PolygonID;

PROMPT TITLE"Dialog";
PROMPT LINE $sel 'Polygon' LIST $PolygonID;
PROMPT DISPLAY;

SELECT FROM [All Nodes] WHERE spatial.polygon_id = $sel;
SELECT FROM [All Links] WHERE spatial.polygon_id = $sel;


Attachment 311

SQL can also be a powerful for themes in the Geoplan. In the example below, the SQL compares the 2D water depth of two simulations and shows the difference if higher than a treshold (0.1):

IIF(sim.depth2d-sim2.depth2d > 0.1, sim.depth2d-sim2.depth2d , 0.0)

You can add this simple expression in the "GeoPlan Properties and Themes > 2D Zone Theme" as shown in the next figures.

Attachment 312Attachment 313

Quote:

---

Originally Posted by Duncan Kitts

This query will take a selected node, trace upstream, refine the selection to include subcatchment, deslect all objects other than the subcatchments

---

Smart, this is often useful and with a few tweaks you can get it to do other equally frequent selections. Thanks

Quote:

---

Originally Posted by Kristian Ravnkilde

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.

---

The following should work for all nodes, you can refine further for manholes, it sets user text 1 to 1 for all nodes that a subcatchment drain to, then selects those nodes with no upstream links and where user text 1 is not equal to 1:-

UPDATE [Subcatchment] SET node.user_text_1=1;
count(us_links.*)=0 AND user_text_1<>1

A section of this query is also very useful in expanding from a selection of nodes to any subcatchment(s) that are associated to that node.

Attachment 326

SELECT FROM [All Nodes] WHERE $node_selected = 1;
UPDATE Subcatchment SET $subcatchment_selected =0;
UPDATE SELECTED SET subcatchments.$subcatchment_selected =1;
SELECT FROM Subcatchment WHERE $subcatchment_selected =1;

This is an SQL to update Head Discharge Curves.
You can add the curve to a txt file and run the script to update the curve automatically. The SQL will prompt what curve ID to update and do all the job!

The SQL script:

LIST $QDIDS STRING;
SELECT ALL DISTINCT head_discharge_id INTO $QDIDS;

LIST $CurveQ STRING;
LOAD $CurveQ FROM FILE 'C:\Curve.dat';
LIST $CurveH STRING;
LOAD $CurveH FROM FILE 'C:\Curve.dat';

PROMPT TITLE 'H-Q Name';
PROMPT LINE $UpdateID 'HQ ID' LIST $QDIDS;
PROMPT DISPLAY;

DELETE From [Head discharge].HDP_table WHERE head_discharge_id = $UpdateID;

LET $n = 1;
WHILE $n<=LEN($CurveQ);
LET $UpdateQ = AREF($n,$CurveQ);
LET $UpdateH = AREF($n, $CurveH);
INSERT INTO [Head discharge].HDP_table (head_discharge_id,HDP_table.discharge,HDP_table.h ead) VALUES ($UpdateID,$UpdateQ,$UpdateH);
LET $n=$n+1;
WEND


An example of a txt in the 'C:\Curve.dat' file:
$CurveQ,90,80,70
$CurveH,0.0,0.3,0.7

This query goes from a selected pipe(s) a number (you enter) of pipe lengths upstream.

PROMPT TITLE 'US Trace';
PROMPT LINE $USCount 'How many pipes segments upstream to select?' DP 0;
PROMPT DISPLAY;

LET $z=1;
WHILE $z <= $USCount;
LIST $nodeID STRING;
SELECT SELECTED DISTINCT us_node_id INTO $nodeID;
LET $i=1;
WHILE $i<=LEN($nodeID);
SELECT FROM [All Links] WHERE ds_node.node_id = AREF($i,$nodeID);
LET $i=$i+1;
WEND;
LET $z=$z+1;
WEND

Quote:

---

Originally Posted by Duncan Kitts

This query will take a selected node, trace upstream, refine the selection to include subcatchment, deslect all objects other than the subcatchments:-

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;
SELECT FROM Subcatchment WHERE $subcatchment_selected =1;

DESELECT ALL

The while keyword will select all nodes upstream for a count of 100. If you need this to be larger you can edit the number as required.

---

Add the following line to sum the populations in the selected subcatchments, useful for determining populations upstream of a pump catchment.

SELECT SELECTED Sum(population) FROM Subcatchment;

INSERT INTO [Node].hyperlinks (node_id, hyperlinks.description,hyperlinks.url) SELECT node_id, 'Sheet_name', 'D:\FileName_Texttobereplaed.pdf' from node;

SET hyperlinks.url=GSUBST('D:\thisisafilename_Texttobe replaced.pdf', 'Texttobereplaced', user_text_1)

The first part of the SQL sets up a Hyperlink with the description ‘Sheet Name’ and the File Location (D:\FileName_Texttobereplaced.pdf) for all nodes.

The second part then takes the file location string and replaces the ‘Texttobereplaced’ with the User_Text_1 value (which might be a drawing number for instance). Similar can be done for links/subcatchments etc...

Export node hyperlinks to a csv file:-

SELECT node_id, hyperlinks.description, hyperlinks.url into File 'D:/HyperlinkSpreadsheet.csv';

Similar can be done for links/subcatchments.

The following can also be used to change the url address should the folders be renamed in Windows Explorer:-

Set hyperlinks.url=SUBST (hyperlinks.url, '\OldFileName\, '\NewFileName\');

This uses the SUBST key word.

SUBST
SUBST(string,thing to replace, thing to replace it with)
Replaces the first instance of the second parameter in the first with the third e.g. SUBST('01880132','01','ND') returns 'ND880132'