SQL Example: Create NDVI Displays

This example shows 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.  We use the same NAIP image as used in the Example: Display an NAIP Four Band Image as Color Infrared (CIR) topic.  The techniques and query we create are a mix of techniques illustrated in the SQL Example: Process Images with 3x3 Filters topic, the SQL Example: Process Images using Dual 3x3 Filters  topic, and the SQL Example: Process RGB Images using Matrix Filters  topic.  This topic assumes we have read all  four topics.

About NDVI

NDVI stands for Normalized Difference Vegetation Index and is primarily used as a way of detecting healthy vegetation.  An NDVI display is a single-channel image that for each pixel has a value in the range -1 to 1.   NDVI images are calculated from some other image, usually an aerial or satellite image, that has at least two data channels, with a channel that acquired the scene using red, visual frequencies,  and a channel that acquired in near-infrared frequencies.    Most source images used to calculate an NDVI display are four band images, such as NAIP images,  which in addition to the red and infrared channels also have blue and green channels.    

 

To calculate NDVI blue and green channels are ignored and only the red and near-infrared channels are used, in the following calculation:

 

NDVI = (IR - Red) / (IR + Red)  

 

Healthy, green vegetation reflects near-infrared frequencies well while at the same time poorly reflecting red frequencies.  The result of the above calculation when IR is high and Red is low is a high NDVI value, closer to 1.   In contrast, when IR reflectance is low, as happens with water and terrain not covered with green foliage, while Red is high, the NDVI calculation result is closer to 0 or even a negative number closer to -1.   

 

NDVI images are typically colored with a palette that helps emphasize the difference between regions of pixels with higher NDVI values and those with lower NDVI values.   There is no single, standard palette used for NDVI images.  This topic illustrates use of some palettes scraped from various sites on the web, to show different effects provided by different palettes.

Our Core Calculation

For our sample, starting imge, we use same, four band, NAIP image of Redding, California, and surroundings, that we used in the the Example: Display an NAIP Four Band Image as Color Infrared (CIR) topic, seen below zoomed into a northeast suburban region near Redding.

 

eg_create_ndvi01_01.png

 

From that topic we learned that four band NAIP imagery uses the following channel order:

 

 

From the SQL Example: Process RGB Images using Matrix Filters  topic we learned how to grab channels from a multichannel image by using the TileChannel function.   For example, we can grab the red channel using:

 

TileChannel([Tile], 0)

 

...and the near-infrared channel using:

 

TileChannel([Tile], 3)

 

It seems our expression to compute NDVI using the formula:

 

NDVI = (IR - Red) / (IR + Red)  

 

...could be written as simply as:

 

(TileChannel([Tile], 3) - TileChannel([Tile], 0)) / ((TileChannel([Tile], 3) + TileChannel([Tile], 0))

 

In fact, it really is that easy.   All we need do is surround the above with a CASTV to return the result as a FLOAT64, since we want NDVI to be a floating point value from -1 to 1, and not just a choice of three integer numbers, -1, 0, or 1.    Using CASTV adds yet more parentheses, but if we are careful counting parentheses we get:

 

CASTV (((TileChannel([Tile], 3) - TileChannel([Tile], 0 )) / (TileChannel([Tile], 3) + TileChannel([Tile], 0 ))) AS FLOAT64)

 

Writing a Query

In the SQL Example: Process RGB Images using Matrix Filters  topic we discovered how to use the TileChannel function by first launching the Channel transform template in the Transform pane, and then pressing the Edit Query button to see the SQL that Manifold uses to implement that template.

 

eg_create_ndvi01_02.png

 

We will do that again, but first we will click the Options button to allow us to specify the name of the destination component.  We may as well have Manifold use those names in the query it writes for us.

 

eg_create_ndvi01_03.png

 

In the Transform Options dialog we specify pleasantly short, and generic names to use for the table and image that the transform will create.    We press OK.

 

eg_create_ndvi01_04.png

 

Back in the Transform pane, we click the Edit Query button.   Manifold displays the query behind the transform template in a Command Window.   In this topic, to save space we will simply show the SQL and not illustrate the Command Window.   The text (with extra newlines to fit within the margins of this documentation):

 

-- $manifold$

--

-- Auto-generated

-- Transform - Channel - Add Component

--

CREATE TABLE [NDVI Tiles] (

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Y_X_x] BTREE ([Y], [X]),

  INDEX [X_Y_Tile_x] RTREE ([X], [Y], [Tile] TILESIZE (128, 128) TILETYPE UINT8),

  PROPERTY 'FieldCoordSystem.Tile' 'EPSG:26910,mfd:{ "LocalOffsetX": 552708,

    "LocalOffsetY": 4490226, "LocalScaleX": 0.6, "LocalScaleY": 0.6 }',

  PROPERTY 'FieldTileSize.Tile' '[ 128, 128 ]',

  PROPERTY 'FieldTileType.Tile' 'uint8'

);

