Tygron Query Language: Difference between revisions
mNo edit summary |
mNo edit summary |
||
| (255 intermediate revisions by 9 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, 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. | |||
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. | |||
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. | |||
More examples can be found at [[TQL Examples]]. | |||
== | ==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. | |||
| | |||
===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: | |||
* [[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 | |||
=== | ===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. | |||
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 <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. | |||
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. | |||
== | ==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. | |||
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. | |||
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]]. | |||
| | |||
=== | {{article end | ||
|seealso= | |||
* [[Excel indicator]]s, [[Panel]]s and [[Global]]s | |||
* [[Select (TQL)]], [[Update (TQL)]] and [[Clause (TQL)]] | |||
* [[Query tool]] | |||
|howtos= | |||
* [[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}} | |||
}} | |||
{{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:
- Select statements, for data retrieval from Items in a Project
- Update statements, for writing data (back) into the Project and its Items
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
See also