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.

 

 The current build does not do previews.   Those will be added in upcoming builds.

 

Tutorial Videos

See the video tutorials introducing the Select and Transform panes:

 

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. This is easy to add, if necessary, with one click in the Schema dialog. See the Add an Index to a Table topic.

 

Transforms on tile fields that depend on tile placement also require that the table have a spatial index on the picked tile field.

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+6.    

 

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, 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 pull 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.  

 

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 field.  The pane then shows a list of templates that can be used with the data type of that field.  Pick a template to launch it.

 

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

 

 

Component

The name of the component the template is using.  Automatically set to the active window.  When the active window has more than one layer, we can choose the component desired from a pull down list in the box.

Field

The field on which the template operates, also called the source field.   Choose any field from the active component.  When a drawing is the active component, the pane can operate using any field within the drawing's table.  Likewise, when a drawing's table is the active component, the pane can operate using the geometry field in that table, to transform object geometry displayed in the drawing.

Filter

Reduce long lists of templates, and find which template hosts a desired operation by entering text to filter by.  The pane will only show templates that have in their names, or in the names of options they provide, the text entered into the filter box.  

Templates

Templates provide commands that can be done with the chosen type of field.  Templates often include many operations or options within them.   Operations that do similar things or use similar parameters are grouped together within a template, to allow parameter and option choices to persist as much as possible within repetitive or iterative workflow using similar operations. Double-click a template to launch it.

Recent

The ten most recently used templates together with their secondary operational choices appear as shortcuts, such as the contains option within the Search template for text.   Double-click a recently used item to launch it.
 
The list of recently used templates persists within the same Manifold session even if we close the project and open a new project.  However, the list of recently used templates will disappear if we close Manifold and then launch a new Manifold session.

Pinned

Recently used shortcuts appear with a white pushpin.  Pinned shortcuts appear with a black pushpin at the top of the list.  Click a white pushpin to turn it black, and to pin that shortcut to the top of the template list. To unpin a shortcut, click the black pushpin to turn it white.   If the cursor is on a recent or pinned shortcut, the spacebar toggles the pushpin color.
 
Pinned shortcuts will persist at the top of the templates list for fields of that data type, even if we close and restart Manifold in a new session.   

(tooltips)

Hover the mouse cursor over a template to get a tooltip with a brief description of the template, usually providing some examples of operations it can do.

Cursor

Click a template to move the row cursor onto that template.  Move the cursor using the up and down arrow keys.  When we move the cursor onto a template, that puts the focus on that template.

Edit Parameters

Click to launch the template indicated by the cursor.

 

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

Up one level.  Return to the main template list to allow choosing the component or field.  Use this button to choose a field that is a different data type from the current field.

<component name>

Gives the name of the component or layer that the template is using.   Change to any other layer in the same component that also has a field of the current data type.

Field

The subject/source field on which the template operates.   Choose any other field of the same data type.

Operation

Choose the operation option to use within this template.   Some templates can have very many operations.  

<parameter boxes>

Different templates, and different operations within templates, will automatically display the parameter boxes required for the operation.

Unit

Units of measure that can be used for the operation, based on the projection in use by the component.   Projections such as Latitude / Longitude that use angular units of measure will offer a choice of Arc Minute, Arc Second, Degree, or Radian, automatically converting the chosen unit into whatever unit is used by the projection (usually degrees).   Projections such as Pseudo Mercator that use linear units of measure will offer a very long list of all linear units of measurement known to Manifold, with Meter appearing by default.  The template will automatically convert the chosen unit into whatever linear unit (feet, survey feet, etc.) is used by the projection.   This can make it very easy to apply operations like shift: if we want to shift some objects three miles we can choose Mile as the unit of measure and the shift operation using 3 in the  X and Y boxes will shift objects by three miles even if the drawing's projection uses meters or feet for the coordinate system.

Field, Value, or Expression

Value boxes, such as the X and Y boxes for the shift operation, allow us to choose values from a different field, literal values we enter, or values generated by an  expression that is a snipped of SQL.

Collation  

A Collation option appears with some templates applied to text fields, for example, the Replace template that is used for text search and replace operations.   It is primarily used to specify case or no case searches.  Press the collation picker button to choose a different collation, for example, neutral for case sensitive search and replace.   Various collations offer different languages and different settings, such as reckoning accents on characters or not.