CREATE IMAGE [NDVI] (

  PROPERTY 'Table' '[NDVI Tiles]',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 9410, 12140 ]'

);

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [NDVI Tiles] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV ((TileChannel([Tile], 0)) AS UINT8)

FROM [m_4012230_nw_10_h_20160717]

THREADS SystemCpuCount();

TABLE CALL TileUpdatePyramids([NDVI]);

 

Adapting the above query to create an NDVI image is simple.  We replace the CASTV line above:

 

  CASTV ((TileChannel([Tile], 0)) AS UINT8)

 

... with the expression we need to calculate NDVI:

 

  CASTV (((TileChannel([Tile], 3) - TileChannel([Tile], 0 )) / (TileChannel([Tile], 3) + TileChannel([Tile], 0 ))) AS FLOAT64)

 

In addition, we change all references to UINT8 to FLOAT64, and uint8 to float64, since we will be filling the tiles in the destination table and image with floating point numbers, not integers.  

 

Everything else stays the same, since the Edit Query button thoughtfully wrote all the accessory SQL required to create a single-channel image and a table and to populate it with a single channel of data calculated using our NDVI expression.   Our adapted query, showing changes in magenta:

 

-- $manifold$

--

-- Create NDVI Display

--

CREATE TABLE [NDVI Tiles] (

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Y_X_x] BTREE ([Y], [X]),

  INDEX [X_Y_Tile_x] RTREE ([X], [Y], [Tile] TILESIZE (128, 128) TILETYPE FLOAT64),

  PROPERTY 'FieldCoordSystem.Tile' 'EPSG:26910,mfd:{ "LocalOffsetX": 552708,

    "LocalOffsetY": 4490226, "LocalScaleX": 0.6, "LocalScaleY": 0.6 }',

  PROPERTY 'FieldTileSize.Tile' '[ 128, 128 ]',

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [NDVI] (

  PROPERTY 'Table' '[NDVI Tiles]',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 9410, 12140 ]'

);

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [NDVI Tiles] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV (((TileChannel([Tile], 3) - TileChannel([Tile], 0 )) /

    (TileChannel([Tile], 3) + TileChannel([Tile], 0 ))) AS FLOAT64)

FROM [m_4012230_nw_10_h_20160717]

THREADS SystemCpuCount();

TABLE CALL TileUpdatePyramids([NDVI]);

 

Running the above query by pressing the ! Run button in the main toolbar, we get:

 

eg_create_ndvi01_05.png

 

The image as created has pixels with floating point values between -1 and 1, so it is uniformly black.  We must use the Style pane to color it.

 

eg_create_ndvi01_06.png

 

In the Style pane we choose Channel 0 as a single channel to use.  We use ten Breaks with equal intervals.   We apply the Color Brewer CB Red-yellows to Greens palette, not a bad "off the shelf" palette to use for NDVI purposes.  Press Update Style.

 

eg_create_ndvi01_07.pngeg_create_ndvi01_01.png

 

