In this example we combine multiple area objects into a single area object by using the Merge template in the Transform pane, using the areas (dissolve) option. We show two examples using Merge : area (dissolve). The first example uses regions in France that sometimes are shown using multiple area objects. We use Merge to dissolve such multiple areas per region into a single area per region. The second example starts with a world map that shows provinces for countries. We use Merge : area (dissolve) to combine all provinces for a country into a single area object for that country.
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.
We open a drawing called Regions that shows regions in France as they were before the administrative reorganization of 2016, using area objects to represent regions.
As we zoom into the region surrounding the region named Provence-Alpes-Cote d'Azur (henceforth in this topic, Provence), we see what appears to be a small island region within the adjacent Rhone-Alpes region.
We zoom further in and then Ctrl-click onto the small island region to select it.
We also Ctrl-click onto the main Provence region to select that as well.
We can take a look at the selected records by opening the drawing's table.
Opening the Regions Table we see that two records have been selected that correspond to the two areas selected in the drawing.
That tells us that the selection in the Regions drawing window involves two separate objects, that is, two separate rows in the table. Both the larger area conventionally known as Provence as well as the smaller island region within the Rhone-Alpes region are cited in the list as parts of the Provence-Alpes-Cote d'Azur region.
What is going on is that the Provence region really does include a small, unconnected island portion of France that is the canton of Valreas, an isolated bit of Provence within the Rhone-Alpes region, that is a relic of Papal holdings in the area dating back to the 14th century.
We would like to combine the canton of Valreas island portion of Provence with the main area of Provence into a single area object. That is easy using the Merge : areas (dissolve) transform operation.
With the focus on the map window, in the Transform pane we choose the Regions layer and the Geom field, and then we double-click the Merge template to launch it.
In the Merge template we choose the area (dissolve) operation.
For the Group option, we choose the Number field, which gives the number of each province, so that all areas that have the same province Number will be merged together. We could use the Province field to group, so that all provinces with the same name will be grouped together.
For the Result destination, we choose New Table and specify Merged regions for the name of the New drawing. As we enter the name for the drawing, the pane will automatically fill in an analogous name for the table. We can change that if we like.
If we would like to see a preview, we can press the Preview button.
A preview is not particularly useful in this case, since the result of merging the various areas into single, branched objects for each region looks exactly the same as the starting configuration of regions.
Press Transform to apply the template.
A new Merged regions drawing and its table appear in the Project pane. We drag and drop the new Merged regions drawing as a layer into the map window.
In the illustration above, we have Styled the new Merged regions drawing using green fill color, and we have turned off the original Regions layer.
Opening the new drawing's table, we see that it has inherited all of the fields from the original table. However, there is now only one record for each region Number (and thus, for each Province name). Multiple area objects that had the same region number and province name have been merged together into single, multibranched area objects.
We can see that multiple area objects for the same region have been combined into a single object for that region by Ctrl-clicking one of the two parts of the Provence-Alpes-Cote d'Azur region.
Both parts are selected, since they are now part of the same area object.
Opening the table we see the record for the Provence-Alpes-Cote d'Azur region has been selected. We Ctrl-click the column header for the Number field to sort by that field: that shows there is only one record for region number 93, where before there were two.
We start with a drawing called Provinces that shows provinces of various countries in the world. We would like to combine the provinces for each country into a single country area object, to create a drawing that shows countries of the world.
The Provinces drawing is shown above in a map window, with a Bing streets layer below to provide context. The provinces are shown using default, gray formatting.
Opening the drawing's table, we see that each province record has the name of the province and an iso_a2 field that gives the ISO two letter country code for that province. We can merge using the iso_a2 field to merge provinces into the country for each province.
With the focus on the open map window, in the Transform pane we choose the Provinces layer and the Geom field in that layer. We double-click the Merge template to launch it.
In the Merge template we choose area (dissolve) as the Merge into operation. For the Group field, from the pull down menu we choose iso_a2, so that all areas that have the same iso_a2 text code will be merged together.
For the Result destination, we choose New Table and specify Countries for the name of the New drawing and an analogous name for the New table.
To see a preview of what will happen, press the Preview button.
The preview is drawn in blue preview colors above all other layers in the map. A blue preview caption bar appears at the top of the map window, giving the name of the template being previewed. We can right-click the caption bar for a context menu of commands to control the preview display.
In this case, the preview is more useful, because it shows how the various provinces will be aggregated / dissolved into countries. We can see how the provinces are aggregated by using the caption bar context menu to show some of the layers underneath the preview.
For example, if we would like to see layers below the preview, we can right-click the caption bar and choose 75% opacity to render the preview with partial opacity.
That allows layers below to be visible.
We can right-click the caption bar and choose Left or Right to show the preview only on the left or right side of the window, dragging the thin blue vertical separator line left or right to adjust the width of the preview.
We can combine effects from the preview caption bar context menu as we like, to get the effect we find most useful to understand what the preview shows. In the illustration above we have restored 100% opacity to the preview.
If we like what we see, to apply the template we press Transform.
A new Countries drawing and its table appear in the Project pane. We drag and drop the new Countries drawing as a layer into the map window.
The new Countries layer appears in default gray format above the Provinces layer, hiding it. All of the many provinces have been combined into their respective countries, with a single area object for each country.
Opening the Countries Table, we can see how the fields from many different province records per country have been aggregated into single country records. The Merge : area (dissolve) operation uses a simple aggregation method for fields. It simply takes the first value encountered when aggregating multiple records and it uses that first value.
That method works OK for things like the iso_a2 field, where all the values are exactly the same for each country, but produces strange results for the name field, where each country record has whatever was the first name value encountered in a province for that country. That is no problem since it is a simple matter to delete such fields, and it is more valuable to have a very simple merge/dissolve operation than to complicate the dialogs with elaborate choice of how to aggregate fields.
If we want to bring fields from the Provinces table into the new Countries table and to aggregate multiple different values per country into a single value using some rule other than simply copying the first value encountered, we can do that using the Join dialog. The Join dialog has superb, easy to use controls for doing such aggregation, and it takes but a moment after creating the Countries drawing and table to do a quick join with the Provinces table to bring whatever fields we want into the Countries table, using whatever aggregation / transfer method we want.
To Style our new Countries drawing we will add a random number field to the Countries Table.
Using the steps shown in the Computed Fields topic, we add a new computed field called Random using the pseudo-random expression:
(TABLE CALL ValueSequenceRandomInt(1, 300, [mfd_id]))
That generates a random number from 0 to 299 in the new Random field.
This example shows how to fill a column with random
numbers using a computed field. That is useful in situations
where we might be adding more records, and we would like each new record
to automatically have a random number in the computed field. If
we simply want to fill a column with random numbers, but not use a computed
field, we could use the Transform
- Numbers: Random template.
We can use the random number field to thematically format area fill color for the Countries areas:
The thematic formatting uses color to show us how the various countries are different objects. To see how all former province objects have become part of a single country object, we can Ctrl-click onto the continental ("lower 48") part of the United States to select the US.
Right away, all parts of the US, which are now all part of the same area object, are selected. In addition to the Lower 48 portion of the US, Alaska is selected as well as Hawaii and all other parts of the US that are scattered across the world.
In the Countries table the record for the US is also shown in red selection color. In the illustration above, we have chosen View - Filter - Selected in the main menu, to show only selected objects in the table.
The area object in the US record is a complex, multibranched area object with many branches to it that show the various non-contiguous parts of the United States. The name happens to be Minnesota because that was the first province (state) name encountered during the aggregation process
The theme of this example continues in a related example, in the SQL Example: Learn SQL from Edit Query - Merging Areas topic.
Old Data - The illustrations of France in this topic use data from the US military, which show the regions of France as they were before 1 January 2016, when a law passed in 2014 took effect that reduced the number of regions in France from 22 to 13.
New Data - World provinces were downloaded from the Natural Earth website.
The canton of Valreas - The island portion of Provence within the Rhone-Alpes region derives from 14th century Papal holdings when the Popes were resident at Avignon. Pope John XXII purchased Valreas in 1317 and Pope Clement VI added to the Papal properties around Valreas to form what is now the territory of the canton. To this day the canton is more popularly known by locals as the Enclave des Papes, the enclave of the Popes.
The papal enclave surrounding Valreas remained part of the Papal estates until the French Revolution, when the Departments of France were first organized. In 1791 the residents of the enclave in a referendum chose to be part of Vaucluse in Provence, thus creating an island enclave of Provence within the territory of a different department. In modern times the canton retains its ancient fame for Côtes du Rhône wines, truffles and lavender.
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.
Example: Copy one Column into Another Column with Transform - How to use the Transform pane 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 Pane - How the Expressions tab of the Transform pane may be used to change the values of fields.
SQL Example: Learn SQL from Edit Query - Merging Areas - We learn how to write an SQL query that does a Merge : area (dissolve) operation by cutting and pasting from what the Edit Query button automatically generates.
Example: Construct JSON String using Select and Transform - Use the Select pane and the Transform pane 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 Templates - In this example we open a drawing and edit objects in the drawing using Transform pane templates. Includes examples of saving results to a new component and also the Edit Query button.
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 saving results to a new component and also the Edit Query button.
Example: Clip Areas with a Transform Expression - Use the Expression tab of the Transform pane to clip areas in a drawing to fit within horizontal bounds. Includes examples of saving results to a new component and also the Edit Query button.
Example: Simplify Lines with a Transform Expression - Use the Expression template in the Transform pane 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.
Example: Flooded Roads - We consider a hypothetical case of a 10 meter rise in sea level in the San Francisco Bay area, and we find what highways and major roads would be flooded by such a rise. The example uses both raster and vector data sets, combines a number of techniques and uses the Contour, Buffer, Merge, and Clip transform templates.
Example: Create a Bounding Box for Building Footprints with Merge - We would like to create a rectangular area that covers our area of interest, a region for which we have building footprints, that we can use to clip other data. We import a shapefile containing building footprints, and then we use the Merge template to create a rectangle that is the bounding box for all building footprints.