In this example we use the Expression template in the Transform pane to create three different sizes of buffers for different lines in a drawing. We then automatically create a query which does the same thing.
We will start with a drawing that shows the locations of Roman roads in Europe and around the Mediterranean as lines. The drawing is shown as a layer in a map with a Bing satellite base layer for background. Major roads have been styled in green and minor roads in yellow, slightly thinner lines.
If we open the table for the drawing we see that there is a CLASS field that describes whether the road is a major or minor road.
Clicking Ctrl-End jumps us to the end of the table. We can see that the values in the CLASS field include the text values Major Road and Minor Road
We will click on the map and then zoom into a region in France center on the town of Bourges where we know that some roads are major roads and some roads are minor roads. Our task is to create buffer zones, that is, area objects, around the major and the minor roads but to create a substantially larger buffer around the major roads instead of around the minor roads.
The point of such a task in archaeological research might be to define target regions for remote sensing searches, perhaps using ground-penetrating radar, with the notion that it may be worthwhile to search larger regions adjacent to major roads where there might be a greater likelihood of finding structures or other remains than with minor roads.
With the focus on the map window, in the Transform pane we choose the Roads layer and the Geom field. We double-click the Buffer template to launch it.
In the Buffer template, we enter 2000 for the Distance, leaving Meter as the unit of measure in the Unit box. The Roads drawing is in a coordinate system that uses linear units of measure so the Unit box pull down menu is loaded with a very long list of linear units of measure, like meters, feet, yards, miles and so on. If the Roads drawing was in Latitude / Longitude, the Unit box pull down menu would be loaded with angular units like degrees, radians, arc minutes, and arc seconds.
For the Result destination, we use the default Same Field setting.
Press Preview to see a preview.
Pressing the Preview button shows the previewed results of the Buffer operation using blue preview colors along with a blue preview caption bar at the top of the window with the name of the template used for the preview. The blue lines in the preview show how boundary lines will be converted into areas.
Previews are shown on top of all map layers. We can toggle the preview off and on by clicking the blue preview caption bar. To close the preview, right-click on the caption bar and choose Hide Preview.
What the preview shows is fine, but we do not want to create the same buffers for both major and minor roads, we want in a single step to create buffers of different sizes for the major roads and the minor roads.
To do that, we click on the Up
button in the Transform pane, to jump up one level so we can choose a
different template.
In the Transform pane, we double-click on the Expression template to launch it. The Expression template allows us to write an SQL expression that will be evaluated and the results of the expression placed into the Result destination.
To write an expression, we press the Edit Expression button to launch the expression builder dialog. The expression builder is similar to the Query Builder dialog for creating queries.
We write the expression in the upper pane. In the lower pane, Manifold shows us all the fields of the table and a list of SQL constructs, such as operators and functions, we can use.
We plan on using the GeomBuffer function. To find it, we start entering the name of the function into the filter box. As we enter Geom the list of functions shown gets reduced to only those which match what we have entered so far in the filter box.
As we get to GeomB the list is reduced even further.
By the time we enter GeomBu that leaves only one function. We double-click on the GeomBuffer function to add it into the Expression window without having to manually keyboard the name of the function.
White space is not significant in the Expression tab, so we can start a new line after the <geom>, argument and then a few space character to indent the next line, which makes the expression we are writing fit better into the illustration and look prettier.
The pane has all the fields associated with the drawing's table so we can save some more keyboarding by double-clicking the <geom> argument in the function template to highlight it. We can then double-click the [Geom] field in the lower pane, to use that field in the GeomBuffer expression without having to keyboard the name of the field.
That automatically enters the name of the field. We then change the <tolerance> argument to a value of 0 (meaning automatic tolerance) and proceed to manually enter the rest of the expression we will use.
In this case we will use the following expression:
GeomBuffer([Geom], CASE [CLASS]
WHEN 'Major Road' THEN 1000
WHEN 'Minor Road' THEN 5
ELSE 0.05 END, 0)
The value of the <distance> argument for the buffer is set by CASE depending on the value of the CLASS field. We will use a distance of 1000 when the value of CLASS is 'Major Road', a value of 5 when the value of CLASS is 'Minor Road' and a value of 0.05 when the value of CLASS is anything else.
Press OK to accept the expression and to return to the Transform pane.
Back in the Transform pane, for the Result destination we choose New Field, and enter the name of the new field, Geom buffer, into the Result box. This will add a new geometry field to the same table, which allows us to create new buffer geometry side-by-side with the existing geometry of roads. The Transform pane creates a New drawing to visualize the new geometry field, which we name Buffer. We can name the new geometry field and the new drawing however we like, but it makes sense to use names that describe what they are.
Press Preview. We are not required to try previews before a transform, but doing a preview can be a great way to verify an expression we have written is doing what we want.
In this case, the previewed results look correct, with different size buffers for different classes of road.
Press Transform.
A new geometry field called Geom buffer appears in the Roads Table. In the Project pane, a new drawing called Buffer appears. We can drag and drop that as a layer into the map window.
In the illustration above, we have turned off the Roads layer, and we have styled the Buffer layer to use orange colors for area fill color and area stroke color.
We can see that larger buffer areas have been created around major roads. The minor roads also have buffers created around them, but in a zoomed out view the difference between buffers which are at a distance of 1000 meters from the line and those which are 5 meters from a line gives the impression there are no buffers on the minor roads.
We can see that effect by zooming far into the drawing and changing the values in the CASE to be used for buffer distances for major roads and for minor roads to 100 and 20. We do that by clicking the Edit Expression button in the Transform pane, editing the expression in the expression builder dialog, and then pressing OK to return to the Transform pane.
The expression in the above illustration is:
GeomBuffer([Geom], CASE [CLASS]
WHEN 'Major Road' THEN 100
WHEN 'Minor Road' THEN 20
ELSE 0.05 END, 0)
In addition to changing the expression, in the pull down menu for the Result destination, we have chosen the Geom buffer field created in the previous transform run.
We press Preview, to check our work before committing the transform.
If we zoom far in to where such smaller buffer distances are visible we see that the buffer areas on the major roads really are bigger than the buffer areas drawn around the lines for the minor roads. We can pan and zoom the map without losing the preview.
Press Transform.
Whatever we save into the Geom buffer field will automatically be visualized by the Buffer drawing.
By saving the results of the transform into the Geom buffer field, we can run the transform however many times we like, and the results will be saved into the Geom buffer field and displayed by the Buffer layer. This is an alternative way of trying out changes, instead of using the Preview button.
For example, we can make the buffers on the major roads, at 3000 meters, very much larger than those on the minor roads, at 5 meters.
The expression in the above illustration is:
GeomBuffer([Geom], CASE [CLASS]
WHEN 'Major Road' THEN 3000
WHEN 'Minor Road' THEN 5
ELSE 0.05 END, 0)
3000 meter buffers are too large to fit into a smaller view, so we zoom back out.
Since previews are fast, we press Preview to see what will happen:
We like what we see, so we press Transform.
If we prefer, we can make the buffers on the minor roads, at 2000 meters, larger than the buffers on the major roads, at 500 meters.
The expression in the above illustration is:
GeomBuffer([Geom], CASE [CLASS]
WHEN 'Major Road' THEN 500
WHEN 'Minor Road' THEN 2000
ELSE 0.05 END, 0)
The Transform pane remembers settings after each operation, so it is easy to click the Edit Expression button, change the expression, and then run the transform again, pressing Preview if we would like a preview, and then pressing Transform.
Press Preview.
Press Transform.
At any time, instead of writing the results to a new field in the same table, if we want, we can either save the results to the original Geom field, or to a New Table, that is, creating a new table and a new drawing.
For example, to add a new drawing and its table, in the Result box pull down menu we choose New Table.
We can then specify the name of the New drawing and New table, in this case entering Small road buffers as the name 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.
That we save the result to a new component does not change our ability to get a preview. We can still press the Preview button:
The preview will be the same as before, since it is exactly the same result, just being saved to a different Result destination.
Press Transform.
A new drawing, named Small road buffers, and its table appear in the Project pane. We can drag and drop the Small road buffers drawing into the map to see the new drawing in context.
In the illustration above, the new Small road buffers drawing appears using default, gray and black colors.
Note that saving the results to a New Table does not change anything in the original data. It simply writes the new objects which are created by the expression into a new table and then creates a new drawing to visualize that table's geometry.
We can Style the newly created drawing of buffers, to use transparent fill color for the buffer areas, so only the area borders appear. We can color those in yellow and green to show minor and major roads.
Objects that are created as a result of Transform operations writing to a new table will whenever possible inherit the data content of attribute fields for each object's record, so we can thematically format a new drawing, like the Small road buffers drawing, using fields like the CLASS field for major road or minor road.
At any time we can see the SQL that the Expression template, and the expression we wrote, uses to work its magic.
We change the name of the Result destination drawing and table, and then press Edit Query. We change the name since Manifold checks the accuracy of the expression it generates against errors, and if we use the name of existing components (which we have just created in the prior steps) the check will show we will try to re-use a name already in use.
When we click the Edit Query button a Command Window will open up that is loaded with the query that the template would execute.
The Edit Query button generates an SQL query in three parts. The first part of the query is a comments block that reports the parameters loaded into the transform operation which were used to generate the query. The text for the second and third parts of the query is shown below:
-- prepare begin
CREATE TABLE [New buffers Table] (
[mfd_id] INT64,
[CLASS] VARCHAR,
[Geom] GEOM,
INDEX [mfd_id_x] BTREE ([mfd_id]),
INDEX [Geom_x] RTREE ([Geom]),
PROPERTY 'FieldCoordSystem.Geom' ComponentFieldCoordSystem([Roads Table], 'Geom')
);
CREATE DRAWING [New buffers] (
PROPERTY 'Table' '[New buffers Table]',
PROPERTY 'FieldGeom' 'Geom'
);
-- prepare end
DELETE FROM [New buffers Table];
INSERT INTO [New buffers Table] (
[CLASS],
[Geom]
) SELECT
[CLASS],
(GeomBuffer([Geom],CASE [CLASS] WHEN 'Major Road' THEN 500 WHEN 'Minor Road' THEN 2000 ELSE 0.05 END,0))
FROM [Roads Table] THREADS SystemCpuCount();
The second part of the query, between the -- prepare begin and -- prepare end comments, creates the destination table and drawing. The third part populates the destination table (and thus the drawing) by running the expression we specified. In the query above, the THREADS statement parallelizes the query, telling the query engine to use as many threads as there are CPU cores available.
We can use this capability to save the query for later use, to edit the query in a more sophisticated way than is convenient in the Expression template or simply to learn more about how SQL can be used.
If we would like to save the query shown in the Command Window as a query component, we choose Edit - Save as Query.
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: 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: 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.