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.

Calculating NDVI

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.

 

 

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.

 

We will do that again, but first we will set up the Transform pane to use the names we want for the Result destination, so those will automatically appear in the query.  We may as well have Manifold use those names in the query it writes for us.

 

 

We can start with the focus either on the image window or on the image's table, open in a window as seen above.  

 

In the illustration above we have right-clicked the column header for the Tile field and chosen Style, to change the format for the Tile field to a format that reports the size of the tile as well as the data type of each channel and the number of channels.   The other illustrations in this topic showing image tables have also changed the Tile field format to show the data type of channels.

 

With the focus on the table window, in the Transform pane we choose the Tile field, and then we double-click the Copy template to launch it.

 

 

In the Copy template we choose channel as the Use option.  For the Channel to extract we choose channel 0.

 

For the Result destination we choose New Table.   For Channel type we use the default float64.  The source image uses uint8 but we want to use float64 in the new image we create.

 

We enter NDVI for the name of the New image and NDVI Tiles for the name of the New table.  We can use whatever names we like, but it is wise to use short names that remind us what the created components are supposed to be.

 

Press the Edit Query button.   

 

Manifold pops open a Command Window that is loaded with the SQL query the transform template uses to do the job.   In this topic, to save space we will simply show the SQL and not illustrate the Command Window.   The query text:

 

-- $manifold$

--

-- Auto-generated

--

-- Copy

--   Layer: m_4012230_nw_10_h_20160717 Tiles

--   Field: Tile

--   Use: channel

--   Channel: channel 0

--   Result: (new table)

--   Channel type: float64

--   New image: NDVI

--   New table: NDVI Tiles

--   Resources: all CPU cores, all GPU cores

--   Transform selection only: FALSE

--

 

-- prepare begin

 

CREATE TABLE [NDVI Tiles] (

  [X] INT32,

  [Y] INT32,

  [mfd_id] INT64,

  [Tile] TILE,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

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

  PROPERTY 'FieldCoordSystem.Tile' ComponentFieldCoordSystem([m_4012230_nw_10_h_20160717 Tiles], 'Tile'),

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

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [NDVI] (

  PROPERTY 'Table' '[NDVI Tiles]',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'Rect' CAST(ComponentFieldBounds([m_4012230_nw_10_h_20160717 Tiles], 'Tile', FALSE) AS NVARCHAR)

);

 

-- prepare end

 

DELETE FROM [NDVI Tiles];

INSERT INTO [NDVI Tiles] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

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

FROM [m_4012230_nw_10_h_20160717 Tiles];

 

TABLE CALL TileUpdateFieldPyramids([NDVI Tiles], 'Tile');

ALTER IMAGE [NDVI] (

  ADD PROPERTY 'Rect' Coalesce(

    CAST(ComponentFieldBounds([NDVI Tiles], 'Tile', TRUE) AS NVARCHAR),

    ComponentProperty([NDVI], 'Rect'))

);

 

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

 

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

 

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

 

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

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

 

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 blue and removing unnecessary comments at the beginning:

 

-- $manifold$

 

-- prepare begin

 

CREATE TABLE [NDVI Tiles] (

  [X] INT32,

  [Y] INT32,

  [mfd_id] INT64,

  [Tile] TILE,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

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

  PROPERTY 'FieldCoordSystem.Tile' ComponentFieldCoordSystem([m_4012230_nw_10_h_20160717 Tiles], 'Tile'),

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

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [NDVI] (

  PROPERTY 'Table' '[NDVI Tiles]',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'Rect' CAST(ComponentFieldBounds([m_4012230_nw_10_h_20160717 Tiles], 'Tile', FALSE) AS NVARCHAR)

);

 

-- prepare end

 

DELETE FROM [NDVI Tiles];

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 Tiles];

 

TABLE CALL TileUpdateFieldPyramids([NDVI Tiles], 'Tile');

ALTER IMAGE [NDVI] (

  ADD PROPERTY 'Rect' Coalesce(

    CAST(ComponentFieldBounds([NDVI Tiles], 'Tile', TRUE) AS NVARCHAR),

    ComponentProperty([NDVI], 'Rect'))

);

 

Run the above query by pressing the ! Run button in the main toolbar.   A new NDVI image and table appear in the Project pane.   We double-click open the new NDVI image:

 

 

The image is a single channel image with pixels having floating point values between -1 and 1.  Manifold displays it using a reasonable guess as to the range to use for pixel values, displaying the image as grayscale.  We must use the Style pane to color it.

 

 

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.

 

 

The result is a classic NDVI display, seen above with the natural color RGB image seen below 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.  

 

 

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 can zoom further into the windows for a closer look:

 

 

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 below for comparison.  

 

 

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.    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 the Microsoft Edge browser.

 

 

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

 

 

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.

 

 

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.   

 

 

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

 

 

 

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.

 

 

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.

 

 

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.

 

 

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

 

 

We zoom farther out, to a slightly different location.

 

 

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

 

 

The original image is shown above, panned and zoomed to the same location, for comparison.

 

See a related topic, SQL Example: Compute Area of an NDVI Interval, to learn how to quickly calculate the area covered by a particular interval.   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.

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.

 

 

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.

 

 

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.

 

 

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.

 

 

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.

 

 

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.  (The Breaks number of 10 is what would be used if the Tally button were pressed, and is not the current number of intervals used.)

A More Advanced Query

We can improve our query by adding an ALTER IMAGE statement to add a StylePixel property to the image, using whatever JSON string we desire, automatically setting that Style property.   That will automatically color the image using whatever palette we want.

 

-- $manifold$

 

-- prepare begin

 

CREATE TABLE [NDVI Tiles] (

  [X] INT32,

  [Y] INT32,

  [mfd_id] INT64,

  [Tile] TILE,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

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

  PROPERTY 'FieldCoordSystem.Tile' ComponentFieldCoordSystem([m_4012230_nw_10_h_20160717 Tiles], 'Tile'),

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

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [NDVI] (

  PROPERTY 'Table' '[NDVI Tiles]',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'Rect' CAST(ComponentFieldBounds([m_4012230_nw_10_h_20160717 Tiles], 'Tile', FALSE) AS NVARCHAR)

);

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 } }'

);

 

