Fetching ST_PixelData from an ST_Raster column into an ST_PixelData column

You can use the ST_Raster getPixelData function to extract the ST_PixelData value from an ST_Raster value. This value can be inserted into an ST_Pixeldata or it can be fetched into a ST_PixelData variable.

Steps:
  1. Use a SELECT statement to extract the ST_PixelData value from the ST_Raster.

    If you place the SELECT statement inside an INSERT statement, the result of the extracted ST_PixelData will be inserted into the specified column.

    In the following examples, the first-level ST_PixelData value is fetched from the ST_Raster column of the urban_area table and inserted into the ST_PixelData column, pdata, of the pixels table.

    Oracle

    INSERT INTO PIXELS (pdata)
    SELECT t.raster.getPixelData('level=1')
    FROM URBAN_AREA t
    WHERE t.raster.raster_id = 1;

    PostgreSQL

    INSERT INTO pixels (pdata)
    SELECT getPixelData(raster,'level=1')
    FROM urban_area
    WHERE raster_id(raster) = 1;

    SQL Server

    INSERT INTO pixels (pdata)
    SELECT raster.getPixelData('level=1')
    FROM urban_area
    WHERE raster.raster_id = 1;

    In these examples, the ST_PixelData is fetched from the ST_Raster column of the urban_area table into the ST_PixelData variable using the ST_Raster getPixelData method.

    Oracle

    DECLARE
    p sde.st_pixeldata;
    BEGIN
     SELECT t.raster.getPixelData() INTO p
     FROM URBAN_AREA t
     WHERE t.raster.rasterid = 1;
    END;
    /

    PostgreSQL

    DROP FUNCTION IF EXISTS fetch_pixeldata();
    
    CREATE OR REPLACE FUNCTION fetch_pixeldata()
    RETURNS integer AS '
    
    DECLARE p st_pixeldata;
    BEGIN
     SELECT getPixelData(raster) INTO p
     FROM urban_area
     WHERE raster_id(raster) = 1;
    END;'
    LANGUAGE plpgsql;
     
    SELECT edit_pixeldata();
    DROP FUNCTION IF EXISTS fetch_pixeldata();

    SQL Server

    DECLARE @p ST_PIXELDATA;
    SET @p = (SELECT raster.getPixelData(NULL)
              FROM urban_area
              WHERE raster.raster_id = 1);

Related Topics


11/18/2013