Formula Calculator

Attribute calculation may be done through the Global Mapper interface, by selecting Calculate/ Copy Attribute Values from the Layer Menu. The tool can calculate a new attribute value (or update the value for an existing attribute) for features in a layer based on a formula. That formula may use other attributes as operands.

In scripting, the formula calculator is used by the CALC_ATTR_FORMULA command and the DEFINE_VAR command. It allows you to calculate a new value (or update an existing value) based on a formula. In the case of CALC_ATTR_FORMULA, the calculator allows you to calculate new attribute values based on a feature's attributes. In the case of DEFINE_VAR, the calculator allows you to calculate new script variable values. Formulas may combine numbers and strings and either feature attribute references or script variable references. A number of functions are provided to operate on these values.

Many features of the formula calculator are identical when used in the two applications; where different, the difference will be noted below.

Formula reference

Formulas are expressions that can combine strings, numbers and attributes to form a new value. The formula language also includes a number of functions that may be called to manipulate formula values.

Note that although attributes are fundamentally strings, we may use them to represent numbers, and that can cause ambiguity in formulas, since the operation may equally be applicable if the attributes are treated as strings or numbers, so the calculator needs to know how to interpret them. To guide the formula calculator in these circumstances, the user can choose one of several calculation modes: automatic, numeric, or string. In automatic mode, the default, for operations like comparison ('=', '<>'), relational ('<', '<=', '>', '>=') or addition ('+'), the calculation is performed based on the type of the first operand. For numeric mode, the calculation is performed as if its arguments were numeric. For string mode, the calculation is performed as string. The calculation mode may be set in the Calc Attribute Setup dialog, or as a parameter to the CALC_ATTR_FORMULA. It is important to understand the rules when using attribute values in conjunction with literal values (numbers and strings)

For example, the ELEVATION attribute typically denotes a numeric value. Suppose that its value is "1203.47". Consider the formula "ELEVATION + 100". The result is dependent on how the ELEVATION term is interpreted:

Calculation mode: Automatic: Since ELEVATION is considered to be a string, this will be a string concatenation operation, with result "1203.47100"

Calculation mode: Numeric: ELEVATION can successfully be treated as a number, so the result is "1303.47"

Calculation mode: String: ELEVATION is treated as a string, so the result is "1203.47100"

 

Conversely, consider the formula "100 + ELEVATION":

Calculation mode: Automatic: ELEVATION can successfully be treated as a number, so the result is "1303.47"

Calculation mode: Numeric: ELEVATION can successfully be treated as a number, so the result is "1303.47"

Calculation mode: String: ELEVATION is treated as a string, so the result is "1203.47100"

In many cases, conversions are performed automatically; for example, when a string expression is expected in a a function parameter, a conversion from numeric type is performed. If required, there are functions to turn an expression into one or the other data type; see the NUM, STR, and BOOL functions below. For example, if the calculation mode is automatic, then we can force ELEVATION to be treated as a number by using the NUM function: "num(ELEVATION) + 100"

When using a string as a number, conversions are not guaranteed, particularly string-to-numeric conversions (for example, converting the string "four" to a number would give the result 0); these are generally ignored in the calculator. If you want to be certain that a string contains a number, you can use the ISNUM function to verify it.

Values

Formula operators

  Formulas use various mathematical and logical operators to form a result, similar to spreadsheet formulas. They are (in order of precedence, low-to-high):

You may also use parentheses to specify order of operations. In the absence of parentheses, higher precedence operations are performed before lower precedence operations. That is, in the formula "a + b * c", the result is the value of 'a' plus the product of 'b' and 'c' (equivalently "a + (b * c)").

Functions

  The calculator provides a number of formulas to aid in calculation of values. Note that function names are case-insensitive; e.g., abs is the same as ABS

Regular Expressions

Regular expressions are used in the MATCH, SEARCH, REPLACE, and FIND functions. Regular expressions allow a very rich and powerful set of capabilities for performing pattern matching in strings. Regular expressions in Global Mapper are specified using a modified version of the ECMAScript regular expression language, documented here: Modified ECMAScript regular expression grammar.

Assume that the attribute "DLG3CODEPAIR is the string "120,250". Some examples:

Note that the SEARCH function only returns the first match in the string (if any); there is no facility for returning further matches.

The third parameter of the REPLACE function is the replacement string. The replacement string allows you to specify literal characters as the replacement for the match pattern, but you may also use the following special sequences to specify other replacements:

$n - The n-th backreference (i.e., a copy of the n-th matched group specified with parentheses in the regex pattern). n must be an integer value designating a valid backreference, greater than 0, and of two digits at most. E.g., '$2' specifies the second parenthesis-specified group in the match pattern.

