Tygron Query Language: Difference between revisions

From Tygron Support wiki
Jump to navigation Jump to search
mNo edit summary
 
(30 intermediate revisions by 2 users not shown)
Line 1: Line 1:
TQL is short for Tygron Query Language. It provides a means of obtaining and adjusting data of a [[session]]. It is comprised of a number of key phrases, divided into 2 categories: statements and clauses. The statements indicate what data interaction should take place; retrieving using a <code>SELECT</code> statement, or adjusting using an <code>UPDATE</code> statement. The clauses provide a means to filter the data the interaction is applied to. A query always consists of exactly 1 statement and 0 or more clauses.
TQL is short for Tygron Query Language. It provides a means of obtaining and adjusting data of a [[session]]. It is comprised of a number of key phrases, that can be divided into 2 categories: statements and clauses. The statements indicate what data interaction should take place; retrieving using a [[Select (TQL)|<code>SELECT</code>]] statement, or adjusting using an [[Update (TQL)|<code>UPDATE</code>]] statement. The clauses provide a means to filter the data the interaction is applied to. A query always consists of exactly 1 statement and 0 or more clauses. Each clause always consists of a [[Clause (TQL)|Clause parameter]] and a value.  


Interactions can take place with the data in 2 ways: as item data or polygon data. Item data pertains to a specific component of a [[project]], such as individual [[building]]s, [[indicator]]s, or [[global]]s. Polygon data pertains to geographic data; surface areas in the [[project area]], such as the footprint of [[building]]s, the surface area of [[neighborhood]]s, or calculated [[grid overlay|grid]]s. Depending on what kind of statement is used, the query will interact with its clauses treating them as items or as polygons.
TQL can access both property data from individual items, as well as spatial information. Property data pertains to a specific component of a [[project]], such as individual [[building]]s, [[indicator]]s, or [[global]]s. Polygon data pertains to geographic data; surface areas in the [[project area]], such as the lot size of [[building]]s, the surface area of [[neighborhood]]s, or calculated [[grid overlay|grid]]s. Depending on what kind of statement is used, the query will interact with its clauses treating them as items or as polygons.


An example of a TQL query is <code>SELECT_LOTSIZE_WHERE_CATEGORY_IS_SOCIAL_AND_STAKEHOLDERTYPE_IS_MUNICIPALITY</code>. In this example, <code>SELECT_LOTSIZE</code> is the statement. <code>CATEGORY_IS_SOCIAL</code> is the first clause, and <code>STAKEHOLDERTYPE_IS_MUNICIPALITY</code> is the second clause. It would return all the land area taken up with social housing, which is also owned by a municipal stakeholder.
An example of a TQL query is:
: <code>SELECT_LOTSIZE_WHERE_CATEGORY_IS_SOCIAL_AND_STAKEHOLDERTYPE_IS_MUNICIPALITY</code>.  


Note that all clauses in a TQL query are cumulative. All clauses are connected by the term <code>AND</code>. This means all clauses must be met before the data is "counted". Depending on how you use TQL in your project or applications, it is possible to simulate an "OR" functionality as well (i.e. <code>CATEGORY_IS_SOCIAL</code> ''or'' <code>CATEGORY_IS_NORMAL</code>). This can be done simply by executing multiple queries, one for each "OR" section.


