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.

 

Previews in Manifold show in blue preview color what a proposed command will do before we issue that command.  Previews can save us from errors.  When it is obvious what a proposed operation will do we can correct our workflow before committing any changes.

 

We start with a map with one layer, a drawing of regions in France as areas.

 

 

With the focus on the open map window, in the Transform pane the Regions drawing is automatically picked since it is the only layer in the map.   We choose the Geom field, the geometry field, in the drawing and then we double-click the Convert template to launch it in the Transform pane.

 

 

In the Convert template we choose line as the Convert to option.  

 

Press Preview to see a preview.

 

 

The preview shows what that template would do to the drawing in blue preview color, with the preview layer being a virtual layer that appears above all other layers and map contents.  A blue preview caption bar appears at the top of the window, giving the name of the template that is being previewed. Using the line option, the Convert template takes area objects and converts them to lines by drawing a line for the boundary of the area.

 

 

If we would like to see just the preview, without the Regions layer, we can double-click the Regions tab to turn that layer off.

 

We can see what SQL magic is used behind the scenes to accomplish the conversion of areas into boundary lines by pressing the Edit Query button in the Transform pane.

 

 

That launches a Command Window loaded with the SQL query that is used to do the conversion.   The query is simple:  the heart of the query is a single function, GeomConvertToLine([Geom]), which takes the [Geom] field and converts whatever is in it into a line object.   

 

Manifold is full of such useful functions.   In addition to reading documentation and browsing functions within lists in the Query Builder, clicking on templates in the Transform pane to see what they do and then clicking the Edit Query button to see how the SQL works is a great way to learn about functions and how to use them.

 

Now that we know about the GeomConvertToLine([Geom]) function we can use it in our own, custom work.   We copy the function from the Command Window (no need to keyboard when we can copy and paste) and then back in the Transform pane we paste it into an expression.

 

 In the Transform pane we press the Up button to move up one level, so we can choose a different template.

 

We double-click the Expression template to launch it in the Transform pane.

 

 

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

 

 

We enter a single line in that dialog, pasting the query fragment we copied earlier from the Command Window:

 

GeomConvertToLine([Geom])

 

We then click OK to get back to the Transform pane.

 

 

The expression now loaded into the Transform pane uses the same function used by the query we automatically created.  The Expression template is like a blend of the convenience of a point-and-click infrastructure of the Template tab with the custom power of SQL in the Command Window.   

 

Press Preview to see a preview of what the expression does.

 

 

Not surprisingly, since we copied and pasted the function from the SQL created for the Convert template, the preview for the function as evaluated in the Expression template creates exactly the same preview as for the Convert template.  That is OK and cool, but more interesting is that now that we know about the function we can play around with writing a more customized expression using that function.

 

In the Transform pane, we press the Edit Expression button to launch the expression builder dialog again.

 

 

The drawing of provinces has an identification number for each region in the Number field for each record.   We enter an expression that converts to lines only those provinces which have an identification number greater than 30:

 

CASE

WHEN [Number] > 30

  THEN GeomConvertToLine([Geom])

ELSE [Geom]

END

 

Press OK to return to the Transform pane.

 

 

Press Preview to update the preview.

 

Manifold will immediately evaluate the expression to create a preview.

 

 

The preview above shows that all regions which have a Number value greater than 30 will be converted into lines.   Those regions which have a Number value less than or equal to 30 will have their geometry copied in unmodified form, that is, as areas.

 

 

If we would like to see just the preview, we can double-click the Regions tab to turn that layer off.

 

So far, what we have created are just previews.  The actual data has not been modified.   If we like what we see, we can apply the transform by clicking the Transform button.   That will modify the geometry in place within the Geom field.

 

If we prefer, we can save the result into a different Result destination than Same Field.  We can save into a new geometry field in the same table, or we can save to a new table, thus creating a new drawing and table.

 

 

To save to a new drawing and table, in the pull down menu for the Result destination we choose New Table.

 

 

For the New drawing name we enter Regions with lines, and we enter an analogous name for the New table.   We can enter whatever names we want, but it makes sense to use reasonably descriptive names to remind us what the new drawing and table are about.

 

Press Transform to apply the transform template.

 

Instantly, a new Regions with lines drawing and table are created in the project pane.   We can drag and drop the new Regions with lines drawing into the map window.

 

 

The new Regions with lines drawing appears using default formatting.  In the illustration above we have turned off the Regions layer.

 

To Style the new Regions with lines drawing the same as the Regions drawing, in the Project pane we can copy the StyleAreaColorBack property value from the Regions drawing and then paste it into a StyleAreaColorBack property for the Regions with lines drawing.   Styles in Manifold are just snippets of JSON text, so they can be copied and pasted like any other text.

 

 

The illustrations above show the Regions with lines layer and the Regions layer with the other layers turned off, to make comparison easy.

 

See Also

Style

 

Transform Pane

 

Command Window

 

Queries

 

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