Summary
The Field Calculator Processor can be used to compute a value. The processor evaluates an expression to produce the necessary 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.
Examples
- The Field Calculator Processor can be used to add or subtract time from a date/time value to align the timestamp with a local time zone. This can be done by adding or subtracting an epoch value in milliseconds (as a long integer) from the event record’s timestamp expressed as epoch milliseconds (also as a long integer). The modified epoch timestamp value can then be populated in a new date type field.
- The processor can convert a multitude of units such as distance, speed, time, temperature, height, weight, and more.
- The processor can be used for real-time data rectification from workers in the field. String functions such as replace(), toLowerCase(), toUpperCase(), and concat() can be used to ensure data coming from the field meets a certain criteria or specification in real-time. For example, concat() can be used to remove spaces from collected data. Likewise, toLowerCase() can be used to enforce lower case letters across all collected data. The output of these string functions can then be written back to the feature service layer in real-time.
- The processor can be used to recursively search for keywords or phrases from a comments field (string) in a ArcGIS Survey123 feature layer. By using the string function contains(), user submitted comments can be reviewed for road condition content such as pothole, debris, flooding, cracking, or blocked. If this type of content exists, the field calculation will return a Boolean true value. The true event record can then be filtered and processed in real-time to immediately notify public works or some other authority to respond.
Usage notes
- 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 event record. The processor also supports several 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().
- The processor can be configured to store the output calculation in an existing field or a new field using the Target Field parameter. Altering an event record's schema by adding a new field requires GeoEvent Server to create a new GeoEvent Definition. The new GeoEvent Definition will be managed by GeoEvent Server and deleted if changes are made to the processor or the GeoEvent Service in which the processor is used.
- When specifying an existing field to write the calculated value(s) to, it is not required a GeoEvent Definition be specified first. Choosing a GeoEvent Definition from the Definition menu only serves to narrow the list of available fields to choose from in the Field menu.
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 Processor
| 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) |
Conversion functions for Field Calculator Processor
| Function | Description |
|---|---|
toBoolean(value) | Returns the provided value as a boolean. The value passed in is converted to a string, cast to lower case, trimmed to remove white space at the ends and then evaluated. Any string value that matches one of the following will return boolean true: 'true', 'y', 'yes', 'on', or '1'. Any string value that matches one of the following will return boolean false: 'false', 'n', 'no', 'off', '0' or '-1'. All other string values will return a null value. A null or empty string will return a null value. |
toDate(value) | Returns the provided value as an epoch long integer value in milliseconds. The provided value can be either a date, long, or string. Date and long values are returned unchanged as epic long integer values in milliseconds. String values are evaluated against the following date formats formats: ‘yyyy-MM-dd'T'HH:mm:ss', 'EEE MMM dd HH:mm:ss zzz yyyy', 'MM/dd/yy hh:mm:ss aa', 'MM/dd/yy HH:mm:ss', and 'MM/dd/yy HH:mm'. If the string matches one of these formats, it is converted to a date and returned as an epoch long integer value in milliseconds. All other values return null. |
toShort(value) | Returns the provided value as a signed short 16-bit integer value in the range of -32,768 to 32,767. If the value cannot be converted to a numeric value within the range, the function returns null. |
toInteger(value) | Returns the provided value as a signed 32-bit integer value in the range of −2,147,483,648 to 2,147,483,647. If the value cannot be converted to a numeric value within the range, the function returns null. |
toLong(value) | Returns the provided value as a signed long 64-bit integer value in the range of −9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. If the value cannot be converted to a numeric value within the range, the function returns null. |
toFloat(value) | Returns the provided value as a signed 32-bit float value. If the value cannot be converted to a numeric value, the function returns null. |
toDouble(value) | Returns the provided value as a signed 64-bit double value. If the value cannot be converted to a numeric value, the function returns null. |
Time functions for Field Calculator Processor
| Function | Description |
|---|---|
currentTime() | Returns the current time as an epoch long integer value in milliseconds |
receivedTime() | Returns the timestamp of when the input connector created the event |
currentOffsetUTC() | Returns the difference between the local server's current date/time and UTC |
Geometry functions for Field Calculator Processor
| Function | Description |
|---|---|
toPoint(X, Y, SpatialRef) | Takes the arguments (X - double, Y - double, spatial reference WKID - integer) and returns a point geometry with X, Y coordinates in the provided horizontal spatial reference. The spatial reference must be a well-known ID (numeric). Incoming argument values will be cast to the correct type if possible (on a failure, the value will be null). If any of the incoming arguments are null or cannot be coerced into the correct type, this function returns null. |
toPoint3D(X, Y, Z, SpatialRef) | Takes the arguments (X - double, Y - double, Z - double, spatial reference WKID - integer) and returns a 3D point geometry with X, Y, Z coordinates in the provided horizontal spatial reference. The spatial reference must be a well-known ID (numeric). Incoming argument values will be cast to the correct type if possible (on a failure, the value will be null). The Z argument can be null; if it is null, a 2D point geometry with X, Y coordinates in the provided horizontal spatial reference will be returned. If any argument other than the Z is null or cannot be coerced into the correct type, this function returns null. |
Common functions for Field Calculator Processor
| Function | Description |
|---|---|
abs(value) | Returns the absolute value of the argument |
ceil(double) | Returns the ceiling (next greatest integer) of the specified double value |
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 |
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 Processor
| 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 Processor
| 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 Processor
| 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 Processor
| 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 |
Parameters
| Parameter | Description |
|---|---|
| Name | A descriptive name for the processor used for reference in GeoEvent Manager. |
| Processor | The name of the selected processor. |
| Expression | The expression to be evaluated by the processor against each incoming event record. Expressions can be mathematical or lexical. Possible inputs for this parameter include:
For a complete list of supported operators and functions that can be used as part of an expression with the processor, see the supported operators and functions above. Note:An example of a simple expression is Altitude + 100. The expression is written to evaluate the value of a field called Altitude to add 100 to it. If the Altitude field from an event record had a stored value of 12500, the expression would result in a new value of 12600 (12500 + 100 = 12600). This can be stored in a string or integer type field. If a similar expression is specified as ‘Altitude’ + 100, the expression is written to take the literal string, Altitude, and add 100 to it. If the new field storing the outcome of the expression is a string, the output will be Altitude100 since string concatenation is supported. If the new field storing the output of the expression is of an integer data type however, the expression is logically invalid and will not result in a value. If the expression uses a string function such as length(Altitude), the expression is written to count the number of total characters in the string from the Altitude field. If altitude is expressed as an integer data type (for example, 12500) the above expression is logically invalid and will not result in a value. The reason being, length() is a string expression that is expecting a string value in the parenthesis; not an integer. However, if altitude is expressed as a string, the above expression would return a value of 5, since the string 12500 contains 5 characters). |
| Target Field | Specifies the target field for the value(s) calculated by the processor. The target field is where the result of the expression will be written to. The default is Existing Field.
|
| New Field Name (Conditional) | The name of the new field where the value(s) calculated by the processor will be written to. Property is shown when Target Field is set to New Field and is hidden when set to Existing Field. |
| New Field Type (Conditional) | Specifies the data type of the new field created by the processor. The default is Boolean. Available data types include:
Property is shown when Target Field is set to New Field and is hidden when set to Existing Field. |
| New Field Tag (Conditional) | The name of an existing field tag that will be applied to the new field. There is no default for this parameter. Note:The tag must already exist for the new field to be tagged. The processor does not dynamically create tags. For more information on managing and creating tags, see Managing tags. Property is shown when Target Field is set to New Field and is hidden when set to Existing Field. |
| New GeoEvent Definition Name (Conditional) | The name assigned to the new GeoEvent Definition. The new GeoEvent Definition will combine the schema of the inbound event record with the new field used for storing the calculated value(s). Property is shown when Target Field is set to New Field and is hidden when set to Existing Field. |
| Existing Field Name | The name of an existing field where the value(s) calculated by the processor will be written to. There is no default value for this parameter. Note:Use the Definition menu to identify the GeoEvent Definition of the inbound event records. Choosing a GeoEvent Definition will narrow the scope of available fields to choose from. Use the Field menu to identify the specific name of the existing field where the value(s) calculated by the processor will be written to. |
Considerations and limitations
- Below are several considerations when using the Field Calculator Processor:
- Expressions can be mathematical or lexical (based on strings, rather than numerical values).
- When working with literal strings, always 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 an event record's schema, which requires GeoEvent Server to create a new GeoEvent Definition. When using the processor to create a new field, 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.
- When processing or filtering is included as part of any GeoEvent Service, the total throughput of event records will decrease due to compute overhead. Consider this when designing a GeoEvent Service that implements the Field Calculator Processor, or any other processor for that matter. To increase the performance of a GeoEvent Service or of an overall GeoEvent Server site, consider preprocessing or filtering the real-time data prior when possible.
- Consider the data types of the field or tagged field when constructing an expression in the Field Calculator Processor. Sometimes expressions are logically invalid due to the data type of the field and will not return a value. Other times expressions are logically valid but can produce an unexpected result due to the data types involved. For example, attempting to calculate the mathematical sum of two numbers expressed as strings to store in a new field with a double data type is not valid. The same expression is valid however if the new field receiving the value is instead a string, but the resulting value may not be expected. Each of the numbers will be concatenated together rather than mathematically added together.