Example: Transfer Options and Merge Areas

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

 

The transfer Options button appears in the Transform dialog 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.

 

The example 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, including a value of 2 for the one point record that is scrolled out of view, is a total of 58.

 

eg_transfer_options01_01.png

 

If we open the Temp Objects Drawing, which visualizes the Tgeom field, we see it is empty.  That is expected since all of the records have a NULL value in the Tgeom field.

eg_transfer_options01_03.png

 

With the focus on the open Temp Objects Drawing we choose Edit - Transform to launch the Transform Dialog.

 

 

eg_transfer_options01_04.png

 

Tgeom is already loaded into target field box at the top of the dialog.   Launching the Transform dialog in the context of a drawing always operates on the geom field from which the drawing is generated.     We choose Geom as the source, value field and then we click on the Options button to specify how we would like the other fields to be specified.

 

eg_transfer_options01_05.png

 

Seen above are the default settings.  The mfd_id and Tgeom fields have a setting of transfer, which means that the template's logic will handle them.   The Geom field and the Price fields both have copy as the setting, which means to simply 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 do not care about the Geom field since what we care about is what ends up being place into the Tgeom field as a result of the template.   But we do care about the Price field.  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_06.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_07.png

 

We click OK to accept that change to the Options.   Note that by default the dialog shows the pattern of names that will be used for the new components that will be created.   These are guides that we can change if we like.

 

eg_transfer_options01_08.png

 

Back in the Transform dialog, 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 Tgeom field in that table.   There is no option to Update Field as most templates offer in the Transform Dialog 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.

 

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.

eg_transfer_options01_09.png

 

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

 

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

 

 

eg_transfer_options01_08a.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_08b.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,

  [Tgeom] GEOM,

  [Price] INT32,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Tgeom_x] RTREE ([Tgeom])

);

CREATE DRAWING [Objects Merge Areas Drawing] (

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

  PROPERTY 'FieldGeom' 'Tgeom'

);

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

INSERT INTO [Objects Merge Areas] (

  [Geom], [Price],

  [Tgeom]

) SELECT

  First([Geom]), Sum([Price]),

  GeomMergeAreas([Geom])

FROM [Temp Objects Drawing];

 

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

 

Note also from the above that Merge Areas is one of the transform templates that does not utilize CPU parallelism, which we can see 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.

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,

  [Tgeom] GEOM,

  [Price] INT32,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Tgeom_x] RTREE ([Tgeom])

);

CREATE DRAWING [Objects Merge Areas Drawing] (

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

  PROPERTY 'FieldGeom' 'Tgeom'

);

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

INSERT INTO [Objects Merge Areas] (

  [Geom], [Price],

  [Tgeom]

) SELECT

  First([Geom]), Sum([Price]),

  GeomMergeAreas([Geom])

FROM [Temp Objects Drawing]

WHERE GeomIsArea([Geom]);

 

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

eg_transfer_options01_08c.png

The illustration above shows the use of the word Area in the filter box.   That reduces the very long list of operators and functions to only those that have the text Area in their names, so if we do not remember the exact name of the GeomIsArea function we can be reminded and we can just double-click on the function to enter it into the query text without having to manually keyboard it.   After all, even if we know SQL very well there is no point in wasting time on manual keyboarding or risking typos when a double-click will do.

 

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

 

eg_transfer_options01_10a.png

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

 

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

eg_transfer_options01_10b.png

 

 

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

 

eg_transfer_options01_11.png

 

Since the Temp Objects Drawing is empty we will select the areas in the Objects drawing.   With the focus on the open Objects drawing we press Edit - Transform to launch the Transform dialog.

 

eg_transfer_options01_12.png

 

The dialog 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: Note the use of Restrict to selection in the dialog above.

 

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 dialog 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 2, 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

Transform Dialog

 

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 dialog 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 dialog 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 Dialog -  How the Expressions tab of the Transform Dialog may be used to change the values of fields.   We include an example of changing the price of selected products and using two different Transform dialogs open at the same time for two different table windows.

 

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: Construct JSON String using Select and Transform - Use the Select and Transform dialogs to manually construct a JSON string using values from other fields in a table. Shows how we can manipulate text to build desired contents in a field.

 

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

 

Example: Use a Transform Dialog Expression to Create Buffers in a Drawing - Use the Expression tab of the Transform Dialog 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.

 

Example: Clip Areas with a Transform Dialog Expression - Use the Expression tab of the Transform dialog to clip areas in a drawing to fit within horizontal bounds.   Includes examples of using the Add Component button and also the Edit Query button.

 

Example: Smooth Lines with a Transform Dialog Expression - Use the Expression tab of the Transform dialog to make lines smoother in a drawing so that longer lines are smoothed more.  Includes examples of using the Edit Query button to show how different queries are created automatically depending on if we want to update a field or to add a new component.