Transform Pane

The Transform pane allows manipulation and editing of values in fields in tables, drawings or images, either by using pre-built transform templates or by using SQL expressions.  For example, by manipulating the value of geometry fields in tables used to drive drawings, the Transform pane manipulates objects in drawings, for example, reshaping them, clipping them or transforming them, like transforming area polygons into border lines for those polygons.  Likewise, by manipulating the value of tile fields in tables used to drive images, the Transform pane also manipulates pixel values in images.  The Transform pane works when any component that is based on a table has the focus, such as tables, drawings, labels or images.

The Transform pane often groups many similar operations within a single template, allowing choice of the specific operation desired after launching the template.  For example, the Reshape template for geometry fields includes operation options such as rotate, scale, shift and similar.    Grouping operations that are similar or have similar parameters allows rapid application and reapplication of similar Reshape functions by changing fewer options or values, without having to repeatedly choose and re-choose parameters that have not changed.

For additional discussion, see topics suggested in the Transform Reference topic and in Transform Topics.

Tutorial Videos

See the video tutorials introducing the Select and Transform panes:

5 Minute Tutorial - Previews

Select and Transform Part 1

Select and Transform Part 2

Index Required

To use the Transform pane the table must have at least one identity index in the table, such as the usual btree index on the mfd_id identity field.  Indexes are added automatically when importing or linking data from GIS formats so normally we do not have to think about this requirement in GIS work.   Occasionally when importing data from tables in non-GIS formats we might have to manually add an index.   This is easy to do with one click in the Schema dialog. See the Add an Index to a Table topic.

Transforms on tile fields in images that depend on tile placement also require that the table have a spatial index on the picked tile field.  A spatial index is automatically added when importing or linking images.  However, if we create an image using programming or some other means that does not create an index, we can add a spatial index on the tiles, normally an rtree index, with a few clicks in the Schema dialog.

Launch, Docking, and Undocking

Switch to the Transform pane by clicking its tab if it is open, by choosing Transform in the View - Panes menu to launch the pane, or by using the keyboard shortcut Ctrl+7.    

Shift-click the pane's name tab to undock the pane.   Shift-click the title bar to dock it again, or right-click the title bar and choose Dock.  An undocked pane can be resized and moved anywhere on our Windows desktop.   Close a docked pane by choosing its tab and then pressing the X button to close.  Close an undocked pane by right-clicking the title bar and choosing Close.  

We can dock the pane to the left and right of our Manifold desktop by right-clicking the name tab and choosing Dock Right or Dock Left.    We can dock the pane in an upper or lower row in our Manifold desktop, above or below other panes, by right-clicking the name tab and choosing Dock Up or Dock Down.   

Operates on the Active Window and Specified Layer

The pane automatically configures for whichever window has the focus when the pane is open.  Click a different window to move the focus to that window and the pane automatically adjusts.  

When working with an active window like a map that has more than one layer, choose the layer desired from the drop down list of components in the topmost box of the pane.  The layer picked in the pane can be different from whatever is the active layer in the map.

The Transform pane opens to a top level list of pre-built commands, called templates, that may be used with the data type of the specified source field.  

Templates will automatically adapt to the data type of the specified source field.  The Filter box can be used to narrow down a long list of templates, or to find a template that does a particular operation.  The Filter box searches within templates for the names of operations within templates, as well as the top level names of templates.

A complete list of templates, organized by the data type of the field with which they work, appears in the Transform Reference topic.  

Two Step Workflow

The Transform pane uses two step workflow:  

Step 1: Pick a layer and a source field.  The pane then shows a list of templates that can be used with the data type of that source field.  

Step 2: Pick a template.  The template opens with options for operations and parameters that work with the data type of the source field.  Without returning to Step 1, we can change parameters, choose a different layer in the map with fields of the same data type, or choose a different template that works on fields of the same data type.

Two step workflow ends up being faster than single step, because much workflow is iterative, doing the same thing to a different field, or making a slight change and applying the same operation.   After the initial step we can rapidly repeat the second step over and over, with easier recycling of settings.  For example, once we choose a numeric field in the first step we can very rapidly apply operation after operation to many different numeric fields in different layers, with most settings remaining the same, persisting into the next operation.  

Pick a Template

Double-click a template within the list to launch that template using the specified component and source field.  We can also repeat-click a template, or focus on that template and press Enter, or focus on that template and press the Edit Parameters button.  That launches the template within the pane.

Templates often provide many operations that can be picked from a pull-down list, like the shift operation in action in the illustration above.    The pane will automatically configure to show options and parameter boxes that work with the chosen operation.  Templates will automatically adapt to the data type of the source field.    

Using the Transform pane:

1. Choose the component to use.  Components that are layers in the active window will be available.
2. Choose a field within that component.   The pane will populate with templates that can be used with fields of that data type.
3. Double-click the desired template.
4. The pane will display parameters for whatever operation in that template is chosen.  Some templates have many operations.
5. Choose the desired operation with parameters as desired.
6. Choose the Action desired if a mode other than replace selection is desired.
7. Press the Transform button.

Picking a template launches it for use, with options and controls appearing as required for the data type of the chosen field and the operation desired.  The name of the template in use will appear at the top of the pane, with controls and parameter boxes appearing in the pane as required.

Typical Controls within a Template

New table

Result Destinations

When presenting available Result destination options, the Transform pane automatically adapts to any restrictions, such as read-only limitations, on possible destinations.   For example, attempting to perform a transform on a computed field or on the built-in mfd_id identity field disables the Same Field result option. Attempting to perform a transform on a table that cannot alter its schema (common for result tables of queries, or for read-only tables in remote databases) disables the New Field result option.