Result

Specify the destination for the result of the transform.

 

  • Same Field - Appears as the default when the template creates the same number of records as the source component.  Place the result of the transform into the same field that was used as the source of the transform.  This overwrites prior values in that field with new values that are the result of the transform.

  • Existing Field - Choose an existing field in the same table that is of a compatible data type. A list of existing fields will appear as choices when the template creates the same number of records as the source component.  Place the result of the transform into the specified field.  This overwrites prior values in that field with new values that are the result of the transform.  Tile fields are considered to be compatible when they have the same tile size and the same number of channels.

  • New Field - Create a new field of the specified type in the table, and populate it with the results of the transform, optionally also creating a new drawing (for geometry fields) or a new image (for tile fields).    Appears as a choice when the template creates the same number of records as the source component.   

  • New Table - Create a new table using the name specified in the New table box, and create a new field in that table into which the result of the transform will be placed. Also create a new drawing (for geometry fields) or a new image (for tile fields).  Appears as the only choice when a transform creates a different number of records as the source component.  

 

Result Type

The data type to use for the field that will hold the result.   Appears when results are sent to a new field or a new table and there is an option to choose the data type.   For example, numeric results will usually allow a choice of any numeric data type, such as various integer or floating point numeric data types.   Geometry results will usually allow a choice of Manifold's native geom type, geommfd type, or geomWKB type.  For a tile field, the data type is that of the channel values.

New drawing

Appears when geometry results are sent to a new field in the same table or sent to a new table.  Automatically creates a new drawing of the specified name that uses the resulting geometry field.

New image

Appears when tile results are sent to a new field in the same table or sent to a new table.  Automatically creates a new image of the specified name that uses the resulting tile field.

New table

Appears when geometry or tile results are sent to a new table.  The name to use for the new table.

Resources

A choice of CPU and GPU parallelization resources the system is allowed to use:

 

  • all CPU cores - Allow parallelization up to using all CPU cores (threads) with no use of parallel GPU allowed.
  • all CPU cores, all GPU cores -  Allow parallelization up to using all CPU cores (threads) and parallel use of all GPU cores.
  • one CPU core - Allow use of only one CPU core (thread) with no use of parallel GPU allowed.
  • one CPU core, all GPU cores - Allow use of only one CPU core (thread) and parallel use of all GPU cores.

 

CPU "cores" are used in the Windows meaning of the word core, meaning hyperthread for CPUs that support hyperthreading when hyperthreading is turned on in the BIOS.   Since most modern CPUs and systems support hyperthreading, when Windows reports the number of cores it is really reporting the number of threads.

 

GPU cores are either used fully parallel for all cores or GPU is not used at all.

 

The Resources setting puts limits on what the system is allowed to use.  It does not force parallelization if that would result in slower operation.   Manifold will optimize on the fly to decide whether a given operation at that moment of machine loading and data in use will go faster if parallelized, and, if so, to what degree parallelization would be best.  

 

For example, on a 48 core CPU Manifold will not launch 96 parallel threads to add one plus one to get two, since that would be slower than simply doing the addition in one thread.  The optimizer might decide it is better to run only CPU parallel, or only GPU parallel, or a mixture of both simultaneously.  

Transform selection only

Check to apply the transform only to selected records (objects).    

 

Operations such as clip will in addition have a "selection only" option box, such as Clip with selection only, that allow using only selected objects in the clipping drawing.  

Transform

Apply the transform template.

Edit Query

Pop open a Command Window loaded with an SQL query that accomplishes what this template does using the current settings.

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 identify 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 above, that shows building footprints in Monaco as polygonal areas.  One of the buildings has been selected.

 

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

 

 

The Transform pane loads the Convert template.   We choose line in the Convert to box.  In the Result box we choose New Field, specifying GeomAlt (a good mnemonic, we think, for "alternative geometry") as the name of the new geometry field to be created.   To make it convenient to see that new GeomAlt field, we also 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.

 

What we will do is take the results of the conversion of areas into lines, and put that result into a new geometry field within the same table, and then we also in the same step 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.  It 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.  

 

 

Expression

Write an SQL expression into this pane, either manually or assisted by the expression builder.

Filter

Enter text to be matched, case not significant.   The list of fields,  operators, and functions will be reduced to show only those items which match the text.  For example, entering null reduces the list to only those items that have NULL in their names.

