SQL Example: Learning to Union Areas in SQL from Edit Query

In the Example: Union Areas topic we Ctrl-clicked on two different areas to select them, and then we used the Transform panel's Union Areas template to combine them into a single area.   That is not a bad procedure if we do not mind manually selecting areas to be combined but if we have many areas to be combined it would be better to use SQL.

 

This example shows how, and it does so by using a convenient way to learn SQL within Manifold if we are new to Manifold and do not yet have our heads around the system.

 

Important: For simplicity, the following examples do not include a THREADS SystemCpuCount() command in the query.  When writing queries manually using the Command Window we should make sure to add a THREADS SystemCpuCount() command to the query to automatically parallelize the query to use all CPU cores in our system.  See the THREADS command for more info.

 

We start with a map that has one layer, a Regions drawing that shows the regions of France as areas.

 

eg_union_areas01_20.png

 

This drawing shows some regions using many area objects to show the different parts of regions, such as islands.

 

eg_union_areas01_21.png

 

For example, if we open the drawing's table, Regions Table, we see that the many islands off the coast of Bretagne (known as Brittany in English) are each represented by a separate area object, that is, by a separate record in the table.

 

eg_union_areas01_22.png

 

In the Regions 2 drawing we will use Zoom Box (a right-click and drag mouse motion) to zoom closer into Bretagne.

 

eg_union_areas01_23.png

 

We then Ctrl-click onto the main area of Bretagne to select it.

 

Our strategy for hacking up the SQL we want is to select a region and then in the Transform panel choose the Union Areas template with the Restrict to selection box checked.   We then press the Edit Query button to create SQL automatically that implements the template.   That SQL will include everything necessary to achieve the Union Areas operation on the given selection and to create the components that result.   

 

We will use that SQL as something we can edit that does most of what we want.   All we need to do is to replace the part of the query that cites the given selection with whatever it is we want to do by way of a custom selection.   For example, we can write a SELECT query (or have Manifold write it for us) that selects all areas which have Bretagne in their names.   We can take that SELECT query and drop it into what we have copied that creates all of the Union Areas infrastructure and we are done.

 

With the focus on the Regions drawing layer as seen above, with the main area of Bretagne selected, in the Contents pane we choose the Transform panel.

 

eg_union_areas01_24.png

 

Important:  Remember to check the Restrict to selection box.  We want the created query to use the selection.

 

We switch the action button to Add Component.  We press Edit Query to open a Command Window loaded with a query generated by the template.

 

eg_union_areas01_25.png

We see that the created query is in three parts.

 

The first part creates a table with all the housekeeping details required:

 

CREATE TABLE [Regions Table Union Areas] (

  [mfd_id] INT64,

  [Region] VARCHAR,

  [Geom] GEOM,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Geom_x] RTREE ([Geom]),

  PROPERTY 'FieldCoordSystem.Geom' '{ "Axes": "XY", "Base": "WGS 84 (EPSG:4326)", "CenterLat": 0, "CenterLon": 0, "Eccentricity": 0.08181919084262149, "MajorAxis": 6378137, "Name": "WGS 84 / Pseudo-Mercator (EPSG:3857)", "System": "Pseudo Mercator", "Unit": "Meter", "UnitScale": 1, "UnitShort": "m" }'

);

 

The second part creates a drawing using that table:

 

CREATE DRAWING [Regions Table Union Areas Drawing] (

  PROPERTY 'Table' '[Regions Table Union Areas]',

  PROPERTY 'FieldGeom' 'Geom'

);

 

The third part inserts into the table the result of the Union Areas operation, taking the input data from the selection:

 

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

INSERT INTO [Regions Table Union Areas] (

  [Region],

  [Geom]

) SELECT

  First([Region]),

  GeomUnionAreas([Geom])

FROM CALL Selection([Regions], TRUE);

 

Much of the query is nothing special.   It is mostly ordinary housekeeping code such as specifying the coordinate system to be used, making sure the table is created with all the indexes we want, using a PRAGMA to ensure a pretty progress bar in the event so many objects are involved there will be time to show a progress bar and so on.

 

But as mundane as that housekeeping code may be, it nonetheless is essential and even if we are Manifold experts it is more convenient to have Manifold write it for us than to keyboard it ourselves.     Most of the above query we will recycle as is.    What we will change is the single line that specifies the input data upon which all that infrastructure operates:

 

FROM CALL Selection([Regions], TRUE);

 

That line is equivalent to

 

FROM <specify the input data here>;

 

In this case, the input data comes from a CALL to the Selection( ) function to get whatever is selected.  That is the part we will replace with our own SELECT statement.   We will use Manifold to write that SELECT statement for us.

 

We click back onto the Regions drawing layer to make it the active window and then we use the Select panel.  

 

eg_union_areas01_26.png

 

It does not matter that the main part of Bretagne is still selected in the window, since we will be using the panel to write a query and not to make a selection in the window.

 

eg_union_areas01_27.png

 

We choose the Text Contains template and we choose the Region field as the Value field.  In the Search for box we choose the Value setting and then we specify Bretagne as the string to Search for.    We press Edit - Query.  

 

Manifold will open another Command Window (the one we opened earlier is still open) loaded with a query that will implement the template as specified.

 

eg_union_areas01_28.png

 

The result is a very simple query, certainly simple enough that anyone familiar with SQL could write it manually with very little effort.   But there is no harm in having Manifold write it for us if we are still getting acquainted with the system and we want to ensure we miss nothing while learning.

 

The query is a single SELECT statement:

 

SELECT * FROM [Regions Table]

WHERE StringContains([Region], 'Bretagne');

 