The default name for a new field is set to a blank string to force entering it before running the transform. After the transform is run, the field name is left unaltered so that repeating the transform without any changes quickly fails with a name already in use error and does nothing, alerting the user to alter the name or to choose a different Result destination. The default names for new components likewise are set to blank strings to force entering them, and after the transform is run the component names are left unaltered so that repeating the transform without any changes quickly fails, alerting the user to change names or to choose a different destination.

Indexes in New Result Tables

Sending transform results into a new table automatically creates an autogenerated identity field and a unique index on that field within the new table that is created.   When such new tables are created in a Manifold .map project or data source, the autogenerated identity field and index will be the usual mfd_id and mfd_id_x field and index.   When such new tables are created in some external data source, such as a DBMS, whatever facilities available within that data source will be used to create an autogenerated identity field and unique index.

If the target database does not support autogenerated fields, the transform will fail with an error message.   Manifold supports autogenerated fields in the vast majority of databases to which Manifold can write.   The only notable exceptions are databases that use the Microsoft Jet engine, namely Access and Excel files.

However, for Access and Excel, in any event it is a much better idea to copy data into the Manifold .map project, perform the transform within the .map project, and then copy the result back into the Access or Excel file.   That is because Jet is fragile: performing the transform within the Manifold .map instead of directly on the data stored in place within the Access or Excel file limits the amount of time the Jet database is being written to, which helps ensure its integrity.   That is especially important when the Jet database is being opened from a network share.  

A limitation on creating new tables to accept the results of a transform occurs with tile results:   If the target database does not support spatial indexes that are required for the resulting field, the transform will fail with an error message.  Since at the present time only .map project files support creating spatial indexes on tile fields, this requirement limits the creation of new tables by the transform pane for the results of transforms to new tables within a .map project file.  This limitation may be removed in future builds.   It does not apply to new tables created to host geometry fields added by a transform, since creating new tables with a new geometry field can be done both within .map projects and also within most external databases.

Fields Copied into New Result Tables

When sending transform results into a new table, all fields from the existing source table are copied over into the new table along with the result field, except for geometry and tile fields.

When the result field of a transform is a geometry data type, the new table excludes all other original geometry fields and creates a spatial index only on the new geometry field. When the resulting field is a tile data type, the new table excludes all other original tile fields and creates a spatial index on the new tile field.

The copy/exclude logic arises from the most likely circumstances of workflow.   If in addition to the result of the transform we want to retain all prior geometry or tile fields in the table, which could be a large amount of data, the most effective way to do that is simply to save the result to a new field in the same table.  There is no need to create a new table.

In contrast, if we want to place the result in a new table, it is less likely we care about other geometry or tile fields in the prior table.  For example, it is often the case we may save intermediate transform results as new geometry or tile fields in the same table, and then when we save to a new table we want to start a new chain of operations and results starting with the field saved to the new table, with any prior geometry or tile fields left behind in the old, source table.

If we want to retain all prior geometry or tile fields in a new table, the straightforward way to do that is to first copy and paste the original table to make a copy, and to then apply the transform to that copy, which allows us to save the result as a new field into that table.  

Example

We begin with a drawing, seen below, that shows building footprints in Monaco as polygonal areas.  One of the buildings has been selected.

With the focus on the opened map window, choose the Transform pane.   The buildings drawing is automatically loaded as the subject component.  Choose the Geom field in the buildings drawing, and then double-click the Convert template.

The Transform pane loads the Convert template.   Choose line in the Convert to box.  In the Result box we choose New Field, specifying GeomAlt (a mnemonic for "alternative geometry") as the name of the new geometry field to be created.   To make it convenient to see that new GeomAlt field, enter the name buildings alt into the New Drawing box so the template will automatically create a new drawing that is based on that new GeomAlt field.

Setting up the Transform pane as shown above will take the results of the conversion of areas into lines, and put that result into a new geometry field within the same table.  The template will also create a new drawing on that new geometry field.  In the same table we will have two different geometry fields, one showing the footprints as areas, and the other showing the footprints as outlines, and we will have two different drawings that show the two different geometry fields.  

The above is a convenient way to keep two different geometric representations of the same data within the same table, and also having the convenience of two drawings at hand that can also visually display those two different geometric representations.

We could convert the area objects into lines "in place," that is within the same Geom field, but to preserve our drawing of building footprints as polygonal areas, we will write the results to a new field.  Another option we could use would be to put the results in a separate, new table.   In the Result box we could choose (new table), specifying buildings alt as the name of the New drawing and buildings alt Table as the name of the New table.   We will not do that, because in this case we prefer to keep all the data together in one table, so that both of the different geometric representations in that table share all of the other attribute fields for each object.

Press Transform.

The transform adds a new field to the table for the buildings drawing, fills that field with the results of the transform, and it creates a new drawing called buildings alt in the project pane.   We drag and drop the new buildings alt drawing into the map, and we turn off the buildings layer so we can see the new layer clearly.    

The Transform pane also quickly reports the time the transform took at the bottom of the pane.    The time report is a way of telling us that the transform went into action.  Transforms are so fast in Manifold that it is easy to think nothing happened, and to click the Transform button again.   The time readout lets us know the transform worked.  It will disappear after ten seconds.

The new layer is exactly as expected.  It shows the result of converting polygonal areas into the boundary lines of those areas.   Since selection of a record will also select all objects in that record, including in both drawings if the record has two different geometry fields that power two different drawings, the object selected in the buildings drawing is also selected in the new drawing as well.

To show how quickly we can reuse parameters, in the Convert to box we choose point and in the Result box we choose an existing field in the table, the new GeomAlt field we created in the previous step.  Press Transform and the template populates the GeomAlt field with the results of converting the area objects in the Geom field into points.   Since the buildings alt drawing vizualizes whatever is in the GeomAlt field, it immediately shows the results.

