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, as shown in the Example: Two Drawings from the Same Table topic.   This topic continues that theme by showing 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.

 

eg_add_second_computed_geom_field01_01.png

 

We begin with a map that contains one drawing layer, called Mexico.   This uses the sample data set showing states in Mexico, which often appears in Manifold examples.  The drawing uses Pseudo Mercator projection.

 

eg_add_second_computed_geom_field01_02.png

 

We pop open to take a look at the drawing's table.  We can see it is a simplified form of the data set as used in other examples.  It has only two fields, the name of the province and a geom field.    For a cleaner look, we used Edit - Schema to delete the many other fields found in the sample data set, and we used the Transform panel to convert the names of states to title case instead of the original all upper case names.

 

We will add a second geom field that is a computed field.  For each record we would like to create the inner centroid of the area and to put that into the second geom field we create.   We will use a snippet of SQL in an expression for the computed field.

Learn How to Create Inner Centers

If we are skilled Manifold users we might not need this step, and we could proceed immediately to the creation of a second geom, dashing off freehand whatever expression we like.   However, it could be we do not recall the name of the function used to create inner centers or how to use it and we would like some help.

 

The easiest way to learn is to have Manifold do the work for us, by using the Edit Query button in the Transform panel.   We choose a point-and-click template that does what we want, and then we see what SQL Manifold writes to implement that.   We can then copy and paste what we want from what Manifold creates.  

 

eg_add_second_computed_geom_field01_03.png

 

With the focus on the Mexico layer in the map window, we launch the Transform panel in the Contents pane.   We verify that the Geom field is the target, and we choose the Center, Inner template.

 

eg_add_second_computed_geom_field01_04.png

 

Right away, Manifold previews what the template would do in the map window.   We can see this is the right template since it creates an inner center point for each area.

 

However, we do not want to modify the drawing, we simply want to see the SQL used to do the above, so instead of pressing the Update Field button we press the Edit Query button.

 

eg_add_second_computed_geom_field01_05.png

 

Manifold pops open a Command Window with the SQL to create inner centroids already loaded into the Command Window as a query.   This shows us every nuance of how to use the relevant functions.   We highlight the part that actually creates the geom we want, and we Ctrl-C to Copy it:

 

GeomMakePoint(GeomCenterInner([Geom], 0))

 

The above uses the GeomCenterInner function to get the X, Y location of the inner center of the area.  It then uses the GeomMakePoint function to create a point at that X, Y location.

 

Seeing what Manifold writes is a great way to learn how to use functions correctly or to remind ourselves of details we might have forgotten.  For example, we might have forgotten that the GeomCenterInner function returns X, Y values, not a point object, so we might have forgotten that we need to wrap the result with GeomMakePoint to actually create a point.    Copying and pasting the expert SQL Manifold automatically writes for us saves us from making mistakes.

Create a Second, Computed Geom

With the focus on the Mexico Table we choose Edit - Schema to launch the Schema dialog.

 

eg_add_second_computed_geom_field01_06.png

In the Schema dialog we:

 

  1. Click on <new field>

  2. Enter the name Center for the new field name.

  3. Choose geom for the type of the field.

  4. Ctrl-V to Paste the expression we copied above.

  5. Press Add to add the field.

  6. Press OK to commit the change to the schema and to exit the dialog.

 

 

The above creates a new, computed geom field in the table.  The value of that geom is computed for each record using the expression that we copied from the Command Window text:

 

GeomMakePoint(GeomCenterInner([Geom], 0))

 

For each record, Manifold will take the geom for the area from the field called Geom, it will get the X,Y coordinate of the inner center using the GeomCenterInner function and then it will create a point using  the GeomMakePoint function, placing the geom for that point into the field called Center.

 

eg_add_second_computed_geom_field01_07.png

In the table we can see that a new field called Center has been added to the table, and that for each record it contains a geom that is a point.   The Center column uses gray background because it is a computed field and thus is read-only.   It cannot be manually edited since the values within are computed automatically.