The result is a classic NDVI display, seen above at left with the natural color RGB image seen at right for comparison. Pixels with low NDVI values, like those in lakes and rivers, are colored red.   Pixels in regions with healthy, green foliage are shades of darker green.   Roads built up areas are in between with shades of yellow.  We matched the views in the above two windows by using the Locations button to set the view seen in the NDVI window to be the same as that in the natural color image window.

 

eg_create_ndvi01_08.pngeg_create_ndvi01_18.png

 

Zooming further in, using View - Zoom to Native so each pixel in the data is rendered using one pixel on screen, we can see how individual trees appear in different shades of green. The natural color image is seen at right for comparison.  Experts who know how to use NDVI can use it to not only tell the health of foliage but also to pick out different plant species in agricultural or forest scenes.

Harvesting NDVI Palettes From the Web

There is no one standard palette used to color NDVI data, but it is easy enough to capture palettes from different web sites and to build up our own library of palettes we can use.  The trick we will use is Manifold's Color Picker tool, plus the use of legends that show color intervals in web illustrations.

 

We begin by harvesting a palette, said to be good for showing leaf health, from an academic web site at http://web.nmsu.edu/~tcarrill/what_is_ndvi.htm, seen below in Microsoft's Edge browser.

 

eg_create_ndvi01_09.png

 

Turning the legend right-side up for further illustrations, we launch the Style pane, resetting breaks to 15 and pressing the Tally button:

 

eg_create_ndvi01_10.pngeg_create_ndvi01_11.png

 

Starting at the bottom interval, our task is to double-click into each of the number boxes and change the number there to the first number used by the equivalent interval from the right-side-up palette shown at right.

 

eg_create_ndvi01_12.pngeg_create_ndvi01_11.png

 

For example, above we have changed the lowest interval number to 0.80, which the pane reads out as 0.8.    That corresponds to the fifteenth interval in the palette at right.

 

Next, we change the color box for that last interval to the dark green color used for the fifteenth interval in the palette.   

 

eg_create_ndvi01_13.png

 

We can do that by double-clicking into the color well in the style pane, and then choosing the Color Picker tool.

 

 

eg_create_ndvi01_14.png

 

We can click anywhere on our Windows desktop where the desired color appears.  For example, we can click the desired color in the illustration in the web page in our browser window, and Manifold will harvest the color.

 

eg_create_ndvi01_15.png eg_create_ndvi01_11.png

 

We repeat the procedure above to first, specify the number for the interval, and then second, to pick the color to be used with the Color Picker tool.

 

eg_create_ndvi01_16.png eg_create_ndvi01_11.png

 

Once we have populated all the intervals using the values and colors in the palette we want to use, we press the Update Style button to apply the new palette.

 

eg_create_ndvi01_17.pngeg_create_ndvi01_18.png

 

The new palette changes the appearance, emphasizing differences in greens.   The natural color image is seen at right for comparison.  

 

eg_create_ndvi01_19.pngeg_create_ndvi01_01.png

 

Zoomed further out, we see how non-foliage regions have been grouped together in shades of brown.

Saving NDVI Palettes

We do not want to go through the inconvenience of re-entering each palette manually if we want to use it again.  Lucky for us, nearly everything in Manifold is exposed in a table, the mfd_meta table, with styles also being exposed as simple JSON text strings in the StylePixel property of an image.    From either source we can Copy the style text and save it into a text file or Comment component.  Whenever we want to re-use that style, we simply copy it from the text file and then Paste it into the StylePixel property of the NDVI image.

 

To copy the StylePixel property of the NDVI image, we right-click onto the NDVI image in the Project pane (or, alternatively, we can right-click onto the NDVI tab at the bottom of the image window) and choose Properties in the context menu that pops up.

 

eg_create_ndvi01_20.png

 

We then right-click onto the text cell value for the StylePixel property, and choose Copy from the context menu.  That copies the long, JSON text string that is in that cell.

 

eg_create_ndvi01_21.png

 

We then Paste the JSON text string into a Comments component, like that above (with most of the text continuing out of the window to the right), or into any text file.   For example, we can create a .txt file with NotePad and use that.

Reusing NDVI Palettes

Reusing an NDVI Palette we have previously saved is a simple matter of copying the JSON string and pasting it into the StylePixel property.

 