In the illustration at right above, we have turned the buildings layer back on, and in the Layers pane we have set the opacity of the buildings layer to 10%, so it provides some context for points in the layer above without visually dominating the display.

It is time to see what the Transform selection only check box does.   Checking that box will apply the template only to selected objects.

We choose line in the Convert to box, and we check the Transform selection only option.  Press Transform, and only the selected object is converted into a line, with the results being placed in the GeomAlt field and therefore also visualized in the buildings alt layer.   Note that only the selected object has changed.  Because none of the other objects were selected, there are no results from them to overwrite the multipoints that are in the GeomAlt fields for the other records, so they remain unchanged.

Expressions

SQL expressions can be used in the Transform pane either in the Expression template (see the Transform - Expression topic) or to provide a value for some option box where the value is the result of an expression.    Exactly the same Expression dialog is used in both cases to help build the expression.

In the illustration at left above we have chosen the Expression template.   Pressing the Edit Expression button will launch the Expression dialog, allowing us to compose a new expression or to edit an existing expression that may already be in the pane.

In the illustration at right above, we have chosen the Expression option to provide a value for parameter that is used by the Reshape template's shift operation.    Choosing the Expression option also launches the Expression dialog, allowing us to compose a new expression or to edit an existing expression that may already be in that parameter box.

The Expression Dialog

Click the Edit Expression button to launch the Expression dialog, to enter a new expression or to edit an existing expression.   The Expression dialog facilitates writing an expression with Query Builder features.  

The Expression dialog provides two panes within the dialog: an upper pane in which we can write expressions, and a lower pane that shows fields in the table, operators, and functions we can use.  Just as with the query builder in the Command Window, double-clicking on a field or an operator or function will add it to the expression we are building.   Expressions must evaluate to a boolean result to be used for selections.

When we click the OK button to apply edits to an expression, the dialog automatically checks the syntax of the entered expression and pops open an error message on any error.  The dialog also automatically checks the type of the entered expression and pops open an error message if the type is incompatible with the expected type. The expected type for the expression is shown in the left bottom corner of the dialog.

The same Expression dialog is used to write expressions used in parameter boxes.   Expressions shown in parameter boxes are automatically compressed on the fly to maximize room of available display space, which tends to be limited in value boxes.  Comments and unnecessary whitespace are removed, multiple lines are squeezed into a single line, and so forth.  Opening the expression will show the original form, with comments and whitespace restored.  Expressions that consist of multiple terms are also enclosed in parentheses ( ) to enforce computation order in the query text.

Choosing Fields, Values, or Expressions

The parameter boxes for templates in the Transform pane usually allow a variety of ways to choose the data they use.  They usually allow choice of a field, taking the value from whatever is in the specified field, specification of a literal Value, or entry of an SQL Expression.

In the illustrations below we will use the table above, which has numeric fields giving the number of households, automobiles, buses and trucks for provinces in Mexico.   

We will use a typical Select template, the add (+) choice in the Arithmetic template, to add different values to the number of automobiles in each province.   We will take the result and place it into a new field in the table called Transportation.  

Choosing the Same Field for Target and Source

Choosing the same field for both source field and source field tells the transform to take whatever is in the field, apply the desired template or expression and then put the result back into that same field.  We are transforming the field in place.  We can do that so long as we do not change the number of objects in the drawing, so that the result of transforming a record can go back into that same record.

Suppose we have a drawing that shows objects in the Geom field of a table:

We launch the Transform pane and choose Geom as the source field.   

We double-click on the Buffer template and then in the template, enter a Distance of 10.  We leave the Result box at the default setting of (same field).   That means the results we do based on the geometry in the Geom field will be written back into that field.

Press Transform.

Right away, the drawing updates to show the new geometry for each object.  Instead of the starting geometry that showed points, lines and areas, the new geometry is all areas that have their borders ten meters away from the area outlines, or the lines, or the points, of the original objects.

While it is often very convenient to transform fields in place we should keep in mind such changes are permanent with no undo.   An alternative way of making such changes is to make them first to a temporary, scratch pad field as shown in the Example: Two Drawings from the Same Table topic.

Literals in Values and in Expressions

Transforms that provide a combo box into which a field may be entered will also usually allow us to enter either a literal, such as number or a string, or an SQL expression.   However, SQL has slightly different syntax rules for entering literal values.   See the Identifiers, Constants and Literals topic for details on literals.

• To enter a literal value, from the drop down menu choose Value.  We can then enter a value without having to quote the value with single ' quote characters if it is text or # characters if it is a date.

• To enter an SQL expression, from the drop down menu choose Expression.   We can then enter the desired SQL expression, which can reference other fields.   In such expressions, if we use any literal values, we must use SQL syntax for literals and bracket them, for example, using single ' quote characters to bracket text and # characters to bracket dates.

Keep in mind when entering an SQL expression that when entering an expression we are speaking SQL: any literal values used in that expression must use SQL syntax for specifying literals for the desired data type.  For example:

• Datetime values are enclosed in # characters as in #12/15/2016 12:00:00#

• Strings are enclosed in single quote ' characters as in 'Eccentricity'.  

• Numbers cannot start with a decimal point but must have a zero before the decimal point for decimal fractions, as in 0.023.   

The difference between the above two settings can be confusing to beginners, especially in the case of very simple SQL expressions that do the same thing as a literal value.

Consider the illustration above, where we use the Replace template to replace all occurrences of Netherlands with The Netherlands.   The Replace with box has been set to use a Value, as we can see from the Ab "value" icon it shows.   Since the template has been told to use a value, and the template knows a text field is being transformed, the template knows to expect a literal text value and there is no need to use quote characters around The Netherlands.  

