Help

Accessing ST_Raster properties

Complexity:
Intermediate
Data Requirement:
Use your own data
Goal:
Learn which functions return information about an ST_Raster value and how to use them.

The ST_Raster type includes several functions that return the properties of an ST_Raster value. Examples of using these functions are included in this topic. Be sure to substitute values appropriate for your data in the SQL examples.

Using the describe function

Use the describe function to obtain a list of ST_Raster value properties. Executing the describe function without an argument returns the general properties of the ST_Raster value.

To return properties about a color map if the raster value has one, include the colormap argument. If the raster value does not have a color map, and this argument is specified, an error is returned. If the ST_Raster value has a color map, the hasColormap function returns true (1). In addition to the general properties of the ST_Raster value, specifying the colormap argument also returns the color map index that has been applied.

Storage properties about an ST_Raster value can be returned by including the storage argument with the describe function. This also lists the storage properties ordered by pyramid level and subordered by raster band. The storage properties include the minimum, maximum, mean, standard deviation of the pixel values within each band, and pyramid level. The storage properties also include a count of the total number of pixels as well as the compressed size broken down by band and pyramid level.

Returning general properties of an ST_Raster value

If you use the ST_Raster describe function without specifying an argument, the query returns the general properties of each ST_Raster value.

The following examples demonstrate using a SELECT statement with the describe function to return the general properties of the ST_Raster values in a table named urban_areas. Only those records that meet the criteria of the WHERE clause are returned.

  1. Use the SELECT statement appropriate to your database management system (DBMS).

    Oracle

    SELECT t.raster.describe()
FROM URBAN_AREA t
WHERE NAME = 'ALL_CITIES';

    PostgreSQL

    SELECT describe(raster)
FROM urban_area
WHERE name = 'all_cities';

    SQL Server

    SELECT raster.describe(NULL)
FROM urban_area
WHERE name = 'all_cities';

Returning storage properties of an ST_Raster value

The ST_Raster describe function with the storage argument returns the general properties of each ST_Raster value along with its storage properties.

The following examples show how to use the describe function with the storage argument to return the storage properties of the ST_Raster for the urban_areas table.

  1. Use the SELECT statement appropriate to your DBMS.

    Oracle

    SELECT t.raster.describe('storage')
FROM URBAN_AREA t
WHERE NAME = 'ALL_CITIES';

    PostgreSQL

    SELECT describe(raster,'storage')
FROM urban_area
WHERE name = 'all_cities';

    SQL Server

    SELECT raster.describe('storage')
FROM urban_area
WHERE name = 'all_cities';

Returning the colormap index of an ST_Raster

The ST_Raster describe function with the colormap argument returns the general properties and the colormap index of each ST_Raster value. If the ST_Raster value does not have a colormap, an error is returned when the colormap argument is included.

  1. Use the SELECT statement appropriate to your DBMS.

    Oracle

    SELECT t.raster.describe('colormap')
FROM URBAN_AREA t
WHERE NAME = 'ALL_CITIES';

    PostgreSQL

    SELECT describe(raster,'colormap')
FROM urban_area
WHERE name = 'all_cities';

    SQL Server

    SELECT raster.describe('colormap')
FROM urban_area
WHERE name = 'all_cities';

Using the hasColormap function to determine the presence or absence of a colormap

Use the hasColormap function to determine whether or not an ST_Raster value has a colormap index applied to it. The hasColormap function returns a Boolean true if a colormap index is present; otherwise, it returns false.

The hasColormap function on the SELECT clause returns a 1 if the ST_Raster value has a colormap and 0 if it does not.

  1. Oracle

    SELECT t.raster.hasColormap(), NAME 
FROM URBAN_AREA t;

    PostgreSQL

    SELECT hasColormap(raster), name 
FROM urban_area;

    SQL Server

    SELECT raster.hasColormap(), name 
FROM urban_area;

Restricting the return set to records that have or do not have a colormap

The hasColormap function can be added to the WHERE clause to return those records that either do or do not have a colormap.

  1. Use the SELECT statement appropriate to your DBMS.

    Oracle

    SELECT t.raster.describe('colormap')
FROM URBAN_AREA t
WHERE t.raster.hasColormap(raster) = 1;

    PostgreSQL

    SELECT describe(raster,'colormap')
FROM urban_area
WHERE hasColormap(raster) = 1;

    SQL Server

    SELECT raster.describe('colormap')
FROM urban_area
WHERE raster.hasColormap() = 1;

Using the hasStats function to determine if ST_Raster statistics have been calculated

The hasStats function returns a Boolean true if a ST_Raster value has had its statistics calculated.

Use the hasStats function in the SELECT clause to determine if statistics have been calculated on the ST_Raster value. This function returns 1 for those ST_Raster values that have statistics and 0 for those that do not.

  1. Oracle

    SELECT t.raster.hasStats(),NAME
FROM URBAN_AREA t;

    PostgreSQL

    SELECT hasStats(raster),name
FROM urban_area;

    SQL Server

    SELECT raster.hasStats(),name
FROM urban_area;

