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:
-
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:
-
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'));
Caution: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:
-
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
11/18/2013