That is exactly the same SELECT statement that creates the input data, that is, all records which have Bretagne in their Region field, which we want to feed into the more elaborate query Manifold wrote for us earlier, replacing the

 

FROM <specify the input data here>;

 

line with...

 

SELECT * FROM [Regions Table]

WHERE StringContains([Region], 'Bretagne');

 

...a FROM that uses the input data our SELECT creates.    We copy the SELECT statement above and paste it into the first Command Window we opened, replacing the

 

CALL Selection([Regions], TRUE);

 

call to the Selection( ) function originally used.    The Command Window now contains:

 

eg_union_areas01_29.png

 

We press the ! run button in the main toolbar to run the query.   The result is that two new components, a table and a drawing to show objects in the table, are created in the Project pane.

 

eg_union_areas01_30.png

We can drag and drop the new drawing into our map.   In the illustration above we have double-clicked off the Regions layer, to better see the new drawing.  We see that the new drawing does indeed contain everything that would be selected that has Bretagne as the name of the Region.

 

eg_union_areas01_31.png

 

Opening the newly created table we see that it has one record, which means that all of the different parts we see in the drawing are all part of the same area, the result of the Union Area operation that we wanted.

Using the New Area

Let us now make use of the new area we created.   We will delete the many areas that make up Bretagne in the original Regions drawing and then paste the single new area in their place.

 

We click on the Regions Table to make it the active window.

 

eg_union_areas01_32.png

 

We then use the Select panel.

 

eg_union_areas01_33.png

 

We choose the Text Contains template, enter Bretagne as the Search for string in the Region field and then we press Replace Selection to make that the new selection.

 

eg_union_areas01_34.png

 

In the table we press Delete to delete the selected records.   

 

eg_union_areas01_35.png

 

There are other regions that have multiple area objects, but in this example we have just worked on Bretagne.   Collapsing all areas with the same value in a field into one area is a simple use of SQL we will reserve for another example.

 

eg_union_areas01_36.png

 

Deleting all of the Bretagne records also deletes those objects in the drawing that shows the table.  There is now empty space in the region where formerly Bretagne and offshore islands were drawn.   We will now fill that region with the single Bretagne area our hacked query created.

 

eg_union_areas01_37.png

 

We Ctrl-click on the record handle of the single area in the Regions Table Union Areas and then press Ctrl-C to copy the selected record.

 

eg_union_areas01_38.png

 

We click on the Regions Table and press Ctrl-V to paste the record, seen above as the first row.

 

eg_union_areas01_39.png

 

The new object immediately appears in the Regions drawing that shows the table.   What seem to be many small area objects in the form of islands are all parts of the single Bretagne area object that we created with Union Areas.

Notes

Old Data - The illustrations 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. 

 

Not the Quickest Way - The point of this example is to show how to learn SQL from the Edit Query button.  In real life, we would not hunt for multiple objects containing the same field but would union all of the areas with the same value in a given field at once in the same query:

 

CREATE TABLE [Regions Table Union Areas] (

  [mfd_id] INT64,

  [Region] VARCHAR,

  [Geom] GEOM,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Geom_x] RTREE ([Geom]),

  PROPERTY 'FieldCoordSystem.Geom' '{ "Axes": "XY", "Base": "WGS 84 (EPSG:4326)", "CenterLat": 0, "CenterLon": 0, "Eccentricity": 0.08181919084262149, "MajorAxis": 6378137, "Name": "WGS 84 / Pseudo-Mercator (EPSG:3857)", "System": "Pseudo Mercator", "Unit": "Meter", "UnitScale": 1, "UnitShort": "m" }'

);

CREATE DRAWING [Regions Table Union Areas Drawing] (

  PROPERTY 'Table' '[Regions Table Union Areas]',

  PROPERTY 'FieldGeom' 'Geom'

);

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

INSERT INTO [Regions Table Union Areas] (

  [Region],

  [Geom]

) SELECT

  First([Region]),

  GeomUnionAreas([Geom])

FROM [Regions Table] GROUP BY [REGION];

 

The query above uses GROUP BY to aggregate all regions with the same name for the union.

 

See Also

Selection

 

Contents Pane

 

Contents - Select

 

Contents - Transform

 

Transform Options

 

Transform Templates

 

Transform Templates - Drawings

 

Transform Templates - Geom

 

Transform: Overlay

 

Transform: Overlay Topology

 

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 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: Overlay Contained -  A frequent use of overlays is to sum the values of many points that fall within an area and to transfer that sum to a new field for an area.  In this example we take a drawing that has cities in the US with a population value for each city.  We use Overlay Contained  to sum the population of each city within a state and to transfer that sum to a total population for the state.

 

Example: Overlay Containing - One of the most common uses of overlays is to transfer fields from areas to points that are contained in those areas.    Tasks such as transferring a census block group number or zip code number from a drawing of areas to points that fall within each area are extremely common.   In this example we transfer the name of a French region  to the points that represent cities which fall within each region.

 

Example: Construct JSON String using Select and Transform - Use the Select panel and the Transform panel 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: Transfer Options and Merge Areas - Using the Merge Areas Transform panel template, an exploration of the difference between using Copy and Sum for transfer options.

 

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.

 

Example: Clip Areas with a Transform Expression - Use the Expression tab of the Transform panel 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 Expression - Use the Expression tab of the Transform panel 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: Transfer Options and Merge Areas - Using the Merge Areas Transform panel template, an exploration of the difference between using Copy and Sum for transfer options.

 

Example: Transform Templates, Expressions and Queries - We learn to use a function by clicking on a template in the Transform panel, seeing what it does in a preview, looking at the query Manifold creates and then trying out the function in the Expression tab.