Fields, Operators, Functions

Lists of fields and their types from the table used by the component that is the subject of the Transform pane, along with lists of available SQL operators and functions that may be used in the expression.

Data type

The data type of the targeted field or parameter to which the expression must evaluate.   For example, if the targeted field is a text field, the expression we write must evaluate to a text value.   Writing an expression which does not evaluate to the indicated data type will cause an error message to pop open when we press OK.

 

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.  

 

 

Choose a field

Clicking on the icon for the Value box pulls down a menu offering a typical selection of choices.  We are adding to the Automobiles field, a numeric field, so the pull down menu shows all numeric fields in the table.

 

The pull down menu also allows choice of Value or Expression.   We choose the Buses field that is in the table.

Choosing the Buses field means that we will add the number of buses in each province to the number of automobiles in that province, and place the result into a new field called Transportation.  

 

Enter a literal Value

If we want to specify a literal, specific text value to add to the number of automobiles in each province, we choose Value in the pull down menu.    The small 123 icon indicates it is a numeric literal.  The icon for Value depends on the parameter type, for example, a small Ab icon indicating a text literal and a small 123 icon indicating a numeric literal value.

In the Value box we can then enter a literal value, in the illustration above, entering the number 10000.  Choosing the Value and then specifying the literal value 10000 means that we will add 10000 to the number of automobiles in each province, and place the result into a new field called Transportation.  

Enter an SQL Expression

We can specify the value to be added using an SQL expression.  Choose Expression in the pull down menu to tell the template we will use an SQL expression.  The result of the expression must be the same data type as the source field.

 

Suppose we want to add the number of buses plus the number of trucks to the number of automobiles.

 

We choose Expression and that launches the expression builder dialog.   We enter the expression

 

[Buses]+[Trucks]

 

and then press OK in the expression builder dialog.   The new expression appears in the Value box.

Using that expression means that we will add the number of buses in each province to the number of trucks in each process, and then add that sum to the number of automobiles in that province, and place the result into a new field 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.   

 

 

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:

 

 

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 pull 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 &nbsp; 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.  

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.

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 and apply a template, 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.

 

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 pull 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 - Split Highways

 

See Also

Transform Reference

 

Transform - Expression

 

Transform - Binary

 

Transform - Boolean

 

Transform - Datetime

 

Transform - Geometry

 

Transform - Numbers

 

Transform - Numeric Vectors

 

Transform - Text

 

Transform - Tiles

 

Transform - UUID

 

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 tab of 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: Zoom In to See Transform Previews for Big Images - A short example showing how previews for the Transform pane will appear in large images only when zoomed in far enough so computation of the preview does not cause objectionable delays.

 

Example: Parallel Speed Increase in an Image Transform - A short example illustrating how checking the Allow parallel execution option (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 Expressions tab of the Transform pane may be used to change the values of fields.  

 

Example: Overlay Contained -  A frequent use of overlays is to sum the values of many points that fall within an area and to transfer that sum to a new field for an area.  In this example we take a drawing that has cities in the US with a population value for each city.  We use Overlay Contained  to sum the population of each city within a state and to transfer that sum to a total population for the state.

 

Example: Overlay Containing - One of the most common uses of overlays is to transfer fields from areas to points that are contained in those areas.    Tasks such as transferring a census block group number or zip code number from a drawing of areas to points that fall within each area are extremely common.   In this example we transfer the name of a French region  to the points that represent cities which fall within each region.

 

Example: Overlay Topology Intersect - In this example we use the Overlay : intersect template 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.   The drawing of points we trim will become the US cities drawing that is used in the Example: Overlay Contained topic.

 

Example: Union Areas - Combine multiple area objects into a single area.   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: Learning to Union Areas in SQL from Edit Query - We learn how to write an SQL query that does a custom Union Areas 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 using the Add Component button and also the Edit Query button.

 

Example: Use a Transform Expression to Create Buffers in a Drawing - Use the Expression tab of 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 using the Add Component button and also the Edit Query button.

 

Example: Clip Areas with a Transform Expression - Use the Expression tab of the Transform pane to clip areas in a drawing to fit within horizontal bounds.   Includes examples of using the Add Component button and also the Edit Query button.

 

Example: Smooth Lines with a Transform Expression - Use the Expression tab of 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.