Another way to do the same thing is to tell the Replace with box to use an SQL expression by choosing Expression in the drop down menu.  That launches the Expression dialog, into which we enter the very short and simple SQL expression of:

'The Netherlands'

Press OK in the Expression dialog and the above expression appears in the Transform pane, as seen in the illustration above.   We might think of SQL expressions as using SQL statements and SQL functions, but they can also consist of nothing more than simple literal strings within quotes, like the above.  The f 'formula' icon tells us we are entering an expression.

While it would be a bit weird to use SQL expressions for simple search and replace when we could just use the Value without having to bother to use quote characters, there are times when it is convenient to use SQL expressions even for something as simple as search and replace.

For example, suppose our table with the Country field originated somewhere on the web where there are many bits of HTML trash littering the contents, such as the use of   HTML constructions (which means "non-breaking space character") that we would prefer to replace with sensible space characters.    We can enter a space character into the Replace with box as a Value easily enough, but the problem with space characters is that they are not visible when we keyboard them.  It is therefore very easy to forget if the space character has been entered and, if so, to see if it hasn't accidentally been entered twice.  

An easy solution for that is to use an SQL expression that quotes the Unicode \u0020 escaped way of saying "space character."  That will show up in the Replace with box as  ' ', since the space character will be evaluated for display, but it does show the space character within single quotes.   Since we can use an expression in the Search for box as well as the Replace with box, this is a convenient way of searching and replacing other difficult characters, such as line feeds, carriage returns, and other invisible or whitespace characters.

In an expression, we also can take advantage of SQL constants built into Manifold.  

For example, if we do not remember that '\u000d\u000a' is the sequence of escaped Unicode characters meaning a carriage return followed by a line feed, we can simply use the Manifold SQL constant CRLF, which evaluates to those characters.  

Combo Boxes can contain SQL expressions

Tech Tip: A totally cool thing about the Transform pane is that we can write SQL expressions into the combo boxes that appear for various templates.   Most combo boxes will allow us to choose a field from a drop down list of fields of the appropriate type.  We can also enter a fixed value like a number or a string.   But we can also choose Expression and enter an SQL expression using the table's fields.

Counting Starts with Zero

Instead of saying "first things first" computer people might say "zeroeth things zeroeth."  Templates that count steps, such as the character position in a string, use counting that starts with zero:  0, 1, 2, 3, and so on.   The first position, such as the first character in a string, is position 0.   The second character is in position 1 and so on.

Previews

Pressing the Preview button shows a preview in blue preview color of what the transform operation will do, when possible.  For example, when working with table windows if the result is altering an existing field in a table or creating a new field in a table the preview can be shown.  When creating a new table that preview cannot be shown in the existing table.  When working with map windows a preview can be shown when a new component would be created, for example, tracing regions of common pixels in an image to create a new drawing with polygons for those regions.     A preview is just a temporary view and does not change anything.

Previews are persistent within the window in which they are launched, and the Transform pane remembers previews for open windows.    After launching a preview in one map window, we can switch to a different map window or table and do a preview there for a different Transform operation, and the preview in the first window will remain.

Previews in Map Windows

By map windows we mean any visual window, such as a drawing, image, labels, or map window.   We will use the buildings alt drawing created in the prior set of examples.   

The buidings alt drawing is drawing using the GeomAlt geometry field.   We created the GeomAlt geometry field by using the Convert template to convert areas to their boundary lines.    We can now use the Convert template to convert the closed boundary lines into areas, and we can use previews to see what will happen before applying the transform.

Pressing the Preview button shows the previewed results of the Convert : area 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.

If desired, we can change the preview color used by Manifold in the Tools - Options dialog.   This allows us to use a color different than blue preview color in cases where a visual display already uses very much blue color or to provide a more discernible color in case of color blindness.

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.

We check the Transform selection only box, and then we right-click the caption bar and choose Hide Preview.  When not making any changes in the parameters of the template other than changing the selection, we must either refresh the display with View - Refresh or to hide the preview to force a refresh, before pressing Preview again to refresh the preview.   If we make changes other than just checking or unchecking the Transform selection only box, we can press the Preview button to update the preview without  requiring a refresh.

Press Preview.   The new preview shows how only the selected boundary line will be converted into an area.    Since the preview is drawn on top of all other layers, we cannot see the selected boundary line below.  

If we would like to compare the preview with a display without a preview, we can right-click the blue caption bar and then choose either Left or Right to show the preview only for the left or the right half of the screen, and then we can drag the blue vertical dividing line between preview and no preview to the left or right as desired.   This is a convenient way of seeing both the preview and parts of the layers underneath the preview.

Previews will persist until we turn them off, refresh the layer, or delete a layer they use from the map.   We can pan and zoom the view, or do other commands in other panes or dialogs, and the preview will still be there.

For example, in the illustration above we have added a Bing satellite imageserver layer to the project, and then we dragged and dropped the Bing satellite layer into the map.   We noticed that the black lines used for the buildings alt layer were not so visible against the satellite imagery, so we used the Style pane to change them to bright green, thicker lines.    We can do all that and the preview will still remain.

Another way to see layers below the preview is to right-click onto the preview caption bar and to choose partial opacity, for 75%, 50%, or 25% opacity.

50% opacity allows part of the layer below to show through the preview.  As can be seen above, the effect can be combined with left or right split screen.   To make the preview layer fully opaque, choose 100% opacity.

Previews in Table Windows