eg_create_ndvi01_22.png

 

Suppose, for example, we have previously saved the palette used by the USDA on the Crop Explorer web site.   We can highlight it and press Ctrl-C to copy it, just as we would copy any other text.

 

eg_create_ndvi01_23.png

 

We launch the Properties dialog for our NDVI image, right-click onto the text cell for the StylePixel property, and choose Paste to paste the text.  Press OK.

 

eg_create_ndvi01_24.pngeg_create_ndvi01_25.png

 

The result is a presentation using the USDA Crop Explorer palette.  The Style pane will show that as well, a palette that uses only eight intervals.

A More Advanced Query

We can improve our query by using the techniques discussed in topics like the SQL Example: Process RGB Images using Matrix Filters topic to compartmentalize things like the name and Rect values for the source image.  This will make the query easier to re-use with different images:

 

-- $manifold$

--

-- Create NDVI Display

--

 

VALUE @source_image TABLE = [m_4012230_nw_10_h_20160717];

VALUE @source_image_rect  NVARCHAR = '[ 0, 0, 9410, 12140 ]';

 

CREATE TABLE [NDVI Tiles] (

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Y_X_x] BTREE ([Y], [X]),

  INDEX [X_Y_Tile_x] RTREE ([X], [Y], [Tile] TILESIZE (128, 128) TILETYPE FLOAT64),

  PROPERTY 'FieldTileSize.Tile' '[ 128, 128 ]',

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [NDVI] (

  PROPERTY 'Table' '[NDVI Tiles]',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' @source_image_rect

);

ALTER IMAGE [NDVI] (

-- Use leaf palette

ADD PROPERTY 'StylePixel' '{ "Channel": 0, "Fill": "boundaverage", "Value": 4333842, "Values": { "-0.31": 4333842, "0.05": 10441002, "0.16": 13412885, "0.25": 16580099, "0.3": 15068672, "0.35": 13688576, "0.4": 12177152, "0.45": 10665984, "0.5": 9154560, "0.55": 7708160, "0.6": 6196736, "0.65": 4554752, "0.7": 2977792, "0.75": 1532160, "0.8": 20992 } }'

);

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [NDVI Tiles] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV (((TileChannel([Tile], 3) - TileChannel([Tile], 0 )) /

    (TileChannel([Tile], 3) + TileChannel([Tile], 0 ))) AS FLOAT64)

FROM @source_image

THREADS SystemCpuCount();

ALTER TABLE [NDVI Tiles] (

  ADD PROPERTY 'FieldCoordSystem.Tile' ComponentCoordSystem(@source_image)

);

TABLE CALL TileUpdatePyramids([NDVI]);

 

Probably the most useful part of the query above is the use of the ALTER IMAGE statement to add a StylePixel property to the image that is whatever JSON string we desire, automatically setting the property.   

One More Enhancement

If we like, we can add a few more JSON strings to the query, with all of them except the one we want to use commented out with -- characters:

 

-- $manifold$

--

-- Create NDVI Display

--

VALUE @source_image TABLE = [m_4012230_nw_10_h_20160717];

VALUE @source_image_rect  NVARCHAR = '[ 0, 0, 9410, 12140 ]';

CREATE TABLE [NDVI Tiles] (

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Y_X_x] BTREE ([Y], [X]),

  INDEX [X_Y_Tile_x] RTREE ([X], [Y], [Tile] TILESIZE (128, 128) TILETYPE FLOAT64),

  PROPERTY 'FieldTileSize.Tile' '[ 128, 128 ]',

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [NDVI] (

  PROPERTY 'Table' '[NDVI Tiles]',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' @source_image_rect

);

