Inserting images into a table that contains an ST_Raster column

To insert raster data into a raster column of type ST_Raster, you must provide an appropriate data source. Unless the data source is from another ST_Raster column, it must be converted to the ST_Raster type's format. The conversion is accomplished with the ST_Raster constructor.

The ST_Raster constructor accepts two possible data sources: a TIFF file or an ST_PixelData object.

Shown below are the SQL INSERT statements with the three possible data sources for each of the three supported database management systems.

Inserting raster values from one ST_Raster column into another

Steps:
  1. Insert the results of a query of an existing ST_Raster column into another ST_Raster column.

    Oracle

    INSERT INTO URBAN_AREA (RASTER,NAME) 
    SELECT RASTER,'BOSTON' 
    FROM BOSTON_AREA;
    

    PostgreSQL

    INSERT INTO urban_area (raster) 
    SELECT raster 
    FROM boston_area;
    

    SQL Server

    INSERT INTO urban_area (raster) 
    SELECT raster 
    FROM boston_area;
    

Converting a TIFF file to an ST_Raster

Steps:
  1. Convert a TIFF file to ST_Raster type using the ST_Raster constructor and insert it into an ST_Raster column.

    Oracle

    INSERT INTO URBAN_AREA 
    VALUES(SDE.ST_RASTER('C:\milwakee.tif','compression=lz77');
    

    PostgreSQL

    INSERT INTO urban_area 
    VALUES (st_raster('C:\milwaukee.tif','compression=lz77));
    

    SQL Server

    INSERT INTO urban_area 
    VALUES(st_raster::construct('C:\milwaukee.tif',NULL,'compression=lz77'));
    
    CautionCaution:

    Be sure the path specified is valid for the server where the database and file are located.

Converting an ST_PixelData value to ST_Raster

Steps:
  1. Convert an ST_PixelData value to an ST_Raster value using the ST_Raster constructor and insert it into an ST_Raster column.

    Oracle

    DECLARE
    data SDE.ST_PIXELDATA;
    BEGIN
    SELECT t.raster.getPixelData('level=1') INTO DATA FROM ALANTA
    t;
    INSERT INTO URBAN_AREA
    VALUES (sde.st_raster(data, 'compression=lz77'));
    END;
    /
    

    PostgreSQL

    DROP FUNCTION IF EXISTS insert_pixeldata();
    CREATE OR REPLACE FUNCTION insert_pixeldata()
    RETURNS void AS '
    DECLARE data st_pixeldata;
    BEGIN 
     SELECT getPixelData(image, 'level=1' ) 
     INTO data 
     FROM atlanta;
     INSERT INTO urban_area
     VALUES
     (st_raster(data,''compression=lz77''));
    END;'
    LANGUAGE plpgsql;
    SELECT insert_pixeldata();
     DROP FUNCTION IF EXISTS
    insert_pixeldata();
    

    SQL Server

    DECLARE
    @data ST_Pixeldata;
    SET @data =
    (SELECT image.getPixelData('level=1') FROM atlanta);
     INSERT INTO urban_area
     VALUES (ST_Raster::construct(NULL, @data,
     'compression=lz77'));
    

Related Topics

6/19/2015