The Field Calculator Processor can be used to compute a value. The processor evaluates an expression to produce the needed value. The expression can include literal strings, numeric constants, and event data taken from named fields in the GeoEvent being processed. The calculated value can be written into an existing field, overwriting the data currently in that field, or it can be written into a new field created by the processor.
The Field Calculator Processor is a versatile processor. Relatively constant expressions such as Distance * 0.3048 can be used to calculate an equivalent distance in meters given a distance expressed in feet. The processor also supports a variety of Java String functions such as the expression replaceAll(Description, 'foo', 'bar'), for example, which can be used to replace all instances of the substring "foo" with the substring "bar" in the description field of each GeoEvent. The processor also supports a number of mathematical functions. For example, you can identify which of two GeoEvent fields contains a larger value using max(Time1, Time2) or generate a random number using random().
Some things to consider when using the Field Calculator Processor:
- Expressions can be mathematical or lexical (based on strings, rather than numerical values).
- When working with literal strings, be sure to enclose them in single quotes: 'Cat' + ' ' + 'Dog'.
- The processor will output a null value if a type conversion fails or the expression cannot be evaluated.
- Operands in an expression can be literal field names or tags applied to fields in a GeoEvent Definition. For example, the expression Odom1 + Odom2 could be used to add values in the Odom1 and Odom2 fields to produce a simple sum. If the Odom1 field was tagged MILEAGEA and the Odom2 field was tagged MILEAGEB, the expression MILEAGEA + MILEAGEB could be used.
- A good-faith effort will be made to handle data of different types within an expression. For example, 325 + 0.125, will be converted to an equivalent 325.0 + 0.125. If the specified output field expects a long integer value, the calculated double value will be truncated.
- Watch for overflow when creating expressions. For example, each term in the expression 60 * 60 * 80 * 10000 is a short integer, but the result exceeds the 32-bit range of a normal integer. Expressing each term as a double value such as 60.0 * 60.0 * 80.0 * 10000.0 avoids this issue.
- Writing a computed value to a new field alters a GeoEvent's schema, which requires GeoEvent Server to create a new GeoEvent Definition. When using this processor to create new fields, a field name and data type must be specified. A name for the GeoEvent Definition can be specified as well as an optional tag, which, if specified, will be applied to the new field in the resulting GeoEvent Definition.
The operators and functions supported by the Field Calculator Processor are illustrated below.
Operators and functions supported by Field Calculator
| Operation | Operator | Example |
|---|---|---|
Addition | | Expression adds the numeric values from the fields Odom1 and Odom2 to produce a simple summation. String concatenation (for example, 'Hello' + 'World') is also supported. |
Subtraction | | Expression subtracts the numeric values found in the fields tagged VALUEA and VALUEB to produce a simple difference. Unlike addition, which supports string concatenation, you cannot use subtraction to calculate the difference between two strings. |
Multiplication | | Expression multiplies the numeric value in the field named Altitude and the constant 0.3048 to produce a simple product (in this case, converting a value in feet to an equivalent value in meters). |
Division | | Expression divides the numeric value in the field named Distance by the constant 1.609344 to produce a simple quotient (converting a value in kilometers to an equivalent value in miles). |
Modulus | | Expression divides the numeric value in the field tagged VALUEA by the numeric value in the field tagged VALUEB and returns the remainder as a double value. |
Logical AND | | Expression performs a logical AND on the Boolean values from the fields named Flag1 and Flag2 to produce a Boolean result. Input fields must be of type Boolean. Literal strings (for example, TRUE) and constant values (for example, 1) in an expression will not be converted to an equivalent Boolean as part of the evaluation. |
Logical OR | | Expression performs a logical OR on the Boolean values from the fields named Flag1 and Flag2 to produce a Boolean result. |
Logical NOT | | Expression performs a logical negation on the Boolean value from the field Flag1 to produce a Boolean result. Supports nested expressions such as !(Flag1 && Flag2). |
Greater-Than | | Expression performs a logical evaluation to determine if the value in the field named Altitude is greater than the specified constant (result is a Boolean value). |
Greater-or-Equal | | Expression performs a logical evaluation to determine if the value in the field named Altitude is greater than or equal to the specified constant (result is a Boolean value). |
Less-Than | | Expression performs a logical evaluation to determine if the value in the field named Altitude is less than the specified constant (result is a Boolean value). |
Less-or-Equal | | Expression performs a logical evaluation to determine if the value in the field named Altitude is less than or equal to the specified constant (result is a Boolean value). |
Equality | | Expression performs a logical evaluation to determine if the values found in the fields tagged VALUEA and VALUEB are equivalent (result is a Boolean value). Equivalency is only supported for numeric types with the initial product release (10.2.0). |
Inequality | | Expression performs a logical evaluation to determine if the values found in the fields tagged VALUEA and VALUEB are not equal (result is a Boolean value). Inequality is only supported for numeric types with the initial product release (10.2.0). |
Note:
The Field Calculator Processor will attempt to auto-convert values when presented with mixed types. For example, the expression 10.0 + 5 would auto-convert to handle the summation as the addition of two floating point values. Mixing numeric values and string values such as codeValue + 'SomeString' where codeValue is a numeric value will produce an unspecified result. The correct approach would be to explicitly convert codeValue to a string using a string function such as valueOf(object) described below.Note:
The Field Calculator Processor equality and inequality operators should not be applied directly to floating point values. To correctly compare if two decimal values are equal, you must first scale the values, round the values to long integers, and then compare the long integer values.
- Do not compare MyFloat1 == MyFloat2.
- Instead, compare round(MyFloat1*1000) == round(MyFloat2*1000).
The Field Calculator Processor includes functions that mirror those found in java.lang.Math. For a complete technical specification and list of functions, refer to the Java developer documentation. A summary of the most popular functions is included below.
Constants for Field Calculator
| Function | Description |
|---|---|
E() | Returns Euler's number (e) raised to the power of the specified double value |
PI() | Returns a double representation of pi (the ratio of a circle's circumference to its diameter) |
Time functions for Field Calculator
| Function | Description |
|---|---|
currentTime() | Returns the local server's current system time as an epoch long in milliseconds |
receivedTime() | Returns the timestamp of when the input connector created the event |
Common functions for Field Calculator
| Function | Description |
|---|---|
abs(value) | Returns the absolute value of the argument |
ceil(double) | Returns the ceiling (next greatest integer) of the specified double value |
currentTime() | Returns the system's current date/time as a long integer (epoch milliseconds) |
floor(double) | Returns the floor (next smallest integer) of the specified double value |
hypot(double x, double y) | Returns sqrt((x*x) + (y*y)) without intermediate overflow or underflow |
max(value a, value b) | Returns the greater of two specified argument values |
min(value a, value b) | Returns the smaller of two specified argument values |
random() | Returns a double value greater than or equal to 0.0 and less than 1.0 |
receivedTime() | Retrieves the date/time the event record was received by an input connector as a long integer (epoch milliseconds) |
round(value) | Returns the closest long to the argument (given a double value) |
Note:
A Field Calculator Processor will handle numeric type conversions when possible. For example, when providing different numeric types to a function (for example, max(3.14159,25)), the values will be cast to comparable types (int, float, or double) in order to perform the comparison. The processor will also cast calculated values when writing the values to an event's attribute field (for example, 6 + 7 will be written as 13.0 when the event's field is of type double).
Exponentials for Field Calculator
| Function | Description |
|---|---|
sqrt(double) | Returns the square root of the specified double value |
cbrt(value) | Returns the cube root of the specified double value |
exp(double) | Returns Euler's number (e) raised to the power of the specified double value |
pow(double, double) | Returns the value of the first argument raised to the power of the second argument |
Logarithms for Field Calculator
| Function | Description |
|---|---|
log(double) | Returns the natural logarithm (base e) of a double value |
log10(double) | Returns the base 10 logarithm of a double value |
String functions for Field Calculator
| Function | Description |
|---|---|
For reference, the syntax examples below use the following example input data:
| |
length(string) Returns: integer Example: 'TrackID length: ' + length(TrackID) | Returns the length of the string. Result: "TrackID length: 7" |
isEmpty(string) Returns: boolean Example: isEmpty(EmptyStr) | Returns true if the source's length is 0. Result: true |
isNull(string) Returns: boolean Example: isNull(NullValue) | Returns true if the source value is null. Result: true |
equals(string, string) Returns: boolean Example: equals(TrackID, EmptyStr) | Returns true if the given string attribute values are equivalent. Returns false if the strings are not equivalent; null if values other than strings are provided as input. Result: false |
equalsIgnoreCase(string, string) Returns: boolean Example: equalsIgnoreCase(TrackID, toString(TrackNum)) | Compares the source string to another string, ignoring differences in case. Returns true if the given string attribute values are equivalent, ignoring case. Returns false if the strings are not equivalent; null if values other than strings are provided as input. Result: false Explanation: "AA-1234" does not equal "1234" |
compareTo(string, string) Returns: integer Example: compareTo('ABCXD', 'ABCZD') | Compares two strings lexicographically. Returns the value 0 if the two strings are equal; a negative value if the first string is lexicographically less than the second string; and a value greater than zero if the first string is lexicographically greater than the second. Result: -2 Explanation: int('X') - int('Z') returns -2 because 'X' is two positions less than 'Z' in the alphabet. |
compareToIgnoreCase(string, string) Returns: integer Example: compareToIgnoreCase('abcde', 'ABCDE') | Compares two strings lexicographically, ignoring differences in case. Result: 0 |
startsWith(string, value) Returns: boolean Example: startsWith('Programming', 'Program') | Returns true if the string begins with the specified prefix; false otherwise. The second argument, value, can be a string or integer value which can be implicitly cast to a string. Result: true |
endsWith(string, value) Returns: boolean Example: endsWith(TrackID, TrackNum) | Returns true if the string ends with the specified suffix; false otherwise. The second argument, value, can be a string or integer value which can be implicitly cast to a string. Result: true Explanation: "AA-1234" ends with "1234" |
indexOf(string, string, startIndex) Returns: integer Example: indexOf('ABCDABCBCA','BC',3) | Returns the first index at which a specified substring is found within a search string, starting at the specified index. For index searching from the beginning of the string, specify the search start at index 0. Result: 5 Explanation: Starting with the character at position 3, the first occurrence of the substring 'BC' is at position 5. |
lastIndexOf(string, string, startIndex) Returns: integer Example: lastIndexOf(TrackID,'-',length(TrackID)) | Returns the last index at which a specified substring is found within a search string, searching backward from the specified index. For index searching from the end of the string, specify the search start at length(source). Result: 2 Explanation: Starting at the end of the string and searching backward, the substring '-' was found as position 2. |
substring(string, intIndexBegin, intIndexEnd) Returns: string Example: substring('ABCDABCXYZABCDABC',7,10) | Returns a substring extracted from the source string as a new string. The substring begins at the specified intIndexBegin and extends to the character at index (intIndexEnd - 1). The third parameter can be expressed as a nested function length(source) or -1 which implies end of string. Result: "XYZ" Explanation: Starting at character index 7 ('X') return the three characters at positions 7, 8, and 9. |
concat(string, string) Returns: string Example: concat('Hello', 'World') | Concatenates the specified string to the end of the source string. Result: "HelloWorld" |
matches(string, regex) Returns: boolean Example: matches(TrackID, '[A-Z]+[-][0-9]+') | Returns true if the specified regular expression pattern matches the specified string; false otherwise. The regular expression pattern, regex, is specified as a second string. Result: true Explanation: The whole of the TrackID matches the pattern "one or more letters, followed by a dash, followed by one or more numeric digits. |
contains(string, substring) Returns: boolean Example: contains(TrackID,'-') | Returns true if the source string contains the specified substring. Result: true Explanation: The TrackID contains a literal '-' somewhere within the string value. |
replaceFirst(string, regex, replacement) Returns: string Example: replaceFirst(TrackID,'[0-9]+','nnnnn') | Replaces the first substring matching the specified regular expression pattern with the specified replacement string, returning the result as a new string. Result: "AA-nnnnn" Explanation: The first substring matching the pattern "one or more numeric digits" was replaced with a literal string 'nnnnn'. |
replaceAll(string, regex, replacement) Returns: string Example: replaceAll('ABABCABABABD','AB','X') | Replaces each substring of the source string that matches the given regular expression with the given replacement. Returns the resulting string as a new string. Result: "XXCXXXD" Explanation: Every occurrence of the regular expression pattern 'AB' has been replaced with a literal string 'X'. |
replace(source, target, replacement) Returns: string Example: replace('2017-12-31','-','/') | Replaces each occurrence of a substring, specified as a literal string, with a specified replacement substring (also specified as a literal string). Returns the resulting string as a new string. Result: " 2017/12/31" |
toLowerCase(string) Returns: string Example: TrackID + ' to lower: ' + toLowerCase(TrackID) | Converts all of the characters in the source string to lowercase using the rules of the default locale. Returns the string, converted to lowercase, as a new string. Result: "AA-1234 to lower: aa-1234" |
toUpperCase(string) Returns: string Example: TrackID + ' to upper: ' + toUpperCase(TrackID) | Converts all of the characters in the source string to uppercase using the rules of the default locale. Returns the string, converted to uppercase. Result: "AA-1234 to upper: AA-1234" Explanation: In this example, the TrackID alphabetic portion was already upper-case. |
trim(string) Returns: string Example: | Returns a copy of the source string, with leading and trailing white space omitted. Result: "GeoEvent Server" |
toString(fieldName) Returns: string Example: toString(Geometry) Example: toString(Epoch) | Returns a string representation of the value in the specified event attribute field. These functions are intended to allow string representations of Date and Geometry attributes to be nested within other functions such as substring, so that real-time analytics can extract information from the higher-order data type. Result: "{""x"":32.125,""y"":-117.125,""spatialReference"":{""wkid"":4326}}" Result: "Fri Nov 03 17:07:56 PDT 2017" |
valueOf(fieldName) Returns: string Example: valueOf(Geometry) Example: valueOf(Epoch) | Returns a string representation of the value in the specified event attribute field. The toString( ) and valueOf( ) functions wrap identical implementations. |
Trigonometric functions for Field Calculator
| Function | Description |
|---|---|
acos(double) | Returns the arc cosine of a value (returned angle is between 0.0 and pi) |
asin(double) | Returns the arc sine of a value (returned angle is between -pi/2 and pi/2) |
atan(double) | Returns the arc tangent of a value (returned angle is between -pi/2 and pi/2) |
atan2(double y, double x) | Returns the angle theta from the conversion of rectangular coordinates (x, y) to polar coordinates (r, theta) |
cos(double) | Returns the trigonometric cosine of an angle |
cosh(double) | Returns the hyperbolic cosine of a double value |
sin(double) | Returns the trigonometric sine of an angle |
sinh(double) | Returns the hyperbolic sine of a double value |
tan(double) | Returns the trigonometric tangent of an angle |
tanh(double) | Returns the hyperbolic tangent of a double value |
toDegrees(double) | Converts an angle measured in radians to an approximate angle in degrees |
toRadians(double) | Converts an angle measured in degrees to an approximate angle in radians |