|
Posted on
2004-12-22 10:12
色彩与空间
阅读( 2295)
评论()
编辑
收藏
举报
REM 创建包含SDO_GEOMETRY类型的表
CREATE TABLE "GIS"."GIS_TAB" ("ID" CHAR(25), "ID1" VARCHAR2(20),
"MI_STYLE" VARCHAR2(254), "MI_PRINX" NUMBER, "GEOLOC"
"MDSYS"."SDO_GEOMETRY") PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE
"USERS" LOGGING NOCOMPRESS ;
CONNECT GIS;
REM 创建唯一索引
CREATE UNIQUE INDEX "GIS"."GIS_TAB_IDX" ON "GIS_TAB" ("MI_PRINX" )
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1
FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING ;
REM 插入元数据
BEGIN
declare
schema_name varchar2(200);
username varchar2(64);
begin
execute immediate 'select sys_context(''userenv'', ''CURRENT_SCHEMA'') from dual ' into schema_name;
execute immediate 'select user from dual ' into username;
if username != schema_name then
mdsys.sdo_meta.change_all_sdo_geom_metadata(schema_name, 'GIS_TAB','GEOLOC',mdsys.SDO_dim_array(MDSYS.SDO_DIM_ELEMENT('X',0,400000,.00002),MDSYS.SDO_DIM_ELEMENT('Y',0,400000,.00002)),262148) ;
else
execute immediate 'INSERT INTO USER_SDO_GEOM_METADATA values (''GIS_TAB'',''GEOLOC'',mdsys.SDO_dim_array(MDSYS.SDO_DIM_ELEMENT(''X'',0,400000,.00002),MDSYS.SDO_DIM_ELEMENT(''Y'',0,400000,.00002)),262148) ' ;
end if;
end;
COMMIT;
END;
REM 创建RTREE的空间索引
CREATE INDEX "GIS"."GIS_TAB_SX" ON "GIS_TAB" ("GEOLOC" ) INDEXTYPE IS
"MDSYS"."SPATIAL_INDEX" ;
|