ALTER IMAGE [NDVI] (

-- Use USDA Crop Explorer palette

-- ADD PROPERTY 'StylePixel' '{ "Channel": 0, "Fill": "boundaverage", "Value": 15183459, "Values": { "0": 15183459, "0.1": 16769940, "0.2": 15204269, "0.3": 10289052, "0.4": 6553443, "0.5": 3264305, "0.6": 39424, "0.7": 25856 } }'

-- Use classic palette

-- ADD PROPERTY 'StylePixel' '{ "Channel": 0, "Fill": "boundaverage", "Value": 14102567, "Values": { "0": 14102567, "0.1": 16018755, "0.2": 16625249, "0.3": 16703627, "0.4": 14282635, "0.5": 10934634, "0.6": 6733155, "0.7": 1742928 } }'

-- Use leaf palette

ADD PROPERTY 'StylePixel' '{ "Channel": 0, "Fill": "boundaverage", "Value": 4333842, "Values": { "-0.31": 4333842, "0.05": 10441002, "0.16": 13412885, "0.25": 16580099, "0.3": 15068672, "0.35": 13688576, "0.4": 12177152, "0.45": 10665984, "0.5": 9154560, "0.55": 7708160, "0.6": 6196736, "0.65": 4554752, "0.7": 2977792, "0.75": 1532160, "0.8": 20992 } }'

);

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [NDVI Tiles] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV (((TileChannel([Tile], 3) - TileChannel([Tile], 0 )) /

    (TileChannel([Tile], 3) + TileChannel([Tile], 0 ))) AS FLOAT64)

FROM @source_image

THREADS SystemCpuCount();

ALTER TABLE [NDVI Tiles] (

  ADD PROPERTY 'FieldCoordSystem.Tile' ComponentCoordSystem(@source_image)

);

TABLE CALL TileUpdatePyramids([NDVI]);

 

The example above uses a "classic" palette favored by some web sites, and producing a strikingly garish display.  If we want to save many palettes, we could put them at the beginning of the query as a series of VALUE statements, similar to how various matrix filters are saved as illustrated in examples like the SQL Example: Process Images using Dual 3x3 Filters topic.

An Alternative Approach

As is often the case in GIS, there is a different way to get to the same result, an NDVI display.  In the example above we created a new table and a new image, populating the table with the results of the CASTV expression that computed NDVI.   If we like, instead of creating a second table we can simply create a second tile field in the same table, a tile field which is a computed field that calculates the NDVI value.  We can then create a second drawing from the same table, using the new NDVI tile field.   

 

Our workflow:

 

 

We begin by renaming the long m_4012230_nw_10_h_20160717 Tiles and  m_4012230_nw_10_h_20160717 names to simply Source Tiles for the table and Source for the image.   Long names are tedious.   

 

Next, we open the Source Tiles table, to add a new computed tile field to the table.

 

eg_create_ndvi01_26.png

 

We click Edit - Schema to launch the Schema dialog.  

 

eg_create_ndvi01_27.png

 

btn_schema_add.png Press the Add command button and then choose Field in the drop down menu.

 

eg_create_ndvi01_27a.png

 

In the Field dialog, we enter TileNDVI as the name of the new field.  We choose tile as the Type, and float64 as the pixel type.   We leave the 128 x 128 size and the Reduce defaults as is.

 

tech_ravi_sm.png

btn_coord_sys_picker.pngWe want the coordinate system to be the same as that used in the Tile field, so we click the projections picker and choose the UTM zone 10N projection.  

 

Think ahead: The easiest way to re-use a projection that is used somewhere else is to think ahead a bit and to add it to the Favorites list.  When we first open the imported .tif file, in the Component pane we click the projection picker button and add the projection the image uses to the list of Favorite Coordinate Systems.   It will then appear in the short list of favorites as soon as we click the coordinate picker.  Now, when we want to use that projection in the Field dialog, we can just click on it from a short list.

 

We want the new TileNDVI field to be a computed field, so we press the Edit Expression button to launch the Expression dialog.

 

eg_create_ndvi01_27b.png

 

In the Expression dialog we enter the SQL expression:

 

CASTV (((TileChannel([Tile], 3) - TileChannel([Tile], 0 )) / (TileChannel([Tile], 3) + TileChannel([Tile], 0 ))) AS FLOAT64)

 

The Expression dialog doesn't care about extra white space, so we can use white space and new lines to format the above as legibly as we like.  

 

