Chapter 13. Column Formulas

13.1. Introduction to Formulas

Cartographica supports formulas in columns, providing a way to compute new values from existing columns or from geometric attributes of the feature for which the column is being calculated. The formula interpreter uses an embedded python interpreter with the basic functions plus the math library imported at the base. This chapter lists the functions and constants available.

Cartographica uses an embedded Python 3 interpreter to interpret the formulas, or "expressions" that make up the Column Formulas. This provides an enormous amount of flexibility in these expressions and although we detail the key aspects of python expressions, basically anything that can be executed as a python expression is fair game in a Column Formula in Cartographica.

The simplest expression is just a replacement value. Although it's not the best use of processing power, it is an effective way to put a constant value in a column as a placeholder.

The most common use of Column Formulas is to create a new value based on the values of another column or columns in Cartographica. To create these simple relationships, use standard arithmetic operators (below) to combine fields.

Example 13.1. Arithmetic calculation

For example, a population column and an area column can be combined to determine density.

POPULATION / AREA

Example 13.2. Extract sub-string

Another use of Column Formulas is to create new columns by splitting data that has been encoded into a string. For example: a column named CITYSTATE containing Washington, DC may be appropriate for an address line, but may not be appropriate for your needs, so you could use .

CITYSTATE.split(',')[0].strip()

to grab the city and

CITYSTATE.split(',')[1].strip()

to grab the state from the CITYSTATE column. Breaking this down:

the split method splits a string into an array of sub-strings that were previously separated by the character in quotes (,)
that array is dereferenced to it's 2nd element (python's understanding of the world is based on the 0 being the first position)
that string has the strip method run against it, which removes spaces from the start and end of the string

Thus, you get the state.

Example 13.3. Extract string by position

Or if you had a column BLOCKGROUP containing a block group identifier 210670001001 and wanted to pull out the FIPS State and county code from it, you could use:

BLOCKGROUP[0:5]

to grab the city and

CITYSTATE.split(',')[1].strip()

(pulling the first 5 characters, starting at position 0 from the text string).

Thus, you get the state.

	Version 1.5 Change
Versions of Cartographica prior to Version 1.5 used the built-in Python 2.7 interpreter on macOS to interpret functions. In versions, 1.5 and later, a version 3.8+ interpreter is used. For most uses in Cartographica, this does not change the behavior, however string functions are now mostly methods on string objects (as shown in the Example 13.2, “Extract sub-string” example above), and thus must be called on the text string objects, as opposed to called directly. Cartographica will attempt to warn you when you have an old-form function in use, so that you may change it to the new method-based invocation. Fortunately, the 1.5 methods are compatible with 1.4.x, so you can safely change the formulas and save them for MapSets that are in use by both.

Version 1.5 Change

Versions of Cartographica prior to Version 1.5 used the built-in Python 2.7 interpreter on macOS to interpret functions. In versions, 1.5 and later, a version 3.8+ interpreter is used. For most uses in Cartographica, this does not change the behavior, however string functions are now mostly methods on string objects (as shown in the Example 13.2, “Extract sub-string” example above), and thus must be called on the text string objects, as opposed to called directly.

Cartographica will attempt to warn you when you have an old-form function in use, so that you may change it to the new method-based invocation.

Fortunately, the 1.5 methods are compatible with 1.4.x, so you can safely change the formulas and save them for MapSets that are in use by both.

Creating a Column Formula

Select the layer in which you want to create a formula in the layer stack
Choose Window > Show Layer Info to bring up the Layer Info window.
To create a new column in the layer, click on the + button or choose, Layer > Add Column
Click on the new column name and change its name to something meaningful
Click the Set Formula… button to bring up the formula sheet
Figure 13.1. Formula sheet
Drag a column name from the bottom pane to add a reference to another column to this formula. Colors of the column names denote the type of data.
Now add the rest of the formula by typing in the formula area and dragging other column references to it.
When you are done, click OK to set the formula

While you enter the formula, Cartographica is constantly evaluating it looking for potential problems and providing you guidance in how to solve those problems. Unfortunately, there are some errors for which Cartographica cannot determine the problem and can only tell you that the result would be invalid.

For other cases, consult the Expression Warnings table for meanings and suggested solutions to problems detected by Cartographica.

Table 13.1. Expression Warnings

Warning Message	Meaning
No result value for test data	This expression results in no value. Generally this is the result of calling a function that doesn't return a value
Math error-some fields may be wrong/missing	This expression evaluates to an arithmetic error using the test data. This warning indicates that under some circumstances (possibly all), field values will not be set. A common cause of this is an expression that results in infinity or attempts to divide by zero using the test data.
Syntax error in formula	This expression is invalid. This is often displayed during typing due to issues such as missing parenthesis, missing operators and other errors.
Interpreter exception	This expression is invalid in a way that we cannot determine.
Geometries can't be stored in columns	The result of the expression will be a geometry. The most common cause of this is a missing final expression when evaluating a centroid or other geometric value.
Unknown type	The result of this expression will be an unknown type. Because of this, no data will be stored in the resultant column. Check that any functions used in the expression don't return complex types, such as geometries
Unknown attribute	The result of this expression references an attribute (using dot notation) that is not known to the interpreter. Check the spelling of anything after a dot.
Unknown symbol:	The referenced symbol is unknown to the interpreter. This is generally caused either by putting two column references next to each other without an operator or by a typo
Wrong geometry type	A function in this expression that works on a geometry is being used with the wrong type of geometry. For example: you are trying to take the area of a point.
Other geometry error	The expression uses some geometry function incorrectly, resulting in confusion for the geometry processor
Can't initialize interpreter	Internal error, please report to ClueTrust


Cartographica does not resolve circular references or directional references. Each column is executed in column order when they are loaded and are updated when they are changed. As such, if you have a Column Formula that is dependent on another Column Formula, you may experience unexpected results. To refresh an individual value, just set the formula again.

Cartographica does not resolve circular references or directional references. Each column is executed in column order when they are loaded and are updated when they are changed. As such, if you have a Column Formula that is dependent on another Column Formula, you may experience unexpected results.

To refresh an individual value, just set the formula again.