Extracting a raster subset from an ST_Raster value

The ST_Raster subset function returns a subset of itself as an ST_Raster value. This function can be useful for creating a new ST_Raster value from an old one in which you want only part of the ST_Raster value such as the pixels that fall within an extent defined by either geographic or pixel coordinates. Alternatively, you may want only a particular band of an ST_Raster value, or perhaps you want to restrict the output to a particular band and the pixels defined by an extent.

In the following examples, the subset function is used to extract the extent of the City of Paris by geographic coordinates. The result is inserted into a new record of the same table under the name of Paris. The subset result is compressed with JPEG compression and has pyramids built with bilinear interpolation before being inserted.

Steps:
  1. Use the SQL statement for your database management system.

    Oracle

    INSERT INTO URBAN_AREA (sde.st_raster, name)
    SELECT t.raster.subset(
    'extent=(2.313,48.825,2.381,48.891)',
    'compression=rgb,level=-1,bilinear'),'Paris'
    FROM URBAN_AREA t
    WHERE NAME = 'ALL_CITIES';
    

    PostgreSQL

    INSERT INTO urban_area (sde.st_raster, name)
    SELECT subset(raster,
    'extent=(2.313,48.825,2.381,48.891)',
    'compression=rgb,level=-1,bilinear'),'paris'
    FROM urban_area
    WHERE name = 'all_cities';
    

    SQL Server

    INSERT INTO urban_area (dbo.st_raster, name)
    SELECT raster.subset(
    'extent=(2.313,48.825,2.381,48.891)',
    'compression=rgb,level=-1,bilinear'),'Paris'
    FROM urban_area
    WHERE name = 'all_cities';
    

Related Topics

6/19/2015