Oracle GeoRaster的初步使用--遥感影像数据导入与导出

--创建一个自增的ID序列
CREATE SEQUENCE image_id_seq
	increment by 1
	start with 1
	nomaxvalue
	minvalue 1
	NOCYCLE;

--创建GeoRaster表（GeoRaster表就是带有SDO_GEORASTER类型列的表）
CREATE TABLE world_image(
	id NUMBER PRIMARY KEY,
	name VARCHAR(30),
	image SDO_GEORASTER
);

--创建一个自增的触发器
CREATE OR REPLACE TRIGGER image_id_trig
	BEFORE INSERT ON world_image
	FOR EACH ROW
	BEGIN
		SELECT image_id_seq.nextval INTO :new.id FROM dual;
	END;
/	
	
--创建GeoRaster Data Table（RDT）表用来存储GeoRaste影像数据
CREATE TABLE world_image_rdt OF SDO_RASTER
(
	PRIMARY KEY(RASTERID, PYRAMIDLEVEL, BANDBLOCKNUMBER, ROWBLOCKNUMBER, COLUMNBLOCKNUMBER)
)
LOB(RASTERBLOCK) STORE AS (NOCACHE NOLOGGING);

--插入GeoRaster表一条记录（初始化GeoRaster对象）
INSERT INTO world_image (name, image) VALUES ('earth', SDO_GEOR.INIT('WORLD_IMAGE_RDT'));

--获取文件读的权限(SCOTT和MDSYS用户都要获得读权限)
call dbms_java.grant_permission('MDSYS','SYS:java.io.FilePermission', '/home/oracle/images/earth.tif', 'read');
call dbms_java.grant_permission('SCOTT','SYS:java.io.FilePermission', '/home/oracle/images/earth.tif', 'read');
--导入遥感影像数据到GeoRaster Data Table表中
DECLARE
	img SDO_GEORASTER;
BEGIN
	--Select the georater column
	SELECT image INTO img FROM world_image WHERE name='earth' FOR UPDATE;
	--Import into the georater object
	SDO_GEOR.IMPORTFROM(img, 'blocksize=(512, 512)', 'TIFF', 'file', '/home/oracle/images/earth.tif');
	--update the column
	UPDATE world_image SET image=img WHERE name='earth';
	COMMIT;
END;
/

--获取文件写的权限
call dbms_java.grant_permission('MDSYS','SYS:java.io.FilePermission', '/home/oracle/images/earth_ex.tif', 'write');
call dbms_java.grant_permission('SCOTT','SYS:java.io.FilePermission', '/home/oracle/images/earth_ex.tif', 'write');

--导出遥感影像数据
DECLARE
	img SDO_GEORASTER;
BEGIN
	SELECT image INTO img FROM world_image WHERE name='earth';
	SDO_GEOR.EXPORTTO(img, NULL, 'TIFF', 'file', '/home/oracle/images/earth_ex');
END;
/