Example: Transfer Options and Merge Areas

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

 

The transfer Options button appears in the Transform panel 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, which are also used in many of the examples for Transform Template topics, including the Transform Templates - Drawings topic in which the Merge Areas template is documented.

 

eg_transfer_options01_01.png

 

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

 

eg_transfer_options01_02.png

 

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.

 

eg_transfer_options01_03.png

 

With the focus on the open Objects Drawing, in the Contents pane we choose the Transform panel.   With Geom as the target field and the source field, we choose the Merge Areas template.   The panel 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.

 

eg_transfer_options01_04.png

 

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.

 

eg_transfer_options01_05.png

 

In the menu we choose sum for the Transfer method.

 

eg_transfer_options01_06.png

 

We click OK to accept that change to the Options.  

 

eg_transfer_options01_07.png

 

Back in the Transform panel, we click Add Component.   There is no option to Update Field as most templates offer in the Transform panel 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.   

 

eg_transfer_options01_08.png

 

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.

 

eg_transfer_options01_09.png

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

 

eg_transfer_options01_10.png

 

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.

 

eg_transfer_options01_08a.png

 

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:

 

eg_transfer_options01_08b.png

 

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

 

eg_transfer_options01_08c.png

 

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

 

eg_transfer_options01_10a.png

 

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

 

eg_transfer_options01_10b.png

 

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

 

eg_transfer_options01_11.png

 

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

 

eg_transfer_options01_12.png

 

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

 

ico_nb_arrow_blue.png 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 panel.

 

eg_transfer_options01_13.png

 

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

 

eg_transfer_options01_14.png

 

Back in the Transform panel 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.  

 

eg_transfer_options01_15.png

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

 

eg_transfer_options01_16.png

 

See Also

Selection

 

Contents Pane

 

Contents - Select

 

Contents - Transform

 

Transform Options

 

Command Window

 

Queries

 

Transform Templates - Drawings

 

Example: Easy Guide to Diversity and Other Transfer Rules - Illustrated examples using Overlay Touching with easily-understood visual diagrams to show the results produced by  Average, Diversity, Diversity Index, Major, Maximum, Median, Minimum, and Sum transfer rules.  No more troubles understanding Diversity!