Query Tool: Difference between revisions

From Tygron Support wiki
Jump to navigation Jump to search
(11 intermediate revisions by 4 users not shown)
Line 1: Line 1:
{{learned|file=QueryLogo-Q3-2015.jpg|what the Query Tool is|how to use the Query Tool|what you can use the Query Tool for}}
[[File:Tql_webinar.png|link=https://www.tygron.com/nl/2019/05/22/tql-en-upgrades/]]


==What is the Query Tool?==
==What is the Query Tool?==
The Query Tool is a support tool in the Tygron Engine that aids the user by constructing data requests in the [[TQL|Tygron Query Language (TQL)]] and displaying the query output. By using this tool, the user can quickly verify if the data needed can be extracted from the project data with the available query elements. Also the tool allows for verification of the validity of the retrieved data. All this can be achieved without creating and uploading a dedicated Query Spread Sheet. See also: [[Excel (Indicator)]]
The Query Tool is a support tool in the {{software}} that aids the user by constructing data requests in the [[TQL|Tygron Query Language (TQL)]] and displaying the query output. By using this tool, the user can quickly verify if the data needed can be extracted from the project data with the available query elements. Also the tool allows for verification of the validity of the retrieved data. All this can be achieved without creating and uploading a dedicated Query Spread Sheet. See also: [[Excel (Indicator)]]


==How does the Query Tool relate to the Tygron Engine==
==How does the Query Tool relate to the {{software}}==
[[File:QueryToolPanel-Q3-2015.jpg|right|thumb|400px|Query Tool Panel]]
[[File:QueryToolPanel-Q3-2015.jpg|right|thumb|400px|Query Tool Panel]]


A project in the Tygron Engine can use multiple geospatial data sources. In the Netherlands the Tygron Engine uses for instance BAG and Top10NL data, while in New York the Tygron Engine uses the PLUTO data set from the NYC Municipality. Both regions use OpenStreetMaps for the placement of roads etc.
A project in the {{software}} can use multiple geospatial data sources. In the Netherlands the {{software}} uses for instance BAG and Top10NL data, while in New York the {{software}} uses the PLUTO data set from the NYC Municipality. Both regions use OpenStreetMaps for the placement of roads etc.


To retrieve all sorts of data that are present, but for which no dedicated data structure (predefined indicators) already exists, the TQL (Tygron Query Language) has been developed. By using queries with the below structure, dedicated data requests can be sent to the Tygron Engine, from which the results can then be calculated in, for instance, an Excel Indicator.
To retrieve all sorts of data that are present, but for which no dedicated data structure (predefined indicators) already exists, the TQL (Tygron Query Language) has been developed. By using queries with the below structure, dedicated data requests can be sent to the {{software}}, from which the results can then be calculated in, for instance, an Excel Indicator.


: <code>SELECT</code> [[TQL#Queries|Result Parameter]] <code>WHERE</code> [[TQL#Clauses|Clause]] <code>AND</code> [[TQL#Clauses|Clause]] <code>AND</code> [[TQL#Clauses|Clause]] <code>AND</code>... etc.
: <code>SELECT</code> [[TQL#Queries|Result Parameter]] <code>WHERE</code> [[TQL#Clauses|Clause]] <code>AND</code> [[TQL#Clauses|Clause]] <code>AND</code> [[TQL#Clauses|Clause]] <code>AND</code>... etc.
Line 16: Line 16:
<br clear=all>
<br clear=all>


==How to use the Query Tool in the Tygron Engine==
==How to use the Query Tool in the {{software}}==


{{Editor ribbon|header=Tools|bar=Query Tool}}
{{Editor location|Query Tool}}


The Query Tool can be found in the Tygron Engine Editor, under the ribbon header, in the [[Tools]] tab, by selecting the Query Tool Button. The Query Tool consists of three areas; the construction drop down menus, the actual query as a result from the construction selections and its result. It is theoretically possible to add an unlimited number of clauses. This being said, only a limited amount (of combinations) of options is available to do so.
The Query Tool can be found in the {{software}} Editor, under the ribbon header, in the [[Tools]] tab, by selecting the Query Tool Button. The Query Tool consists of three areas; the construction drop down menus, the actual query as a result from the construction selections and its result. It is theoretically possible to add an unlimited number of clauses. This being said, only a limited amount (of combinations) of options is available to do so.


{{Editor steps|title=use the Query Tool|Select Editor &#8594; Tools &#8594; Query Tool|Select the desired Result Parameter|Add clauses, as many as needed|Select the clause properties from the drop down menus|Notice the full query string appears in the Query field|Notice the actual Query result on the bottom of the panel}}
{{Editor steps|title=use the Query Tool|Select Editor &#8594; Tools &#8594; Query Tool|Select the desired Result Parameter|Add clauses, as many as needed|Select the clause properties from the drop down menus|Notice the full query string appears in the Query field|Notice the actual Query result on the bottom of the panel}}
Line 34: Line 34:


==Uses of the Query Tool==
==Uses of the Query Tool==
There are a couple of  main uses for the Tygron Query Tool.
There are a couple of  main uses for the Tygron Query Tool:
:*The Query Tool can be used to verify if data is available for queries in the project area.  
:*The Query Tool can be used to verify if data is available for queries in the project area.  
:*The tool can also be used to create queries, which can be copied and pasted in an Excel file to be uploaded as an Excel Indicator.  
:*The tool can also be used to create queries, which can be copied and pasted in an Excel file to be uploaded as an Excel Indicator.  
:*The other way round is also possible, recreating a query in the tool from an Excel Indicator file, to verify if the query actually grabs data from the project area.
:*The reverse is also possible, recreating a query in the tool from an Excel Indicator file, to verify if the query actually grabs data from the project area.


===Verifying available Data in a Project Area===
===Verifying available Data in a Project Area===
: When a new project has been generated, the Query Tool can quickly check if the area contains data that can be retrieved by a Query. Doing so enables the user to quickly check the available data, without having to create a separate Excel Indicator spreadsheet with queries.
: When a new project has been generated, the Query Tool can quickly check if the area contains data that can be retrieved by a Query. Doing so enables the user to quickly check the available data, without having to create a separate Excel Indicator spreadsheet with queries.


: For instance, when a project area has been created with a safe zone terrain, by importing a relevant feature service from ArcGIS, the query tool can quickly verify if the safe zone data can be retrieved with a Query.
: For instance, when a project area has been created with a safe zone terrain, by importing a relevant feature service from [https://www.arcgis.com/features ArcGIS], the query tool can quickly verify if the safe zone data can be retrieved with a Query.


{{Editor steps|title=verify available data in a project area with an ArcGIS safe zone|Load or create a game with an ArcGIS safe zone|Select [[Editor]] &#8594; Tools &#8594; Query Tool|Select <code>FLOORSIZE</code> as Result Parameter|Add <code>TERRAINTYPE</code> IS <code>SAFE_ZONE</code> as clause|Verify that the Query result returns a value}}
{{Editor steps|title=verify available data in a project area with an ArcGIS safe zone|Load or create a project with an ArcGIS safe zone|Select [[Editor]] &#8594; Tools &#8594; Query Tool|Select <code>FLOORSIZE</code> as Result Parameter|Add <code>TERRAINTYPE</code> IS <code>SAFE_ZONE</code> as clause|Verify that the Query result returns a value}}
<br>
<br>
: [[File:QueryToolVerifyData-Q3-2015.jpg|thumb|left|Verify data in project area]]
: [[File:QueryToolVerifyData-Q3-2015.jpg|thumb|left|Verify data in project area]]
Line 52: Line 52:
: When setting up an Excel Indicator, the Query Tool can be used to configure Queries, after which the full Query text string can be copied over to the Excel Indicator file.
: When setting up an Excel Indicator, the Query Tool can be used to configure Queries, after which the full Query text string can be copied over to the Excel Indicator file.


: Looking at the above example, it is easy now to copy and paste the just created query <code>SELECT_FLOORSIZE_WHERE_TERAINTYPE_IS_SAFE_ZONE</code> into an Excel Indicator spreadsheet.
: Looking at the above example, it is easy now to copy and paste the just created query <code>SELECT_FLOORSIZE_WHERE_TERAINTYPE_IS_SAFE_ZONE</code> into an appropriate [[Excel (Indicator)#Naming Cells|name field]] on an Excel Indicator spreadsheet.


{{Editor steps|title=configure a query for an Excel Indicator spreadsheet|Load or create a game with an ArcGIS safe zone|Select Editor &#8594; Tools &#8594; Query Tool|Select <code>FLOORSIZE</code> as Result Parameter|Add <code>TERRAINTYPE</code> IS <code>SAFE_ZONE</code> as clause|Copy the created Query text string, and paste this in the appropriate name field of the Excel Indicator spreadsheet}}
{{Editor steps|title=configure a query for an Excel Indicator spreadsheet|Load or create a project with an ArcGIS safe zone|Select Editor &#8594; Tools &#8594; Query Tool|Select <code>FLOORSIZE</code> as Result Parameter|Add <code>TERRAINTYPE</code> IS <code>SAFE_ZONE</code> as clause|Copy the created Query text string, and paste this in the appropriate name field of the Excel Indicator spreadsheet}}
<br>
<br>
: [[File:QueryToolConigurefQuery-Q3-2015.jpg|thumb|left|Copy a query for pasting in an Excel Indicator spreadsheet]]
: [[File:QueryToolConigurefQuery-Q3-2015.jpg|thumb|left|Copy a query for pasting in an Excel Indicator spreadsheet]]
Line 61: Line 61:
===Using the Query Tool to test queries from an Excel Indicator spreadsheet===
===Using the Query Tool to test queries from an Excel Indicator spreadsheet===
[[File:QueryErrorMessage-Q3-2015.jpg|thumb|300px|right|Query error message]]
[[File:QueryErrorMessage-Q3-2015.jpg|thumb|300px|right|Query error message]]
: Another use is checking the validity of queries that were created outside the Tygron Engine. Going back to the above example, we created the following query:
: Another use is checking the validity of queries that were created outside the {{software}}. For example, consider the following query:
: <code>SELECT_FLOORSIZE_WHERE_CATEGORY_IS_OFFICES_AND_STAKEHOLDER_IS_MUNICIPALITY_AND_TERRAINTYPE_IS_SAFE_ZONE</code>
: <code>SELECT_FLOORSIZE_WHERE_CATEGORY_IS_OFFICES_AND_STAKEHOLDER_IS_MUNICIPALITY_AND_TERRAINTYPE_IS_SAFE_ZONE</code>


: This query seems to look good on paper, the string makes sense: Give the floor size of all municipal offices that are located in the safe zone. However, when uploading the query in a spread sheet for an Excel Indicator, the query will result in an error. If the user first attempts to recreate the query in the Tygron Engine Query Tool, he will notice that the clause <code>STAKEHOLDER_IS_MUNICIPALITY</code> is incorrect. The Tygron Engine assigns fixed numbers to stakeholders, in this case a '0'. The correct clause should read <code>STAKEHOLDER_IS_0</code>, and the full Query should be <code>SELECT_FLOORSIZE_WHERE_CATEGORY_IS_OFFICES_AND_STAKEHOLDER_IS_0_AND_TERRAINTYPE_IS_SAFE_ZONE</code>.
: This query seems to look good on paper, the string makes sense: Give the floor size of all municipal offices that are located in the safe zone. However, when uploading the query in a spread sheet for an Excel Indicator, the query will result in an error. If the user first attempts to recreate the query in the {{software}} Query Tool, he will notice that the clause <code>STAKEHOLDER_IS_MUNICIPALITY</code> is incorrect. The {{software}} assigns fixed numbers to stakeholders, in this case a '0'. The correct clause should read <code>STAKEHOLDER_IS_0</code>, and the full Query should be <code>SELECT_FLOORSIZE_WHERE_CATEGORY_IS_OFFICES_AND_STAKEHOLDER_IS_0_AND_TERRAINTYPE_IS_SAFE_ZONE</code>.
<br>
<br>
{{Editor steps|title=use the Query Tool to test queries from an Excel Indicator spreadsheet|Create a query outside of the Tygron Engine (ex.on a spreadsheet)|Load or create a game with the project area in the Editor|Select Editor &#8594; Tools &#8594; Query Tool|Recreate the query from the spreadsheet in the Tygron Engine Query Tool|Observe if the Query from the tool matches the query created outside the Tygron Engine|Observe if the output result matches expectations}}
{{Editor steps|title=use the Query Tool to test queries from an Excel Indicator spreadsheet|Create a query outside of the {{software}} (e.g. on a spreadsheet)|Load or create a project with the project area in the Editor|Select Editor &#8594; Tools &#8594; Query Tool|Recreate the query from the spreadsheet in the {{software}} Query Tool|Observe if the Query from the tool matches the query created outside the {{software}}|Observe if the output result matches expectations}}
 
{{Template:Editor_current_situation_nav}}

Revision as of 15:45, 27 February 2020

Tql webinar.png

What is the Query Tool?

The Query Tool is a support tool in the Tygron Platform that aids the user by constructing data requests in the Tygron Query Language (TQL) and displaying the query output. By using this tool, the user can quickly verify if the data needed can be extracted from the project data with the available query elements. Also the tool allows for verification of the validity of the retrieved data. All this can be achieved without creating and uploading a dedicated Query Spread Sheet. See also: Excel (Indicator)

How does the Query Tool relate to the Tygron Platform

Query Tool Panel

A project in the Tygron Platform can use multiple geospatial data sources. In the Netherlands the Tygron Platform uses for instance BAG and Top10NL data, while in New York the Tygron Platform uses the PLUTO data set from the NYC Municipality. Both regions use OpenStreetMaps for the placement of roads etc.

To retrieve all sorts of data that are present, but for which no dedicated data structure (predefined indicators) already exists, the TQL (Tygron Query Language) has been developed. By using queries with the below structure, dedicated data requests can be sent to the Tygron Platform, from which the results can then be calculated in, for instance, an Excel Indicator.

SELECT Result Parameter WHERE Clause AND Clause AND Clause AND... etc.

The Query Tool provides an interface in the Editor that allows for quick creation and verification of such TQL Queries.

How to use the Query Tool in the Tygron Platform

Editor → Current Situation (Ribbon tab) → Queries (Ribbon bar)

The Query Tool can be found in the Tygron Platform Editor, under the ribbon header, in the Tools tab, by selecting the Query Tool Button. The Query Tool consists of three areas; the construction drop down menus, the actual query as a result from the construction selections and its result. It is theoretically possible to add an unlimited number of clauses. This being said, only a limited amount (of combinations) of options is available to do so.

How to use the Query Tool:
  1. Select Editor → Tools → Query Tool
  2. Select the desired Result Parameter
  3. Add clauses, as many as needed
  4. Select the clause properties from the drop down menus
  5. Notice the full query string appears in the Query field
  6. Notice the actual Query result on the bottom of the panel

Uses of the Query Tool

There are a couple of main uses for the Tygron Query Tool:

  • The Query Tool can be used to verify if data is available for queries in the project area.
  • The tool can also be used to create queries, which can be copied and pasted in an Excel file to be uploaded as an Excel Indicator.
  • The reverse is also possible, recreating a query in the tool from an Excel Indicator file, to verify if the query actually grabs data from the project area.

Verifying available Data in a Project Area

When a new project has been generated, the Query Tool can quickly check if the area contains data that can be retrieved by a Query. Doing so enables the user to quickly check the available data, without having to create a separate Excel Indicator spreadsheet with queries.
For instance, when a project area has been created with a safe zone terrain, by importing a relevant feature service from ArcGIS, the query tool can quickly verify if the safe zone data can be retrieved with a Query.
How to verify available data in a project area with an ArcGIS safe zone:
  1. Load or create a project with an ArcGIS safe zone
  2. Select Editor → Tools → Query Tool
  3. Select FLOORSIZE as Result Parameter
  4. Add TERRAINTYPE IS SAFE_ZONE as clause
  5. Verify that the Query result returns a value


Verify data in project area


Configuring a Query for an Excel Indicator

When setting up an Excel Indicator, the Query Tool can be used to configure Queries, after which the full Query text string can be copied over to the Excel Indicator file.
Looking at the above example, it is easy now to copy and paste the just created query SELECT_FLOORSIZE_WHERE_TERAINTYPE_IS_SAFE_ZONE into an appropriate name field on an Excel Indicator spreadsheet.
How to configure a query for an Excel Indicator spreadsheet:
  1. Load or create a project with an ArcGIS safe zone
  2. Select Editor → Tools → Query Tool
  3. Select FLOORSIZE as Result Parameter
  4. Add TERRAINTYPE IS SAFE_ZONE as clause
  5. Copy the created Query text string, and paste this in the appropriate name field of the Excel Indicator spreadsheet


Copy a query for pasting in an Excel Indicator spreadsheet


Using the Query Tool to test queries from an Excel Indicator spreadsheet

Query error message
Another use is checking the validity of queries that were created outside the Tygron Platform. For example, consider the following query:
SELECT_FLOORSIZE_WHERE_CATEGORY_IS_OFFICES_AND_STAKEHOLDER_IS_MUNICIPALITY_AND_TERRAINTYPE_IS_SAFE_ZONE
This query seems to look good on paper, the string makes sense: Give the floor size of all municipal offices that are located in the safe zone. However, when uploading the query in a spread sheet for an Excel Indicator, the query will result in an error. If the user first attempts to recreate the query in the Tygron Platform Query Tool, he will notice that the clause STAKEHOLDER_IS_MUNICIPALITY is incorrect. The Tygron Platform assigns fixed numbers to stakeholders, in this case a '0'. The correct clause should read STAKEHOLDER_IS_0, and the full Query should be SELECT_FLOORSIZE_WHERE_CATEGORY_IS_OFFICES_AND_STAKEHOLDER_IS_0_AND_TERRAINTYPE_IS_SAFE_ZONE.


How to use the Query Tool to test queries from an Excel Indicator spreadsheet:
  1. Create a query outside of the Tygron Platform (e.g. on a spreadsheet)
  2. Load or create a project with the project area in the Editor
  3. Select Editor → Tools → Query Tool
  4. Recreate the query from the spreadsheet in the Tygron Platform Query Tool
  5. Observe if the Query from the tool matches the query created outside the Tygron Platform
  6. Observe if the output result matches expectations