Create a New Drawing using the Second Geom

Using the procedure shown in the Example: Two Drawings from the Same Table topic we create a new drawing using the Center field as the drawing's geom field.   IN the Project pane we right-click onto Mexico Table and we choose New Drawing from the context men.

 

eg_add_second_computed_geom_field01_08.png

 

In the New Drawing dialog we specify Centers as the name of the new drawing, and for the geometry we choose the Center field.  Note the icon for the Center field that indicates it is a computed field.    The system advises us the coordinate system in use has not been confirmed by showing the coordinate system in red text.  We press the Coordinate System button.

 

eg_add_second_computed_geom_field01_09.png

 

From the menu we choose one of the two favorites that is used as a standard, the Pseudo Mercator coordinate system.   We then press the Create Drawing button.

 

eg_add_second_computed_geom_field01_09a.png

 

A new drawing called Centers appears in the project pane.  There is no "Centers Table" because the Centers drawing shows data from the existing Mexico Table.

 

eg_add_second_computed_geom_field01_09b.png

 

If we right-click onto the Centers drawing in the Project pane and choose Properties we can see from the Properties dialog that the Centers drawing indeed uses Mexico Table as the drawing's table, and the Center field as the source field for the drawing's geometry.

 

eg_add_second_computed_geom_field01_10.png

 

We drag and drop the Centers drawing into the map and then we use the Style panel in the Contents pane to color the inner centers points with a bright green fill color.

 

The display above shows a map with two drawings in it.   The Mexico drawing shows geometry from the Geom field in the Mexico Table.  The Centers drawing shows geometry from the Center field in the Mexico Table.   The Center field is a computed field for which the geometry within the field is automatically computed as the inner center of the area that is in the Geom field for that record.

Examples

We can see how any changes in the Geom field's area object will automatically cause a change in the Center field's object that is computed based on the Geom field's area object.    We will show a variety of examples that illustrate how the Center field's geom is automatically re-computed based on whatever is in the Geom field.  We will also see how having two geom fields in the same record will select both of them if the record is selected.

 

Using the techniques shown in the Example: Change the Shape of Areas topic we can move an area in the Mexico drawing and see what happens to the Centers point automatically created from that area.

 

eg_add_second_computed_geom_field01_11.png

 

In the above illustration we have Alt-clicked an area in the Mexico drawing.  We then clicked into the coordinates list in the Record panel's Coordinates tab, and we pressed Ctrl-A to select all of the coordinates for that area.  If we drag the large edit handle box (difficult to see under the cross cursor in the illustration above) all of the coordinates will move with it.

 

eg_add_second_computed_geom_field01_12.png

 

The effect is that we move the entire area to a new location.

 

eg_add_second_computed_geom_field01_13.png

 

When we press the Update Record button in the Record panel we commit the changes.    The area moves to the new location.  The Centers point also moves, because the geometry for that point is automatically recomputed based on the new geometry of the moved area.  

Restore the Moved Area

We will move the area back to where it was, by copying the original geometry for the area from a backup project and pasting it into the table.   First, we alt-click on the moved area to see the name of the state in the Record panel's values tab.   That step is not illustrated, but it tells us the state that was moved is called Coahuila.  

 

Next, we need to find an unmodified original version of the Mexico drawing.  To do that, we launch a second session of Manifold on our Windows desktop and in that session we open the original, sample data set that was used to create the Mexico project used so far in this example topic.

 

eg_add_second_computed_geom_field01_14.png

 

In that second session of Manifold we open the drawing's table.  As seen above, it still has all of the extra attribute fields and it still has all uppercase names for the states.  We right-click onto the Geom cell for the COAHUILA record and choose Copy from the context menu.  We have just copied the geometry for that area onto the Windows Clipboard.

 

Next, we switch to our original Manifold session in which we are doing this example.  

eg_add_second_computed_geom_field01_15.png

 

