Example: Add a Computed Field to a Table

In this topic we add a computed field to a table.   The value of a computed field in each row is automatically calculated based on an expression using snippets of SQL.  Such snippets might be so simple, like A * B, we might not even think of them as SQL, but the expression could be a very robust and intricate expression using SQL functions and other constructs.

 

For our first example we use a very simple expression that multiplies two fields.

 

eg_addfield02_01.png

 

We open a table called Products that is taken from the nwind example database.  The table has been simplified to only five fields to more easily fit into illustrations.   The Products table shows a list of products, each having a price.   We will add a new field called Total Price that multiplies Unit Price by Units in Stock to get what the total price would be to purchase all of the units in stock for that record.  Perhaps "Inventory Value" would be a better name for that calculated result, but in this example we simply use the name Total Price.

 

We choose Edit - Schema  to launch the Schema dialog for the table.

 

eg_addfield02_02.png

In the Schema dialog we click on the <new field> choice to add a new field.

 

eg_addfield02_03.png

Additional controls appear at the bottom of the Schema dialog.  We enter Total Price as the name of the new field and we choose a type of float64.  We then enter the expression

 

[Unit Price] * [Units in Stock]

 

into the Expression box.  As with any SQL expression, the names of the fields are in square [ ] brackets, to make it clear what we intend since the field names have spaces in them.  We press Add to add the new computed field to the schema.

 

eg_addfield02_04.png

 

We then press OK to update the table's schema to add the new computed field, and to close the Schema dialog.

 

eg_addfield02_05.png

 

The table immediately updates to include the new, computed field and to show the values computed in that field.  The new computed field is shown in light gray background color since it is a read-only field.  The contents of a computed field are calculated based on the expression for that field and cannot be edited by double-clicking into a cell to edit that cell.   From the table we can see, for example, that 702 is indeed the result of multiplying 18 by 39.

 

Most calculations go so fast that computed fields appear to be immediately filled in.   What actually is going on is that initially the column is shown with light gray color and then as calculations are completed for each record the value for that record is filled in.   For very complicated calculations we might notice a flicker of gray color while the fields are filled in, but in most cases the table just appears as if it was always filled with those values.

 

If we ever forget what the expression is that drives a computed field, we can choose Edit - Schema again to open the schema for the table.

 

eg_addfield02_06.png

 

The Schema dialog will report the Expression used to populate that computed field.

 

Dynamic Computation

Computed fields are dynamically updated.  If we change the values of fields used in the expression the value shown for the computed field will change.  This is similar to how changing the value of a cell in a spreadsheet application like Excel will automatically cause cells that use that value to recalculate their contents.

 

eg_addfield02_07.png

 

For example, suppose we double-click into the Units in Stock field for the first record and change the value from 39 to 100.   We press Enter to accept the edit into the cell.

 

eg_addfield02_08.png

 

As soon as the edit is committed, the Total Price computed field value immediately is recalculated to show the new value of 1800.  computed fields will automatically recompute their contents for any changes in fields that participate in the expressions that create them.

 

Using SQL Functions in Computed Fields

The above example is a simple one using the multiply operator, *, to multiply two fields.  It is so simple we probably don't even think of it as an "SQL expression" or the * asterisk character as the "SQL multiply operator."   But beyond the very many SQL operators we can use we can also use all the rest of SQL, like SQL functions, as well.  

 

eg_addfield02_09.png

 

Consider the map above, which contains a buildings drawing layer showing the outline of buildings in Monaco as area objects.    We would like to add a computed field to that drawing's table that computes the area in square meters of each building object.   The buildings drawing is in a coordinate system, Orthographic centered upon Monaco, which provides accurate area measurements using the coordinates in that system.

 

eg_addfield02_10.png

 

We open the buildings Table and then choose Edit - Schema to open the Schema dialog.   

 

eg_addfield02_11.png

 

In the Schema dialog we click <new field>, we enter Bldg Area as the name for the new field, a type of int32, and then we enter the expression

 

GeomArea([Geom], 0)

 

