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.

 

The Expression Context tab provides a place to write SQL that is executed just once to establish the context in which expressions written in the Expression tab are evaluated.   For example, we can place pragmas, scripts, function definitions or the definitions of global values in the Expression Context tab.   This is particularly a good place to put global values that are computed, so the computation of the global value happens just once and then it can be used for each record by the expression in the Expression tab.  

 

See the Schema topic for a discussion of computed fields and the use of expressions.

Example  - Compute Geodetic Area 

Suppose we have a Mexico drawing in Latitude / Longitude coordinate system that shows the provinces of Mexico as area objects.    The table for the drawing is shown below.

 

 

We would like to add a computed field that gives the area of each province in meters using a precise geodetic computation over the surface of the WGS84 ellipsoid used in the Latitude / Longitude coordinate system, and not the simple computation usually done on the Euclidean Plane.  

 

We can use the CoordMeasureArea SQL function to compute the area of each geom.  That function is guided by a measure object, which incorporates the options we want in a structure Manifold treats as a table.  We use the CoordMeasureMake function to create a measure object based on options we provide as parameters: a coordinate system definition, the unit of measure, and a geodetic computation Boolean flag.  

 

Because our Mexico table has area geoms in the default Latitude / Longitude coordinate system.   We can get the first argument for CoordMeasureMake , the definition of that default Latitude / Longitude coordinate system, by using CoordSystemDefaultLatLon().  See the discussion for those functions in the SQL Functions topic.

 

Before we create a computed field, we can test our proposed SQL by launching the Command Window and trying out the expression we will use within a SELECT statement.

 

 

The SELECT query above is:

 

SELECT CoordMeasureArea(

  CALL CoordMeasureMake(CoordSystemDefaultLatLon(), 'Meter', TRUE),

     [Geom]

  )

  FROM [Mexico];

 

Running it  works fine, and reports the geodetic area for each province.  Running the query is a good way to verify we are using the functions correctly, without syntax errors.    

 

 

We can use the heart of the above query as an expression, as seen above, to add a computed field called Geodetic Area to the table.  The expression, slightly reformatted to fit into the illustration, and written entirely in the Expression tab, is:

 

CoordMeasureArea(

  CALL CoordMeasureMake(

    CoordSystemDefaultLatLon(), 'Meter', TRUE

  ), [Geom])

 

Adding that computed field to the schema also works fine, just like the SELECT query we used to test it.

 

 

However, the SELECT query we wrote is inefficient, because it computes the same measure object for each record.   Recall the SELECT query:

 

SELECT CoordMeasureArea(

  CALL CoordMeasureMake(CoordSystemDefaultLatLon(), 'Meter', TRUE),

     [Geom]

  )

  FROM [Mexico];

 

We could re-write that query as two queries, the first computing a global value using CoordMeasureMake just once, and then the SELECT query using that global value for each record as part of the expression context:

 

 

That also works fine, the query text in the illustration above being:

 

VALUE @measure TABLE = CALL CoordMeasureMake(

  CoordSystemDefaultLatLon(), 'Meter', TRUE);

 

SELECT CoordMeasureArea(@measure, [Geom])

  FROM [Mexico];

 

We can add a computed field to the table that works the same way, first computing the VALUE statement just once in the Expression Context tab, and then using that global value in the Expression tab's expression.

 

With the focus on the Mexico Table, we launch the Edit - Schema dialog, we press the Add button and choose Field.

 

 

In the Field dialog we give the name Geodetic Area for the new field, we choose a Type of float64 and we press the Edit Expression button.

 

 

In the Expression dialog we click on the Expression Context tab and we enter the statement we want to be part of the expression context for our computed field:

 

VALUE @measure TABLE = CALL CoordMeasureMake(

  CoordSystemDefaultLatLon(), 'Meter', TRUE);

 

That is the global value definition for the @measure global value we want to be part of the expression context, that is, the execution context, for the main expression we will write.

 

We could write more than one statement in the Expression Context tab, for example, defining other global values or defining functions.  Anything we put into the Expression Context tab will be executed just once, to set up the context for the expression in the Expression tab, which will be executed for each record.

 

 

We click on the Expression tab and we enter the expression we will use:

 

CoordMeasureArea(@measure, [Geom])

 

The expression uses the global value @measure we defined in the expression context.  This expression will be executed for every record, a big deal if we had billions of records, but whatever was computed to get the @measure global value will have been computed just once.

 

Press OK.

 

 

Back in the Field dialog, we see the expression we entered.  

 

 

We can click on the Expression Context tab to see the expression context we entered.

 

Press OK.

 

 

Back in the Schema dialog the new field is shown in provisional, blue color.  We press Save Changes.  

 

 

The new computed field appears, with values computed for each record.

 

Example - Using a Script to Show Date Modified

Using the same Mexico Table we can add a datetime field which shows the date when an object's geometry was last modified.  We will write both a script and a function in the Expression Context tab, and then use the function in the Expression tab.

 

 

With the focus on the opened Mexico Table, we choose Edit - Schema to launch the Schema dialog.  Ctrl-E is a keyboard shortcut to launch the Schema dialog.

 

 

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

 


In the Field dialog we specify the name Date Modified and we choose datetime from the long list of data types in the Type box.

 

We press Edit Expression to launch the Expression dialog.

 

 

In the Expression dialog, we press the Expression Context tab and enter the following:

 

SCRIPT funcs ENGINE 'c#' [[

  class Script

  {

    static System.DateTime F(Manifold.Geom unused) { return System.DateTime.Now; }

  }

]];

FUNCTION currentdate(@unused GEOM) DATETIME AS SCRIPT INLINE funcs ENTRY 'Script.F';

 

