Manifold tables can contain fields defined by expressions that are computed on the fly, called computed fields. Computed fields in Manifold tables are like cells that are computed by formulas in an Excel spreadsheet: if any values used in the calculation change, the computed field cell will automatically change as well. Computed fields can be created in tables stored within a Manifold .map project. We can even create an index on a computed field, so long as the table for the computed field is stored in the .map project (and not in an external database).

Manifold can create *virtual*
computed fields in external databases, such as Oracle, MySQL, SQL Server,
PostgreSQL, DB2, ESRI GDB,
GPKG, etc,
which will appear as computed fields in a Manifold project when that DBMS
is linked as a data source into the project (but will not appear as computed
fields in non-Manifold tools). This would allow having a virtual
computed field with geoms composed on the fly from X and Y values stored
in the database. Virtual computed fields on databases cannot refer
to other computed fields and cannot participate in indexes.

Computed fields are a great way to automatically compute areas, bearings and other characteristics, to add date stamps like the last time the record was modified, to create custom text configurations from other fields, and for many more purposes. Constraints are related to computed fields in that they also are based on expressions computed on the fly.

We add computed fields or constraints to a table using the Schema dialog, or with SQL queries. This topic shows use of the Schema dialog. See the Computed Fields and Constraints topic for an SQL approach to adding computed fields or constraints.

Open the table and choose Edit - Schema,

*OR...*right-click on the table in the Project pane and choose Schema.Press the Add button in the Schema dialog's toolbar and choose Field.

In the Field dialog, provide a Name for the field and choose the data Type for the field.

Press the Edit Expression button, to launch the Expression dialog.

In the Expression dialog's Expression tab, enter an SQL expression that performs the desired computation, using the expression builder to help compose the expression. More sophisticated expressions can use the Expression Context tab as well.

Press OK.

Back in the Field dialog, press OK.

Back in the Schema dialog, press Save Changes.

See the Schema topic for details on controls. The expression builder in the Expression dialog is similar to the Query Builder for SQL expressions in the Command Window. See the Example: Expression Context and Computed Fields topic for step by step examples using the Expression Context tab.

Computed fields can be based on fearsomely intricate expressions, but they often are used with very simple arithmetic that, despite being very simple, provides great usefulness.

We add a computed field to a table that multiplies unit price by units in stock to get the total value of a product in inventory.

We open a Products table based on the nwind example database. For each product the table gives the Name of the product, the Unit Price of the product and the Units In Stock of that product. We would like to add a computed field called Inventory Value that multiplies the Unit Price field by the Units In Stock field to get the total value of that product in inventory.

With the table open, we choose Edit - Schema to launch the Schema dialog.

We press the Add button and choose Field in the resulting menu.

We enter Inventory Value for the name, and we choose int32 for the data type. Using an integer data type for an amount involving money, instead of a floating point number, like float32 or float64, is a simple, albeit less exact, way of ensure the results appear without many digits after the decimal point.

So far, this is the same workflow as for adding any new field to the table. To make the new field a computed field, we must add an expression. To do that, we press the Edit Expression button. That launches the Expression dialog.

The Expression dialog has a pane at the top in which we can write an expression, and a list of templates below that the built-in expression builder uses. We can write an expression freehand, or we can compose the expression using the expression builder templates, or we can write the expression using a mix of freehand and expression builder.

At the top of the expression builder templates is a list of fields in the table. To add a field to the expression, we double-click it in the expression builder list. It appears in the expression pane at the current cursor position. We double-click the Unit Price field to add it to the expression.

It appears surrounded by square bracket [ ] characters, which Manifold uses to disambiguate any names that have spaces or special characters in them. The expression builder always uses square brackets, even when not necessary, for a consistent visual style.

Next, we manually enter a space character, the asterisk * character (the multiplication operator in SQL), and another space character. We can then double-click the Unit Price field to add it to the expression.

That finishes writing the expression we would like to use:

[Unit Price] * [Units In Stock]

We could have written such a short expression free-hand, of course, manually keyboarding the names of the fields instead of double-clicking them in the expression builder. However, getting in the habit of using the expression builder helps to avoid typographical errors, especially with long field names or long function names.

We press OK.

The Field dialog shows the new expression we have created. We press OK.

Back in the Schema dialog, the new Inventory Value computed field appears in provisional blue color, showing the name, type, and the first few characters in the expression. To see the entire expression we can hover the mouse cursor over the expression and the entire expression will be shown in a tool tip.

To create the new computed field in the table, press Save Changes. If we change our mind and decided not to make any changes to the table, we could press Close.

