SQL Example: Learn SQL from Edit Query - Merging Areas

In the Example: Merge : areas (dissolve) topic we used the Transform pane's Merge : areas (dissolve) template to combine multiple areas, grouped by a field value, into a single area.   As convenient as it is to use the Transform pane, we can also do the same thing in an SQL query.

 

This example shows how, and it does so by using a convenient way to learn SQL within Manifold.  Looking at the SQL queries the Edit Query button writes for us is a great way to learn SQL and to learn how to use Manifold SQL functions.

 

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.

 

 

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

 

 

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.

 

 

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

 

 

We then Ctrl-click onto the main area of Bretagne to select it.  Only the larger Bretagne area is selected, with many island areas that are also part of Bretagne are not selected.

 

 

We can see that by Ctrl-clicking on the Region column header to sort the table by that column.   Only one of the many Bretagne records has been selected, the one on which we ctrl-clicked in the map.

 

Our strategy for hacking up the SQL we want is to select a region and then in the Transform pane choose the Merge : areas (dissolve) operation with the Transform selection only box checked.   We then press the Edit Query button to create SQL automatically that implements the transform.   That SQL will include everything necessary to achieve the Merge : areas (dissolve) 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 Merge : areas (dissolve) infrastructure and we are done.

 

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

 

In the Merge template we choose the area (dissolve) operation.  

 

For the Group option, we choose the Region field, which gives the name of the region for each area,  so that all regions with the same name will be dissolved into the same, single ara.

 

For the Result destination, we choose New Table and specify Merged region for the name of the New drawing and an analogous name for the New table.   

 

Check the Transform selection only box.  We want the created query to use the selection

 

Press Edit Query.

 

That opens a Command Window loaded with a query generated by the template.

 

 

The query Manifold created for us is:

 

-- $manifold$

--

-- Auto-generated

--

-- Merge

--   Layer: Regions

--   Field: Geom

--   Merge into: area (dissolve)

--   Group: Region

--   Result: (new table)

--   Result type: geom

--   New drawing: Merged region

--   New table: Merged region Table

--   Resources: all CPU cores, all GPU cores

--   Transform selection only: TRUE

--

 

-- prepare begin

 

CREATE TABLE [Merged region Table] (

  [mfd_id] INT64,

  [Region] VARCHAR,

  [Geom] GEOM,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Geom_x] RTREE ([Geom]),

  PROPERTY 'FieldCoordSystem.Geom' ComponentFieldCoordSystem([Regions Table], 'Geom')

);

CREATE DRAWING [Merged region] (

  PROPERTY 'Table' '[Merged region Table]',

  PROPERTY 'FieldGeom' 'Geom'

);

 

-- prepare end

 

DELETE FROM [Merged region Table];

INSERT INTO [Merged region Table] (

  [Region],

  [Geom]

) SELECT

  [Region],

  GeomUnionAreas([Geom])

FROM CALL Selection([Regions Table], TRUE) GROUP BY [Region];

 

Considering how the query works we can see that the created query is in three parts.

 

The first part is a group of comments that report the options used for parameters in the transform template:

 

-- $manifold$

--

-- Auto-generated

--

-- Merge

--   Layer: Regions

--   Field: Geom

--   Merge into: area (dissolve)

--   Group: Region

--   Result: (new table)

--   Result type: geom

--   New drawing: Merged region

--   New table: Merged region Table

--   Resources: all CPU cores, all GPU cores

--   Transform selection only: TRUE

--

 

The second part creates a table and a drawing with all the housekeeping details required:

 

-- prepare begin

 

CREATE TABLE [Merged region Table] (

  [mfd_id] INT64,

  [Region] VARCHAR,

  [Geom] GEOM,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Geom_x] RTREE ([Geom]),

  PROPERTY 'FieldCoordSystem.Geom' ComponentFieldCoordSystem([Regions Table], 'Geom')

);

CREATE DRAWING [Merged region] (

  PROPERTY 'Table' '[Merged region Table]',

  PROPERTY 'FieldGeom' 'Geom'

);

 

-- prepare end

 

The third part cleans any old data from the table, and then populates the table with the results of a GeomUnionAreas function, taking the input data from the selection and using GROUP BY to group that input data by region:

 

DELETE FROM [Merged region Table];

INSERT INTO [Merged region Table] (

  [Region],

  [Geom]

) SELECT

  [Region],

  GeomUnionAreas([Geom])

FROM CALL Selection([Regions Table], TRUE) GROUP BY [Region];

 

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, 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 Table], TRUE)

 

That line is equivalent to

 

FROM <statement that specifies 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 pane.  

 

 

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

 

With the focus on the map window, in the Select pane we choose the Regions layer and the Region field and then we double-click the Search template to launch it.

 

 

In the Search template we choose text as the Use option.    We choose contains as the Condition option.   

 

From the pull down menu in the Value box we choose Value, to enable us to enter the literal text we want to search for with the contains condition.    We enter the text Bretagne into the Value box.

 

