Expression Builder – Avenza Systems

The Expression Builder is used for the entry and editing of expressions. It is used in a number of locations such as creating new attribute values and properties, making selections or apply styles. Expressions are built using a combination of names, operators and functions. Edit Expression is available for a number of tools throughout Geographic Imager:

Geographic Imager tool	Description
Lines and Areas Import	When importing spatial data, use the Filtering options to create a Layer Filter.
Points and Text Import	When importing spatial data, use the Filtering options to create a Layer Filter.

The type of attribute is indicated by an icon preceding the column name:

	Boolean	Column contains True or False values
	Integer	Column contains only whole numbers (limited to 10 digits)
	Double	Column contains numbers carrying decimal values (formerly Real)
	String	Column can contain attributes that are both alpha and numeric

Building Expressions

expression-builder-dialog

Expressions can be built by typing or by clicking the operator buttons and items in the Objects list (attribute name and values, constants and functions). These items are colour coded for easy identification: attribute names in purple, string values in orange, operators and numerical values in grey, constants in green and functions in black. Click any of the operator buttons or double-click an item from the Objects list to insert it into the expression.

It is recommended to use the interface rather than the keyboard to build expressions, to guarantee functions are formatted correctly (e.g. with brackets and appropriate case) and for attribute values of type strings, quotations marks are added automatically. Note that expressions are case-sensitive. For string comparison, all strings values can be converted to a same case using the appropriate function (LOWER(“string”) or UPPER(“string”)).

Validity

The validity of the expression will be displayed below the Expression Entry text area and will be updated as the expression is built. The Expression Validity icon will report if the expression entered is valid (green check mark). Otherwise it will report invalid (red exclamation) and include additional warning notes.

Operator Buttons

Click to insert an operator at the current cursor position. Available operators are as follows:

Operator	Description
(	Open clause operator
)	Close clause operator
AND	Logical AND operator
OR	Logical OR operator
=	Logical equal to comparator
!=	Logical not equal to comparator
<	Logical less than comparator
>	Logical greater than comparator
+	Mathematical addition operator
-	Mathematical subtraction operator
/	Mathematical division operator
*	Mathematical multiplication operator
&	Text concatenate operator

Expression Components

Items in the Objects list are divided into three function categories: math, string and miscellaneous. Information on a selected item is displayed in the Description panel. Double-click to insert an object at the current cursor position.

	Function	Description
	ABS	Absolute value of a number
	ACOS	Inverse of the cosine of an angle
	ASIN	Arcsine of an angle
	ATAN	Arctangent of an angle
	CONTAINS	True if source string contains search string
	COS	Cosine of an angle
	DEGREES	Converts values from radians to degrees
	ENDSWITH	Returns true the source string ends with the suffix string
	FIXED	Numbers as string values
	FROM_HEX	Returns the decimal value of the hexadecimal string
	IF	Conditional statement
	IF_CASE	Conditional statement based on multiple cases
	LEFT	Extracts the first N characters of a string
	LENGTH	Returns the number of characters in a string
	LIKE	Searches the source string using wild cards
	LOG	Gets the logarithm of the value (base 10 log)
	LN	Gets the natural logarithm (base e log, e = 2.718281828)
	MAX	Returns biggest attribute value
	MAX_COLNAME	Returns name of attribute which has biggest value
	MIN	Returns smallest attribute value
	MIN_COLNAME	Returns name of attribute which has smallest value
	LOWER	Converts string to lower case
	MID	Extracts N characters of a string from a specified location
	MOD	Gets the remainder of numerator divided by divisor
	NUMBER	String values as numbers
	POW	Base to the power of an exponent
	PROPER	Strings converted to capital case
	RADIANS	Converts values from degrees to radians
	RAND	Generates a random value between zero (min) and value (max)
	REGEX	Searches the source string using a regular expression
	RIGHT	Extracts the last N characters of a string
	ROUND	Rounds to specified decimals of precision
	ROUNDDOWN	Rounds down to specified decimals of precision
	ROUNDUP	Rounds up to specified decimals of precision
	SEARCH	Returns the position of a character in a string
	SIN	The sine of an angle
	SPLIT	Splits a string and extracts the indexed part of it
	SQRT	Square root of a value
	STARTSWITH	Returns true if the source string starts with the prefix string
	SUBSTITUTE	Replaces a set of characters by another in a string
	SUBSTITUTE_RX	Replaces a regular expression by characters in a string
	TAN	Tangent of an angle
	TO_HEX	Returns hex string version of value
	TRIM	Removes all spaces in a text (except single ones between words)
	UPPER	Converts string to upper case