When the Transform pane operates on a table window pressing the Preview button adds a virtual preview column to the table in blue preview color, with the name of the transform template used in the column head.   Close a preview column by right-clicking the column head and choosing Hide Preview.

The preview column floats over the table window and first appears on the right hand edge of the window.  It can be repositioned next to any column we want by dragging the preview column head. The left edge of the preview column will snap to the boundaries of other columns to make it easier to reposition.    We can also resize the preview column by dragging the vertical boundaries of the column to make it wider or narrower.

Previews are computed using full float64 accuracy even if the subject numbers are lower precision data type.  This may lead to slightly different transform template results as compared to the preview if lower precision data types are used.  For exact matches, use float64 data types and not float32 for floating point numbers.

With the focus on the table window above, in the Transform pane we choose the Units in Stock field and then we double-click the Arithmetic template to launch it.

We enter 2 for the Value and choose the Units on Order field for the Result destination.   The business is getting ready for a Christmas season and wants to have twice as many units on order as it has in stock.

Press the Preview button.

A virtual preview column appears in blue preview color at the right edge of the table, with the template being previewed, Arithmetic, as the column name.  The result destination column, the Units on Order column, is marked with a dot icon to show where the previewed data will go.   We can drag the column head next to any other column, and the column will snap to align to column boundaries when dragged close to a boundary.

In the illustration above we have dragged the preview column near to the Units on Order column and it snaps next to it.   The preview column is a virtual column that floats above the other columns.  If we like, we can position it over other columns, and we can resize the width of the preview column by dragging the side borders.   

For example, we can drag the left border of the preview column to cover part of the Units on Order column if we like.

If we do not want the preview column to be so wide, we could drag the right border of the column inward to reduce the width.  We can do that with the column expanded or minimized.

At any time we can click the up icon to minimize the preview column.

We can drag the minimized column head to a new position, or if we like, we can resize the minimized column head to resize the preview column, and then click the down icon to expand it again.

Seeing both the original values in the destination and also a preview of what will be put into that destination allows easy comparisons.     

Preview Computations are Dynamic

Manually editing records that are used in a preview computation will automatically update the preview column.    Consider the example below, where the preview column shows the result of multiplying the Units in Stock value by 2.  

We can double-click into the Units in Stock cell for the first record to change the value from 39 to 100.

As soon as we press Enter to commit the change, the preview column will update the former value of 78 to 200.

Changes made to tables by other processes, for example, by running an SQL query that makes changes to the table data, will not update the preview.  We can close the preview and launch it again to refresh it.

Previews Persist

When we press the Preview button to command a preview, that preview will continue to appear in the table window even if we change the focus to a different window.   We can have multiple previews running in multiple different windows.   

Significant changes, such as choosing a different template, in the Transform pane will end the preview.  Minor changes such as changing values will not close the preview, but they will not update it.   To update the preview in such settings, press the Preview button again.

The ability to do other work without losing the preview can be very useful.

Consider our example of a preview that shows the result of multiplying the Units in Stock field by 2 and saving the result to the Units on Order field:

It is often the case that we work with tables that have unneeded fields hidden, using the Layers pane.    For example, the table above has had the mfd_id field hidden.  Suppose there are other fields that are hidden which we would like to see, to help us consider the previewed results?

If the table has a hidden Quantity Per Unit field we would like to see, in the Layers pane we can click it on.

The field appears in the table window, but the preview persists and does not disappear.   We can move the preview column as we like to facilitate any comparisons we might want to make.

We might drag the preview column to the left so it snaps into position next to the Quantity Per Unit column.  That makes it easier to compare how many units we propose to put in the Units on Order field and what that means in terms of quantities per unit for various products.

Looking at the row for the Fish Roe product, we see that product ships in the form of twelve 200 ml jars for each unit.  If we put 62 units on order, that means we will have 744 jars of fish roe on order.  Do we really need that many jars of caviar on order?

Geom, Tile, and Binary Fields

Previews in a table window for a transform operation that produces geometry, tile, or binary values in a table window will show only the virtual preview column header, with a tooltip reporting that the preview is unavailable.

For example, suppose we start with an open table for a drawing.   With the focus on the table, in the Transform pane we choose the Geom field and then we double-click the Center template to launch it.

In the Center template we choose inner (area) as the Center operation.  For the Result destination we choose New Field and name the new field Geom centroid, and specifying the name Mexico centroids for the name of the New drawing.

Press Preview.

The preview pops open a virtual, preview column header captioned Center, but it does not show any values for that preview column.   If we move the mouse over the preview column header we see a tooltip that advises us to use the map window if we want to see a preview of the geometry that will be created.

Operations that produce geometry or tile values should be previewed in a map window, since geometry or tile values are visualized in map (or drawing, or tile windows).  

Operations that produce binary values are not previewed because checking whether a particular binary value is intended is impractical without a highly specialized user interface.

Transform Pane Previews / Select Pane Previews

The Select pane also does previews, using blue preview color just like the Transform pane previews do.   We can show either a Transform pane preview or a Select pane preview, but not both at the same time: Transform pane and Select pane previews will override each other.

If we show a preview in a window using the Transform pane and then we switch to the Select pane and press the Preview button in the Select pane, the Select preview will appear and the Transform pane preview will be hidden.  If we then press the Preview button in the Transform pane, the Transform preview will appear and the Select pane preview will be hidden.   

Going back and forth between pressing Preview in the Transform and Select panes will alternate between Transform and Select previews. Whichever pane commands a preview last will win, showing its preview.  

Create a Query Automatically with Edit Query

At any time we can see the SQL used by the Transform pane to implement the template commanded.   To do so we press the Edit Query button and a Command Window will open up that is loaded with an automatically generated SQL query that implements the template with the specified settings.    This allows us to modify the query as desired using the full facilities of the Command Window, to save the query for later use or simply to learn more about how SQL can be used.

