Example: Transfer Options and Merge Areas

In this example we utilize the Merge Areas Transform pane template to explore the difference between using Copy and Sum for transfer options.

 

 User interfaces for the Transform pane have been changed.  See the Transform Reference topic for guides to new interfaces while this topic is updated.  Field transfers are now done using the Join dialog and no longer use an Option button in the Transform pane.

 

The transfer Options button appears in the Transform pane when a transform template creates new records or fields.   The Transform Options dialog specifies how values in existing fields are transferred to the greater or lesser number of records.  

 

For example, the Merge Areas transform typically takes multiple records containing one area each and merges them into a single record containing one area that has multiple branches.  The Options dialog allows specifying how values in fields for multiple records will be transferred into the fields for the single record.

 

We will use the same table and drawings created in the Example: Two Drawings from the Same Table topic.

 

 

The example drawing contains three areas, two lines and two points.

 

 

We have modified the table slightly to add a Price field, providing a value for each object that is the "price" of that object.   The prices of the three areas add up to 37.  The prices of all the objects added up is a total of 58.

 

 

With the focus on the open Objects Drawing, we choose the Transform pane.   With Geom as the target field and the source field, we choose the Merge Areas template.   The pane automatically switches the command button to Add Component.  We click the Options button to specify how we would like the other fields to be transferred into the new drawing.

 

 

Seen above are the default settings.   The dialog shows the pattern of names that will be used for the new components that will be created.   These are default names that we can change if we like.

 

The mfd_id and Geom fields have a setting of transfer, which means that the template's logic will handle them.   The Price field has copy as the setting, which means to copy whatever value ends up getting passed along into the record that the template creates.   That is normally whatever is the value in the first record in the case of a template that collapses many records into one record.

 

We will change that, since we would like the value of Price that gets put into the single record created by Merge Areas to be the sum of the prices of the areas that were merged.   We double-click into the transfer method cell for the Price field.

 

 

In the menu we choose sum for the Transfer method.

 

 

We click OK to accept that change to the Options.  

 

 

Back in the Transform pane, we click Add Component.   There is no option to Update Field as most templates offer in the Transform pane because the Merge Areas transform changes the number of records in the resulting table compared to the original table.   It therefore must create a new table to host that different number of records that results.  

 

When we click Add Component the template goes to work and adds two new components to the project, a new table and a new drawing to show the Geom field in that table.   

 

 

By default, the new drawing is called Objects Merge Areas Drawing.  If we open it we see that it contains what appear to be three areas.  It contains no lines or points since those are discarded by the Merge Areas transform template.

 

If we open the newly created table, called Objects Merge Areas, we see that it has only one record.  What appear to be three area objects in the drawing are really a single, multibranched area object that has three branches.   See the Example: Create an Area with Holes and Islands topic for an example of how a single multibranched area can seem to be multiple areas.

 

 

But when we look at the Price field, we may be puzzled to see that it contains the value of 58, which is the sum of the Price values for all of the original records, and not, as we might have thought, 37, the sum of the Price values for only the three original areas.   Why is that?

 

The value of 58 is the literal sum of the Price values for all of the records in the origin table.    When we choose sum as the transfer rule for the Price field we tell the system to sum all of the values in that field, including for those records whose geometry does not end up in a branch within the new record.

Using Edit Query

We can see how the SQL works in the template by clicking the Edit Query button in the Transform pane.  

 

 

If we had clicked the Edit Query button just before pressing the Add Component button in the sequence of steps given above, Manifold would have launched the Command Window populated with the query about to be launched for the template.

 

 

The text for the query above is:

 

-- $manifold$

--

-- Auto-generated

-- Transform - Merge Areas - Add Component

--

CREATE TABLE [Objects Merge Areas] (

  [mfd_id] INT64,

  [Geom] GEOM,

  [Price] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Geom_x] RTREE ([Geom]),

  PROPERTY 'FieldCoordSystem.Geom' '{ "Name": "Orthographic", "System": "Orthographic", "CenterLat": 0, "CenterLon": 0, "Axes": "XYH", "Base": "World Geodetic 1984 (WGS84)", "MajorAxis": 6378137, "Eccentricity": 0.08181919084262149, "Unit": "Meter", "UnitScale": 1, "UnitShort": "m" }'

);

CREATE DRAWING [Objects Merge Areas Drawing] (

  PROPERTY 'Table' '[Objects Merge Areas]',

  PROPERTY 'FieldGeom' 'Geom'

);

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [Objects Merge Areas] (

  [Price],

  [Geom]

) SELECT

  Sum([Price]),

  GeomMergeAreas([Geom])

FROM [Objects Drawing];

 

We can see in the third line from the end that the value of Price is simply generated by Sum([Price]).  

Summing the Price only for Areas

Suppose we want the total Price in the destination table to be the sum of prices only for areas?     There are two ways to accomplish that:

 

 

 

We will consider both, first considering how to modify the query.

Editing the Query

After we have pressed the Edit Query button to take a look at the query the template intends to use, we could modify the generated SQL within the Command Window by adding a single WHERE clause at the very end:

 

-- $manifold$

--

-- Auto-generated

-- Transform - Merge Areas - Add Component

--

CREATE TABLE [Objects Merge Areas] (

  [mfd_id] INT64,

  [Geom] GEOM,

  [Price] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Geom_x] RTREE ([Geom]),

  PROPERTY 'FieldCoordSystem.Geom' '{ "Name": "Orthographic", "System": "Orthographic", "CenterLat": 0, "CenterLon": 0, "Axes": "XYH", "Base": "World Geodetic 1984 (WGS84)", "MajorAxis": 6378137, "Eccentricity": 0.08181919084262149, "Unit": "Meter", "UnitScale": 1, "UnitShort": "m" }'

);

CREATE DRAWING [Objects Merge Areas Drawing] (

  PROPERTY 'Table' '[Objects Merge Areas]',

  PROPERTY 'FieldGeom' 'Geom'

);

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [Objects Merge Areas] (

  [Price],

  [Geom]

) SELECT

  Sum([Price]),

  GeomMergeAreas([Geom])

FROM [Objects Drawing]

WHERE GeomIsArea([Geom]);

 

Seen in the Command Window, the above modification looks like this:

 

 

Clicking the ! run button in the main menu or pressing F5 runs the query.

 

 

The result is value for Price that we expect, the sum of all values for Price for records that were areas.

 

 

Opening the drawing that is created shows that the drawing also is exactly what we expected.

Selecting Areas and Restricting to the Selection

Another way of doing the same thing would be to first select the areas in the source drawing.   We could do that manually for just a few areas by Ctrl-clicking areas to select them...

 

 

...or we could select all of the areas in the drawing using a template in the Select pane, as seen above.

 

 

With the focus on the open Objects Drawing, we choose the Transform pane.  

 

 

The template launches with the Geom field chosen as the target field.   We click on Merge Areas to choose that template.

 

We check the Restrict to selection box and then we click the Options button.

 

Important: For this method to work we must check the Restrict to selection box in the Transform pane.

 

 

In the Options dialog we choose sum as the Transfer method for the Price field and then we click OK.

 

 

Back in the Transform pane we click Add Component.  Manifold goes to work applying the Merge Areas template and creates a new table and a new drawing.

 

If we open the new table, called Objects Merge Areas, we see that the Price value is the sum of the Price values for the originating area records.  

 

Opening the new drawing we see that it, too, is exactly as expected.   

 

 

See Also

Selection

 

Select Pane

 

Transform Pane

 

Command Window

 

Queries