The new Inventory Value computed field appears in the table. It is shown in gray background color used for read-only fields, because it is a read-only field that takes its value from an automatic computation. We cannot double-click into an Inventory Value cell to manually change the value.

The new Inventory Value computed field can be used like any other field. A computed field in a Manifold table is a permanent part of the table. When we close the table and open it again, the computed field is still there. When we use the table in other queries, for example, a JOIN, the computed field can be used just like the other queries.

For example, we can Ctrl-click the column header for Inventory Value to sort by that field in ascending order, and then Ctrl-click on the column header again to sort in descending order. At a glance, we can see which products have the highest inventory value considering the number of units in stock and the price for each.

The Inventory Value field is computed using very simple arithmetic, but having it provides tremendous value in being able to see at a glance where our inventory money is tied up, in a way which is not possible just by looking at Unit Price and Units In Stock numbers separately. Without the Inventory Value field we might not have noticed we have more money tied up in maple syrup than in luxury goods like crab meat or caviar.

In the illustration above we have hovered the mouse cursor above the product with the greatest Inventory Value to see the full name of the product, more characters than can fit into the width allotted for that column.

A Manifold computed field is dynamic. It automatically updates when other fields or other values on which it depends change, just like the value in an Excel spreadsheet cell created by a formula will recalculate when values in other cells in the formula change.

Suppose we double-click into the Units In Stock cell for the Tibetan Barley Beer record, and we change the value from 17 units in stock to 5 units in stock.

Instantly, the Inventory Value for the Tibetan Barley Beer record changes from 323 to 95.

Computed fields in Manifold are related to the idea of *derived
fields*, a feature most DBMS products (including Manifold) provide.
Derived fields are *static*,
like the results computed for a one-time report, while computed fields
in Manifold are *dynamic*, like
the computed cells in an Excel spreadsheet. Derived
fields are also impermanent appearing in a results table and disappearing
when the results table is closed, while computed fields in Manifold tables
are a permanent part of the table, which can be used like any other field
in the table.

A derived field is a field that an SQL query computes on the fly from other fields, typically appearing in the results table of the query. Consider the query:

SELECT [Name], [Unit Price] * [Units In Stock] AS [Inventory Value]

FROM [Products];

The above query creates a results table where the Inventory
Value field in the results table is a *derived
field*, computed on the fly by multiplying the values in the Unit Price field with the Units In Stock field. However,
the results table is a virtual table that has no permanent existence.
It appears as a report of the results of the query and it disappears when
we close it. If we wanted to save the results as a table, we would
have to create a new table using SELECT ... INTO, as in:

SELECT [Name], [Unit Price] * [Units In Stock] AS [Inventory Value]

INTO [Valuation]

FROM [Products];

The above query creates a new table called Valuation and fills it with the results of the query, the Name field and the computed Inventory Value derived field for each record.

The Valuation table is permanent and won't disappear when we close it, but the Valuation table is just a static snapshot of inventory value at the moment the table was created. If we changed the Unit Price for some items and changed the Units In Stock quantities, the Valuation table would not automatically be updated. It is not like an Excel spreadsheet, where cells created by formulas update automatically. Instead, it is just a report that was created on a one-time basis.

In contrast, a computed field in a Manifold table is a permanent part of the table. When we close the table and open it again, the computed field is still there. When we use the table in other queries, for example, a JOIN, the computed field can be used just like the other queries.

The Manifold computed field is also dynamic. It automatically updates when other fields or other values on which it depends change.

Suppose we double-click into the Units In Stock cell for the Tibetan Barley Beer record, and we change the value from 17 units in stock to 5 units in stock.

Instantly, the Inventory Value for the Tibetan Barley Beer record changes from 323 to 95.

We might think of computed fields in terms of numerical calculations, but they can be the result of any expression, for example, concatenating text. We will take this example at a faster pace, skipping a few illustrations for steps covered in the prior example.

Consider the Employees table above, also based on the nwind sample database. It has two text fields called Last Name and First Name. We would like to concatenate (combine) those in a single field called Name.

We launch the Schema dialog by choosing Edit - Schema. We press the Add button and choose Field.

In the Field dialog we enter Name as the name of the new field. We choose nvarchar, the Unicode text type, for the Type. Many Manifold users always choose nvarchar instead of varchar for text fields, to ensure that no matter how they choose to use their tables, they are always ready for Unicode, that is, international character sets. Manifold automatically converts between nvarchar and varchar when exporting to formats that cannot handle nvarchar.

The expression we enter is

[First Name] & ' ' & [Last Name]