For example, if we wanted to see the SQL behind the Buffer template shown earlier in this example, we can press the Edit Query button.

A Command Window will open, loaded with the query.  The query text is:

-- $manifold$

--

-- Auto-generated

--

-- Buffer

--   Layer: Objects

--   Field: [Geom]

--   Distance: 10

--   Unit: Meter

--   Result: [Geom]

--   Resources: all CPU cores, all GPU cores

--   Transform selection only: FALSE

--

VALUE @system NVARCHAR = ComponentFieldCoordSystem([Objects Table], 'Geom');

VALUE @systemScaleXY FLOAT64X2 = CoordSystemScaleXY(@system);

VALUE @unitDegMeter FLOAT64 = CoordUnitScale(CoordUnitByName('Meter'));

VALUE @unitX FLOAT64 = @unitDegMeter / VectorValue(@systemScaleXY, 0);

VALUE @unitY FLOAT64 = @unitDegMeter / VectorValue(@systemScaleXY, 1);

VALUE @unitXY FLOAT64X2 = VectorMakeX2(@unitX, @unitY);

UPDATE (

  SELECT [mfd_id], [Geom],

    GeomScale(GeomBuffer(GeomScaleRev([Geom], @unitXY), 10, 0), @unitXY) AS [Geom New]

  FROM [Objects Table] THREADS SystemCpuCount()

) SET [Geom] = [Geom New];

The query is written with a comments section at the top that shows the parameters that were used.   The query also is structured, for example, using VALUE statements to declare global values, to modularize the query and to help make it more legible and easier to customize.    

Queries that Create New Fields or Components

Queries for transforms that create new fields or new components separate the part of the query that performs one-time setup from the rest of the query that can be run repeatedly.  For example:

To avoid illustrations requiring scrolling to see the whole query, we will not show the Command Window that opens for the above, and will only show the query text.  The query text generated for the above settings would be:  

-- $manifold$

--

-- Auto-generated

--

-- Buffer

--   Layer: Objects

--   Field: [Geom]

--   Distance: 10

--   Unit: Meter

--   Result: [Geom]

--   Result type: geom

--   New drawing: Buffers

--   New table: Buffers Table

--   Resources: all CPU cores, all GPU cores

--   Transform selection only: FALSE

--

-- prepare begin

CREATE TABLE [Buffers Table] (

  [mfd_id] INT64,

  [GML] VARCHAR,

  [Text] VARCHAR,

  [Number] FLOAT64,

  [Name] NVARCHAR,

  [Length] FLOAT64,

  [JSON] VARCHAR,

  [Text_WKT] NVARCHAR,

  [Position] FLOAT64X2,

  [WK binary geometry] VARBINARY,

  [SqMeters] FLOAT64,

  [Dist] FLOAT64,

  [Geom] GEOM,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Geom_x] RTREE ([Geom]),

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

);

CREATE DRAWING [Buffers] (

  PROPERTY 'Table' '[Buffers Table]',

  PROPERTY 'FieldGeom' 'Geom'

);

-- prepare end

VALUE @system NVARCHAR = ComponentFieldCoordSystem([Objects Table], 'Geom');

VALUE @systemScaleXY FLOAT64X2 = CoordSystemScaleXY(@system);

VALUE @unitDegMeter FLOAT64 = CoordUnitScale(CoordUnitByName('Meter'));

VALUE @unitX FLOAT64 = @unitDegMeter / VectorValue(@systemScaleXY, 0);

VALUE @unitY FLOAT64 = @unitDegMeter / VectorValue(@systemScaleXY, 1);

VALUE @unitXY FLOAT64X2 = VectorMakeX2(@unitX, @unitY);

DELETE FROM [Buffers Table];

INSERT INTO [Buffers Table] (

  [GML], [Text], [Number], [Name], [Length], [JSON], [Text_WKT], [Position], [WK binary geometry], [SqMeters], [Dist],

  [Geom]

) SELECT

  [GML], [Text], [Number], [Name], [Length], [JSON], [Text_WKT], [Position], [WK binary geometry], [SqMeters], [Dist],

  GeomScale(GeomBuffer(GeomScaleRev([Geom], @unitXY), 10, 0), @unitXY)

FROM [Objects Table] THREADS SystemCpuCount();

The part of the query which creates a new table and a new drawing is contained within the prepare begin and prepare end comments.  That makes it easy to customize the query to create a new table and drawing the first time we run it, and to then run the query again without that section, to update what was already created.

Using Edit - Query to Change Data Types

Simple modifications in queries created by the Edit - Query button are a great way to accomplish simple tasks like using CAST to change data types, copying data from one data type into another, and creating a new field all in one step.

We start with a table that was copied from a CSV file data source, with the data source created with the Read all fields as text box checked.   We then used the Edit - Schema dialog to add an identity field and index with one click, so the table is editable.

The table shows populations of provinces in Mexico.  One field gives the name of each province, and the other, called PopText, gives the population of that province, but as text, using nvarchar text data type.   The tooltip on a column header shows the data type of that field.   

We would like to create an int32 field that has the population values as integers, not as text.

With the focus on the table window, in the Transform pane we choose the PopText field and then the Copy template.

For the Result destination we choose New Field and enter PopInt32 as the name of the new field.   For the Result type we choose nvarchar.   

We want to create an integer field, not a text field, but since the dialog will not allow us to copy a text type into an integer type, for now we choose a text type, planning on altering that to an integer type when we edit the query.

Press Edit Query.

A new Command Window opens, populated with the query seen above.     The operative portion of the query is:

-- prepare begin

