Help

Workflow: Mosaicking raster data to an existing ST_Raster value

Complexity:
Intermediate
Data Requirement:
Use your own data
Goal:
Use SQL UPDATE statements to remove a color map from ST_Raster values in specific records in the urban_area table, mosaic rasters to the raster values in these records, then reapply a color map to the same values.

You can use a SQL UPDATE statement and the ST_Raster mosaic function to mosaic rasters to an existing ST_Raster value. You can use an image file, a folder of multiple images, or values extracted from another ST_Raster column as input to mosaic rasters to an existing value.

However, before you can mosaic raster to an existing value, you must remove the color map, if present, from the existing ST_Raster value. After the mosaic operation is done, you can add the color map back to the ST_Raster value.

The examples in this topic show you how to remove a color map from specific records in the urban_area table, add rasters to the raster values in these records, then reapply a color map.

Removing a color map

Use the ST_Raster deleteColormap function to remove a color map from an ST_Raster value.

This must be done before additional rasters can be mosaicked to the raster values in the specified table.

  1. Use an UPDATE SQL statement with the deleteColormap function to remove the color map from raster values in those rows in the urban_area table that have a name value equal to ALL_CITIES.

    Oracle

    UPDATE URBAN_AREA t
SET RASTER = t.raster.deleteColormap()
WHERE NAME = 'ALL_CITIES';

    PostgreSQL

    UPDATE urban_area
SET raster = deleteColormap(raster)
WHERE name = 'all_cities';

    SQL Server

    UPDATE urban_area
SET raster = deleteColormap()
WHERE name = 'all_cities';

    After deleteColormap successfully completes, the raster value renders as grayscale rather than a color-mapped value.

Mosaicking more raster data to an existing ST_Raster value

The mosaic operation iteratively adds more raster data to an existing raster value. In this manner, a raster value can grow to exceed many terabytes; the raster value is limited only by the storage capacity of the database management system (DBMS).

Example 1: Inserting all rasters in a folder to an ST_Raster column

You can use the mosaic function's recursive option in the SQL UPDATE statement to have the specified folder scanned repeatedly for a specified type of file. In these examples, the cities folder is recursively searched for all files with a .tif extension. These files are then mosaicked to the raster value identified as all_cities in the urban_area table.

  1. Mosaic all the .tif files under a specified folder to an existing ST_Raster value using the mosaic function and the recursive option.

    Oracle

    UPDATE URBAN_AREA t
SET RASTER = t.raster.mosaic('E:\cities\*.tif', 
'recursive,log=E:\log.txt')
WHERE NAME = 'ALL_CITIES';

    PostgreSQL

    UPDATE urban_area
SET raster = mosaic(image,'E:\data\*.tif', 
'recursive,log=E:\log.txt')
WHERE name = 'all_cities';

    SQL Server

    UPDATE urban_area
SET raster = raster.mosaic('E:\data\*.tif',NULL,
'recursive,log=E:\log.txt')
WHERE name = 'all_cities';

Example 2: Extracting ST_PixelData and adding it to an ST_Raster column

The first pyramid level of the pixels of the raster column of the japan table whose name column value is tokyo are mosaicked to the raster column of the urban_area table whose name column value is all_cities. In this case, the ST_Raster getPixelData function extracts the ST_PixelData value from the ST_Raster value into the predefined ST_PixelData variable called data. The variable is then passed to the ST_Raster constructor to convert it back to the ST_Raster format.

  1. Use the getPixelData function to extract an ST_PixelData value from the ST_Raster value in the japan table and insert it to a raster value in the urban_area table.

    Oracle

    DECLARE
data ST_PIXELDATA;
BEGIN
SELECT t.raster.getPixelData('level=1') 
INTO data
FROM JAPAN t 
WHERE NAME = 'TOKYO';

UPDATE URBAN_AREA t
SET image = t.raster.mosaic(data,'log=E:\log.txt')
WHERE NAME = 'ALL_CITIES';
END;
/

    PostgreSQL

    DROP FUNCTION IF EXISTS  mosaic_from_pixeldata();
CREATE OR REPLACE FUNCTION
mosaic_from_pixeldata()
RETURNS integer AS '
 DECLARE data sde.st_pixeldata;
 BEGIN
SELECT getPixelData(image) INTO data
FROM japan
WHERE name = 'tokyo';
UPDATE urban_area
SET raster = mosaic(raster,data,''log=E:\log.txt'')
WHERE name = ''all_cities'';
END;'
 
LANGUAGE plpgsql;
SELECT mosaic_from_pixeldata();
DROP FUNCTION IF EXISTS
mosaic_from_pixeldata();

    SQL Server

    DECLARE
@data ST_PIXELDATA;
SET @data =
(SELECT raster.getPixelData('level=1')
FROM japan
WHERE name = 'tokyo');
UPDATE urban_area
SET raster =
raster.mosaic(NULL,@data,'log=E:\log.txt');

Applying a color map to the updated ST_Raster value

Once you finish updating the ST_Raster value, you can reapply a color map to define the colors used to display the raster value. Use the setColormap function to reapply a color map to the raster.

The setColormap function requires a TIFF image file as input. In the following examples, the color map from the citycolors.tif file is used to add a color map to those raster values in the urban_area table for all rows that have a name value equal to all_cities.

  1. Use an UPDATE SQL statement and the citycolors.tif input image file to add a color map to records in the urban_area table that contain a name value equal to all_cities.

    Oracle

    UPDATE URBAN_AREA t
SET raster = t.raster.setColormap('/net/gis/gis1/citycolors.tif')
WHERE name = 'ALL_CITIES';

    PostgreSQL

    UPDATE urban_area
SET raster = setColormap(raster,'/net/gis/gis1/citycolors.tif')
WHERE name = 'all_cities';

    SQL Server

    UPDATE urban_area
SET raster = raster.setColormap('/net/gis/gis1/citycolors.tif')
WHERE name = 'all_cities';

Related topics