The SQL expression we use  is the operative CASTV expression that computes NDVI value using the red and near-infrared channels.  It appears in the various queries above in this example.   

 

We press OK.

 

eg_create_ndvi01_27c.png

 

Back in the Field dialog, we check our work and then press OK.  

 

eg_create_ndvi01_27d.png

 

The new computed field appears in the schema, shown in provisional, bluish color.  It will not be added to the table until we press the Save Changes button.

 

We now add an index on the new computed field.

 

eg_create_ndvi01_28.png

 

btn_schema_add.png Press the Add command button and then choose Index in the drop down menu.

 

eg_create_ndvi01_28a.png

 

We enter the name X_Y_TileNDVI_x for the new index.  That follows customary Manifold naming practice of naming indexes using the name of their key field with an _x appended, and in the case of indexes on tiles prefixing the name with the names of the X and Y fields that are used.    For the Tile that is indexed we choose the TileNDVI field, and for pixel type float64.  The other fields we leave at default values.  Press OK.

 

eg_create_ndvi01_28b.png

 

The new index appears in provisional, bluish colors.   

 

So far, we have added a new tile field to the table, which is a computed field.  The values in the TileNDVI field will be automatically computed from the three-channel Tile field using the NDVI formula we pasted into the Expression box.   We have also added an X_Y_TileNDVI_x spatial index on that new, computed tile field.

 

We take a moment to review our work.  If we detect an error or want to change something, we could make changes or press Close to exit the dialog without making any changes.  We press Save Changes to apply the change to the table.  

 

Manifold thinks for about ten seconds, since it must compute all pixels in the image to populate the TileNDVI field and to build a spatial index on that field. 

 

eg_create_ndvi01_29.png

 

A new column appears in the Source Tiles table, the computed TileNDVI field we added.  We see it is populated with the results of the expression, a single channel that has float64 values.  

Create an Image and Style It

Next, we will create a new image based on the new field, and then Style that new image.   We could do this in a mix of dialogs, but it is quicker to simply adapt the last big query written above to do all that in a query.   

 

We write:

 

-- $manifold$

 

-- Create a new image

CREATE IMAGE [Source NDVI] (

  PROPERTY 'Table' '[Source Tiles]',

  PROPERTY 'FieldTile' 'TileNDVI',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 9410, 12140 ]'

);

 

-- Style the image

ALTER IMAGE [Source NDVI] (

-- Use leaf palette

ADD PROPERTY 'StylePixel' '{ "Channel": 0, "Fill": "boundaverage", "Value": 4333842,

"Values": { "-0.31": 4333842, "0.05": 10441002, "0.16": 13412885, "0.25": 16580099,

"0.3": 15068672, "0.35": 13688576, "0.4": 12177152, "0.45": 10665984, "0.5": 9154560,

"0.55": 7708160, "0.6": 6196736, "0.65": 4554752, "0.7": 2977792, "0.75": 1532160,

"0.8": 20992 } }'

);

 

-- Update intermediate levels

TABLE CALL TileUpdatePyramids([Source NDVI]);

 

 

We have added comments to make clear what each part of the query does.  

 

The CREATE IMAGE statement creates a new image using the TileNDVI field within the Source Tiles table.   That, too, is a simple adaptation of the CREATE IMAGE statement in the earlier queries.  In this case, we have not abstracted the Rect values with a VALUE statement, since this is a simpler query than the ones we wrote earlier.

 

Styling the image with ALTER IMAGE is a simple cut and paste of the same ALTER IMAGE statement used earlier, just with the name of the destination image adjusted to Source NDVI.  We have un-commented the ADD PROPERTY clause to use the "leaf" palette.   This is an example of how a simple cut and paste into an SQL query can save much clicking in an interactive dialog.

 

We do not need to specify the coordinate system, since we built that into TileNDVI computed field when we created it in the Field dialog, so there is no ALTER TABLE statement specifying the coordinate system as was used in earlier queries.

 

