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.
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.
Press the Add command button.
Choose Field in the drop down menu.
We enter the name Total Price for the new field and choose a data type of float64. We would like to create the field as a computed field, so we press Edit Expression to launch the expression builder, which we will use to create the expression we would like to be computed for the value of the field.
In the expressions pane, we enter the expression
[Unit Price] * [Units in Stock]
Even with such a simple expression we would use the expression builder commands, which work like query builder commands in the Command Window. For example, we could double-click the [Unit Price] template under Fields to add it to the expression, manually add an asterisk * character, and then double-click the [Units in Stock] template to add that to the expression. When using fields that have spaces in their names within SQL expressions, we always enclose them in square [ ] brackets.
Back in the Field dialog, we see the expression has been added. We press OK.
The new computed field appears in provisional, blue color to indicate the change has not yet been committed. We press Save Changes to add the new computed field to the schema and to close the Schema dialog.
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.
The Schema dialog will report the Expression used to populate that computed field.
If the expression is too big to fit into the available cell size, hovering the mouse over the expression will report it in a tooltip.
Another way to see a lengthy expression is to double-click the field row to open the Field dialog.
That will report the entire expression as well, in a form we can highlight and Copy, should we want.
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.
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.
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.
We can style computed fields just like other fields.
Right-click onto the column header for the Total Price computed field, and in the context menu choose Style. In the Style dialog that pops open, choose the currency format. When used with neutral language or US English language settings, that will automatically choose US style currency settings, using a dollar sign, commas for breaks in larger numbers, and a decimal point to set off decimal digits.
The result shows the Total Price using currency format. In the illustration above, we have also right-clicked the column header for Unit Price and have used Style to set currency formatting for that field as well.
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 in addition to the very many SQL operators we can use we can also use all the rest of SQL, like a seemingly endless range of SQL functions, as well.
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. This allows us to use a simple SQL function, GeomArea, which works in whatever is the coordinate system used. For greater accuracy, we could do a geodetic computation using the GeomAreaGeo function, which is slightly more complicated to use because it requires four arguments. Another approach is to keep the buildings layer in Latitude / Longitude projection and then use the high-accuracy CoordMeasureArea function, as shown in the Example: Expression Context and Computed Fields topic.
In this example, we use the simple GeomArea function.
We open the buildings Table and then choose Edit - Schema to open the Schema dialog.
Press the Add command button and choose Field in the drop down menu.
In the Field dialog we enter Bldg Area as the name for the new field using a type of int32. Since this is to be a computed field, we click Edit Expression to launch the expression builder, to create the expression that will be used to compute the value of the field.
In the Expression dialog we enter the expression
into the expression pane. The GeomArea function computes the area of area objects, and returns NULL for line and point objects. We press OK.
Back in the Field dialog, we review our work and, if satisfied, we press OK.
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. Another way to display only rounded full integers, while using full float64 precision, would be to right-click onto the Bldg Area field in the table once it is created and to choose a formatting style that shows only the integer part of the number.
For explicit control if we preferred to use a float64 data type, we could have used the Round or RoundDecs functions, as in
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.
The new computed field appears in provisional, bluish color. We then press Save Changes to update the schema and to close the dialog.
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.
In the illustration above we have created a new Labels layer called Area m2 that uses 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 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.
With the focus on the buildings Table, we launch Edit - Schema.
Press the Add command button and choose Field in the drop down menu.
We enter the name Area SqFt for the new field. Again, we choose a type of int32. We press the Edit Expression button.
In the Expression dialog we enter the expression
[Bldg Area] * 10.7639
to convert the areas from square meters to square feet. Note that the Bldg Area computed field we added earlier in this topic now appears as a template option in the expression builder. We press OK.
We quickly check our work, and press OK.
The new Area SqFt computed field appears in provisional, bluish color. We press Save Changes to commit the change to the table and to close the dialog.
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.
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 and SQL execution capability found in the Command Window. If we would like some assistance or to try out our ideas for expressions we can use the Command Window to help create 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 Schema dialog's expression builder when adding a new computed field.
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 revisit 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];
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 Center and Centroids topic.
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
and paste that into the Expression box for a new computed field.
With the focus on buildings Table, we launch Edit - Schema.
Press the Add command button and choose Field in the drop down menu.
In the Field dialog, we choose a name of X for the field and a data type of float64. We click the Edit Expression button.
In the Expression dialog we paste the snippet of SQL we copied from the Command Window into the expression pane:
We press OK.
Back in the Field dialog we check our work and press OK.
The new computed field appears in provisional, bluish color. We press Save Changes to commit the changes to the table and to close the dialog.
A new computed field called X appears in the table, reporting the X coordinate for the center of each building area. In the illustration above, we have also right-clicked onto the column header for the X field, chosen Style, and then we have chosen a format for the field that only shows four digits after the decimal point.
To add a new, Y field, we repeat the above workflow used to create an X field, but we use a slightly different expression.
Once again, in the Schema dialog we Add a Field. In the Field dialog we specify the name Y and a type of float64. We press Edit Expression.
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 enter the expression:
to report the Y component of the center of the building. Press OK.
In the Field dialog we press OK.
Press Save Changes.
A new computed field called Y appears in the table, reporting the Y coordinate for the center of each building area. In the illustration above, we have also right-clicked onto the column header for the Y field, chosen Style, and then we have chosen a format for the field that only shows four digits after the decimal point.
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.
An alternative, easier approach is to first use the Transform pane 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.
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:
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.
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.
Suppose we have a drawing called Antennas that shows the location of antennas at various locations in Monaco.
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.
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.
With the focus on the Antennas Table, we launch Edit - Schema.
In the Schema dialog we Add a Field named Circles, using a type of geom. We press Edit Expression.
In the Expression dialog we enter the expression:
GeomMakeCircle(GeomCenter([Geom], 0), [Strength])
into the expression pane. We copy that expression from the Command Window and paste it into the Schema dialog to avoid making typographical errors. The illustration above shows the dialog just after the paste, with not quite enough room to see the entire expression that was pasted at once. We press OK.
In the Field dialog we can see the beginning of the expression to verify the entire expression was pasted. We press OK.
We press Save Changes.
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.
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 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.
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.
User Interface Basics
Computed Fields - Computed fields using the Schema dialog.
Computed Fields and Constraints - An SQL approach to computed fields.
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 pane.
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.
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.