Workflow: Updating the pixel data in an ST_Raster value—Help

Updating an ST_Raster value with a temporary ST_PixelData variable
Updating an ST_Raster value with a persistent ST_PixelData column

Complexity:: Intermediate
Data Requirement:: Use your own data
Goal:: Learn two ways to fetch ST_PixelData from an ST_Raster value, edit it, and write the edited pixels back to the ST_Raster value.

The pixel data editing workflow involves the following:

Fetching the ST_PixelData from an ST_Raster value
Editing the pixels of the ST_PixelData
Writing the edited pixels of the ST_PixelData back to the ST_Raster value

You can fetch the ST_PixelData into a variable or insert it into a pixel table. If you fetch the value into a variable, all three steps can be done as part of the same SQL statement. If you insert the value to a table, the value is preserved, allowing you to do the steps of the workflow separately.

To complete this workflow, use the ST_Raster getPixelData function to fetch the ST_PixelData from the ST_Raster value, and use the ST_Raster mosaic function to write it back.

Updating an ST_Raster value with a temporary ST_PixelData variable

You can declare an ST_PixelData variable, then use the getPixelData function to fetch the ST_PixelData from an ST_Raster value into the variable.

Declare a variable, fetch the pixel data into the variable, edit the pixels, then mosaic the altered values back to the ST_Raster value.

In these examples, the ST_PixelData value is fetched from the ST_Raster column of the urban_area table, inserted into an ST_PixelData variable, edited, and mosaicked back to the ST_Raster value.

Oracle

--Define the variable.
DECLARE
  p sde.st_pixeldata;

--Fetch the ST_PixelData from the urban_area table into the variable.
BEGIN
   SELECT t.raster.getPixelData() INTO p
   FROM URBAN_AREA t
   WHERE t.raster.rasterid = 1;

--Reset pixel values
   FOR i IN 1..256 LOOP
     FOR j IN 1..256 LOOP
       IF(p.getvalue(1,i,j)=0) THEN
        p.setvalue(1,i,j,100);
       END IF;
     END LOOP;
   END LOOP;

--Mosaic the altered pixels to the urban_area table.
   UPDATE URBAN_AREA t
     SET raster = t.raster.mosaic(p,'compression=lz77,level=-1,nearest')
     WHERE t.raster.raster_id = 1;
END;
/

PostgreSQL

--Drop the variable (function) if it already exists.
DROP FUNCTION IF EXISTS edit_pixeldata();

--Define a varaible.
CREATE OR REPLACE FUNCTION edit_pixeldata()
RETURNS integer AS '
DECLARE p st_pixeldata;

--Fetch the ST_PixelData from the urban_area table into the variable.
BEGIN
   SELECT getPixelData(raster) INTO p
   FROM urban_area
   WHERE raster_id(raster) = 1;
   
--Reset pixel values
   FOR i IN 1..256 LOOP
     FOR j IN 1..256 LOOP
       IF(getvalue(p,1,i,j)=0) THEN
          p := setvalue(p,1,i,j,100);
       END IF;
     END LOOP;
   END LOOP;

--Mosaic the altered pixels to the urban_area table.
UPDATE urban_area
 SET raster = mosaic(raster,p,''compression=lz77,level=-1,nearest'')
 WHERE raster_id(raster) = 1;
END;'
LANGUAGE plpgsql;

--Drop the variable.
SELECT edit_pixeldata();
DROP FUNCTION IF EXISTS edit_pixeldata();

SQL Server

--Define the variable.
DECLARE @p ST_PIXELDATA, @i int, @j int;

--Fetch the ST_PixelData from the urban_area table into the variable.
SET @p = (SELECT raster.getPixelData(NULL)
          FROM raster.urban_area 
          WHERE raster.raster_id = 1);

--Reset pixel values.
WHILE( @i<256)
BEGIN
  WHILE(@j<256)
  BEGIN
    IF(@p.getValue(1,@i,@j)=0)
      SELECT @p = @p.setValue(1,@i,@j,100)
    SET @j=@j+1
  END 
  SET @i=@i+1 
END

--Mosaic the altered pixels to the urban_area table
UPDATE raster.urban_area 
SET raster = raster.mosaic(NULL,@p,'compression=lz77,level=-1,nearest')
WHERE raster.raster_id = 1;

