SQL Example: Compute Area of an NDVI Interval

In this example, 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.

 

This example uses NDVI images created in the SQL Example: Create NDVI Displays topic.

 

 

The NDVI raster image, called NDVI, shown above is colored in the Style pane using a palette from the USDA Crop Explorer, as discussed in the SQL Example: Create NDVI Displays topic.

 

With the focus on the open NDVI image window, we take a look at the Style pane to see the palette in use.

 

 

Using the above palette, all pixels in the NDVI image which have values between 0 and 0.1 are colored medium brown.  According to the USDA Crop Explorer legend for NDVI imagery, that interval covers sparse vegetation.  In this example we would like to calculate the area covered by all such pixels.   That will give us the total area in the NDVI image where vegetation is sparse, for example, areas covered by highways and parking lots.

 

To calculate the area covered by all pixels within a given range of values, we will need to know the areal size of each pixel.    That value is reported by the Info pane.

 

With the focus on the open NDVI image, we take look at the Info pane.

 

 

The Info pane tells us our NDVI raster has pixels that are 0.6 meters by 0.6 meters in size.  That means each pixel covers 0.36 square meters.   The Info pane also reports the image uses the NAD83 / UTM zone 10N (EPSG:26910) coordinate system.   That is a reasonably accurate, meter-based coordinate system suitable for making measurements of areas.    If the image used some a coordinate system unsuited for making areal measurements, for example, Latitude / Longitude, we would first want to reproject the image into a sensible coordinate system.

 

We now have all the information we need to write a query to do the desired calculation.

 

We choose View - New Command Window - SQL to launch a Command Window for writing queries.

 

 

In the Command Window we enter the query:

 

SELECT Sum(TileValueCount(

     TileMaskRange([Tile], 0, 0.1, TRUE, TRUE)

   )) * 0.36 FROM [NDVI];

 

With the focus on the Command Window we run the query by pressing the ! Run button in the main toobar.

 

 

The result reported is 5252713.92, the total number of square meters covered by pixels which have a value between 0 and 0.1, inclusive.

How the Query Works

The TileValueCount SQL function gives the number of non-NULL pixels in a tile.   The example provided for the TileValueCount function in the Tile SQL Functions topic shows how to compute the area covered by the non-NULL pixels in an image:

 

VALUE @pixel_size INT64=100;

SELECT Sum(TileValueCount([tile])) * @pixel_size FROM [image];

 

That example uses a VALUE statement to declare a global variable giving the areal size of pixels, and then it uses that global variable in the query instead of using a literal number giving the areal size of pixels.  

 

In our example, if we wanted the total area covered by non-NULL pixels in our NDVI image we could write a very similar statement:

 

SELECT Sum(TileValueCount([Tile])) * 0.36 FROM [NDVI];

 

We could enter that query into the Command Window to see what it reports.

 

 

The result is 41125464, the total area in square meters under all non-NULL pixels in the NDVI image.   Counting only the areal contribution of non-NULL images is important, since the image is slightly skewed, as a rhomboid, as a result of the projection in use.  

 

However, for our task we do not want the area under all non-NULL pixels, we only want the area under non-NULL pixels within a certain range of pixel values.  To get only pixels that have a value between 0 and 0.1, we use the TileMaskRange function, which returns all pixels with values within the given range, and returns NULL for pixels outside that range.

 

TileMaskRange([Tile], 0, 0.1, TRUE, TRUE)

 

The first TRUE argument tells the function that the range is inclusive, to include the 0 and 0.1 values when picking pixels in the range.  The second TRUE argument says to pass those pixel values and mark all pixels outside the range as NULL.

 

If we take the TileMaskRange expression above and use it as the source of tiles within the TileValueCount function, we will count the number of pixels with values from 0 to 0.1.  If we multiply that number by the areal size of each pixel, we get the area of pixels within the indicated range.

 

The final query, with some additional white space formatting for legibility, becomes:

 

SELECT Sum(TileValueCount(

     TileMaskRange([Tile], 0, 0.1, TRUE, TRUE)

   )) * 0.36 FROM [NDVI];

 

If we wanted, we could declare global variables using VALUE to modularize the query, so it could be more easily used with other images that had different sized pixels, or using different ranges, for example:

 

VALUE @image NVARCHAR = [NDVI];

VALUE @pixel_size FLOAT64 = 0.36;

VALUE @pixel_min FLOAT64 = 0;

VALUE @pixel_max FLOAT64 = 0.1;

 

SELECT Sum(TileValueCount(

     TileMaskRange([Tile], @pixel_min, @pixel_max, TRUE, TRUE)

   )) * @pixel_size FROM @image;

 

We can load that query into the Command Window to see what happens:

 

 

As expected, the result is the same.  We can save the above query using Edit - Save as Query to have it available for computing the NDVI area for other intervals.

 

Videos

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.

See Also

Info Pane

 

Style: Images

 

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.

 

Example: TileMaskRange Expressions - Using 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.

 

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.