In the drawing's table we right-click onto the Geom field for the Coahuila record and we choose Paste.   We have just replaced the moved geometry of the Coahuila area with the original geometry copied onto the Windows Clipboard from the backup, original version of the data.

 

eg_add_second_computed_geom_field01_16.png

 

The moment we paste that original geometry into the cell, the area moves back to the original position.   The Centers point also moves, since it is now recomputed based on the geometry that was pasted into the Geom cell.

 

Important: This technique of copying a geom from one table into another works correctly only if both geoms use exactly the same coordinate system.  In the above example the technique works because the drawing in the destination project was created from the drawing in the source project, so both share identically the same projection.

Selection

Selections in a table apply to all drawings and labels that are created from that table.   If we select a record in the table, the associated objects and labels for that record will also be selected in all windows within which they appear.

 

eg_add_second_computed_geom_field01_17.png

 

In the Mexico Table we select the record for Durango.  It appears in red selection color.

 

eg_add_second_computed_geom_field01_18.png

 

Instantly, both the area for Durango within the Geom field that is shown in the Mexico drawing and also the inner center computed from that area within the Center field that is shown in the Centers drawing are also displayed in red selection color within the map.

 

In the map above, both the Mexico drawing and the Centers drawing appear as layers.  However, their relationship as a result of being created from the same table still applies when they appear separately in different maps.   We can see that by creating labels from the Centers drawing.

 

eg_add_second_computed_geom_field01_19.png

 

In the Project pane we right-click onto the Centers drawing and choose New Labels.  In the New Labels dialog we choose Name as the field to use for the label text.  We press the Create Labels button to create a new labels component called Labels.

 

eg_add_second_computed_geom_field01_20.png

 

We create a new map, add a Bing satellite layer to it and then we drag and drop the new Labels layer into the map. After some tinkering with the Style panel to alter the size and color of the labels, we see the display above.    The labels in the map have been created using the Centers drawing, which draws its geometry from the Center field in  Mexico Table.   The Center field is a computed field based on the Geom field in the same table.  A selection in any of the components, the Labels component, the Centers drawing, the Mexico drawing or Mexico Table, will be the same selection in all of them.

 

That will be true, as well, of every other drawing created from Mexico Table.   For example, suppose ion the Project pane we Copy the Mexico drawing and we Paste it, naming the copy Mexico Outlines.  We have created a second drawing using the same geom in the same table, just as is illustrated in the Example: Multiple Drawings from the Same Table topic.

 

eg_add_second_computed_geom_field01_21.png

 

We can drag and drop the new Mexico Outlines drawing into a map with a Bing satellite layer as a background.   Initially, the Mexico Outlines layer will use the same solid color, solid area style used by the Mexico drawing (after all, it is a copy).  But since it is a different drawing we can Style it differently.   In the illustration above we use bright green Color and transparent Fill color, so the area objects appear as bright green outlines.    Styling areas with transparent fill color is a quick and dirty way of giving the appearance of lines.  

 

eg_add_second_computed_geom_field01_22.png

 

Back in the other map that has labels, we can Ctrl-click the Oaxaca label to select it.   It switches to using red selection color.   By selecting that label, we have selected the record in Mexico Table associated with the label, and thus have also selected every geom in that record as well.

 

eg_add_second_computed_geom_field01_23.png

Immediately, the area object for Oaxaca in the Mexico Outlines drawing also redisplays in red selection color.   It is cool we can exploit the effect that selecting a record in a table by selecting any label or object using that record in any other window will also select all other labels and geoms based on that record.   However, in the above case the automatically filled-in rendering of selected areas may not be what we want.

 

We might want to create actual border lines for each area, and not try to get by with the quick and dirty trick of styling areas with transparent fill color.   Doing that is easy.

 

eg_add_second_computed_geom_field01_24.png

 