That is the First Name field, concatenated with a string consisting of a space character (written between single ' quote characters), concatenated with the Last Name field.

We press OK. and then back in the Field dialog we press OK again.

We press Save Changes.

The new computed field appears in the table. We now have a field in the table that automatically concatenates last name and first name to provide a full name, which we can use in other expressions, to create labels, and so on.

We can take advantage of Manifold's many SQL functions that can operate on text.

For example, in the Restuarants table above, similar to that used in the Example: Street Address Geocoding topic, we have an address text field that provides the complete address for an In-N-Out restaurant. We would like to create a computed field called street that provides only the street portion of the address, without the town, state and zip code.

We launch the Schema dialog, perhaps by right-clicking the Restaurants table in the Project pane and choosing Schema. We press the Add button and choose Field.

In the Field dialog we enter street as the Name of the new field, we choose nvarchar as the Type, and we press the Edit Expression button.

In the Expression dialog we enter the expression:

StringRegexpReplace([address], ',.*', '', 'c')

... using the StringRegexpReplace function. How we learned to use this function is discussed later in this topic.

We press OK. Back in the Field dialog we press OK again.

Back in the Schema dialog, we press Save Changes.

The new street computed field appears in the table, neatly providing just the street portion of the address. We can now create labels from this street field, which we can label points on a map for each restaurant, giving the address in shorter form than the full address.

How did we learn to use the StringRegexpReplace function? We could read through functions in the SQL Functions topic, or we can see what Manifold does when it SQL functions in Transform templates.

We start with the table above. We can get the result we want by starting with the full address and then eliminating everything that comes after the first comma. To figure out how to do that, we switch to the Transform pane in the Contents pane. We can use the preview Manifold gives us to try different transform templates to find the one that does what we want.

We choose the address field as the target. After some tinkering, we zero in on using the Replace Regexp, All template using the regular expression ,.* as a Pattern, that is, a comma , character followed by a dot . character followed by an asterisk * character. Refreshing our regular expression knowledge by reading the Regular Expressions topic, we know that the .* sequence matches any group of one or more characters, so the ,.* sequence matches all of the characters that come after the first comma, including the comma. The Replace with box is empty, so the result of the above settings is what the preview shows:

The first comma and everything after it is replaced with nothing, that is, deleted. The great thing about previews is that if we are not sure how regular expressions work, we can try different things to see what they do until we get it right. This only a preview, with no changes made to our data if we do not press the Update Field button.

We will not do that, but instead we will press the Edit Query button in the Transform pane to see the SQL that Manifold users to make that change to the address field. Manifold opens a Command Window loaded with the following query:

-- $manifold$

--

-- Auto-generated

-- Transform - Replace Regexp, All - Update Field

--

PRAGMA ('progress.percentnext' = '100');

UPDATE (

SELECT [mfd_id],

[address],

StringRegexpReplace([address], ',.*', '', 'c') AS [n_address]

FROM [Restaurants]

THREADS SystemCpuCount()

) SET [address] = [n_address];

All we care about is the central expression that generates what the rest of the query uses to update the address field:

StringRegexpReplace([address], ',.*', '', 'c') AS [n_address]

That is the expression we want to use for our computed field. With those arguments, the StringRegexpReplace function takes whatever text is in the address field and it returns a string that is just those characters up to, but not including, the first comma , character. That is exactly what we want to put into our new street computed field.

By "creating geometry" we mean creating geoms for objects. A classic example is creating points at the centers of area objects.

We begin with a drawing of Mexico showing provinces as areas.

We open the table: we would like to add a computed field of type geom that contains the center point for each area.

We launch the Schema dialog by choosing Edit - Schema. We press the Add button and choose Field.

We enter the name CenterGeom and choose geom as the type. We press Edit Expression.

Suppose we cannot quite remember the name of the function we would like
to use? We enter center
in the filter box, to see only those functions with *center*
in their names. We double-click
onto GeomCenterInner to add that
function to the expression. We manually enter [Geom]
as the first argument and 0 for
the second argument, knowing that a tolerance of zero means automatic
tolerance.

However, we see from the templates list that the function does not return a geom but an x2 coordinate pair value. We must use a different function to turn that into a point geom. We may dimly remember that is how it is done from reading other topics in this documentation, but if we do not remember the name of the function we can again use the filter box to help jog our memories.

We position the cursor above the expression in progress, and enter point into the filter box. Looking at the list, we double-click onto the GeomMakePoint function, recognizing that function as the one we want to use to convert an x2 value into a point geom.

Double-clicking the template adds it to the expression. We can now use a few quick highlight and delete moves to edit the text, so that the GeomCenterInner expression we created earlier replaces <valuex2> within the parentheses of the GeomMakePoint function.

Using simple cut or copy and paste moves, or other simple editing moves based on what we insert using the expression builder is much faster than manually keyboarding. Our final expression is:

GeomMakePoint(GeomCenterInner([Geom], 0))

We press OK, and then back in the Field dialog we press OK as well.

In the Schema dialog we press Save Changes.

The new CenterGeom computed field appears in the table, filled with the point geom values it has computed for each record from the area geom values.

To create a drawing using the CenterGeom field, we right-click onto Mexico Table in the Project pane and we choose Create - New Drawing.

We specify the name Mexico Centers for the new drawing, and we choose the CenterGeom field for geometry. Note that a computed field can be used like any other in such dialogs.

We press the coordinate picker button and specify Latitude / Longitude projection, which is what we know the area geoms in this drawing use. The point geoms we compute from those area geoms use the same coordinate system.

We press Create Drawing.

We can now drag and drop the new drawing into the map, and Style it using stars for the center points.

We may have many uses for centroid points automatically created from areas. For example, we may want two different versions of a drawing that shows real estate parcels in various locations: a version using areas when zoomed into a parcel and a version using the center point to show on a larger scale map that shows the locations of various parcels with a point icon.

Manifold System Release 8 provides a built-in collection of fields automatically
calculated by the system, called *intrinsic
fields*. Intrinsic fields are computed on the fly to report
values of interest, such as the length of lines or the area of area objects.
They are similar to how computed fields work in Release 9,
except that Release 8 intrinsic fields are hard-coded, that is, built
into every table. With Release 9, we can add only those computed
fields we want, and we can easily customize them.

The following expressions provide examples of Release 9 computed field expressions similar to Release 8 intrinsic fields.

Branches |
GeomBranchCount([Geom])
Returns the number of branches in the object. |

Coordinates |
GeomCoordCount([Geom])
Returns
the number of coordinates in the object. A triangular area
contains |

Type |
CASE WHEN GeomIsArea([Geom]) THEN 'area' ELSE (CASE WHEN GeomIsLine([Geom]) THEN 'line' ELSE 'point' END) END
Returns the object type - area, line or point. |

X or Longitude |
VectorValue(GeomCenter([Geom], 0) ,0)
X coordinate of the center of the object, in units of the coordinate system. For Latitude / Longitude systems, this is the Longitude. |

Y or Latitude |
VectorValue(GeomCenter([Geom], 0) ,1)
Y coordinate of the center of the object, in units of the coordinate system. For Latitude / Longitude systems, this is the Latitude. |

Centroid |
GeomMakePoint(GeomCenter([Geom], 0))
A Release 9 feature not available in 8: In a drawing's table we can create a computed field of type geom that is powered by the above expression, which creates a point geom at the centroid of the object in the Geom field. If we have a table with an area in each record, using the Schema dialog we create another field, perhaps called Centroid, of type geom and we enter the above expression. Automatically, as areas or other objects are added, deleted, or edited, the Centroid field will have a geom that gives the point location of the centroid of each object. |

The expressions below work for drawings in any coordinate system, providing results in whatever units are used by the coordinate system. The functions use compute Euclidean measurements, that is, measurements on the Euclidean plane. For accuracy, they should be used with coordinate systems that provide good measurement accuracy in the area of interest. For a step by step example of use, see the Example: Add a Computed Field to a Table topic.

Area |
GeomArea([Geom], 0)
Returns the Euclidean area of an area object in square units of measure used by the coordinate system. Returns NULL for lines and points. |

Bearing |
GeomBearing([Geom])
Returns the Euclidean bearing in degrees of a line object. Returns NULL for areas and points. The line must have a single branch. The bearing is computed from the first to last coordinate of the line. |

Branches |
GeomBranchCount([Geom])
Returns the number of branches in the object. |

Coordinates |
GeomCoordCount([Geom])
Returns
the number of coordinates in the object. A triangular area
contains |

Length |
GeomLength([Geom],0)
Returns the Euclidean length of lines and areas in units of the coordinate system. NULL is returned for points. The length of an area object is the length of its boundary, that is, the perimeter of the area. The length reported for a branched object is the sum of the lengths of the branches. |

Type |
CASE WHEN GeomIsArea([Geom]) THEN 'area' ELSE (CASE WHEN GeomIsLine([Geom]) THEN 'line' ELSE 'point' END) END
Returns the object type - area, line or point. |

X |
VectorValue(GeomCenter([Geom], 0) ,0)
X coordinate of the center of the object, in units of the coordinate system. For Latitude / Longitude systems, this is the Longitude. |

Y |
VectorValue(GeomCenter([Geom], 0) ,1)
Y coordinate of the center of the object, in units of the coordinate system. For Latitude / Longitude systems, this is the Latitude. |

We can create geodetic (computed over the ellipsoid) computed fields for any coordinate system, but when drawings are in the default Latitude / Longitude coordinate system we can utilize especially simple, low level expressions. These can be copied and pasted with no need to customize the expression for the component in use. The following expressions work with drawings in Latitude / Longitude coordinate system, using the WGS84 base, for any area of interest.

Geodetic Area |
GeomAreaGeo([Geom], 6378137.01, 0.08181919084262149, 0)
Returns the geodetic area of an area object in square meters. Returns NULL for lines and points. |

Geodetic Bearing |
GeomBearingGeo([Geom], 6378137.01, 0.08181919084262149, 0)
Returns the geodetic bearing in degrees of a line object. Returns NULL for areas and points. The line must have a single branch. The bearing is computed from the first to last coordinate of the line. |

Geodetic Length |
GeomLengthGeo([Geom], 6378137.01, 0.08181919084262149, 0)
Returns the geodetic length of lines and areas in meters. NULL is returned for points. The length of an area object is the length of its boundary, that is, the perimeter of the area. The length reported for a branched object is the sum of the lengths of the branches. |

Euclidean vs. Geodetic - Projections (another name for coordinate systems, as used in GIS) work by representing the spherical or ellipsoidal shape of the Earth as a flat, Euclidean plane. See the About Projections collection of topics in this documentation.

One reason projections were invented was to simplify the complicated calculations required to compute measurements on the 3D surface of a sphere, with even more difficult calculations required for measurements on the surface of a slightly flattened ellipsoid, a closer approximation to the shape of the Earth than a sphere.

A well-chosen projection does all the calculations up front to create a map that is a flat, Euclidean plane, on which measurements are then easy using simple calculations. Most classic GIS is oriented to such calculations, where part of the art for making accurate measurements is choosing a projection for the area of interest, the region in which we work, that provides good accuracy despite the simplification of making computations on a flat plane.

But that only goes so far, as no projection to a flat plane provides
good accuracy over larger distances where 3D effects of the Earth's ellipsoid
shape play a greater role. Measurements over the 3D shape of the
Earth's ellipsoid are called *geodetic*
measurements, with a full range of SQL functions provided within Manifold
for making such measurements. To make such measurements
using Manifold SQL functions, we normally have to provide arguments that
capture whatever coordinate system we are using, so Manifold can make
necessary conversions.

In the case of default Latitude / Longitude, we can take advantage of low level functions that require Latitude / Longitude in the drawing and only need specification of whatever Base ellipsoid is used. In the case of the WGS84 base used for Manifold's default Latitude / Longitude projection, the numbers used in the expressions above can be plugged in.

If we would like to build computed fields that perform geodetic measurements in drawings using coordinate systems other than Latitude / Longitude, we can use the technique illustrated in the Example: Expression Context and Computed Fields topic, where we first build a measure object using the CoordMeasureMake function and then we use that measure object with CoordMeasure... functions like CoordMeasureArea. That technique, however, requires specifying the name of the drawing used (so the coordinate system in use can be grabbed) and thus it is not a totally cut and paste proposition like the expressions shown in this topic.

Computed Fields and Constraints - An SQL approach to computed fields.

Example: Add a Computed Field to a Table - In this example we add a field to a table. We first set the values for a field dynamically with a computed field using the Add Computed Field option in the New Field dialog. We then illustrate what happens when we fill a field statically with values using the Add Field option. Last, we show what the Add Component choice does in the New Field dialog.

Example: Add a Second Computed Geom Field to a Table - We can create tables with more than one geom field in the table and then we can create drawings which use those additional geom fields. This topic shows how to create a second geom that is a computed field based on the first geom. The topic also shows some "real world" methods, such as how to remember the use of a geometry function to do what we want, and how to restore a geom that has been moved. We close with some illustrations of how multiple geoms might be used, and how selection from any drawing or labels based on the same record selects the corresponding objects or labels in all other components based on that record.

Example: Expression Context and Computed Fields - When creating a computed field in the Schema dialog, using the Expression Context tab can increase efficiency, legibility, and maintainability of the expressions that power the computed field.

Example: Create a Table with a Constraint - Create a simple table that includes a simple constraint upon one of its fields.