Example: Add a Second Computed Geom Field to a Table

We can create tables with more than one geometry (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.



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.



We pop open to take a look at the drawing's table.  We can see it is a simplified form of the Mexico 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 pane 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 pane.   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.  


With the focus on the map, in the Transform pane we pick the Mexico layer and the Geom field.   We double-click the Center template to launch it.



In the Center template we choose the inner (area) option.   


Press Preview to see a preview.



Right away, a preview layer in blue preview color appears in the map, and a blue preview caption bar giving the name of the template that is being previewed appears at the top of 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 Transform button we press the Edit Query button.



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 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 query 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.


Press the Add command button and choose Field in the drop down menu.



In the Field dialog, choose Center as the name of the new field and geom as the type.  Press Edit Expression to enter an expression to use for the computed field.



In the Expression dialog, paste 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.  Press OK.



Back in the Field dialog, press OK.



The new, computed geom field appears in provisional, bluish color.  Press Save Changes to commit the changes to the table and to close the Schema dialog.



In the table we can see that a new field called Center has been added to the table.  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.



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.   We then press the Create Drawing button.



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.



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.



We drag and drop the Centers drawing into the map and then we use the Style pane to color the inner centers points with a bright green fill color.  In the illustration above we have removed the preview from our previous use of the Transform pane by right-clicking on the blue preview caption bar and choose Hide Preview.


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.


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.



In the above illustration we have Alt-clicked an area in the Mexico drawing to pick it for editing, which automatically launches the Values tab in the Info pane.   We then clicked the one of the coordinates to enter Move Coordinates mode.   If we shift-drag the large edit handle box all of the coordinates will move with it.



That moves the entire area to a new location.



When we right-click and choose Save cChanges, or if we press the Update Record button in the Info pane, 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.   In the illustration above, we have Alt-clicked outside of Mexico to end the picked status of the moved area, to show it without handles on coordinates.

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 Info pane 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.



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.  


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.



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.


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.



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



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.   

Create Labels

We can see that by creating labels from the Centers drawing.



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.



We create a new map, called Map 2, we 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 pane 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 in 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.



We can drag and drop the new Mexico Outlines drawing into another new map, Map 3, 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.  



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.



Immediately, the area object for Oaxaca in the Mexico Outlines drawing also redisplays in red selection color.   



Back in the original map, the Oaxaca area and also the centroid are selected as well.


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.


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


 In the Schema dialog we press the Add button and then choose Field.  



In the Field dialog we enter Borders as the name and geom as the type.  We press Edit Expression.



In the Expression dialog, we enter the expression:




We learned how to use the GeomConvertToLine function by using the Transform pane's Convert to Line template and then pressing the Edit Query button to see the SQL that Manifold creates.   Press OK.



Back in the Field dialog, we press OK.



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 also is a computed field.  


We press Save Changes to apply the change to the table.



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



Dropped into the Map, we use Style to show the Borders drawing in yellow color.



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



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



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:




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




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.


See Also



Computed Fields - Computed fields using the Schema dialog.






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


Transform Pane


Transform Topics


Transform Reference


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: 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: 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 pane 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 pane 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 pane 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.