$& - A copy of the entire match

$` - The prefix (i.e., the part of the target sequence that precedes the match).

$ยด - The suffix (i.e., the part of the target sequence that follows the match).

$$ - A single $ character.

Examples

Note that the examples that follow are of attribute formulas; equivalent script formulas would be very similar, but would refer to script variables rather than feature attributes.

Here is a sample of creating a new elevation attribute in feet from an elevation attribute (ELEV_M) in meters, including an appended unit string.

GLOBAL_MAPPER_SCRIPT VERSION=1.00

// Create new ELEV_FT attribute with attribute in feet in any loaded layers

CALC_ATTR_FORMULA NEW_ATTR="ELEV_FT" FORMULA="ELEV_MT * 3.2808"

// Append the unit name to the new attribute

CALC_ATTR_FORMULA NEW_ATTR="ELEV_FT" FORMULA="ELEV_FT + ' ft'"

Here is a sample of some text manipulation using various string and regular expression functions. Assume that the feature set has an attribute named DLG3CODEPAIR that is a string formatted in the form of a comma-separated pair of numbers, e.g. "123,456" (this is the familiar USGS Digital Line Graph major/minor code pair). To implement a script that pulls these numbers out as individual attributes, you could use the clip() function:

GLOBAL_MAPPER_SCRIPT VERSION=1.00

// Create new separate DLG code attributes

CALC_ATTR_FORMULA NEW_ATTR="DLGCodeMajor" FORMULA="clip( DLG3CODEPAIR, '', ',' )"

CALC_ATTR_FORMULA NEW_ATTR="DLGCodeMinor" FORMULA="clip( DLG3CODEPAIR, ',', '' )"

Alternately, you could use regular expressions: '\d' is any digit, '+' means match one or more of the previous pattern, and '$' means match the end of the source string:

GLOBAL_MAPPER_SCRIPT VERSION=1.00

// Create new separate DLG code attributes

CALC_ATTR_FORMULA NEW_ATTR="DLGCodeMajor" FORMULA="search( DLG3CODEPAIR, '\d+' )"

CALC_ATTR_FORMULA NEW_ATTR="DLGCodeMinor" FORMULA="search( DLG3CODEPAIR, '\d+$' )"

To create a new attribute that indicates whether the DLG code pair attribute exists and is valid::

GLOBAL_MAPPER_SCRIPT VERSION=1.00

// Create new attribute indicating DLG code pair exists and is valid

CALC_ATTR_FORMULA NEW_ATTR="DLGCodeValid" FORMULA="if( exists(DLG3CODEPAIR) and match(DLG3CODEPAIR,'\d+,\d+'), 'VALID', INVALID' )"

Here is a further example of text manipulation using the regular expression 'replace' function. Assume that the above feature set is known to contain DLG3CODEPAIR values that contain various problems in formatting, perhaps caused by user input errors. Although the standard format is described by the regular expression "\d+,\d+", actual values may contain extra spaces before or after the numbers, or the comma separator is not used or some other character, say ';' was entered. We'd like to tidy these values up so that they match the correct format. One way to do it might be to use a series of simple formulas to clean up the incorrect values. But the REPLACE function has some powerful features that can be used to fix these sorts of problems.

First, let's define a regular expression that describes the above problems:

'^ *(\d+) *(,|;)? *(\d+) *$'

This says: first try to match any space characters, followed by one or more digits, followed by some spaces, optionally followed by either ',' or ';', followed by some spaces, followed by one or more digits, followed by some spaces.

Note the use of parentheses to designate sub-patterns in the main pattern. This allows us to group smaller patterns so that we can express things like 'optional comma or semicolon' ('(,|;)?') easily, but more importantly, for the REPLACE function, it allows us to identify sub-patterns to the replacement parameter. In the replacement parameter, sub-patterns in the regular expression parameter are referenced using '$1', '$2', and so on. So in our example, the first digit string pattern is identified as '$1', the optional separator is identified as '$2', and the second digit string is identified by '$3'.

Therefore, to get what we want, we would specify the replacement parameter as '$1,$3', meaning the first digit string, followed by a comma, followed by the second digit string. The resultant script command would be as follows (omitting any validation of the attribute existence):

GLOBAL_MAPPER_SCRIPT VERSION=1.00

// Clean up DLG code pair attribute errors

CALC_ATTR_FORMULA NEW_ATTR="DLG3CODEPAIR" FORMULA="replace( DLG3CODEPAIR, ' *(\d+) *(,|;)? *(\d+) *', '$1,$3' )"