into the Expression box. The GeomArea function computes the area of area objects and returns NULL for line and point objects (which do not have dimensional area).   We press Add to add the new field.   

 

tech_ravi_sm.png

Tech Tip:  Choosing a Type of int32 is a mild hack in that it forces use of round numbers in the resulting table.   We are trusting Manifold to automatically CAST the floating point value returned by GeomArea into an integer.   For explicit control, we could have used the Round or RoundDecs functions, as in

 

Round(GeomArea([Geom], 0))

 

or, if Bldg Area was created as a floating point type,

 

RoundDecs(GeomArea([Geom], 0), 2)

 

to round the result to two decimal points.     In this example we just make the new field an integer so the computed result shown in the table is an integer value, keeping our illustrations simple and clean.

 

eg_addfield02_12.png

 

We then press OK to update the schema and to close the dialog.

 

eg_addfield02_13.png

 

The new field immediately appears in the table, with a light gray background since it is read-only, and filled with the computed area for each building object.   Because the buildings drawing geometry is in a coordinate system, Orthographic, which uses meters as units of measure for coordinates, the reported values are in square meters.

 

 We can use the new computed field like any other field.   For example, we can create labels using it.

 

eg_addfield02_14.png

In the illustration above we have created new Labels using the Bldg Area field to label each building object with the area of that building.

 

There are very many SQL Functions available in Manifold we can use for simple, useful expressions in computed fields.   For example, if our drawing had lines and not areas and we wanted the length of each line object we could use GeomLength to get that.  

Computed Fields using Other Computed Fields

Computed fields can utilize other computed fields in their computations.   Suppose, for example, we would like to report the area of each building in square feet instead of in square meters.  

 

To do that, we could have entered a slightly more complex expression when we created the Bldg Area computed field.  We could have entered

 

GeomArea([Geom], 0) * 10.7639

 

as the expression, so that right away the building area would be reported in square feet (multiplying square meters by 10.7639 converts to square feet).     However, since we have already created the Bldg Area computed field we can now create a second computed field that multiplies Bldg Area by the conversion factor to report the result in square feet.

 

eg_addfield02_15.png

 

We launch the Schema dialog again, click on <new field> and enter the name Area SqFt for the new field.   Again, we choose a type of int32 and now we enter the Expression

 

[Bldg Area] * 10.7639

 

to convert the areas from square meters to square feet.   We press Add and then OK.  

 

eg_addfield02_16.png

 

This adds another computed field to the table, which now reports the area of each building in square feet.  Conversions like this are a typical use of computed fields.

 

Using the Command Window to Write Expressions

Entering the desired expression for a computed field by writing it into the Expression box of the Schema dialog when adding a new field is a simplified user interface without the full apparatus of the query builder found in the Command Window or the expression builder found in the Expression tab of the Transform panel.    If we would like some assistance or to try out our ideas for expressions we can use the Command Window or the Transform panel to help create and to preview expressions before we use them in the Schema dialog to add a new computed field.

 

Suppose, for example, we would like to add computed fields that report the X values and the Y values of the X,Y center of each object in the drawing.   We can use the Command Window to try out possible ideas, and then copy and paste what works for us from the Command Window into the Expression box when adding the new computed field.

 

eg_addfield02_17.png

 

To help us get started, if we are new to SQL we may have remembered from examples that the Example: Create a Geocoded Table from a Drawing topic showed how to get the X and Y values for the locations of points.   We will review that topic and re-cycle the expression we learned in that topic.

 

We launch the Command Window and enter a SELECT statement that recycles the expression used in the Example: Create a Geocoded Table from a Drawing topic to report the X coordinate of objects:

 

SELECT VectorValue(GeomCoordXY([Geom], 0),0)

FROM [buildings Table];

 

 

eg_addfield02_18.png

 

When we click the ! run button the statement runs without error and reports an X coordinate for each building, using the GeomCoordXY function to get that X coordinate from the object geometry.

 