The final UPDATE statement builds intermediate levels for the new image, so it pops open instantly visible with no need of any messages about the need to build intermediate levels.

 

Running the query produces the expected result:

 

eg_create_ndvi01_30.png

 

The image is styled using the "leaf" NDVI palette, and it pops open as an immediately-visible image.   There are no messages about computing intermediate levels, since we tacked on a use of the TileUpdatePyramids function at the end of our query.

Dynamic Updates

What is useful about this alternative approach is that the NDVI image is automatically calculated as a computed field within the same table from whatever are the contents of the Tile field.   If the main image changes, perhaps because some other process or program updates the Tile field, the TileNDVI field automatically changes as well and thus the Source NDVI image created from the TileNDVI field changes as well.

 

We can demonstrate using a purely brute-force approach to vandalize some tiles in the source image:

 

eg_create_ndvi01_31.png

 

In the Sourrce image we use Ctrl-Click-and-drag to select some tiles in the image.  

 

eg_create_ndvi01_32.png

 

We click into the Source Tiles table and scroll to one of the regions of selected tiles.   We click on the table not just to see the selected tile records, but also because the Transform pane provides more templates for use with tiles than it does with multi-channel, RGB images.

 

eg_create_ndvi01_33.png

With the Tile field as our target, we check the Restrict to selection box so the template only alters selected records.  We click the Copy template, choose Expression in the pull down menu for the Value, and then we enter the following expression:

 

CASTV (TileMake(128,128,VectorMakeX4(64,64,64,64)) AS UINT8)

 

To unpack the above from innermost function outwards: the data in each pixel in the Tile field is a vector of four numbers.   We create that using the VectorMakeX4 function, putting the number 64 into all four positions of the vector.  That creates pixels where the R, G, B, and IR values are all 64, a four-channel gray color.  We need to create a full 128x128 tile from those numbers, so we use TileMake to do that.  Finally, we want the numbers cast as UINT8, so we use a CASTV.  

 

eg_create_ndvi01_34.png

 

The preview shows the rows and columns that will be affected.  We like what we see, so we press the Update Field button to apply the transform.

 

eg_create_ndvi01_35.png

 

Instantly, the Source image is updated.   We also get a message that we need to take action, so we click the View - Messages menu choice and update intermediate levels to see the above.   With the focus on the Source image we choose Edit - Select None to clear red selection color from the display, as seen below. 

 

eg_create_ndvi01_36.pngeg_create_ndvi01_37.png

 

The Source NDVI image also is updated, again with a message that we need to take action, so we click the View - Messages menu choice and update intermediate levels in the Source NDVI image as well.     Comparing the two views above we see that the tiles we altered in the Tile field of the table resulted in the automatic recalculation of the corresponding TileNDVI records, and thus an automatic update of the Source NDVI image.

Notes

Why the brown box? -  In the Dynamic Updates section above, we used the numbers 64, 64, 64, 64 for the x4 vector when we altered the Tile field.   That results in RGB values of 64, 64, 64, which should be a shade of dark gray.   Why does the illustration show the altered tiles as dark brown?   The Source natural color image has been styled to use the Full Range of values to provide a more natural, saturated appearance.   Since the full ranges of R, G and B channel contents are slightly different, the mapping of the number 64 to within the range of 0 to 255 for the outputs is slightly different for each channel, resulting in the dark brown instead of dark gray.

 

Sample palettes - Following are a collection of sample NDVI palettes which can be copied and pasted.

 

Useful for leaf cover, from http://web.nmsu.edu/~tcarrill/what_is_ndvi.htm:

 

{ "Channel": 0, "Fill": "boundaverage", "Value": 4333842, "Values": { "-0.31": 4333842, "0.05": 10441002, "0.16": 13412885, "0.25": 16580099, "0.3": 15068672, "0.35": 13688576, "0.4": 12177152, "0.45": 10665984, "0.5": 9154560, "0.55": 7708160, "0.6": 6196736, "0.65": 4554752, "0.7": 2977792, "0.75": 1532160, "0.8": 20992 } }

 

USDA Crop Explorer palette:

 

