Patchworks Query Language

Table of Contents

Data Types
Forming Expressions
Functions
Mathematical functions
abs — Returns the absolute value of the input number.
ageclass — This functions will convert an age to an age class category.
ceil — Rounds a number up to the next nearest mathematical integer value.
exp — Returns e raised to the power of number
floor — Rounds a number down to the next nearest mathematical integer value.
inrange — Test if a numeric value is within to other values.
int — Converts the input number or string into an integer value.
isinfinite — Test if a value represents an infinite value.
isnan — Test if a value represents an invalid value (Not A Number).
log — Returns natural logarithm (base e) of a number
max — Returns the largest value in a set of values.
min — Returns the smallest value in a set of values.
number — Converts the input number or string into an double precision number value.
percent — Return the first value as a percentage of the second number.
pow — Return the result of a number raised to a power.
random — Return a pseudo random value between 0 and 1.
round — Returns the closest integer to the argument.
sqrt — Returns the correctly rounded positive square root of a number.
String functions
endswith — Tests if a string ends with the specified suffix.
format — Returns a formatted string using the specified format string and arguments.
indexof — Return the starting position of a substring within a string.
lastindexof — Return the starting position of a substring within a string, searching from the end forward.
left — Return the specified number of characters from the start of a string.
length — Return the length of a string.
lower — Convert a string to lower case.
matches — Tests if a string matches with a regular expression.
matchgroup — Extract a substring from a string using a regular expression.
replaceall — Return a string replacing all occurrences of one substring with another.
right — Return the specified number of characters from the end of a string.
spcomp — Return a percent species composition value from species composition string like used by the Ontario MNRF. In the alternate form encode a series of values in to a species composition string.
startswith — Tests if a string starts with the specified suffix.
string — Converts the input value into a string value.
substring — Returns a string that is a substring of this string.
trim — Trim white space from the beginning and ending of a string.
upper — Convert a string to upper case.
Lookup functions
lookuplist — Extract a value from a list.
lookuptable — Extract a value from a list.
map — Extract a value from a list.
range — Classifies a number in to a numeric range category.
Control functions
catch — Evaluate an expression and handle error conditions.
error — Raise an error condition.
if — Return alternate values depending on the value of a condition.
Curve functions
attribute — Return a reference to an attribute curve that has the matching attribute label.
attributeid — Return a reference to an attribute definition that has the matching id value.
curve — Return a curve defined by a series of X and Y points.
curvefloor — Reshape a curve to not have values descending below a floor value.
curveid — Return a reference to curve definition that has the matching id value.
curvemaxx — Return the highest x value in the curve.
curvemaxy — Return the highest y value in the curve.
curveminx — Return the lowest x value in the curve.
curveminy — Return the lowest y value in the curve.
discount — Return discount curve.
domainof — Return the first value on the domain axis for the specified value from the range axis.
lookupcurve — Return the interpolation of a value along a curve.
mai — Return a mean annual increment curve.
operable — Return a feature curve that is trimmed to the operable timing.
shiftcurve — Return a curve that has been shifted by a specified amount.
ytp — Return the age of the highest point along a curve.
List functions
list — Return a list of values.
split — Split a string and return a list of strings.
Data functions
area — Return the area of a polygon.
column — Return a value from the data table.
columntest — Return a value from the data table or a default expression.
dominantcolumn — Return the name of the column having the highest value.
getenv — Return the value of an environment variable.
getvar — Return the value of previously saved value.
length — Return the length of a line feature.
nonzerocolumn — Return the name of the first matching column having a nonzero value.
offset — Return the offset (year in the future) for the current calculation.
period — Return the planning period for the current calculation.
row — Return the data row number for the current calculation.
setvar — Evaluate an expression, save and return the value.
summarizecolumns — Add the values from all columns that match the regular expression.
summarizeperiods — Add the values of an exprssion over a set of periods.
year — Return the year in the future (offset) for the current calculation.
Selection functions
intersection — Return the set of features from the polygon table that are intersected by selected features in the network table.
neighbours — Return the set of polygon features that are neighbours of a selected set of polygon features.
selectbylocation — Return a selection of records based on a proximity to selected features in a second data source.
selected — Return a true if the record is in the selected set, false otherwise.
selection — Return a selection of records based on an expression.
Data type promotion
Examples
Regular Expressions

This appendix describes the syntax for the SQL-like query language used throughout Patchworks (Query tool, ForestModel, Reports, etc.). The form of language is similar to many other query languages, with relational, logical and arithmetic operators joining data values, and a set of functions to perform string manipulation, mathematical computation and other tasks.