Since we have carefully reviewed the Example: Create a Geocoded Table from a Drawing topic we know that this X coordinate is the X coordinate taken from the first coordinate pair of the coordinates that define the area object which represents each building.   The example topic from which we copied it created a geocoded table from a drawing of points, and since for points the first coordinate pair is the only coordinate pair using the GeomCoordXY function was OK.    However, since our buildings drawing uses areas, we want to get the X location of the center of each area, not the X location of the first coordinate pair that defines the boundary of the area.

 

To do that, we will adjust our query to use the GeomCenter function.  We edit the query to the following:

 

SELECT VectorValue(GeomCenter([Geom], 0),0)

FROM [buildings Table];

 

The GeomCenter function reports an X,Y vector that gives the center of the object.   The VectorValue function with an argument of 0 extracts the X part of that vector.   If we wanted a slightly different centroid we could have used GeomCenterInner or GeomCenterWeight for different centroids as discussed in the Transform: Center and Centroids topic.

 

eg_addfield02_19.png

Pressing the ! run button the query runs correctly and now reports the X value of the center of the building object.   That is what we want, so now that we know we have written the expression correctly we can copy

 

VectorValue(GeomCenter([Geom], 0),0)

 

and paste that into the Expression box for a new computed field.

 

eg_addfield02_20.png

 

We launch the schema dialog, choose a name of X for the field, a data type of float64 and we paste the expression we copied from the Command Window into the Expression box.   We press Add and then OK.

 

eg_addfield02_21.png

 

A new computed field called X appears in the table, reporting the X coordinate for the center of each building area.

 

eg_addfield02_22.png

 

If we want to add the Y component, based on an analogy to what we read in the Example: Create a Geocoded Table from a Drawing topic we change the argument from 0 to 1 in our use of VectorValue to extract the Y component.     We launch the Schema dialog and add a computed field called Y that uses the expression

 

VectorValue(GeomCenter([Geom], 0),1)

 

to report the Y component of the center of the building.   Press Add and then OK.

 

eg_addfield02_23.png

 

A new computed field called Y appears in the table, reporting the Y coordinate for the center of each building area.tech_ravi_sm.png

 

Tech Tip:   The X and Y coordinates reported above are coordinates within the Orthographic coordinate system used for the buildings drawing.   They are not latitude and longitude coordinates.   If we want latitude and longitude coordinates there are two ways to get those.   The first is to write significantly more complicated expressions that on the fly do a coordinate system conversion into latitude / longitude coordinate system and then extract the X and Y.    That is certainly feasible but the resulting expressions are complicated and long.   

 

A second way, easier for beginners, is to first use the Transform panel to create a new drawing that consists of centers for the buildings, change the coordinate system of that drawing to latitude / longitude, and to then use the same procedure as shown above in this topic to get X and Y for each center.   

Adding Z values to Tables

We can use a variation of the above procedure to add a computed field column to a table that shows the Z value (height), if any, that has been specified for an object.   The expression to use for the computed field is:

 

VectorValue(GeomCoordXYZ([Geom], 0),2)

 

We use the GeomCoordXYZ function to extract the first coordinate of the object,  the zero in ([Geom], 0).   We then use the VectorValue function to report the third value of the XYZ triplet.   Using zero based counting the third value is specified using the number 2.  

 

Creating Geometry with Computed Fields

In the above examples we have created computed fields of numeric type.  We can also create fields of geom type, that are populated with computed geometry.

 

eg_addfield02_24.png

 

Suppose we have a drawing called Antennas that shows the location of antennas at various locations in Monaco.  

 

eg_addfield02_25.png

 

The Antennas Table for the drawing includes a field called Strength which gives the signal strength for each antenna.    We would like to create circles that show the radius of reception for each antenna, where the radius of each circle is based on the signal strength of that antenna.   We need to create a computed field that is of type geom and to populate that field with circular areas centered upon each antenna where the radius of the circle is proportional to the signal strength.

 

eg_addfield02_26.png

We test our approach by launching the Command Window and then writing the query:

 

SELECT GeomMakeCircle(GeomCenter([Geom], 0), [Strength])

