Data Tables & Queries in Scholars GeoPortal
In the data table view you can preview the underlying data table and attribute fields. You can also query the table to select and subset data if you are interested in particular attributes or features in the data.
Query expressions are used to extract subsets of data that match the specified condition(s). The input text area in the Query Builder Tool in Scholars GeoPortal is used for building what is known as a WHERE clause, part of a SQL statement. Before beginning the construction of custom query expressions, it is advisable to view the possible field(s) in the table you would like to include in the query.
Clicking on the “ View Table ” button in the top right of the portal, once you have added a data layer to the map, will bring up the table view and query tool.
Query Builder Tool
In Scholars GeoPortal, the Query builder tool allows you to build query statements. To query a layer in Scholars GeoPortal, from within the table view, click “ Query ”.
The query builder tool allows you to double-click and select field names, and type in associated values to retrieve.
For example, to locate records in the Province of Ontario only, you would use the expression:
PROV = ‘ON’
Double-click on populated field names to add them to the query expression text area.
The list entry of each field in the field list includes a field data type. To query a string data type field, wrap the query text in single quotes, as seen in the previous example. Integer (whole number) and double (decimal number) data types do not require single quotes.
Querying String Values
This statement returns any capital city whose name exactly matches “Calgary”:
NAME = ‘Calgary’
The wildcard symbol (%) can be used in combination with the LIKE keyword to search for partial string matches. This can provide a powerful way to explore data by known textual identifiers.
This statement returns any capital city whose province abbreviation begins with the letter “N”:
PROV LIKE ‘N%’
This statement returns any capital city whose province abbreviation ends with the letter “N”:
PROV LIKE '%N'
This statement returns any capital city whose province abbreviation contains the letter “N”:
PROV LIKE ‘%N%’
The wildcard symbol allows for any character and any amount of characters to match. To match any character in a single (or a defined number) space, use the “_” symbol.
This statement returns any capital city that has any character in the first position, an “i” in the second position and any number or type of trailer characters:
NAME LIKE ‘_i%’
Strings can also be matched using the <, >, <=, >=, <> and BETWEEN operators.
This statement returns any capital city whose name starts with a letter greater than or equal to “W” in alphabetical order:
NAME >= ‘W’
This statement returns any capital city whose name starts with a letter greater than or equal to “F”, and less than or equal to “X”, in alphabetical order:
NAME BETWEEN ‘F’ AND ‘X’
Querying Numeric Fields
Numeric fields can be matched using the =, <, >, <=, >=, <> and BETWEEN operators. Numeric query expressions can be arithmetic. The examples below can be tested with the Populated Placenames (PPN) layer.
This statement returns all place names that are in a census subdivision with a population greater than 2.4 million in 2001:
CSD_POP01 > 2400000
This statement uses data provider codes to find all place names that are smaller than 100,000 inhabitants, but are capital cities:
MJR_CITY - CAPITAL = -1
Using AND, OR, NOT, IN
The AND, OR, NOT, and IN operators allow for the creation of more complex query expressions. The examples below use the Aerodromes (AER) layer.
Use the NOT operator to negate a condition. For example, the following query returns all records where the aerodrome is not in Ontario:
NOT PROV = ‘ON’
Use the AND operator to ensure that both conditions are true. For example, the following query returns all records where the aerodrome is not abandoned and is of type “Water”:
NOT STATUS = 'ABANDONED' AND AER_TYPE = 'WATER'
Use the OR operator when any of the conditions are true. For example, the following query returns all records where “KENORA” appears anywhere in the “Name” or “City” fields:
NAME LIKE '%KENORA%' OR CITY LIKE '%KENORA%'
Use the IN operator to search a range of input values. For example, this query returns aerodromes that have their type matching both “Heliport” and “Water”. This is an effective alternative to using the “OR” operator:
IN is also useful for finding exceptional cases in combination with the “NOT” operator. For example, this query returns aerodromes that do not have a precision code of 1 or 2:
PREC_CODE NOT IN(1,2)
For additional assistance with queries in the GeoPortal, contact a person at your institution .