Example: Clip Areas with a Transform Expression

In this example we use the Expression template in the Transform pane to clip areas in a drawing to fit within horizontal bounds.    We create a query in the Command Window that performs the action of the expression by using the Edit Query button.

 

To clip areas using a point-and-click template, and not an SQL expression, see the Clip (Transform) topic.

 

 

We begin with a map that has one layer, a drawing of provinces in Mexico.    The drawing geometry is stored in a geom field in a table called Mexico Table.   The drawing uses the Latitude / Longitude coordinate system, which uses geographic degrees as a unit of measure.

 

 

The drawing has been styled to color the areas using a palette of pastel colors, and the Layers pane has been used to color the background of the map in a calm beige color.

 

Our intention is to "cookie-cut" the area objects seen in the drawing so that any part of any area above latitude 30 is cut off and any part of any area below latitude 20 is cut off.

 

With the focus on the open Map window, in the Transform pane we choose the Geom field.  We double-click the Expression template to launch it in the Transform pane.  

 

 

In the Expression template we click the Edit Expression button to launch the expression builder dialog.

 

 

In the dialog, we enter the following expression:

 

GeomClip([Geom], GeomMakeRect(VectorMakeX4(-120, 20, 0, 30)), true, 0)

 

To learn more about the numbers used or how the expression works, see the discussion  and illustrations near the end of this topic.

 

 

We can write that expression free-hand or assemble it by double-clicking on template parts in the lists of templates and fields that the dialog provides.  

 

How we format the expression, using indents or putting part of the expression on new lines is up to us as white space does not matter in expressions.    SQL normally looks better and reads more easily when indents are used.

 

Press OK to return to the Transform pane.

 

 

To see a preview of what the expression will do, press the Preview button.

 

 

The preview shows in blue preview color  what would be the result in the drawing if the expression were put into effect.   The preview layer is a virtual layer that is drawn above all other contents in the window.   A blue preview caption bar appears in the top of the window, giving the name of the template being previewed.

 

If we would like to change a parameter used in the expression, in the Transform pane we can click the Edit Expression button again, to launch the expression builder dialog.

 

 

We can change the values in the Expression dialog to latitude bounds of 18 and 22, and then we press OK to return to the Transform pane.

 

 

Press Preview to update the preview.

 

 

The new values that are latitude bounds which are closer together will clip even more of the area objects.

 

In the Transform pane, we press the Edit Expression button again, to launch the Expression dialog so we can restore the original latitude values used, of 20 and 30.  

 

 

We make the changes, and press OK to return to the Transform pane.

 

 

Press Preview.

 

 

So far all we have is a preview computed on the fly for display purposes.   The data in the table has not been changed and will not be changed until we press the Transform button.

 

 

 

Instead of changing the data in the table we can save the results into a new component by changing the Result destination to New Table.

 

 

We enter the name Mexico Clipped for 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.  

 

Press Transform.

 

 

That creates a new drawing and table in the Project pane called Mexico Clipped.    We can drag and drop that drawing into our map as a layer.

 

 

The new drawing contains the results of applying the expression to the Mexico drawing's data, but saved into a new drawing without changing the original data.  Since it is a new drawing and has not been styled, it uses default, gray formatting.  In the illustration above we have double-clicked off the lower Mexico drawing layer, to better display the upper layer.

 

Another way to capture the work of the expression without changing the original data is to save it as a query.

 

 

To do that, in the Transform pane we press the Edit Query button.   Therefore, the query we now ask Manifold to create will be a query that does not alter the original data but which creates a new table with the modified data along with a drawing to visualize that data.  The result is a significantly larger query than a query which would simply update the existing table.

 

 

Doing so opens a Command Window that is populated with an automatically generated SQL query that is the equivalent of the expression we wrote in the Transform pane.  We can now modify the query or save it for later use, using Edit - Save as Query.  

 

The full text of the query:

 

-- $manifold$

--

-- Auto-generated

--

-- Expression

--   Layer: Mexico

--   Field: Geom

--   Expression: GeomClip([Geom],GeomMakeRect(VectorMakeX4(-120,20,0,30)),true,0)

--   Result: (new table)

--   Result type: geom

--   New drawing: Mexico Clipped

--   New table: Mexico Clipped Table

--   Resources: all CPU cores, all GPU cores

--   Transform selection only: FALSE

--

 

-- prepare begin

 

