Fetching ST_PixelData from an ST_Raster column into an ST_PixelData column—Help

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.

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