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

 

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.

 

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.

 

We start with a Regions2 drawing that shows the regions of France as areas.

eg_union_areas01_20.png

 

This drawing of regions in some cases utilizes many areas to show the different parts of regions.

 

eg_union_areas01_21.png

 

For example, if we open the drawing's table, Regions2 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 dialog 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 Regions2  drawing as seen above, with the main area of Bretagne selected, we choose Edit - Transform to launch the Transform dialog.

 

eg_union_areas01_24.png

 

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

 

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 [Regions2 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' '{ "Base": "World Geodetic 1984 (WGS84)", "CenterLat": 47, "CenterLon": 2, "Eccentricity": 0.08181919084262149, "MajorAxis": 6378137, "Name": "Orthographic", "System": "Orthographic", "Unit": "Meter" }'

);

 

The second part creates a drawing using that table:

 

CREATE DRAWING [Regions2 Table Union Areas Drawing] (

  PROPERTY 'Table' '[Regions2 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 [Regions2 Table Union Areas] (

  [Region],

  [Geom]

) SELECT

  First([Region]),

  GeomUnionAreas([Geom])

FROM CALL Selection([Regions2], 4, 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([Regions2], 4, 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, as follows:

eg_union_areas01_26.png

We click back onto the Regions2 drawing to make it the active window and then we choose Edit - Select to open the Select dialog.   It does not matter that the main part of Bretagne is still selected in the window, since we will be using the dialog to write a query and not to make a selection in the window.

 

eg_union_areas01_27.png

 

We choose the Text Contains template, we choose the Region field as the Value field and 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 [Regions2 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...

 

FROM (SELECT * FROM [Regions2 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([Regions2], 4, 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

 

If we open the drawing we see that it 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 Regions2 drawing and then paste the single new area in their place.

 

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

eg_union_areas01_32.png

We then choose Edit - Select to launch the Select dialog.

 

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 white 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 Regions2 Table Union Areas and then press Ctrl-C to copy the selected record.

 

eg_union_areas01_38.png

We click on the Regions2 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 Regions2 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. 

 

See Also

Transform Dialog

 

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. 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: 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: Transfer Options and Merge Areas - Using the Merge Areas Transform dialog template, an exploration of the difference between using Copy and Sum for transfer options.

 

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.

 

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