CREATE TABLE [Mexico Clipped Table] (

  [mfd_id] INT64,

  [CAM_1991] FLOAT64,

  [TOTVEH_199] FLOAT64,

  [Place_name] NVARCHAR,

  [Geom] GEOM,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Geom_x] RTREE ([Geom]),

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

);

CREATE DRAWING [Mexico Clipped] (

  PROPERTY 'Table' '[Mexico Clipped Table]',

  PROPERTY 'FieldGeom' 'Geom'

);

 

-- prepare end

 

DELETE FROM [Mexico Clipped Table];

INSERT INTO [Mexico Clipped Table] (

  [CAM_1991], [TOTVEH_199], [Place_name],

  [Geom]

) SELECT

  [CAM_1991], [TOTVEH_199], [Place_name],

  (GeomClip([Geom],GeomMakeRect(VectorMakeX4(-120,20,0,30)),true,0))

FROM [Mexico Table] THREADS SystemCpuCount();

 

The query is large because it must create and specify all of the infrastructure, such as the coordinate system to use, required for a new table and a new drawing based on the new table.   The query would be simpler if it was written to update the existing table, that is, to change the original data.

 

 

Suppose in the Transform pane the Result destination was set to Same Field, instead of creating a new component.   In that case, when we press the Edit Query button the resulting query would be written to operate on the existing table:

 

 

Instead of creating a new table and a new drawing the query is a simple UPDATE query as seen above.  The full text of the query:

 

-- $manifold$

--

-- Auto-generated

--

-- Expression

--   Layer: Mexico

--   Field: Geom

--   Expression: GeomClip([Geom],GeomMakeRect(VectorMakeX4(-120,20,0,30)),true,0)

--   Result: (same field)

--   Resources: all CPU cores, all GPU cores

--   Transform selection only: FALSE

--

 

UPDATE (

  SELECT [mfd_id], [Geom],

    (GeomClip([Geom],GeomMakeRect(VectorMakeX4(-120,20,0,30)),true,0)) AS [Geom New]

  FROM [Mexico Table] THREADS SystemCpuCount()

) SET [Geom] = [Geom New];

 

Instead of a block of statements that create a new table and drawing, the query can simply update the existing table.

How the Expression Works

To understand how the clip expression used in this example works, we can take it step by step to see.   We will begin with the view zoomed out slightly.  

 

 

The expression we wrote in the example above was:

 

GeomClip([Geom],

GeomMakeRect(VectorMakeX4(-120, 20, 0, 30)), true, 0)

 

In this case, the GeoMakeRect function on the fly creates a rectangle from the VectorMakeX4 function result which takes one corner as the longitude, latitude (x, y)  coordinate values of -120, 20 and the diagonally opposite corner with longitude, latitude (x, y) coordinate values of -80, 30.   That rectangle is then used by GeomClip to clip the area objects in to only those portions which fall within the clipping rectangle.  The result is to clip off any parts of any areas that extend above 30 degrees latitude or below 20 degrees latitude.

 

We can see how it works by pressing the Edit Expression button in the Transform pane to launch the Expression dialog again.  

 

 

We write a simplified version of the expression, using only the GeoMakeRect function portion of the full expression:

 

GeomMakeRect(VectorMakeX4(-120, 20, -80, 30))

 

Press OK to return to the Transform pane.

 

 

Press Preview.

 

That expression will be immediately previewed in the drawing window, displaying the rectangle that is created by the GeoMakeRect function.

 

 

In the illustration above, we have right-clicked on the blue preview caption bar and have chosen 50% opacity for the preview, so we can see the relationship between the previewed rectangular area and the drawing underneath.

 

The result of the expression is a rectangular area specified by the four arguments to VectorMakeX4 which specify one corner as the longitude, latitude coordinates of -120, 20 and the diagonally opposite corner at -80, 30.   

 

The display shows what appears to be one rectangular area, but in fact it is a stack of many identical rectangular areas, one for each record in the table.  When the Transform pane evaluates the expression it does so for each record.  Therefore, it replaces each area object for the various records, with each area object shaped in the form of the Mexican province it represents, with an area object that is a rectangle created by the expression.

 

We can use a Map window showing both the Mexico drawing and the clipping rectangle as layers to see how the rectangle created by the GeoMakeRect function is then used by GeomClip to clip the area objects in the Mexico drawing to only those portions which fall within the clipping rectangle.  The result is to clip off any parts of any areas that extend above 30 degrees latitude or below 20 degrees latitude.

 

 