The above defines a script called F, that is written in C#, a .NET language.  The script returns the current value of the system date and time.   We also define a new function, called currentdate, which calls the script to get the current date and time.

 

Next, we click on the Expression tab.

 

 

In the Expression tab we enter the expression:

 

currentdate([Geom])

 

That simply calls the currentdate function we defined in the Expression Context tab.   Calling it with the [Geom] field is puzzling: see the discussion later in this topic for why that field is used as an argument.  

 

Press OK.

 

 

Back in the Field dialog, press OK.

 

 

 

The new, proposed Date Modified computed field appears in the Schema dialog in provisional blue color.  To apply the changes and to exit the Schema dialog, we press Save Changes.

 

 

The new computed field appears in Mexico Table, shown with gray background color since it is a read-only, compute field.  For all records, the Date Modified field value shows the system date and time when the computed field was created, 6/11/2019 16:28:04.

Edit Drawing

We can now add some objects to the drawing to see what date and time appear for those objects in the Date Modified computed field.

 

 

We open the Mexico drawing.

 

 

As discussed in the Editing Drawings topic, we add a line.  

 

 

The new line record appears in the drawing's table, with a Date Modified value of 6/11/2019 16:32:01, clearly a few minutes later than the previous times.

 

 

We add another line.

 

 

The new record appears with a Date Modified value of 6/11/2019 16:33:57, clearly a later time than the first line.

 

 

Next, we Alt-click the first line and shift a coordinate, to modify the line.

 

 

Changing the shape of the line modifies the line.

 

 

Right away, the table updates to show a Date Modified value of 6/11/2019 16:35:09 for the first line, that in the next to last record, with an mfd_id value of 39.    Whenever we modify the geometry of an object in the drawing, the Date Modified value for that object will update to the system date and time when the modification was done.

How the Script and Function Work

Consider the script and function we wrote into the Expression Context tab...

 

SCRIPT funcs ENGINE 'c#' [[

  class Script

  {

    static System.DateTime F(Manifold.Geom unused) { return System.DateTime.Now; }

  }

]];

FUNCTION currentdate(@unused GEOM) DATETIME AS SCRIPT INLINE funcs ENTRY 'Script.F';

 

...with the following in the Expression tab:

 

currentdate([Geom])

 

It may seem pointless to have a geom argument to both the script and the function, since a geom is not used in either the script or the function.   It seems we could simply write:

 

SCRIPT funcs ENGINE 'c#' [[

  class Script

  {

    static System.DateTime F() { return System.DateTime.Now; }

  }

]];

FUNCTION currentdate() DATETIME AS SCRIPT INLINE funcs ENTRY 'Script.F';

 

... into the Expression Context tab and in the Expression tab write:

 

currentdate()

 

We could, in fact, do that.  However, the result would be that the datetime captured when the computed field was first created would be repeated for any subsequent objects created, and the datetime would not be modified on edits.  That happens because the optimizer that executes the expression context sees nothing that would change the values and so it optimizes away the need to rerun the functions.   

 

By including a reference to the geom field in the expression, we force evaluation of that geom and that in turn convinces the optimizer to run the compiled code on any change to the geom, thus generating a fresh datetime on any change.      

 

If we had other fields in the expression on which we wanted to generate a fresh datetime on any change to those other fields, we could expand the list of arguments to the F script function and the currentdate function to include whichever fields we wanted to trigger a fresh datetime.    

 

Such arguments should be declared using the correct types for both C# and for the Manifold function, where the names of types are slightly different.   For example, if we wanted to capture a fresh datetime for any change in the mfd_id field, an integer field, we would write:

 

SCRIPT funcs ENGINE 'c#' [[

  class Script

  {

    static System.DateTime F(int unused) { return System.DateTime.Now; }

  }

]];

FUNCTION currentdate(@unused INT32) DATETIME AS SCRIPT INLINE funcs ENTRY 'Script.F';

 

... into the Expression Context tab and in the Expression tab write:

 

currentdate(mfd_id)

 

In the example, we force a fresh datetime by using a geom as an argument.  The C# language does not have a native geom type, so we use the shared Manifold property of the Script class, writing Manifold.Geom, when defining the F script function.   See documentation for data value types in the Manifold  API Documentation.

 

Notes

What if the Mexico drawing is not in Latitude / Longitude? - Since we know our example Mexico drawing is in Latitude / Longitude projeciton, this example uses a shortcut to build the measure object.  It uses the CoordSystemDefaultLatLon function to provide a coordinate system definition that the CoordMeasureMake function in turn uses to build a measure object.   The CoordSystemDefaultLatLon function takes no arguments, so we do not need to specify the name of the drawing.

 

If our Mexico drawing was in a coordinate system other than Latitude / Longitude, we could get the coordinate system definition by using the ComponentCoordSystem function, using an expression such as:

 

VALUE @measure TABLE = CALL CoordMeasureMake(

  ComponentCoordSystem([Mexico]), 'Meter', TRUE);

 

The ComponentCoordSystem function takes one argument, the name of the drawing, and returns a coordinate system definition for the coordinate system used by that drawing.   We can then use the measure object created by the above expression just as we used the measure object in the topic.

 

See Also

Tables

 

Data Types

 

Indexes

 

Computed Fields - Computed fields using the Schema dialog.

 

Selection

 

Schema

 

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

 

Command Window

 

Query Builder

 

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 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: Add a Computed Field to a Table - In this example we add a computed field to a table, illustrating how the computed field automatically changes when changes are made in the fields it uses for computation.   We also show how computed fields can use geometry, automatically updating centroids when areas are changed.  Last, we show how geometry can be created using computed fields, to create effective radius circles for antennas based on the power of the antenna.

 

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.  

 

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.