ALTER TABLE [Population] (

  ADD [PopInt32] NVARCHAR

);

-- prepare end

UPDATE [Population] SET

  [PopInt32] = [PopText];

The first part prepares the table by adding a new field of type nvarchar.   The second part does the copy, from one text field to another text field.   

We will change both parts slightly.  In the first part we will change the type from nvarchar to int32, so the new field is created as an integer numeric field, not as a text field.   In the second part we will use a CAST operator to change the data type from text type to int32 type:

-- prepare begin

ALTER TABLE [Population] (

  ADD [PopInt32] INT32

);

-- prepare end

UPDATE [Population] SET

  [PopInt32] = CAST([PopText] AS INT32);

It is that easy.   If we wanted to use some other numeric type instead of int32, for example, int64, we simply would have used that instead.  

The Command Window now shows:

Press the ! run button in the main toolbar to run the query, and the Population table is updated:

A new PopInt32 field appears in the table, of data type int32, with populations copied from the PopText field but now converted from nvarchar text data type into int32 integer data type.

Read Only Components

If the table's schema is altered to remove all writable fields the Transform pane will automatically offer only (new table) as a Result destination, since the same field cannot be updated and a new field cannot be created in the same table.

Dynamic Updates

The Transform pane dynamically updates itself given any changes in the table's schema.  For example, if while a Transform pane is open we choose Edit - Schema and then add a new field to the table the available fields in the Expression tab's expression builder will be updated with the new field, and  the pane will adjust and start offering the new field in lists for parameter values.

If we pick the field for use as a parameter value and then delete it from the table, the pane will adjust again and switch the parameter value referencing the now deleted field to use a constant value or a different field, depending on the parameter.

If we open a map and start adding or removing layers, the panes will automatically adjust to all changes.  Changing the active layer in a map has no effect on the panes, and adding a new layer has a minimal effect:  the new layer becomes available for use but the picked layer does not switch to the new layer.  Deleting a layer only has an effect if that layer was the layer picked in the pane.

If the Transform selection only box is checked, we can dynamically select or unselect items in the subject window and the Transform template will be dynamically applied to the selection as it is.   For an example of that effect, see the Labels topic.

Simultaneous Use of Other Facilities

When we have the focus on an open  window and we open the Select pane or the Transform pane, that pane applies to the window that has the focus.  We can open other windows and do other work with Manifold, going back and forth between the other work and the Select and Transform panes and the panes will automatically adapt. 

Context Saved

Switching between component windows automatically saves and restores the state of the Select and Transform panes for each window. For example, we can open a drawing, start preparing a transform, decide to take a look at the records in a different table, open that table in a new window, and then when we return to the drawing, the Transform pane will be in the exact same state as we left it. We can have several alternative transforms or selects being prepared for different windows and switch between them freely.

Performing a select or transform does not clear the relevant pane: the pane remains in the same state where the operation can be repeated with or without modifications.

Time Readout

Since many operations complete nearly immediately, after clicking the Select or Transform button, we might wonder if we clicked the button.  To show that we did indeed click the button and that the operation has already finished, the pane shows the time it took for the operation to complete next to the clicked button.  After 10 seconds, the time readout disappears, ready to appear with a new readout the next time we click the button.

The pane also logs the time for each operation into the Log window.  The log message includes the names of the used layer and field, the name of the template, and the values of key parameters.

Notes

Zoom to Fit - If we open a drawing and then open the Transform pane, setup a transform operation and then press Preview, if we do not see anything in the drawing it could be that the results of the preview are not in view.  We can do a View - Zoom to Fit command to make sure that we can see all objects in the drawing in the window we are using.

Many layers, many fields - Drop down lists will show up to the first 4000 layers in a map and the first 4000 fields in a table.  It is not productive to create maps with thousands of layers or tables with thousands of fields so the 4000 layers/fields in a drop down list limitation should not be an issue in any rational use.

Protections against NULLs - Many transforms protect against unexpected results when encountering NULL values. For example, attempting to append a text value to a text field using Concatenate treats NULL values as empty strings, in order to keep the field value unchanged instead of turning it into a NULL. Sometimes such a protection is undesirable and whether to have it or not depends on the typical use of the transform. Current protections have been added based on engineering judgement.  Anyone finding some of the protections that have been added undesirable, or finding cases where such protections are absent but are required, should please send in a note.

Pass through of unaffected geometry - Geometry transforms that only make sense for a particular geometry type keep geometry values of other types unchanged whenever this makes sense. For example, reversing lines will keep areas and points unchanged instead of turning them into NULLs.

Uneven X and Y scales are OK - Geometry transforms that operate on distances also automatically compensate for uneven X and Y scales. Previously, if a coordinate system of a geometry field had different scales by X and Y, creating a buffer would create a circle in the coordinate system of the drawing which would become an ellipse if the scales for X and Y were made the same. Same for other distance computations. Now the transforms make the X and Y scales even prior to computing the buffer and then force the computed buffer back to the scales used in the coordinate system. This makes the results of computations independent of the scales used in the coordinate system, which is much more reasonable.

Autoselect in Project pane - Creating a new component using a transform automatically selects that component in the Project pane.

Why do some templates only allow a (new table) Result option?   Only templates which result in changes within the same record, without adding or removing any records, can offer an same field or new field within the same table option.   If a template must create additional records or delete some records it can only be implemented using the (new table) option.   The new component that is created can have a different structure or different number of records than the context component.

A good example is considering the different operation of the Convert to Point template and the Decompose to Coordinates template.  

We begin with our Objects sample drawing above.  