To create the illustration above we saved the results of the GeomMakeRect(VectorMakeX4(-120, 20, -80, 30)) expression to a new component, renamed the drawing ClipRect and then created a map with both the Mexico drawing and the ClipRect drawing in it as layers.  The ClipRect drawing layer has 50% opacity set in the Layers pane so we can better see how it overlaps the Mexico drawing below.

 

The illustration shows the coordinates of the corners of the rectangle specified by the VectorMakeX4(-120, 20, -80, 30) function which in turn is used by the GeomMakeRect function to create the clipping rectangle area object.

 

Notes

Where do the magic numbers used in the coordinates come from?  - The latitude values of 20 and 30 are the upper and lower limits above and below which we want to clip away any parts of the Mexico areas.   The Western longitude limit of -120 and the Eastern longitude limit of -80 are completely arbitrary so long as the Western limit is beyond whatever is the furthest Western extent of any of our areas in Mexico and so long as the Eastern extent is likewise beyond whatever is the furthest Eastern extent of any of the areas.   So long as the rectangle extends far enough West and East to cover all the areas to be clipped it does matter what longitudes we use and thus how "wide" the rectangle is.  We are using the rectangle as a "cookie cutter" so as long as the cutter is bigger than the "cookie" we are cutting it doesn't matter how big it is.   

 

If we didn't know the longitudinal boundaries of Mexico but we were sure that all of Mexico was to be found somewhere only in the Western hemisphere we could have just as easily used values of (-179, 20) and (0,30), which would have formed a clipping rectangle between 20 and 30 degrees of latitude that extended from the Prime Meridian at 0 longitude where it cuts through Africa far to the South of Greenwich, England, all the way across the Western Hemisphere to the 179 longitude far out in the Pacific Ocean.  If we were total slackers and had no idea whether Mexico was in the Western or Eastern hemispheres we could have used values of (-179, 20) and (179,30) for the clipping rectangle, which would create a clipping rectangle that extended all the way around the Earth between latitudes 20 and 30.

 

The main reason we used values of (-120, 20) and (-80,30), is that the resulting rectangle fits neatly into a small illustration like the last illustration shown above.  

 

How do we know to use latitudes and longitude numbers?  - The Info pane reports the coordinate system used by a drawing.  When we see Latitude / Longitude as the coordinate system for the Mexico drawing we know that degrees are the units of measure and that numbers such as (-180, 20) and (0,30) will make sense as the geographic degree numbers for the longitudes and latitudes we desire.

 

Another way to see the coordinate system used by the Geom field in the Mexico Table table, and thus by the Mexico drawing, is by right clicking onto Mexico Table in the Project pane and then choosing Properties in the resulting context menu.

 

 

The FieldCoordSystem.Geom property tells us the coordinate system used by the Geom field.  We can right-click onto the cell and choose Edit to see the entire contents.

 

 

Right away we can see that it is a Latitude / Longitude coordinate system and that the units of measure used are degrees.   Properties such as coordinate systems are simply JSON strings in Manifold, that are human readable.

 

Fields inherited - Objects that are created as a result of Transform operations will whenever possible inherit the data content of the fields for that record.   If we replace a state in Mexico with a clipped version of that state, the alteration when we choose Upgrade Field is to the contents of the geom field.   Other fields for that record, for example, any field that gives the population of the state, remain unchanged.  

 

Inner or Outer GeomClip - What does the true argument mean in the GeomClip function?  That specifies the setting for the <inner> argument, whether the clip leaves what is inside the clipping area (when <inner> is true) or whether the clip leaves what is outside the clipping area (when <inner> is false).   For example, when we use true in the expression:

 

GeomClip([Geom],

GeomMakeRect(VectorMakeX4(-120, 20, 0, 30)), true, 0)

 

We get what is inside the clipping rectangle:

 

 

In contrast, when we use false in the expression:

 

GeomClip([Geom],

GeomMakeRect(VectorMakeX4(-120, 20, 0, 30)), false, 0)

 

We get what is outside the clipping rectangle:

 

 

What the different arguments mean in functions is set forth in the various SQL Functions topics.

 

See Also

Style

 

Transform Pane

 

Info Pane: Component

 

Command Window

 

Queries

 

Clip (Transform)

 

SQL Functions

 

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.  

 

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

 

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.