We leave the default none choice for the Trim parameter, meaning we will not trim any characters (including any whitespace characters) from the Name field before making the contains comparison.    

 

We leave the Collation choice at the default neutral, nocase setting, to capture upper and lower case variations of spelling.  We could use the Collation setting to specify a language and language options if we wanted.

 

For Action, we use the default replace selection.

 

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.

 

 

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

  StringContainsCollate([Region], 'Bretagne', CollateCode('neutral, nocase'));

 

We can make it even simpler if we do not care about collations or ignoring case, by using the StringContains function instead of the StringContainsCollate function:

 

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 CALL Selection([Regions], TRUE)

 

portion of the query with...

 

FROM (SELECT * FROM [Regions Table] WHERE

  StringContains([Region], 'Bretagne'))

 

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

 

Selection([Regions], TRUE)

 

portion of text with the pasted SELECT statement, wrapping the SELECT statement within ( ) parentheses.   The Command Window now contains:

 

 

We have added a few characters to indent the WHERE part of the query text we added, to make it more readable.  The query now reads:

 

-- $manifold$

--

-- Auto-generated

--

-- Merge

--   Layer: Regions

--   Field: Geom

--   Merge into: area (dissolve)

--   Group: Region

--   Result: (new table)

--   Result type: geom

--   New drawing: Merged region

--   New table: Merged region Table

--   Resources: all CPU cores, all GPU cores

--   Transform selection only: TRUE

--

 

-- prepare begin

 

CREATE TABLE [Merged region Table] (

  [mfd_id] INT64,

  [Region] VARCHAR,

  [Geom] GEOM,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Geom_x] RTREE ([Geom]),

  PROPERTY 'FieldCoordSystem.Geom' ComponentFieldCoordSystem([Regions Table], 'Geom')

);

CREATE DRAWING [Merged region] (

  PROPERTY 'Table' '[Merged region Table]',

  PROPERTY 'FieldGeom' 'Geom'

);

 

-- prepare end

 

DELETE FROM [Merged region Table];

INSERT INTO [Merged region Table] (

  [Region],

  [Geom]

) SELECT

  [Region],

  GeomUnionAreas([Geom])

FROM (SELECT * FROM [Regions Table] WHERE

  StringContains([Region], 'Bretagne')) GROUP BY [Region];

 

We press the ! run button in the main toolbar to run the query.   

 

The query creates two new components in the Project pane, a table and a drawing to show objects in the table.

 

 

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.

 

 

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 GeomUnionAreas function that we used, the same function used by the Merge : areas (dissolve) transform operation.

 

This example shows how to use Edit Query to learn to write SQL.   We used Edit Query in the Transform pane to learn how to do Merge : area (dissolve) operations in SQL.   We then used Edit Query in the Select pane to learn how to select collections of objects that we can then pass into the dissolve query to merge together.

 

The operative part of the query,

 

DELETE FROM [Merged region Table];

INSERT INTO [Merged region Table] (

  [Region],

  [Geom]

) SELECT

  [Region],

  GeomUnionAreas([Geom])

FROM (SELECT * FROM [Regions Table] WHERE

  StringContains([Region], 'Bretagne')) GROUP BY [Region];

 

shows us how to choose whatever we want with a SELECT statement and then merge those objects together, using GROUP BY to group like objects together in the dissolve.

 

If we want to do a Merge : area (dissolve) operation for all objects in the layer, we could simply write

 

DELETE FROM [Merged region Table];

INSERT INTO [Merged region Table] (

  [Region],

  [Geom]

) SELECT

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

 

Note that the queries written by the Merge : areas (dissolve) operation use GROUP BY as well, which is redundant when we do a selection to select all areas that have the same name, Bretagne, by which we would group.   But the template doesn't know that we will select only those areas.   We might have selected areas for different regions, with different names in their Region field, so it uses GROUP BY to enforce a dissolve operation based on the field that was chosen in the Group box.

Notes

Old Data - The illustrations in this topic 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.   We use the older data because using more regions looks better in illustrations.

 

See Also

Selection

 

Select Pane

 

Transform Pane

 

Transform Reference

 

Topology Overlays

 

GROUP BY

 

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 Expression template in the Transform pane may be used to change the values of fields.  

 

SQL Example: Process Images with 3x3 Filters -  Shows a step-by-step example of developing an SQL query that takes a query written by the Edit Query button and then modifies that query into a general purpose query that can apply any 3x3 filter.   This makes it easy to use matrix filters we find on the web for custom image processing.   We extend the query by using parameters and adding a function, and then show how it can be adapted to use a 5x5 filter.

 

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: Use a Transform Expression to Create Buffers in a Drawing - Use the Expression template in 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 template in 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: Transform Templates, Expressions and Queries - We learn to use a function by clicking on a template in the Transform pane, seeing what it does in a preview, looking at the query Manifold creates and then trying out the function in the Expression tab.