Query for Sewer Last Value

Ward van Laatum

Hi Support,

I'd like to make a query (TQL or panel) to get the Sewer Last Value at the end of my rainfall event.

I got this far, but it isn't getting the right result. Can you help?

SELECT_HEIGHT_WHERE_AREA_WITH_ATTRIBUTE_IS_PROJECTGEBIED_AND_GRID_WITH_ATTRIBUTE_IS_RIOOLBERGING45MM_AND_TIMEFRAME_IS_11

Rudolf

Hey Ward,

"HEIGHT" specifically returns the (average) height of the terrain in the location matching your clause(s). You are looking for GRIDAVG (or perhaps even GRIDVOLUME since you are dealing with water). Make sure your "GRID WITH ATTRIBUTE" clause matches the overlay with the "sewer last value" result type, and you should be good to go.

Should you run into odd TQL results in the future, you can use our documentation to double-check whether the statement and clauses all do what you expect them to do: https://support.tygron.com/wiki/Tygron_Query_Language#Grid_values

Ward van Laatum

Hey Rudolf,

Thank you.

I don't think GRIDAVG will work though, since it will calculate the average value for the entire area, while the sewer is only connected to the paved surface. So I think Ill have to get area of the paved area and divide the GRIDAVG by that surface.

Ward van Laatum

https://community.tygron.com/forum/uploads/187/IH7MM1YGVVLD.jpg

Hey Rudolf,

I'm still trying to figure it out. I added a printscreen to try and clearify my problem. My area is the two blocks on the south side of the main road, and on the north side of the canal and the football pitches. I added another picture of my area of interest. When I click the red area it gives me 18 mm for the Sewer last value. If I ask the TQL it gives me 13 mm (SELECTGRIDAVGWHERE_AREA_IS_myarea_AND_GRID_WITH_ATTRIBUTE_IS_RIOOL45MM_AND_TIMEFRAME_IS_11).

So, the GRIDAVG gives me too low a value, so I'm trying to get the lotsize of all the buildings with the attribute Connected to sewer within my specific area. That should give me the right area and than I can recalculate the right storage. However....I can't get it to work. I think all I need is a way to get the lotsize of all the roads and buildings (and nothing else) . But how? Any ideas?

https://community.tygron.com/forum/uploads/313/1KUQEI97P24O.jpg

Rudolf

Hey Ward,

I've take a moment to puzzle a bit with it, and I think the following should be the most accurate path forward.

You will need 2 queries: a query to obtain the amount of water in the sewer, and a query to obtain the size of the sewer.

Water in the sewer

To get the total amount of water in the sewer you can use either the GRIDAVG, or the GRIDVOLUME statement. The GRIDVOLUME statement is best, as that directly returns the total amount of water, while GRIDAVG would require some additional processing.

The following query will make this possible:

SELECT_GRIDVOLUME_WHERE_AREA_WITH_ATTRIBUTE_IS_PROJECTGEBIED_AND_GRID_WITH_ATTRIBUTE_IS_RIOOLBERGING45MM_AND_TIMEFRAME_IS_11

* GRIDVOLUME (for each cell the cell size and the value are multiplied to obtain a volume (in this case: of water). Those volumes are added together for all cells matching the clauses)

* AREA_WITH_ATTRIBUTE_IS_PROJECTGEBIED (only whereever the relevant calculation area is, indicated by areas with a "PROJECTGEBIED" attribute)

* GRID_WITH_ATTRIBUTE_IS_RIOOLBERGING45MM_AND_TIMEFRAME_IS_11 (the overlay and moment in time with the desired results)

The result will be the total amount of water in the sewer in the defined area.

Size of the sewer

Next, you want the size of the sewer, so that you can determine the height of the water in the sewer.

The following query will make this possible:

SELECT_LANDSIZE_WHERE_AREA_WITH_ATTRIBUTE_IS_PROJECTGEBIED_AND_MINGRIDVALUE_IS_1.0E-6_AND_GRID_WITH_ATTRIBUTE_IS_RIOOLBERGING45MM_AND_TIMEFRAME_IS_11

* LANDSIZE (the amount of m² the found area takes up)

* AREA_WITH_ATTRIBUTE_IS_PROJECTGEBIED (only whereever the relevant calculation area is, indicated by areas with a "PROJECTGEBIED" attribute)

* MINGRIDVALUE_IS_1.0E-6 (only where there is a minimum amount (a thousandsth of a millimeter) of water in a sewer)

* GRID_WITH_ATTRIBUTE_IS_RIOOLBERGING45MM_AND_TIMEFRAME_IS_11 (the overlay and moment in time with the desired results)

The result will be the total size of sewer with water in it.

Getting the result

If you then take the amount of water, and divide that by the total size of the sewer, the result is the height of the water in the sewer.

Notes

A few notes to consider:

* When reading the value by clicking in the 3D world and checking the hover panel, take care that the timeframe currently dispalyed by the overlay should match the timeframe consulted by the TQL.

* The reason I derive the size of the sewer from the grid rather than the polygon defining the sewer, is because a cell is part of a sewer if it intersects with the polygon, meaning there will be a slight difference between the definition's size and the actual used sewer's size. To get a result as close as possible to what you get by clicking in the 3D world with the overlay active, you want to use the values that are as close to how that number is calculated as possible.

* Through the same logic, allow for some very small deviations between numbers, as rounding may still occur if a cell is not entirely in or out of the defined area.

* If you have multiple separate sewer areas and you want to have the height of the water in each of those sewers, look into replacing the AREA_WITH_ATTRIBUTE_IS_PROJECTGEBIED clause with an AREA_IS_ clause. If you are using the default SEWER_STORAGE attribute to define the sewers, you can use AREA_IS_XA_SEWER_STORAGE in an excel for an indicator or panel.

Please try this approach and let me know whether your results match up, or what kind of results you do get for your queries.

Ward van Laatum

Great! Thanks Rudolf! This should work.

Somthing small, but excel did not want me to use 1.0E-6 notation. Replacing it with 0.000006 did work.