Updating an ST_Raster value with a persistent ST_PixelData column

You can fetch the ST_PixelData value into a table, edit it, then mosaic it back to the ST_Raster value. This is done in three separate steps.

You might do this if you need to preserve the fetched value in the second table.

Create a table to store the pixel data value.

In these examples, a table named pixels is created.

Oracle	`CREATE TABLE pixels (pdata sde.ST_PixelData);`
PostgreSQL	`CREATE TABLE pixels (pdata sde.ST_PixelData);`
SQL Server	`CREATE TABLE pixels (pdata dbo.ST_PixelData);`

Use the getPixelData function inside an INSERT statement to fetch the ST_PixelData to a pixel column in the pixels table.

In these examples, the ST_PixelData value is fetched into the pixels table.

Oracle

INSERT INTO pixels (pdata)
   SELECT t.raster.getPixelData(), t.raster.raster_id 
   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;

Fetch the value from the pixels table to a variable, update the pixel values, then place the altered values back in the pixels table.

Oracle

DECLARE
p sde.st_pixeldata;
BEGIN
  
    SELECT pdata INTO p FROM PIXELS; 
    FOR i IN 1..256 LOOP
      FOR j IN 1..256 LOOP
        IF(p.getvalue(1,i,j)=0) THEN
          p.setvalue(1,i,j,100);
        END IF;
      END LOOP;
    END LOOP;

    UPDATE PIXELS t 
    SET pdata = p;
END;
/

PostgreSQL

DROP FUNCTION EXISTS edit_pixels();
CREATE OR REPLACE FUNCTION EDIT_PIXELS()
RETURNS void AS '
DECLARE p st_pixeldata;
BEGIN
  SELECT pdata INTO p 
  FROM pixels; 
  FOR i IN 1..256 LOOP
    FOR j IN 1..256 LOOP
      IF(getvalue(p,1,i,j)=0) THEN
         p := setvalue(p,1,i,j,100);
      END IF;
    END LOOP;
  END LOOP;
  UPDATE pixels SET pdata = p;
END;' LANGUAGE plpgsql;

SQL Server

DECLARE
@p sde.st_pixeldata, @i int, @j int;
SET @p = (SELECT pdata FROM pixels);
SET @i = 0;
SET @j = 0;
WHILE (@i<256)
BEGIN
    WHILE (@j<256)
    BEGIN
      IF(@p.getValue(1,@i,@j)=0)
          SELECT @p=@p.setValue(1,@i,@j,100);
        SET @j=@j+1
    END
    SET @i=@i+1
END
UPDATE raster.pixels SET pdata = @p;

Mosaic the altered values from the pixels table to the ST_Raster value in the urban_area table.

Oracle

DECLARE
p sde.st_pixeldata;
BEGIN
  
  SELECT pdata INTO p FROM PIXELS; 

  UPDATE URBAN_AREA t 
  SET raster = t.raster.mosaic(p,'compression=lz77,level=-1,nearest')
  WHERE t.raster.raster_id = 1; 

END;
/

PostgreSQL

SELECT edit_pixels();

DROP FUNCTION IF EXISTS edit_pixels();

DROP FUNCTION IF EXISTS update_pixels();

CREATE OR REPLACE FUNCTION UPDATE_PIXELS()
RETURNS void AS '
DECLARE p st_pixeldata;
BEGIN
  SELECT pdata INTO p FROM pixels; 
  UPDATE urban_area t 
  SET raster = mosaic(raster,p, ''compression=lz77,level=-1,nearest'')
  WHERE raster_id(raster) = 1; 
END;' LANGUAGE plpgsql;

SELECT update_pixels();

DROP FUNCTION IF EXISTS update_pixels();

SQL Server

DECLARE @p sde.ST_Pixeldata;
SET @p = (SELECT pdata FROM pixels);
UPDATE raster.urban_area 
SET raster = raster.mosaic(NULL,@p,'compression=lz77,level=-1,nearest')
WHERE raster.raster_id = 1;

Updating an ST_Raster value with a temporary ST_PixelData variable

Updating an ST_Raster value with a persistent ST_PixelData column

Related topics