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.

 

eg_transfer_options01_05.png

 

To do that we click on the Price field to highlight it and then in the pull down list 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, 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

 

Notes

Merge Areas not Parallel - A close look at the queries generated by Edit Query in the examples above shows that Merge Areas is one of the transform templates that does not utilize CPU parallelism, at least not yet.   We can see that because there is no THREADS SystemCpuCount() statement in the query.   Some tasks are faster done in a single thread than undertaking the overhead of launching multiple, parallel threads.

 

See Also

Selection

 

Contents Pane

 

Contents - Select

 

Contents - Transform

 

Transform Options

 

Command Window

 

Queries

 

Transform Templates

 

Transform Templates - Boolean

 

Transform Templates - Datetime

 

Transform Templates - Geom

 

Transform Templates - Numeric

 

Transform Templates - Text

 

Transform: Center and Centroids

 

Transform: Escape Templates

 

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: Copy one Column into Another Column with Transform - How to use the Transform panel to copy the contents of one column in a table into another column, but only for selected records.  Uses the Products table from the Nwind example data set.  

 

Example: Transform Field Values using an Expression in the Transform Panel - How the Expressions tab of the Transform panel may be used to change the values of fields.  

 

Example: Union Areas - Combine multiple area objects into a single area.   A drawing of French regions shows some regions as more than one area.  We would like each region to be one area so the table of regions has one record per region.

 

Example: Edit a Drawing with Transform Templates - In this example we open a drawing and edit objects in the drawing using Transform panel templates.  Includes examples of using the Add Component button and also the Edit Query button.

 

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.