This topic provides two examples that show simple use of Manifold SQL functions in the Transform pane, using the Expression template. Manifold provides hundreds of SQL functions, and many of them can be used to transform raster, vector, or attribute data in tables with very simple expressions, often just the function itself, with no need for advanced SQL knowledge.
In the first example, we use the TileMaskRange SQL tile function within an expression in the Transform pane to mark as missing pixels a desired range of pixel values in a single channel Landsat image. The TileMaskRange function is a fast way to eliminate unwanted black or white regions outside of non-rectangular visible pixel regions, like the black border regions in a rotated Landsat image.
In the second example we take a different approach from the Style: Contouring using Colors topic to creating contour displays using rasters.
See the 10 Minute Tutorial - TileMaskRange Expressions video for the video version of this topic. See another example of TileMaskRange use in the SQL Example: Compute Area of an NDVI Interval topic.
Consider the above image, showing band 1 of a Landsat Thematic Mapper image as a single-channel image using uint8 data type. We would like to mark the black regions, which have pixel values of 0, as missing pixels, so they are not displayed.
With the focus on the open image window, in the Transform pane we choose the Tile field of the image, and then we double-click the Expression template to launch it.
In the Expression template, we press the Edit Expression button, and then in the expression builder dialog (not shown), we enter the expression:
TileMaskRange([Tile], 1, 255, TRUE, TRUE)
Press OK in the expression builder dialog to get back to the Expression template.
We choose New Table for the Result, and uint8 for the Channel Type. We enter Masked as the name for the New image that will be created, and an analogous name for the image's table. We could have left the Result destination as Same Field to update the pixel values in place in the same image, but since we would like to use the same original image more than once in this example we will use New Table for the result destination to create a new image with each new variation we create.
The expression uses the TileMaskRange function to change some of the pixels in the image into missing pixels. The syntax for the function is:
TileMaskRange(<tile>, <boundMin>, <boundMax>, <inclusive>, <keepRange>) : <tile>
The function takes a single channel tile and returns a float64 tile that marks pixels with values inside or outside of the range as missing pixels. Boundary values can be included or excluded. The arguments to the function are:
For a tile using the same data type as the source tile, when writing our own SQL queries we would use the CASTV operator to cast the resulting tiles into the desired data type. That is not necessary in an expression using the Expression template, since we can set the Channel type to the desired data type, in this case uint8, and the Expression template will do the CASTV for us.
The expression we have written...
TileMaskRange([Tile], 1, 255, TRUE, TRUE)
... tells the system to find all pixel values in the range from 1 to 255, inclusive (the first TRUE means "inclusive"), and to keep them unchanged (the second TRUE means keeping pixels within the specified range), while marking all other pixels outside the range as missing pixels. In this case, that means marking all pixels that have a value of 0 as missing pixels.
Press Transform to apply the template.
The result is that a new image, called Masked, and its table are created in the Project pane. We can double-click the new Masked image to open it in its own window.
The result is the same image, but with all pixels outside the range from 1 to 255 marked as missing pixels. Missing pixels are not rendered, so they are transparent. In the illustration above, we have used the Layers pane to turn off the background white layer.
In the above steps we have marked as missing pixels only those with a value of 0, since the range for pixel values to be preserved was all other pixels, from 1 to 255. If we like, we can make that a smaller range so that some pixel values above the given range are marked as missing pixels as well.
We press the Edit Expression button and alter the expression to be:
TileMaskRange([Tile], 1, 150, TRUE, TRUE)
The range now is from 1 to 150, inclusive, with all other pixels, those that are 0 or which fall in the range 151 to 255, inclusive, are marked as missing pixels.
We change the name for the destination result image to be Masked2.
A new Masked2 image and table appear in the Project pane. We pop open the new image, and we use the Layers pane to set a background color using a shade of yellow, to easily see which pixels have become transparent.
Zooming into a region near the Southwestern corner of the image, we see that in addition to the black, 0 value pixels, pixels that had values of 151 to 255, that is, lighter pixels, have also been marked as missing.
So far, we have used expressions where the last argument in the function has been TRUE, telling the function to keep pixels within the given range unchanged and to mark as missing pixels outside that range.
We can edit the expression to make that final argument FALSE:
TileMaskRange([Tile], 1, 150, TRUE, FALSE)
That tells the function to mark as missing all pixels within the given range, and to leave unchanged pixels outside that range.
We also change the name of the result image to Masked3.
The result, as seen above, is the inverse situation from the Masked2 image. The black pixels, with a value of 0, are unchanged. Also unchanged are pixels with a value from 151 to 255, inclusive. Pixels with values from 1 to 150, inclusive, have been marked as missing and thus are rendered as transparent.
Depending on the zoom level, we may note that the Masked3 image does not appear to be precisely the inverse of Masked 2.
When views are zoomed using the View - Zoom to Native command so each pixel in the data set appears as one pixel on screen, the Masked3 image will appear to be precisely the inverse of Masked 2, but not when zoomed further out.
For example, the illustrations above and below are zoomed to native scale, so they appear as the inverse of each other. What is seen as an invisible pixel in one is seen as a visible pixel in the other.
If we zoom further out, we see that Manifold tends to prefer to show visible pixels over invisible pixels. Compare, for example, the two views of the entire image as seen below, noting the region marked with a magenta arrow in both.
In the region marked by the arrow there are few invisible pixels in Masked2 window, but in the Masked 3 window (the inverse), there appear many visible pixels in that same region, which indicates that the Masked2 window above should have shown more yellow color, that is, more invisible pixels, in that region.
What is going on is that when we zoom a view further out than native scale, where each image pixel takes up one pixel in the display, the system must interpolate the view. For example, if we zoom twice as far out, then each pixel in the display covers a region that includes four pixels in the image. The system must interpolate what to show in that case, considering the values of the four different pixels and doing an interpolation to compute a value to show for the single pixel on screen that represents those four pixels.
When interpolating colors that is a simple computation, but different logic is required if one or more of those four pixels is a missing pixel. In that case, there is no interpolation that makes logical sense between missing and not missing, there being no such thing as a pixel that is only somewhat missing. In such cases, Manifold overweights non-missing pixels to avoid falsely showing regions as not having any pixels at all, when in fact there are non-missing pixels in those regions.
In this example, we use the TileMaskRange SQL tile function within an expression in the Transform pane to mark as missing pixels desired ranges of pixel elevation values, outside the heights of contour ranges we would like to show in a map. This is a different approach from that used in the Style: Contouring using Colors topic, where we used formatting using the Style pane to mark pixels outside the range of interest as transparent pixels.
We start with a map similar to that used in the Style: Contouring using Colors topic. The All Elevations layer is a raster image that is styled using the thematic format shown below.
The various intervals are colored based on the pixel height values in Channel 0. The data for the image comes from an Elevation Tiles table.
The map has another layer, called 250 Elevation, which is also a raster image that takes its data from the same Elevation Tiles table.
The 250 Elevation image is styled using multiple intervals, all but one using transparent color. The thematic format could have used just three intervals: the 100 interval, the 250 interval, and then 300 and above.
Unlike most other GIS packages, Manifold can create multiple images from the same source table. An image in Manifold is just a visualization, a component that takes no storage space in the project at all: an image is simply a few bytes that say what table to use for the data, what field in the table provides the data, plus a few bytes that say how to style the data for visualization. We can create hundreds of images from the same data in the same table, styling each of them differently, for dramatically different visual effects, as seen in the visual difference between the 250 Elevation image and the All Elevations image.
With both the 250 Elevation image and the All Elevations image layers turned on in the map, we can see how the different style used with the 250 Elevation image picks out a particular swath of elevations, like a contour.
We can create a similar effect using the TileMaskRange function to mark all unwanted pixels as missing, so they are not rendered, and leaving only the desired range of pixels to be displayed.
We could work with either the 250 Elevation image or the All Elevations image, since they both use the Elevation Tiles table.
With the focus on the open Elevation Map window, in the Transform pane we choose the All Elevations layer, and then we choose the Elevation field, which is the field that hosts the tile values for the image's table.
Double-click on the Expression template to launch it in the Transform pane.
Click the Edit Expression button to launch the expression builder dialog. In the first example in this topic we skipped over use of the Expression dialog, so in this example we will show the full procedure.
In the Expression dialog, enter the expression:
TileMaskRange([ELEVATION], 250, 350, TRUE, TRUE)
The expression uses the TileMaskRange function to mark as missing all pixels with values outside the range 250 to 350, inclusive.
Back in the Transform pane, if we would like to preview what the expression will do, we press the Preview button.
The preview shows the results in shades of blue preview color.
We do not want to change the pixel values in the Elevation Tiles table, so we will set up the template to save the results into a new table, that is, a new image. We choose New Table for the Result destination, and float32 for the Channel type (we can see that the All Elevations image uses float32 for tile values by taking a look at the Info pane).
For the name of the new image and table we enter 250-350 Masked, to remind us what range we used.
A new image called 250-350 Masked and associated table appear in the project pane. We can drag and drop the new 250-350 Masked image into the Elevation Map window.
The result is seen in default style. We can copy the StylePixel property from the All Elevations image and paste it into a new StylePixel property in the 250-350 Masked image to copy the same style into the 250-350 Masked image.
The result is as if we cut out only those pixels with height values from 250 to 350, inclusive.
All of the other pixels are marked as missing, so only the pixels with height values from 250 to 350 are displayed.
Why would we use the technique shown above instead of the style technique shown in the Style: Contouring using Colors topic? The main reason is that pixels that are marked as missing do not participate in the visualization. That allows us to use the same thematic formatting styles as used for the source, original image.
For example, the Style for the All Elevations image is shown at left above, and exactly the same style is used with the new 250-350 Masked image. There is no need to alter the thematic format to set only a given swath of pixel values to a desired color with all other intervals set to transparent color, as shown at right above, as used for the 250 Elevation image.
The ability to use the same style, relying on missing pixels being transparent, is a great convenience when masking undesired pixels and using standard color ramps, such as for NDVI and similar.
10 Minute Tutorial - TileMaskRange Expressions - We learn how to use the power of Manifold Release 9's many SQL functions without needing to learn SQL. The Transform pane's Expression template allows us to apply individual SQL functions, to transform rasters, vectors, and tables using hundreds of powerful SQL functions.
The example tutorial shows the TileMaskRange function in action, used to knock out unwanted pixels in images, marking them as missing pixels to make them transparent. We first apply the function to knock out unwanted black border pixels from a Landsat image, so the Landsat image can be seamlessly overlaid on other satellite backgrounds. Along the way we see how Previews can show us what will happen next, to guide our choices of parameters. We also show how to invert mask effects.
Next, we use the function to knock out thematically formatted pixels in a terrain elevation raster to create dramatic and useful raster contouring effects.
Style: Contouring using Colors
Transform - Expression
Transform - Tiles
Tile SQL Functions
SQL Example: Create NDVI Displays - How to create a query that creates an NDVI display from a four-band NAIP image, with tips and tricks on how to copy and paste existing information to get the result we want.
SQL Example: Compute Area of an NDVI Interval - We use a small query to calculate the total area covered by pixels within a given NDVI interval range in an NDVI raster image. For example, if our NDVI intervals have an interval colored in a shade of brown that indicate regions without foliage, we can calculate the total area covered by such regions. If we made such calculations for the same region, perhaps by computing NDVI rasters for different years in which NAIP images were acquired, we could quickly see if regions without foliage were increasing or decreasing in area.
Example: Unique Names using Regular Expressions - We have a table with a text field that contains a list of names, separated by commas. Some of the names are repeated. We would like to transform the list of names into a similar list, but without any repetitions of names. This topic shows how using a regular expression. It shows the power and speed of a concise regular expression, and the flexibility with which regular expressions can be used in SQL queries as well as in the Transform pane.
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.