The hasStats function can be applied to the WHERE clause to specify that the query returns those records that have or do not have statistics. In these examples, the WHERE clause filters for only those records that have statistics on the ST_Raster value. Then the statistics are removed from those raster values.

  1. Oracle

    UPDATE URBAN_AREA t
SET raster = t.raster.deleteStats()
WHERE t.raster.hasStats() = 1;

    PostgreSQL

    UPDATE urban_area
SET raster = deleteStats(raster)
WHERE hasStats(raster) = 1;

    SQL Server

    UPDATE urban_area
SET raster = raster.deleteStats()
WHERE raster.hasStats() = 1;

Returning the compression type of an ST_Raster

The getCompressionType returns the compression property of a raster. The four possible compression types are as follows:

  • NONE: No compression was used to store the data.
  • LZ77: LZ77 uses a length compression that is lossless and is best used on data of low variability or where other compression algorithms cannot be used.
  • JPEG: JPEG stands for Joint Photographic Experts Group, the committee that developed the compression standard. JPEG is a lossy compression type. JPEG compression can only be applied to 8-bit data that does not have a colormap index.
  • JP2: This is the JPEG 2000 wavelet-based compression type. This compression type can be applied to either 8-bit or 16-bit data.

LZ77 compression is used in most cases. However, JPEG compression is used on three-band, 8-bit data or on gray-scale data of high variability. JPEG 2000 compression is usually reserved for 16-bit data.

In these examples, the storage argument is specified with the describe function, and the getCompressionType function is used in the WHERE clause to return only those ST_Raster values that have JPEG compression.

  1. Use the SELECT statement appropriate to your DBMS.

    Oracle

    SELECT t.raster.describe('storage')
FROM URBAN_AREA t
WHERE t.raster.getCompressionType() = 'JPEG';

    PostgreSQL

    SELECT describe(raster,'storage')
FROM urban_area
WHERE getCompressionType(raster) = 'JPEG';

    SQL Server

    SELECT raster.describe('storage')
FROM urban_area
WHERE raster.getCompressionType() = 'JPEG';

Determining the pyramid interpolation type of an ST_Raster value

The pyramid interpolation type is the algorithm during the construction of the pyramid. The getInterpolationType function of the ST_Raster type returns the interpolation type that was used to build the pyramid. If the pyramid is not present, the function returns a null value. The three possible interpolation types are NEAREST, BILINEAR, and BICUBIC.

Typically, the nearest neighbor pyramid interpolation type is used whenever bilinear cannot be. Bilinear is favored because it usually provides a smoother product. However, since Bilinear does not maintain the pixel values of the predecessor level, it cannot be used on colormapped data and does not make sense for pixel data that is less than 8 bit.

These queries return the pyramid interpolation type of all ST_Raster values that have a pyramid.

  1. Oracle

    SELECT t.raster.getInterpolationType()
FROM URBAN_AREA t
WHERE t.raster.getPyramidLevel() > 0;

    PostgreSQL

    SELECT getInterpolationType(raster)
FROM urban_area
WHERE getPyramidLevel(raster) > 0;

    SQL Server

    SELECT raster.getInterpolationType()
FROM urban_area
WHERE raster.getPyramidLevel() > 0;

Determining the band interleave type of an ST_Raster value

The ST_Raster type supports both sequential (BSQ) and contiguous (BIP) band interleave.

The BIP interleave applies only to three-band, 8-bit data that is stored as a single band in (r0, g0, b0, r1, g1, b1 … rn, gn, bn) sequence, whereas BSQ stores the bands in separate sequential order (r0...rn),(g0...gn),(b0...bn).

The following queries return the interleave type for each ST_Raster value in the ST_Raster column of a table.

  1. Oracle

    SELECT t.raster.getInterleaveType()
FROM URBAN_AREA t;

    PostgreSQL

    SELECT getInterleaveType(raster)
FROM urban_area;

    SQL Server

    SELECT raster.getInterleaveType()
FROM urban_area;

Determining the pixel type of an ST_Raster

The getPixelType function returns the pixel type of an ST_Raster value. The following pixel types are supported by the ST_Raster type:

  • 1bit: 1-bit data
  • 4bit: 4-bit data
  • uint8: Unsigned 8-bit integer
  • int8: Signed 8-bit integer
  • uint16: Unsigned 16-bit integer
  • int16: Signed 16-bit integer
  • uint32: Unsigned 32-bit integer
  • int32: Signed 32-bit integer
  • float: Single-precision, floating-point number
  • double: Double-precision, floating-point number

These update statements build pyramids for all the ST_Raster values in the ST_Raster column, but only if the pixel type of the ST_Raster value is an unsigned, 8-bit integer.

  1. Oracle

    UPDATE URBAN_AREA t
SET raster = t.raster.buildPyramid('bilinear')
WHERE t.raster.getPixelType() = 'UINT8';

    PostgreSQL

    UPDATE urban_area
SET raster = buildPyramid(raster,'bilinear')
WHERE getPixelType(raster) = 'UINT8';

    SQL Server

    UPDATE urban_area
SET raster = raster.buildPyramid('bilinear')
WHERE raster.getPixelType() = 'UINT8';

Related topics