More examples can be found in the [[#Examples for common use-cases|example section]].
In this example, <code>SELECT_LOTSIZE</code> is the statement. <code>CATEGORY_IS_SOCIAL</code> is the first clause, and <code>STAKEHOLDERTYPE_IS_MUNICIPALITY</code> is the second clause. It would return all the land area taken up with social housing, which is also owned by a municipal stakeholder.


==Usage==
Note that all clauses in a TQL query are cumulative. All clauses are connected by the term <code>AND</code>. This means all clauses must be met before the data is "counted". Depending on how you use TQL in your project or applications, it is possible to simulate an "OR" functionality as well (e.g. <code>CATEGORY_IS_SOCIAL</code> ''or'' <code>CATEGORY_IS_NORMAL</code>). This can be done simply by executing multiple queries, one for each "OR" section.
TQL can be used in a number of places. The most common place is in the [[excel]]s of custom [[indicator]]s or [[panel]]s. To indicate which cells should contain certain data from the [[session]], cells in an excel file can be given a name. In this case, the name would be a TQL query. When an indicator calculation takes place, the {{software}} will obtain all queries that exist in the excel file of the indicator. For the queries with <code>SELECT</code> statements, the results of those queries are obtained and placed in the corresponding cells. when the calculation of the excel is complete, the cells with an <code>UPDATE</code> statement are read out, and the items in the project indicated by those queries are updated with those values.


TQL can also be used to quickly check data in a project. When in [[editor]] mode, it's possible to open the [[query tool]]. With this tool, queries can be made and directly executed. The result is then presented to the [[user]]. This can be especially handy when it's necessary to quickly check whether a certain type of data is present or correctly readable. The query tool also functions both in and out of [[Test Run|test runs]], allowing a user to also check data in a [[project]] during a session.
More examples can be found at [[TQL Examples]].


It's also possible to execute TQL queries directly via the [[API]]. The advantage of using TQL, rather than inspecting a project's data via the API directly, is that it can be used to perform calculations that require some intermediate processing. For example, calculating the intersection of buildings with an area or neighborhood. Other examples of this include the queries related to values of [[grid overlay|grid]]s, or the multiplication of floor size with a related [[function values | function value]].
==TQL components==
TQL queries are composed of 2 major elements: the [[#Statements|statement]], and the [[Clause (TQL)|clauses]]. Together they may make up a single query.


==Clauses==
===Statements===
Clauses indicate the conditions which must be met for any data to be taken into account for a statement. In a query, they are connected to the statement using the phrase <code>WHERE</code>. Multiple additonal clauses can be added to a query using the phrase <code>AND</code>. Clauses added to a query are cumulative conditions which must all be met. When, for any data, at least one condition is not met, that data is not taken into account or affected by the query.
Statements are the instruction which is to be performed with the [[Project]]. A query always has exactly one statement.  


Clauses can be broadly grouped into 3 different groups: Common clauses, singular clauses, and modifying clauses. For any given statement, different clauses can be applied from across all these groups. The availability and function of clauses is also influenced by whether the statement dictates an interaction with polygon data or with item data. Some clauses may or may not be available at all, depending on this.
There are 2 kinds of [[TQL]] Statements:
* [[Select (TQL)|Select statement]]s, for data retrieval from [[Item]]s in a [[Project]]
* [[Update (TQL)|Update statement]]s, for writing data (back) into the [[Project]] and its [[Item]]s


The order in which the clauses appear in the query is not relevant.
===Clauses===
{{main|Clause (TQL)}}
Clauses indicate the conditions which must be met for any data to be taken into account for a statement. In a query, they are connected to the statement using the phrase <code>WHERE</code>. Multiple additional clauses can be added to a query using the phrase <code>AND</code>. Clauses added to a query are cumulative conditions which must all be met. When, for any data, at least one condition is not met, that data is not taken into account or affected by the query.


===Singular clauses===
For any given [[Select (TQL)]] or [[Update (TQL)]] statement, different clauses can be applied from across all these groups. The availability and function of clauses is also influenced by whether the statement dictates an interaction with polygon data or with item data. Some clauses may or may not be available at all, depending on this.
Singular clauses are clauses which are not used as often, which are only used by specific statements, or only in specific use-cases.


====Variables====
The order in which the clauses appear in the query is not relevant.
{| class="wikitable"
|-
! Clause
! As a polygon
! As an item
! Type
|-
| NAME
| N/A
| The data requested must have this name.
| The name which identifies the requested data.
|}


====Arrays====
For a full list of available clauses, see [[Clause (TQL)]].
{| class="wikitable"
|-
! Clause
! As a polygon
! As an item
! Type
|-
| INDEX
| N/A
| Of the array of data specified, get specifically the data in the indicated index in the array.
| The index of the requested data in the specified array.
|}
Arrays are 0-indexed. I.e., the first value is stored at index 0. If an index is requested which does not exist, "0" is returned. Queries on arrays without an INDEX query return the value at index 0.


====Indicators====
====Limiting Search Polygon====
{| class="wikitable"
Some clause parameters described on this wiki note that they ''Limit the Search Polygon''. The Search Polygon is the geometry created from the specified clauses that will limit the buildings that will be considered when executing the statement. For example <code>SELECT_LOTSIZE_WHERE_AREA_WITH_ATTRIBUTE_SPECIAL_CASE_AND_CATEGORY_IS_SOCIAL</code> will create a search polygon for buildings based on the areas that have the attribute <code>SPECIAL_CASE</code>. The [[Area with attribute (TQL Param)]] is a search polygon limiter.
|-
! Clause
! As a polygon
! As an item
! Type
|-
| INDICATORTYPE
| N/A
| It must be related to this type of [[indicator]].
| Technical name indicating a type of [[indicator]].
|}


====Terms====
It will also have effect when executing the statement: <code>SELECT_LANDSIZE_WHERE_NEIGHBORHOOD_IS_2_AND_AREA_WITH_ATTRIBUTE_SPECIAL_CASE</code>, where together with the [[Neighborhood (TQL Param)]] they limit the search polygon to the intersection of the Neighborhood with Item ID 2 and areas with an attribute <code>SPECIAL_CASE</code>. Ultimately, the statement returns the landsize of the limiting search polygon.
{| class="wikitable"
|-
! Clause
! As a polygon
! As an item
! Type
|-
| STATE
| N/A
| It must be related to this specific text (or "term") used by the {{software}}.
| Technical name indicating a term.
|}


==Usage==
TQL can be used in a number of places. The most common place is in the [[excel]]s of custom [[indicator]]s, [[panel]] or [[global]]s. To indicate which cells should contain certain data from the [[session]], cells in an excel file can be given a name. In this case, the name would be a TQL query. When an indicator calculation takes place, the {{software}} will obtain all queries that exist in the excel file of the indicator. For the queries with <code>SELECT</code> statements, the results of those queries are obtained and placed in the corresponding cells. when the calculation of the excel is complete, the cells with an <code>UPDATE</code> statement are read out, and the items in the project indicated by those queries are updated with those values.


===Modifying clauses===
TQL can also be used to quickly check data in a project. When in [[editor]] mode, it's possible to open the [[query tool]]. With this tool, queries can be made and directly executed. The result is then presented to the [[user]]. This can be especially handy when it's necessary to quickly check whether a certain type of data is present or correctly readable. The query tool also functions both in and out of [[Test Run|test runs]], allowing a user to also check data in a [[project]] during a session.
Modyfing clauses are clauses which, in some fashion, modify the way the query functions, either in some way changing what data is being requests (as opposed to how other clauses merely filter data), or by performing some further operation on the resulting data.


====Map types====
It's also possible to execute TQL queries directly via the [[API]]. The advantage of using TQL, rather than inspecting a project's data via the API directly, is that it can be used to perform calculations that require some intermediate processing. For example, calculating the intersection of buildings with an area or neighborhood. Other examples of this include the queries related to values of [[grid overlay|grid]]s, or the multiplication of floor size with a related [[function values | function value]].
{| class="wikitable"
|-
! Clause
! As a polygon
! As an item
! Type
|-
| MAP
| It must be data from this [[maptype|moment of time]] in a [[session]].
| It must be data from this [[maptype|moment of time]] in a [[session]].
| Technical name of the original/current state or the planned/maquette state.
|}
The precise terms vary based on the simulation type, but the terms which appear in the query are CURRENT and MAQUETTE.
 
====Layer types====
{| class="wikitable"
|-
! Clause
! As a polygon
! As an item
! Type
|-
| LAYERTYPE
| It must be data from this [[layer]].
| N/A
| Technical name of the surface or underground layers.
|}
The precise terms are SURFACE and UNDERGROUND.
 
====Multipliers====
{| class="wikitable"
|-
! Clause
! As a polygon
! As an item
! Type
|-
| ATTRIBUTE_MULT
| Multiply the result by the (spatial) value of an attribute.
| N/A
| Attribute name of any polygonal item.
|-
| FUNCTIONMULT
| Multiply the result by the (spatial) value of an attribute.
| N/A
| The name of a [[function values| function value]].
|-
| CATEGORYMULT
| Multiply the result by the (spatial) value of an attribute.
| N/A
| The name of a [[category value]].
|}
 
====Transformations====
{| class="wikitable"
|-
! Clause
! As a polygon
! As an item
! Type
|-
| BUFFER
| N/A
| Expand the valid polygon(s) used to select data
| Size of the buffer
|}
When a polygon clause (consisting of {{inlink|lcase=1|Specific polygons}}, {{inlink|lcase=1|Attribute polygons}}, or {{inlink|lcase=1|Grids|grid clauses}}, which behaves comparably for this purpose) is used, the "selection polygon" defined by those clauses is buffered before determining what valid data falls in the selection polygon.
 
There is a more in-depth example on how to use the BUFFER clause in the {{inlink|How-to's|how to's}} section.
 
====Relations====
{{:Relation (TQL)}}
 
==Examples for common use-cases==
 
A number of use-cases come up frequently across various projects. Queries of the following forms often provide a firm basis for using TQL to obtain required data.
 
====Function values (selecting)====
'''Parking requirements of social housing'''
 
The parking demand is based on a [[function values | function value]] of each function, and is defined as "parking places per m2". Parking demand is not based on the footprint of a building, but the size of a building. i.e. a building 4 floors high needs more parking than a building with the same footprint 2 floors high.
 
<code>SELECT_FLOORSIZE_WHERE_CATEGORY_IS_SOCIAL_AND_CATEGORYMULT_IS_PARKING_LOTS_DEMAND_PER_M2</code>
 
* <code>SELECT_FLOORSIZE</code>: What we are basically looking for is the floorsize of the building.
* <code>CATEGORY_IS_SOCIAL</code>: We only want to look at buildings which are of the [[Function Category|category]] social housing. For that, a category clause is added.
* <code>CATEGORYMULT_IS_PARKING_LOTS_DEMAND_PER_M2</code>: Lastly, we are not looking directly for the floorsize, but for the parking demand as a result of that floorsize.
 
====Function values (updating)====
'''Traffic intensity on roads'''
 
The traffic intensity is a function value which affects calculations related to [[Traffic Model|traffic]]. Some use-cases require these values to be updated dynamically. This can be done per neighborhood. (In this example, the [[neighborhood]] in question has the ID 1.)
 
<code>UPDATE_BUILDING_TRAFFIC_FLOW_WHERE_CATEGORY_IS_ROAD_AND_NEIGHBORHOOD_IS_1</code>
 
* <code>UPDATE_BUILDING</code>: We're changing an attribute of a building.
* <code>TRAFFIC_FLOW</code>: The attribute we are changing is the TRAFFIC_FLOW, which is the technical name of a function value.
* <code>CATEGORY_IS_ROAD</code>: We only want to update this value on roads.
* <code>NEIGHBORHOOD_IS_1</code>: We are updating only in this specific neighborhood. Nothing outside the neighborhood is updated by this statement.
 
====Grid average====
'''Quality of livability in a neighborhood'''
 
The [[livability (Overlay)|livability overlay]] calculates, for each [[grid overlay|grid cell]], the average livability. The best indication of how well a certain region performs in terms of livability would be to average these values out. That way, its possible to compare places which are not the same size. (In this example, the [[neighborhood]] in question has the ID 1, and the [[grid overlay|grid]] of [[livability (Overlay)|livability]] has the ID 4.)
 
<code>SELECT_GRIDAVG_WHERE_NEIGHBORHOOD_IS_1_AND_GRID_IS_4</code>
 
For any given neighborhood, different points of a [[grid overlay|grid]] may score differently.
 
* <code>SELECT_GRIDAVG</code>: We're looking for the average score across the entire neighborhood.
* <code>NEIGHBORHOOD_IS_1</code>: We don't need the average of the entire map, but only of a specific neighborhood.
* <code>GRID_IS_4</code>: We need to specify the grid we wish to use the values of.
 
====Grid values====
'''The area of open water which is flooded by more than 20 cm of water'''
 
The [[Rainfall (Overlay)|rainfall overlay]] calculates what amounts of water ends up where. The overlay's results are in meters. If we wish to provide some minimum value, it must be in meters as well. 20 cm is 0,2 meters. The [[project]] can also contain multiple types of [[terrain|water]]. All types of have one thing in common, namely that they all have the WATER attribute, which can be tested against. (In this example, the grid of [[Rainfall (Overlay)|inundation]] has the ID 4, and has its result type set to water stress.)
 
<code>SELECT_LANDSIZE_WHERE_MINGRIDVALUE_IS_0.2_AND_GRID_IS_4_AND_TERRAIN_WITH_ATTRIBUTE_IS_WATER</code>
 
* <code>SELECT_LANDSIZE</code>: The end result must be some area; some amount of square meters.
* <code>MINGRIDVALUE_IS_0.2</code>: We only want to count the area where the value calculated is at least 0.2 (meters).
* <code>GRID_IS_4</code>: We need to specify the grid we wish to use the values of.
* <code>TERRAIN_WITH_ATTRIBUTE_IS_WATER</code>: We are only looking for the water which ends up on [[terrain|water]], identified by the "WATER" attribute.
 
====Buffers====
'''The amount of green in or near a neighborhood'''
 
Green buildings can be found by their green [[function value]]. The value is the amount of green in square meters, per square meter of building. Green can be in a neighborhood, but can also be within a certain range near a neighborhood (say, 20 meters) and still have an effective presence.
 
<code>SELECT_LOTSIZE_WHERE_NEIGHBORHOOD_IS_1_AND_BUFFER_IS_20.0_AND_FUNCTIONMULT_IS_GREEN_M2</code>
 
* <code>SELECT_LOTSIZE</code>: The end result must be area taken up by buildings; some amount in square meters.
* <code>NEIGHBORHOOD_IS_1</code>: We want the greenery in a particular neighborhood.
* <code>BUFFER_IS_20.0</code>: We don't just want to look at buildings in the neighborhood itself, but also buildings within 20 meters of the neighborhood
* <code>FUNCTIONMULT_IS_GREEN_M2</code>:The end result must be multiplied by the green value of the respective buildings found.


{{article end
{{article end
|seealso=
|seealso=
* [[Excel indicator]]
* [[Excel indicator]]s, [[Panel]]s and [[Global]]s
* [[Panel]]
* [[Select (TQL)]], [[Update (TQL)]] and [[Clause (TQL)]]
* [[Query tool]]
|howtos=
|howtos=
* [[How to use buffers (TQL)]]
* [[How to use buffers (TQL)]]
* [[TQL Examples]]
|videos=
{{video|link=https://youtu.be/6D29CclkWQ4|description=Information about Residents and Energylabels in the {{software}}.|language=dutch}}
}}
}}


==Video's==
 
{{video|link=https://youtu.be/6D29CclkWQ4|description=Information about Residents and Energylabels in the {{software}}.|language=dutch}}
{{TQL nav}}

Latest revision as of 08:43, 5 September 2024

TQL is short for Tygron Query Language. It provides a means of obtaining and adjusting data of a session. It is comprised of a number of key phrases, that can be divided into 2 categories: statements and clauses. The statements indicate what data interaction should take place; retrieving using a SELECT statement, or adjusting using an UPDATE statement. The clauses provide a means to filter the data the interaction is applied to. A query always consists of exactly 1 statement and 0 or more clauses. Each clause always consists of a Clause parameter and a value.

TQL can access both property data from individual items, as well as spatial information. Property data pertains to a specific component of a project, such as individual buildings, indicators, or globals. Polygon data pertains to geographic data; surface areas in the project area, such as the lot size of buildings, the surface area of neighborhoods, or calculated grids. Depending on what kind of statement is used, the query will interact with its clauses treating them as items or as polygons.

An example of a TQL query is:

SELECT_LOTSIZE_WHERE_CATEGORY_IS_SOCIAL_AND_STAKEHOLDERTYPE_IS_MUNICIPALITY.


In this example, SELECT_LOTSIZE is the statement. CATEGORY_IS_SOCIAL is the first clause, and STAKEHOLDERTYPE_IS_MUNICIPALITY is the second clause. It would return all the land area taken up with social housing, which is also owned by a municipal stakeholder.

Note that all clauses in a TQL query are cumulative. All clauses are connected by the term AND. This means all clauses must be met before the data is "counted". Depending on how you use TQL in your project or applications, it is possible to simulate an "OR" functionality as well (e.g. CATEGORY_IS_SOCIAL or CATEGORY_IS_NORMAL). This can be done simply by executing multiple queries, one for each "OR" section.

More examples can be found at TQL Examples.

TQL components

TQL queries are composed of 2 major elements: the statement, and the clauses. Together they may make up a single query.

Statements

Statements are the instruction which is to be performed with the Project. A query always has exactly one statement.

There are 2 kinds of TQL Statements:

Clauses

Main article: Clause (TQL)

Clauses indicate the conditions which must be met for any data to be taken into account for a statement. In a query, they are connected to the statement using the phrase WHERE. Multiple additional clauses can be added to a query using the phrase AND. Clauses added to a query are cumulative conditions which must all be met. When, for any data, at least one condition is not met, that data is not taken into account or affected by the query.

For any given Select (TQL) or Update (TQL) statement, different clauses can be applied from across all these groups. The availability and function of clauses is also influenced by whether the statement dictates an interaction with polygon data or with item data. Some clauses may or may not be available at all, depending on this.

The order in which the clauses appear in the query is not relevant.

For a full list of available clauses, see Clause (TQL).

Limiting Search Polygon

Some clause parameters described on this wiki note that they Limit the Search Polygon. The Search Polygon is the geometry created from the specified clauses that will limit the buildings that will be considered when executing the statement. For example SELECT_LOTSIZE_WHERE_AREA_WITH_ATTRIBUTE_SPECIAL_CASE_AND_CATEGORY_IS_SOCIAL will create a search polygon for buildings based on the areas that have the attribute SPECIAL_CASE. The Area with attribute (TQL Param) is a search polygon limiter.

It will also have effect when executing the statement: SELECT_LANDSIZE_WHERE_NEIGHBORHOOD_IS_2_AND_AREA_WITH_ATTRIBUTE_SPECIAL_CASE, where together with the Neighborhood (TQL Param) they limit the search polygon to the intersection of the Neighborhood with Item ID 2 and areas with an attribute SPECIAL_CASE. Ultimately, the statement returns the landsize of the limiting search polygon.

Usage

TQL can be used in a number of places. The most common place is in the excels of custom indicators, panel or globals. To indicate which cells should contain certain data from the session, cells in an excel file can be given a name. In this case, the name would be a TQL query. When an indicator calculation takes place, the Tygron Platform will obtain all queries that exist in the excel file of the indicator. For the queries with SELECT statements, the results of those queries are obtained and placed in the corresponding cells. when the calculation of the excel is complete, the cells with an UPDATE statement are read out, and the items in the project indicated by those queries are updated with those values.

TQL can also be used to quickly check data in a project. When in editor mode, it's possible to open the query tool. With this tool, queries can be made and directly executed. The result is then presented to the user. This can be especially handy when it's necessary to quickly check whether a certain type of data is present or correctly readable. The query tool also functions both in and out of test runs, allowing a user to also check data in a project during a session.

It's also possible to execute TQL queries directly via the API. The advantage of using TQL, rather than inspecting a project's data via the API directly, is that it can be used to perform calculations that require some intermediate processing. For example, calculating the intersection of buildings with an area or neighborhood. Other examples of this include the queries related to values of grids, or the multiplication of floor size with a related function value.

How-to's

Videos

Information about Residents and Energylabels in the Tygron Platform. (In dutch)

See also


TQL
Select
Active • Attribute • Color • Distance • Expenses • Floorsize • Global • Height • ID • Gridavg • Gridmin • Gridmax • Gridvolume • Gridstdev • Landpolygons • Landsize • Length • Levees
• Lotpolygons • Lotsize • Name • Net State • Revenue • Sessionstate • State • Target • Token • Topfloor • Units
Update
Address • Area • Building • Global • Indicator • Neighborhood • Net Function • Net Line • Net Load • Panel • Terrain • Zone
Other
Update • Clause


Retrieved from "https://support.tygron.com/w/index.php?title=Tygron_Query_Language&oldid=60563"