Constants #false Boolean false value #true Boolean true value #pi π numerical value (3.141592)

information String values are case-sensitive and must be entered in double quotes (“...”).

information For functions using indexes for text position (MID, SEARCH and SPLIT), the first index number is 0.

information Layer may vary depending on tool.

Examples of Basic Expressions

More examples of each individual function can be found in the Expression Builder dialog box. Expand the Expression Components, choose a function or constant on the Objects list and the Description tab is updated with explanations and examples.

Expression	Result
NAME = “Ontario”	All items with the value “Ontario” in the NAME column are selected.
POPULATION < 1000000	All items with values less than one million in the POPULATION column are selected.
NAME = “Ontario” OR NAME = “Alberta”	All items with the value “Ontario” OR “Alberta” in the NAME column are selected.
NAME = “Ontario” AND POPULATION < 1000000	Only the items containing the value “Ontario” in the NAME column AND values less than one million in the POPULATION column are selected.
LOWER(NAME) (applied to column = name)	All items in column name are assigned the value of the column NAME in lower case (e.g. “ontario” for “Ontario”)
PROPER(name) (applied to column = Proper_name)	All items in Proper_name are assigned the value of the column name in proper case (or capital case) (e.g. “Ontario” for “ontario”)
TRIM(JOINING_COLUMN)	All items are trimmed with all spaces except single ones between words (e.g. " Route 66 " becomes "Route 66"). This is particularly useful prior to using the Join Table function because extra spaces at the beginning, end or in between words will cause the join to fail.

Examples of Complex Expressions

Expression	Result
*LIKE (NAME, “New”)*	All items with the string of letters "New" in the NAME column are selected (e.g. “New Brunswick” and “Province of Newfoundland”)
STARTSWITH (NAME, “o”)	All items that starts with the letter “o” (“Ontario”) are selected.
ROUND((POPULATION/ AREA ),3) (applied to column=density)	All items are calculated as “population divided by area”, rounded to three decimals.
IF_CASE("null", VALUE>0,"positive",VALUE<0,"negative")	A default status of "null" is assigned unless the value is strictly greater than or less than zero.
SUBSTITUTE_RX(ROAD_NAME,"\D","")	All non digit characters (\D is the regular expression for non-digit character) are replaced by nothing (two double-quotes indicate that the new string is blank), therefore only numerical values are kept. For example, if the ROAD_NAME equals "Route 66", the result is "66". This can be useful to prepare an attribute table prior to labeling roads with road number rather than road names.
SPLIT(GEOLOGIC_UNIT,"(",0)	All items containing one or more open parenthesis are split in several text blocks (as many as there are open parenthesis, plus one), and returns the first block found (index 0). For example, if GEOLOGIC_UNIT equals "Qmw(Qc)", the result is "Qmw".
SUBSTITUTE(SPLIT(GEOLOGIC_UNIT,"(",1),")","")	Removes all closing parenthesis (i.e substitute all closing parenthesis with nothing) in the result of the same SPLIT as above, that returns the second text block found (index 1). For example, if GEOLOGIC_UNIT equals "Qmw(Qc)", the result is "Qc": first the SPLIT function returns "QC)" and then ")" is removed with a SUBSTITUTE.