FROM [Antennas Table];

 

We again use the GeomCenter function to get the X,Y center of each antenna.   The "center" of a point, of course, is just the location of the point.   We then use that X,Y location within the GeomMakeCircle function to create a circular area of radius [Strength].   In real life, we would probably use a more sophisticated calculation for the radius, such as the square root of the strength value, but to keep the expression simple for this example we just use the value of [Strength] without any further massaging.  

 

That query runs without error when we press the ! run button, returning a table of geoms that contain area objects.  That tells us we have matched all data types correctly, that we have fed each of the functions we use with a value it expects, and that the result is the data type, a geom, that we want.

 

 

eg_addfield02_27.png

 

With the focus on the Antennas Table, we launch the Schema dialog.   We click on <new field> and give the new field a name of Circles.   We choose type geom and then we enter the expression:

 

GeomMakeCircle(GeomCenter([Geom], 0), [Strength])

 

into the Expression box.   We copy that expression from the Command Window and paste it into the Schema dialog to avoid making typographical errors.   We press Add and then OK.

 

eg_addfield02_28.png

 

A new computed field called Circles appears in the table, populated with geoms that contain area objects.   We can create a drawing from the Circles geom field.    As discussed in the Example: Two Drawings from the Same Table topic, tables can have more than one geom field in them, and thus different drawings, using different geom fields, can be created from the same table.

 

eg_addfield02_29.png

In the Project pane we Right-click on the Antennas Table and then choose New Drawing.   In the New Drawing dialog we choose the Circles field as the geometry field.     The Antennas drawing uses Pseudo-Mercator projection, so that is the coordinate system we use for this drawing as well. tech_ravi_sm.png

 

Tech Tip:   It is a bit sloppy to have created the Circles geom in a drawing that uses Pseudo-Mercator coordinate system.   For better computational accuracy we really should have taken a moment to have converted the Antennas drawing into Orthographic coordinate system centered on Monaco.   That would have provided better accuracy in terms of drawing circles.   However, Pseudo-Mercator is not all that awful a projection at the latitude of Monaco, so for the loose accuracy involved in estimating range for an antenna with a highly imprecise formula, we may as well just use the default coordinate system of Pseudo-Mercator.   That at least has the merit that circles will look like circles when displayed against a web server background from Bing or Google.

 

eg_addfield02_30.png

 

Above we see the new Circles drawing dropped into our map.   Areas have been formatted using Style with transparent fill color and bright green boundary color, to give the appearance of bright green circles around each antenna.   The radius of each circle is the value of Strength for that particular antenna.

 

See Also

User Interface Basics

 

Tables

 

Data Types

 

Selection

 

Computed Fields and Constraints

 

Example: Editing Records in a Table - How to edit the contents of an existing record using mouse and keyboard.

 

Example: Adding Records to a Table - How to add a new record to a table using mouse and keyboard.

 

Example: Create a Table and Add a Record - Create a table with required fields and then add a record with value for those fields.  Creates the OGR-required table to prepare a Manifold project for use by OGR as detailed in the Example: Connect to Manifold from QGIS topic.

 

Example: Add a Field to a Table and Fill It - we add a field to a table and simultaneously for all records fill the field with a specified value.   We do that in two examples, first a very simple one and second, a more elaborate example that also shows restriction to a selection as well as use of the Transform panel.

 

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

 

SQL Example: Create a Table with a Constraint via a Query -  A simple example using SQL to create a table with a constraint.

 

Adding an Index to a Table - A basic topic on adding an index using the built-in capabilities of the mfd_id field.

 

Example: Add a Spatial Index to a Table - A typical use of an index is to provide a spatial index on a geom field in a table, so the geom data can be visualized in a drawing. This topic provides the step by step procedure for adding a spatial index.

 

Example: Add a UUID-based Index to a Table - Create a new computed field that is filled with UUID values on creation and then create an index on that field.   This technique creates an indexed field that has guaranteed unique values for all records and thus the indexed field and record values may be used in other projects.

 

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.