We can see in the illustration above what points would be created by the Convert template with a Convert to setting of point.    The resulting objects are multipoints, that is, branched point objects.  Using the Layers pane, we have set the opacity of the Objects layer to 30% in the illustration above, so the points in the layer above can better be seen.

The illustration above shows the result of the Split template using coordinates as the Split into option.  Both the Split template and the Convert template seem to do the same thing, at least visually, creating a point object at each coordinate which defines an object.  But the Convert : point template allows a choice of either Same Field New Field, or New Table, while the Split : coordinates template allows only the New Table option.  Why?

The answer is the Convert : point template creates multipoint objects, where a single geom in a single record encodes all of the points created for whatever was the original object in that record, while the Split : coordinates template creates a new, standalone point object for every coordinate.   

We can see the difference between the two by using Style to color the points based on their mfd_id field.  Points that are really multipoints, that is, all the same, single object, will be colored the same color, while those points which are parts of different objects will be colored differently.

In the sample Objects drawing, the table has seven records, one record for each of seven objects, with each record containing a geom that specified an area, a line or a point.  The Convert : point template replaces the seven geom values in those seven records with seven altered geom values which encode multipoint objects.    The illustration above shows the result, with multipoints colored by their mfd_id field.   What appear to be three separate point objects at the vertices of the triangular area are in fact just one point object, a multipoint.  The multipoint geom for the triangle shown above with green points contains a multipoint that encodes three points in one geom.

The result of the template for the line below the triangle is a geom with a multipoint that contains the five points at the coordinates of the line, and so on.   The Convert : point template can have a Same Field option because it simply replaces the geom field in each record with an modified geom field.   It does not add or delete records or otherwise change the table's structure.

In contrast, the Split : coordinates template, with results shown in the illustration above, takes a table of seven records and builds a new table that has 27 records, with each record containing a geom that encodes a single point. Each separate point object is colored using a different color in the illustration above.  Because all of the points are colored differently we an see they are all different objects.  There is no Same Field option for the Result of that template, because the template must add records to the table, doing more than simply updating an existing field in existing records.  It must create a new table, so the only option is to have a New Table choice for the result.

Tech Tip: A totally cool thing about the Transform pane is that we can write SQL expressions into the combo boxes that appear for various templates.   Most combo boxes will allow us to choose a field from a drop down list of fields of the appropriate type.  We can also enter a fixed value like a number or a string.   But we can also enter an SQL expression using the combo boxes or the Expression tab, for example, like using the  COALESCE(Status, '') expression  in the source field combo box.   Another example is in the Replace Text, All template listing for text fields, using a CAST expression.  This allows us to combine the convenience of pre-built templates together with the ability to express exactly what we want in a line of SQL.

Units of measure - Some templates use combo boxes within which we can specify a distance or other value using units of measure.  Such templates allow us to specify the unit of measure.

Videos

Select and Transform Part 1

Select and Transform Part 2

5 Minute Tutorial - Previews

5 Minute Tutorial - Split Highways

Create USGS File Names with Transform

Previews in Multiple Windows

Visibility of Towers using a Free Tool

See Also

Panes

Project Pane

Layers Pane

Info Pane

Register Pane

Select Pane

Style Pane

Transform Reference

Transform - Expression

Transform - Binary

Transform - Boolean

Transform - Datetime

Transform - Geometry

Transform - Numbers

Transform - Numeric Vectors

Transform - Text

Transform - Tiles

Transform - UUID

Example: Create USGS File Names with Transform - NAIP images cover almost all of the United States with aerial photography in 4 bands at 1 meter or 0.6 meter resolution.  We would like to download NAIP images for our areas of interest via direct download from the USGS archives on Amazon AWS.  We can create our own indices for NAIP imagery by using the Transform pane to extract and transform the data we want from generic USGS indices for quads and quarter-quads.

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: Create a Drawing from a Geocoded Table - A geocoded table has records with a latitude and longitude for each record.   This example starts with a table containing a list of cities with a latitude and longitude field for the location of each city.   We create a geom from the latitude and longitude fields using a template in the Transform pane and then we create a drawing that shows the cities as points.  This example shows all the infrastructure steps involved.

Example: Automatically Generating CREATE Queries - How to use the Command window to automatically generate SQL in the form of CREATE queries that create a desired component.

Example: Transform Elevation Image to Flatten Bathymetry to Zero - Using the Transform pane  with an image, which contains a single data channel for terrain elevation data for land together bathymetry data for oceans, we use the Expression template in the Transform pane to reset all pixel values less than zero to zero.   This takes all below-zero elevations and sets them to zero, in effect removing bathymetry effects so that ocean areas are represented with zero elevation.  

Example: Parallel Speed Increase in an Image Transform - A short example illustrating how using all CPU cores (on by default) increases speed by a factor of four in a simple use of the Transform pane to modify an image.

Example: Transform Template Units - Templates in  the Transform pane often include parameter boxes using units of measure.  This example uses the Buffer template to show how to change units of measure quickly to whatever units are desired.

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: Overlay : Intersect - In this example we use the Overlay : intersect operation in the Transform pane to trim a drawing of points so that all points which do not fall within areas in a second drawing are deleted.   At the end of the topic, we repeat the operation using the Join dialog in a different approach.

Example: Merge : areas (dissolve) - In this example we combine multiple area objects into a single area object by using the Merge template in the Transform pane, using the areas (dissolve) option.  A drawing of French regions shows some regions as more than one area.  We would like each region to be one area so the table of regions has one record per region.

SQL Example: Learn SQL from Edit Query - Merging Areas - We learn how to write an SQL query that does a Merge : area (dissolve) operation by cutting and pasting from what the Edit Query button automatically generates.

Example: Construct JSON String using Select and Transform - Use the Select and Transform panes 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.

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.

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.