The Edit - Transform dialog allows free-form manipulation of values in fields in tables. By manipulating the value of geom fields in tables used to drive drawings, the Transform dialog also manipulates objects in drawings. Likewise, by manipulating the value of tile fields in tables used to drive images, the Transform dialog also manipulates pixel values in images.
The Transform dialog automatically configures for whichever type of component has the focus when the dialog is launched. If a table window has the focus the Transform dialog will open with the first field in the table loaded as the default target field. If a drawing window has the focus the Transform dialog will open with the geom field of the drawing's table as the default target. If an image window has the focus the Transform dialog will open with the tile field of the image's table as the default target. Templates in the Transform dialog will automatically adapt to the data type of the target field.
The Transform dialog has two modes of operation:
In both cases the alterations can be restricted to just selected records, leaving all other records unchanged.
To alter field values using Transform dialog templates:
To alter field values using Transform expressions:
Previews are great. Once you see them you cannot do without. For drawings, previews compute the operation for objects within the display to indicate what the Transform will do. In most cases Manifold will compute the preview so fast that the display is instantaneous, but with bigger data it can take a few seconds to compute the preview. When using compute-intensive templates or expressions with really big data that shows many objects in the display a preview can take a long time, even minutes.
Tech Tip: To eliminate such delays, zoom in to the drawing so only a few objects are in view before you click the template. The preview will then be instantaneous. Do not open a drawing with a billion objects in it, zoom to fit so that all are in view and then command an expression that takes hours compute. Zoom in to just a few objects and the preview will still be fast, even if the entire job would take hours to do. Remember, Manifold speed and parallelism are great but parallelism is not a magic wand: there are always cases of data that is so big and computations so demanding that even Manifold can take a long time to do the job. That Manifold can do huge jobs in hours that take weeks in other packages is cool, but still, we should plan our time so those hours happen overnight and not when we are sitting at the monitor.
The Transform dialog will open with the Template tab by default. The Template tab provides a list of pre-built selection commands, called templates., which are listed and discussed in the Transform Templates topic. Clicking on a template will highlight it and will open combo boxes that allow us to choose fields and to specify values we would like to use for that template.
Combo boxes generally come pre-loaded with a pull down list of fields in the table and by default contain the name of the first field in the table by alphabetical order. This makes it easy to choose any field in the table to use with a template without having to keyboard the name of the field. Combo boxes also generally allow us to manually enter a specific value, such as a field name or a number, or an SQL expression.
Target Field |
The target field into which the results of the template or expression will be placed. |
Target Field Type |
Normally read-only: reports the data type of the target field.
|
Set Field Values |
Appears only when the dialog is used to add a new field. Allows specifying the field values. |
Template / Expression Tabs |
Choose between pre-built templates or writing a free-form SQL expression |
Templates |
A list of templates that can be used with the context component and the data type of the given target field. Template icons show the effect of their action:
A record icon indicates the template operates on one record. Such templates offer both Update Field and Add Component actions.
A table icon indicates the template operates on a table, adding or deleting records. Such templates offer Add Component only. |
Source Field |
Most templates take a source, or value field from which they take the values upon which they operate. If we want to operate on a field "in place" we choose the same field for the source field as for the target field. For example, if we want to replace the contents of a geom field with buffers built upon whatever objects were in that field we would choose the same field name for both source and target. |
Additional Fields or Parameters |
Some templates will open up additional combo boxes to allow us to enter additional fields or parameters the template needs for its work. For example, the Buffer template opens up Distance and Tolerance boxes to allow those parameters to be specified. |
Options |
Appears when the action button is set to Add Component. Specify the names of components that will be created and how fields will be transferred into the new components. |
Restrict to Selection |
If checked the transform template or expression will apply only to selected records. |
Allow parallel execution |
If checked (default) the template will allow execution using CPU parallelism in multiple threads with as many CPU cores as are available, providing faster performance. Checking this box is equivalent to commanding THREADS SystemCpuCount() in SQL in templates that utilize parallelism. Note that it does not make sense for all templates to utilize parallelism. |
Update Field / Add Component |
Choose either setting by pulling down on the down arrow button.
|
Edit Query |
Click to open the Command Window pre-loaded with an SQL query which accomplishes what the template does given all chosen settings for target field, source field and any additional fields or parameters. |
Close |
Close the dialog |
Clicking the Expression tab in the Transform dialog allows us to alter the contents of fields using SQL expressions that we can build with assistance similar to the Query Builder in the Command Window for SQL.
The Expression tab provides three panes within the dialog: an upper pane in which we can write expressions, a middle pane that shows fields in the table and a lower pane that shows 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.
Target Field |
The target field into which the results of the template or expression will be placed. |
Target Field Type |
Normally read-only: reports the data type of the target field.
|
Set Field Values |
Appears only when the dialog is used to add a new field. Allows specifying the field values. |
Template / Expression Tabs |
Choose between pre-built templates or writing a free-form SQL expression |
Expression |
Write an SQL expression into this pane, either manually or assisted by the expression builder by double-clicking onto fields or operators/functions in the other panes to add them to the expression. |
Available Fields |
A list of fields and their types from the table used by the component that is the subject of the dialog. |
Filter Box |
Enter text to be matched, case not significant. The list of operators and functions below 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. |
Available Operators and Functions |
A list of operators and functions that may be used in the expression. |
Options |
Appears when the action button is set to Add Component. Specify the names of components that will be created and how fields will be transferred into the new components. |
Restrict to Selection |
If checked the transform template or expression will apply only to selected records. |
Allow parallel execution |
If checked (default) the template will allow execution using CPU parallelism in multiple threads with as many CPU cores as are available, providing faster performance. Checking this box is equivalent to commanding THREADS SystemCpuCount() in SQL in templates that utilize parallelism. Note that it does not make sense for all templates to utilize parallelism. |
Update Field / Add Component |
Choose either setting by pulling down on the down arrow button.
|
Edit Query |
Click to open the Command Window pre-loaded with an SQL query which accomplishes what the expression does. Although this option is primarily used with templates, using it with an expression allows use of the somewhat broader query builder facilities in the Command Window. |
Close |
Close the dialog |
A combo box is computer dialog jargon for a box like those used to choose the target field or source field. It is called a combo box because it allows a combination of different ways to specify the value entered into the box.
Choose a field from a pull down list of available fields |
|
Enter a literal value |
When we enter a literal value we must use SQL style to specify the literal value. For example, if the field we are using is a text type, such as nvarchar, we must enter the desired text within single ' quote characters as seen above. |
Enter an SQL Expression |
Yep, this is truly cool, a small but seriously intense detail in Manifold. The above expression takes the contents of the Date field, extracts the day of the month as a number and casts it into text form so it can be used within a text field. |
The button at the bottom of the Transform dialog tells the dialog what to do with the results of a transform template or expression. To commit those changes we must press the Update Field button.
Instead of making changes to the original table we can write the changes the Transform dialog would make to a new component. This will create a new component that contains a copy of the original component along with the changes made by the template or expression. The original table is left unchanged.
To do so we change the Update Field action in the pull-down menu to Add Component.
We can then click on Add Component to save changes to a new component, leaving the original data unmodified.
When the Transform dialog is applied to a table a new table with modified records will be created in the Project pane. When the dialog is applied to a drawing, two new components, a new table and a drawing for that table, will be created in the Project pane.
Some templates, primarily for use with drawings, such as the Decompose to Coordinates template for use with geoms in drawings, do not offer an Update Field option but only offer an Add Component option.
The Transform Options dialog becomes available in the Transform dialog when the action button is set to Add Component, as some transform templates allow. Templates which do not allow changing the action button from Update Field to Add Component do not use Transform options. Transform options control how fields from the target and overlay drawings are brought into the resulting modified target drawing. A classic example of using Transform options is with overlay templates.
Transform options allow specifying:
The Transform Options topic for details.
Yet another cool deal: When a template is selected or an expression is entered, as soon as enough of the expression is entered create a valid SQL expression the Transform dialog will show a preview of what will be done, if possible, using blue preview color. What is displayed is just a preview that will be abandoned if some other template is chosen, a different expression written or the Close button clicked. The specified template or expression will only take effect if the Update Field button is pressed.
The preview is shown as soon as a Template has been chosen or as soon as enough of an Expression has been written to be evaluated.
If a preview does not appear for a transform applied to an image, we should zoom further into the image to reduce the amount of data that must be computed for a preview.
Important: The Transform Dialog can show previews for its work in a drawing if there is at least one object in the drawing. Drawings can not host previews if they are empty, that is, where there are no records in the associated table or where the values for all records in the geom field the drawing uses are NULL values. That is true even if the preview would involve filling the NULL values with non-NULL geom values.
Choosing the same field for both target field and source field tells the dialog to take whatever is in the field, apply the desired template or expression and then put the result back into that field. We are transforming the field in place.
Suppose we have a drawing that shows objects in the Geom field of a table:
We launch the Transform dialog and choose Geom as the target field.
We then click on the Buffer template and choose Geom also as the source field, using a Distance of 10.
Right away, the dialog shows a preview in blue preview color of what will happen if we click the Update Field button to apply the transform.
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.
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 expression, such as a SQL expression.
When specifying values in option boxes, such as for source fields or comparisons, we can usually either choose fields of appropriate data types from the pull-down list or we can specify fixed values manually to use for all records. When specifying literal values, we use SQL style for specifying literals for the desired data type. For example:
Tech Tip: A totally cool thing about the Transform dialog 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 table's fields, for example, like the COALESCE(Status, '') expression used in the source field combo box in the Concatenate template listings for text fields. 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.
See the various examples in the Transform Templates topics.
At any time we can see the SQL used by the Transform dialog to implement the template or expression 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 or expression in use.
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.
Note that queries created by the Transform dialog are automatically created within the Command Window but they are a different way of creating queries automatically than the mechanism describe in the Example: Automatically Generating CREATE Queries topic.
The Transform dialog dynamically updates itself given any changes in the table's schema. For example, if while a Transform dialog 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. If the table's schema is altered to remove all writable fields the Transform dialog will automatically close, since it cannot work with tables that do not have at least one writable field.
When we have the focus on an open table window and we open the Transform dialog with an Edit - Transform command, that Transform dialog works with that particular window. At the same time we can open other windows and do other work with Manifold, going back and forth between the other work and the Transform dialog.
We could even open another table window for that same table and open a second Transform dialog for that second window. We could go back and forth between the two windows and their Transform dialogs and completely independently of each other preview different templates or expressions. We can do that with as many windows and open Transform dialogs as we want.
Zoom to Fit - If we open a drawing and then open the Transform dialog 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.
Why ever uncheck Allow parallel execution? - It does not always make sense to utilize all CPU cores. There are three classic examples why we might want to uncheck this box:
Why do some templates only allow an Add Component Option? Only templates which result in changes within the same record can offer an Update Field option. If a template must create additional records or delete some records it can only be implemented using an Add Component 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.
Looking at the previews both seem to do the same thing, creating a point object at each coordinate which defines an object. The illustration at right would be the preview for either template. But the Convert to Point template allows a choice of either Update Field or Add Component, while the Decompose to Coordinates template allows only the Add Component action. Why?
The answer is that Convert to Points 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. In the example drawing the table started with seven records, one record for each of seven objects. Three of the records contained geom values encoding areas, two records contained geom values for lines and two records contained geom values for points.
The Convert to Points template replaced the seven geom values in those seven records with seven altered geom values which encode multipoint objects. The multipoint geom for the triangle shown above with red points contains a multipoint that encodes three points in one geom. The record for the line below it has a geom with a multipoint that contains the five points at the coordinates of the line, and so on. The Convert to Points template can have an Update 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 Decompose to Coordinates template takes a table of seven records and builds a new table that has 23 records, with each record containing a geom that encodes a single point. There is no option to simply Update Field because the template must do more work than simply update an existing field in existing records. It must create a new table, so the only option is to have an Add Component choice.
Transform Templates - Drawings
Transform Templates - Datetime
Transform: Center and Centroids
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 dialog 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 dialog 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 dialog 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 dialog 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 dialog 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 dialog to modify an image.
Example: Copy one Column into Another Column with Transform - How to use the Transform dialog 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 Dialog - How the Expressions tab of the Transform dialog may be used to change the values of fields. We include an example of changing the price of selected products and using two different Transform dialogs open at the same time for two different table windows.
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 Topology, Intersect template in the Transform dialog 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: Transfer Options and Merge Areas - Using the Merge Areas Transform dialog template, an exploration of the difference between using Copy and Sum for transfer options.
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 dialogs 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 Dialog Templates - In this example we open a drawing and edit objects in the drawing using the Transform dialog Template tab. Includes examples of using the Add Component button and also the Edit Query button.
Example: Use a Transform Dialog Expression to Create Buffers in a Drawing - Use the Expression tab of the Transform Dialog 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 Dialog Expression - Use the Expression tab of the Transform dialog 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 Dialog Expression - Use the Expression tab of the Transform dialog 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 dialog, seeing what it does in a preview, looking at the query Manifold creates and then trying out the function in the Expression tab.
