Using TQL for more specific queries

Ellie

I am trying to calculate the total area of roofs which are flat (i.e. with sloping roof height of 0). So far, in TQL I input the following:
SELECT_LOT_SIZE_WHERE_FUNCTIONMULT_IS_SLANTING_ROOF_HEIGHT
This returns a number which I assume is the lot size of all buildings which have attibute/detail of slanting roof height. Is it possible to somehow specify this query further so I can input a certain number (to extract the lot size of all roofs with value of 0).
Thanks in advance

Godelief

Hi Ellie,

Yes, that is possible. You can make a TQL query more specific by adding more clauses to the query: in the query tool you can click on add clause to add an AND statement to your query.

For your question, there are multiple ways to do this.
Maybe it is the easiest to understand how it works by creating an Average overlay first in where you show the Slanting roof height attribute of the Buildings layer. Then create a new legend to see better what the flat roofs are. Make sure the gridsize of this overlay is small, so the result is more precise.
Then in the query tool you can do the following query:
SELECT_LOTSIZE_WHERE_GRID_IS_5_AND_MAXGRIDVALUE_IS_0.1 (where Grid_is_5 is the id of my Average overlay, the number could be different in your project). Now you have the lotsize of all the constructions that have a slanting roof height value of max 0.1.
However, keep in mind that trees, parks, parking spaces, roads etc are also constructions. You probably do want to substract these lotsizes from the result of the query above. One example query to get the lotsize for the roads is then: SELECT_LOTSIZE_WHERE_GRID_IS_5_AND_MAXGRIDVALUE_IS_0.1_AND_CATEGORY_IS_ROAD
It is probably the easiest to do these calculations in an (http://support.tygron.com/wiki/Excel_(Indicator)) file in where you can put these different category queries in cells next to each other and substract them from the total flat roof lotsize.

I hope my answer is helpful to you!

Ellie

Thank you so much, this was very helpful. I ended up just adding up SELECT_LOTSIZE_WHERE_GRID_IS_5_AND_MAXGRIDVALUE_IS_0.1_AND_CATEGORY_IS_X where X = affordable housing, market housing, and top market housing in order to get the total lot size of all housing categories with flat roofs.

Similar to this, I have a further question regarding TQL and the water storage indicator. I have estimated potential amount of additional water storage in 2 methods, here is an example with affordable housing.

1) Use TQL to UPDATE_BUILDING_WATER_STORAGE_M2_WHERE_CATEGORY_IS_SOCIAL: update to value 0,1. Then, I go to the water storage indicator to see that for neighbourhood 32 there is now an additional 523m3 of innovative water storage

2) Use TQL to SELECT_LOTSIZE_WHERE_CATEGORY_IS_SOCIAL_NEIGHBOURHOOD_IS_32. This gives 3947m2 as the output. Assuming the same principles as above, with each m2 of lot size having potential for 0.1m3 of water storage. Multiplying 3947*0.1 = 397.4m3 of potential water storage.

I would have thought these two methods give the same answer, however there is quite a large difference. Is the water storage indicator calculating this in a different way (i.e. not using lot size)? Note: In this example the sloping roof height is not accounted for.

Godelief

Hi Ellie,

That is indeed also an easy way to get the flat roofs!
For your second question: could it be that in neighborhood 32 there are also more constructions (not only affordable housing) with a water storage function that would count as innovative? The water storage indicator does not only look at affordable housing.

Ellie

To begin with, innovative storage was at 0 in neighbourhood 32. The only changes I made were update the affordable housing buildings with the 0.1m3/m2 of water storage and then immediately look at what the change in innovative water storage was.

Please note that I have done this with 7x neighbourhoods and 3 different types of housing (individually and also combined) for both methods, and there are significant differences in all of them.

Hoping this helps provide more insight!

Godelief

Hi Ellie,

When you download the debug Excel file of the Water storage indicator, you can see the exact queries that are used in the indicator. If you try these queries in the Query tool, the same answers should be given.

For your question: some buildings belongs to multiple categories, based on the open data that is used for creating the 3D model. You can see this when you click on a building in the 3D world: you will see the number of houses and for example the floorsize of shops. See also the attached images.
When you update an attribute of one category of this particular building, the attributes for both categories of this building are updated, since the building belongs to these two categories. Thus the water storage value will be changed for both categories of this building. The water storage indicator takes this into account, and does not look at only one category.
That is where the difference comes from in outcomes.
I hope I explained it clearly.

Ellie

Hi Godelief, I would definitely not have figured that out by myself so thank you!

This does pose a problem for me though, as I would like to only update the proportion that is classed as housing. However I will have a think how to get around this.
Thanks for your help

Ellie W

Hi again Godelief, I have another question which somewhat runs under the same heading.

Essentially what I am trying to do is update the water storage attribute of all buildings which have flat roofs. Based on what I have read, this is not possible with TQL. I would like to do something like this:

UPDATE_BUILDING_WATER_STORAGE_M2_WHERE_ROOF_SLOPE_IS FLAT

Do you have any ideas for a work-around solution?