{ "Channel": 0, "Fill": "boundaverage", "Value": 15183459, "Values": { "0": 15183459, "0.1": 16769940, "0.2": 15204269, "0.3": 10289052, "0.4": 6553443, "0.5": 3264305, "0.6": 39424, "0.7": 25856 } }

 

A classic palette as used by some third party applications, applied to the USDA intervals:

 

{ "Channel": 0, "Fill": "boundaverage", "Value": 14102567, "Values": { "0": 14102567, "0.1": 16018755, "0.2": 16625249, "0.3": 16703627, "0.4": 14282635, "0.5": 10934634, "0.6": 6733155, "0.7": 1742928 } }

 

The Color Brewer Spectral palette applied to the USDA intervals:

 

{ "Channel": 0, "Fill": "boundaverage", "Value": 13975119, "Values": { "0": 13975119, "0.1": 16018755, "0.2": 16625249, "0.3": 16703627, "0.4": 15136152, "0.5": 11263396, "0.6": 6734501, "0.7": 3311805 } }

 

Videos

Manifold Viewer - How Matrix Filters Work - The easy, simple way to learn how filters work! Watch this action-packed video using Manifold Viewer that illustrates how matrix filters, also known as convolution filters, work. In addition to explaining filters, the video provides a real-life look at simple Manifold techniques for moving objects around in drawings using the Shift transform, and fast and easy use of Selection and tables to quickly put desired values into attributes. Sound technical? Sure, but in a very easy and simple way.

 

Manifold Viewer - Create Custom GPU Accelerated Filters in Seconds - A technical video using the free Viewer showing how to create your own, fully custom, fully GPU-parallel, convolution matrix filters, including Emboss, Sobel, Prewitt, and Kirsch edge detection and many more, for use in Viewer or Release 9. Modify the spatial SQL examples in the downloadable example project to specify a custom matrix and in seconds your custom filter can do image processing at GPU-parallel speeds. Viewer is read-only, but you can copy and paste the query text for custom filters to and from Notepad or any other text editor. Download the Custom_Filter_Examples.mxb sample project to try out the video in Viewer or Release 9.

 

Manifold Viewer - Speed Demo with 1280 GPU Cores - 2 Minutes vs 5 Days - Watch the free Manifold Viewer run CPU parallel and GPU parallel with 8 CPU cores and 1280 GPU cores to absolutely crush a job, doing in 2 minutes what would take non-GPU-parallel software 5 days. The video shows Viewer instantly pop open a 4 GB project that contains a huge, multi-gigabyte terrain elevation surface for Crater Lake, Oregon. With a point and click - no parallel code required - we compute the mean curvature at each pixel of the surface using a 7x7 matrix in under two minutes. We combine that with the original surface for enhanced hill shaded effects to better see details. Using an 11x11 matrix takes just over two minutes, a huge computation that takes days in non-GPU-parallel GIS packages.

 

See Also

Images

 

Tables

 

Data Types

 

Transform Pane

 

How Matrix Filters Work

 

Command Window

 

SQL Functions

 

Example: Display an NAIP Four Band Image as Color Infrared (CIR) - How to use the Style pane for images to re-assign channels in a four band NAIP image to produce a Color Infrared (CIR) image display.

 

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.

 

SQL Example: Process Images using Dual 3x3 Filters  - A continuation of the above topic, extending the example query to utilize two filters for processing, as commonly done with Sobel and Prewitt two filter processing.

 

SQL Example: Process RGB Images using Matrix Filters - A continuation of the above two topics, extending the example query to process three channel, RGB images.

 

Example: Enhance Terrain with Curvatures -  We enhance a terrain showing Crater Lake, Oregon, by using mean curvature calculation to bring out details.   The example uses a 4 GB project containing a large terrain elevation surface.  Using a point-and-click dialog with no SQL, we apply automatic CPU parallelism and GPU parallelism to absolutely crush a task in two and a half minutes that would take non-parallel software days.

 

Example: Rearrange Channels using an Expression - We use a simple expression in the Transform pane to rearrange the order of channels within the data.