Similar to how we used Edit - Schema to add a second geom field to the table which was a computed field that gave inner centers, in the above Schema illustration we see that we have added a third geom field to the table called Borders that is also a computed field.  The illustration above shows the computed field after it has been created, so the Expression box is a read-only report of what was used to create the computed field.    In this case, we used the expression:

 

GeomConvertToLine([Geom])

 

We learned how to use that function by using the Transform panel's Convert to Line template and then pressing the Edit Query button to see what SQL Manifold would create.

 

eg_add_second_computed_geom_field01_25.png

 

In Mexico Table we see a third computed field, containing geoms for lines, has been added to the table.   We can create a new drawing, called Borders, from that Borders geom, similar to how we created a Centers drawing from the Center geom.

 

eg_add_second_computed_geom_field01_22.png

 

We can Ctrl-click the Oaxaca label to select it.

 

eg_add_second_computed_geom_field01_26.png

 

Right away, in Mexico Table the associated label is also selected.   All objects in geom fields in that record are also selected.

 

eg_add_second_computed_geom_field01_27.png

 

If we create a Borders drawing from the Borders geom and drop it into the map seen above, we can see how the line object in the Borders field is also selected.   That provides a "select this region" effect without hiding what is inside the region.

 

To see how selecting one record in Mexico Table selects the corresponding object in all drawings created from that same record, we can illustrate the four different drawings we have in this example that are created from that same record in Mexico Table:

 

 

eg_add_second_computed_geom_field01_28.png eg_add_second_computed_geom_field01_29.png

 

The two drawings above are both based on the Geom field in the table.

 

eg_add_second_computed_geom_field01_30.png  eg_add_second_computed_geom_field01_31.png

 

The two drawings above are both based on computed geom fields in the table, the one at left based on the Center computed field and the one at right based on the Borders computed field.

 

Add to the above, of course, the Labels component in which we selected the Oaxaca label with a Ctrl-click.

 

Notes

Is that a point not in inner center position?  - When we created points at inner centers, there seemed to be an outlier that did not fall within an area.

 

eg_add_second_computed_geom_field01_16.png

 

The Center, Inner template is supposed to create a centroid for an area that is guaranteed to fall within the area.  However, in the illustration above we see a point near the right margin of the window that seems to fall outside any areas.

 

eg_add_second_computed_geom_field01_32.png

 

If we zoom into the display we see that the point was created for a very small area, too small to be seen underneath the point at the initial zoom.  

 

 

See Also

Tables

 

Selection

 

Computed Fields and Constraints

 

Contents Pane

 

Transform

 

Contents - Transform

 

Transform Options

 

Transform Templates

 

Transform Templates - Drawings

 

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: Drawings use Geom Fields in Tables  - An essential discussion on how drawings are created from geom fields in tables, including how the drawing knows which coordinate system to use.

 

Example: Multiple Drawings from the Same Table - Illustrates how easy it is to create multiple drawings that use the same table and same geometry by copying and pasting an existing drawing.  Each new drawing takes no additional storage space in the project, but can be formatted differently.   

 

Example: Create a Drawing from a Geocoded Table - A geocoded table has records with a latitude and longitude for each record.   This example starts with a table containing a list of cities with a latitude and longitude field for the location of each city.   We create a geom from the latitude and longitude fields using a template in the Transform panel and then we create a drawing that shows the cities as points.  This example shows all the infrastructure steps involved.

 

Example: Two Drawings from the Same Table - Take a table with a geom field that is visualized by a drawing.  Add a second geom field to the table and create an rtree index on that field so it can be visualized by a drawing.   Copy the first drawing, paste it and adjust the pasted copy so it uses the second geom field. Demonstrate how to use the Transform panel to show "live" modifications in the second drawing compared to the first drawing.

 

Example: Use a Transform Expression to Create Buffers in a Drawing - Use the Expression tab of the  Transform panel to create three different sizes of buffers for different lines in a drawing and then automatically create a query which does the same thing.  Includes examples of using the Add Component button and also the Edit Query button.