-- prepare end

 

DELETE FROM [NDVI Tiles];

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 Tiles];

 

TABLE CALL TileUpdateFieldPyramids([NDVI Tiles], 'Tile');

ALTER IMAGE [NDVI] (

  ADD PROPERTY 'Rect' Coalesce(

    CAST(ComponentFieldBounds([NDVI Tiles], 'Tile', TRUE) AS NVARCHAR),

    ComponentProperty([NDVI], 'Rect'))

);

 

Whenever we run the above query, it will create an image using the "leaf" palette and then fill it with pixels computed using the NDVI equation.

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$

 

-- prepare begin

 

CREATE TABLE [NDVI Tiles] (

  [X] INT32,

  [Y] INT32,

  [mfd_id] INT64,

  [Tile] TILE,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

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

  PROPERTY 'FieldCoordSystem.Tile' ComponentFieldCoordSystem([m_4012230_nw_10_h_20160717 Tiles], 'Tile'),

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

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [NDVI] (

  PROPERTY 'Table' '[NDVI Tiles]',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'Rect' CAST(ComponentFieldBounds([m_4012230_nw_10_h_20160717 Tiles], 'Tile', FALSE) AS NVARCHAR)

);

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 } }'

);

 

-- prepare end

 

DELETE FROM [NDVI Tiles];

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 Tiles];

 

TABLE CALL TileUpdateFieldPyramids([NDVI Tiles], 'Tile');

ALTER IMAGE [NDVI] (

  ADD PROPERTY 'Rect' Coalesce(

    CAST(ComponentFieldBounds([NDVI Tiles], 'Tile', TRUE) AS NVARCHAR),

    ComponentProperty([NDVI], 'Rect'))

);

 

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.

 

 

We click Edit - Schema to launch the Schema dialog.  

 

 

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

 

 

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.

 

We want the coordinate system to be the same as that used in the Tile field, so we click the projections picker and choose the same NAD / UTM zone 10N (EPSG:26910) projection used by the original NAIP image.  Make sure to use the override metrics option when saving a projection used by an image as a favorite.

 

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 Info 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.

 

 

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.

 

 

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

 

 

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.

 

 

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

 

 

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.

 

 

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. 

 

 

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 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'FieldTile' 'TileNDVI',

--Use same Rect as the original image

  PROPERTY 'Rect' CAST(ComponentFieldBounds([Source Tiles], 'Tile', FALSE) AS NVARCHAR)

);

 

-- 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 level

TABLE CALL TileUpdateFieldPyramids([Source Tiles], 'TileNDVI');

 

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 in the Source Tiles table.   That, too, is a simple adaptation of the CREATE IMAGE statement in the earlier queries.  We compute the Rect to use based on the original NAIP image of Redding.

 

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.

 

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:

 

 

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:

 

 

In the Source image we use Ctrl-Click-and-drag to select some tiles in the image.     We will use an expression to write the same color value into all of the pixels in the selected tiles.

 

 

We click into the Source Tiles table and scroll to one of the regions of selected tiles.   We could use the Transform pane with the focus either on the Source image or on the Source Tiles table, but in this case, for no particular reason, we will use the Transform pane with the focus on the table.

 

With the focus on the Source Tiles table, in the Transform pane we choose the Tile field and then we double-click the Expression template to launch it.

 

 

In the Expression template, the first thing we do is to check the Transform selection only box.  We want the transform to apply only to selected tiles, so we check the box first in case we forget to do that later.  

 

Next, we press the Edit Expression button to launch the expression builder dialog.

 

 

In the Expression dialog we enter the following expression:

 

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

 

The expression has had some newlines and white space added so it can fit entirely in view, without scrolling, to make a more compact illustration.  SQL ignores whitespace so we can use whitespace and newlines as we like to create more legible expressions.

 

Press OK to accept the expression and to return to the Transform pane.

 

 

To unpack the above expression 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, the data type used in tiles for the Source image, so we use a CASTV.  

 

Press Transform.

 

 

Nothing changes in the table, since all of the changes in pixel values have happened within the tiles.  However, the change is immediately visible in the image.

 

 

Instantly, the Source image is updated to show that all pixels within selected tiles have been replaced with a gray color.   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  (see the Notes below to understand why the box appears brown and not gray.

 

 

The Source NDVI image also is updated, and we see a message icon that we need to take action, so we click the View - Messages menu choice and update intermediate levels in the Source NDVI image, to get the display seen below:   

 

 

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 Source image? -  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 in the Source image.   Why does the illustration for the Source image 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 a brown shade instead of a gray shade.

 

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

Create USGS File Names with Transform

 

Finding NAIP Imagery with Viewer

 

5 Minute Tutorial - Previews

 

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 from the Examples page on the Manifold website 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

 

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

 

Example: 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.

 

SQL Example: Create Topographic Position Index TPI Displays - In this example, we use a few short lines of SQL to create a Topographic Position Index (TPI) display.  TPI characterizes the undulations of a terrain elevation surface.  TPI value above zero show locations that are higher than then average of immediately surrounding terrain, and thus tend to show ridges.   TPI values below zero show locations that are lower than the average of immediately surrounding terrain, and thus tend to show valleys.  TPI values that are zero show areas of constant slope.

 

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.