Oracle spatial 空間數(shù)據(jù)SQL查詢操作相關(guān)實例

發(fā)布時間: 2012/9/4 17:37:35

--select dlbm,dlmc,trim(zldwdm) as zldwdm_1 from gzdt where nvl(zldwdm,'t')='t' or zldwdm='';
update gzdt set ZLDWDM='510113106' where nvl(zldwdm,'t')='t' or zldwdm='';-

commit;
delete from gzdt where ZLDWDM like '510113106%'
commit;
//獲取空間面的面積
update gzdt set mj=mdsys.sdo_geom.sdo_area(geometry,0.0000000005);
//獲取空間線的長度
update xzdw set cd=mdsys.sdo_geom.sdo_length(geometry,0.0000000005);

//刪除空間數(shù)據(jù) 用SQL語句
//sql insert Oracle spatial object 耕地
delete from spatial;
insert into spatial(dlbm,geometry)
select dlbm,geometry from v_dltb where dlbm in('011','012','013') ;
commit;

//插入空間數(shù)據(jù) 用SQL語句
insert into spatial(dlbm,geometry)
select dlbm,geometry from v_dltb where dlbm in('011','012','013') ;
commit;

//創(chuàng)建空間字段索引 Oracle spatial table
//======================================
drop index index_spatial_v_gb_gdbhdk_h;
drop index index_spatial_v_jj_xzq_h;
drop index index_spatial_v_tdlygh_ytfq_xz_e;
drop index index_spatial_v_tdlyxz_dltb_h;
drop index index_spatial_v_tdly_nydfddj_k;

create index v_gb_gdbhdk_h_spatial_index      on v_gb_gdbhdk_h(geometry)        indextype mdsys.spatial_index;
create index v_jj_xzq_h_spatial_index         on v_jj_xzq_h(geometry)           indextype mdsys.spatial_index;
create index v_tdlygh_ytfq_xz_e_spatial_index on v_tdlygh_ytfq_xz_e(geometry)   indextype mdsys.spatial_index;
create index v_tdlyxz_dltb_h_spatial_index    on v_tdlyxz_dltb_h(geometry)      indextype mdsys.spatial_index;
create index v_tdly_nydfddj_k_spatial_index   on v_tdly_nydfddj_k(geometry)     indextype mdsys.spatial_index;
//======================================

//創(chuàng)建字段索引
//=======================================
drop   index index_fd_v_gb_gdbhdk_h_xzqdm;
drop   index index_fd_v_jj_xzq_h_xzqdm;
drop   index index_fd_v_tdlygh_ytfq_xz_e_xzqdm;
drop   index index_fd_v_tdlyxz_dltb_h_zldwdm;
drop   index index_fd_v_tdly_nydfddj_k_xzdm;

create index index_fd_v_gb_gdbhdk_h_xzqdm         on v_gb_gdbhdk_h(xzqdm);
create index index_fd_v_jj_xzq_h_xzqdm            on v_jj_xzq_h(xzqdm);
create index index_fd_v_tdlygh_ytfq_xz_e_xzqdm    on v_tdlygh_ytfq_xz_e(xzqdm);
create index index_fd_v_tdlyxz_dltb_h_zldwdm      on v_tdlyxz_dltb_h(zldwdm);
create index index_fd_v_tdly_nydfddj_k_xzdm       on v_tdly_nydfddj_k(xzdm);
//=======================================

//讀取空間數(shù)據(jù)字段sql geometry
select DLBM,dlmc,
       mdsys.sdo_geom.sdo_area(geometry,0.0000000005) as geo_mj,
       sdo_util.getnumelem(geometry) as num_elem,
       sdo_util.getVertices(geometry) as Vertices,
       sdo_util.GetNumRings(geometry) as Num_Rings,
       sdo_util.to_gmlgeometry(geometry) as gmlgeo,
       geometry
from v_dltb

//兩空間圖層相交運算
//任意相交運算mask=anyinteract
delete from gzdt;
insert into gzdt(dlbm,geometry)
select a.dlbm,
SDO_GEOM.SDO_INTERSECTION(a.geometry, b.geometry, 0.0001) as geometry
from v_dltb as a
v_ytfq as b
where sdo_relate(a.geometry,b.geometry,'mask=ANYINTERACT')='TRUE'

//在內(nèi)部運算mask=inside
delete from gzdt;
insert into gzdt(dlbm,geometry)
select a.dlbm,
SDO_GEOM.SDO_INTERSECTION(a.geometry, b.geometry, 0.0001) as geometry
from v_dltb as a
v_ytfq as b
where sdo_relate(a.geometry,b.geometry,'mask=INSIDE')='TRUE'

//dltb_jbnt疊加分析
select * from v_dltb
where dlbm in('011','012','013') and dldwdm like '510112106%';
//
select d.dlbm,d.dlmc,
       d.tbmj,d.tbdlmj,d.xzdwmj,d.lxdwmj,d.tkmj,
       mdsys.sdo_geom.sdo_area(d.geometry,0.0000000005) as geo_mj,
       sdo_util.getnumelem(d.geometry) as num_elem,
       sdo_util.getVertices(d.geometry) as Vertices,
       sdo_util.GetNumRings(d.geometry) as Num_Rings,
       sdo_util.to_gmlgeometry(d.geometry) as gmlgeo,
       SDO_GEOM.SDO_INTERSECTION(d.geometry, y.geometry, 0.0001) as geometry
from v_dltb d,
     v_ytfq y
where d.dldwdm like '510112106%' and (d.dlbm in('021') or d.dlbz in('k','K')) and
       y.xzqdm like '510112%' and
       mdsys.sdo_geom.relate(d.geometry,'INSIDE',y.geometry,0.0001)='INSIDE';
//提取v_gbjj圖層有效幾何圖形數(shù)據(jù)
select * from v_gbjj
where sdo_geom.validate_geometry_with_context(GEOMETRY,0.0001)='TRUE'
//提取v_gbjj圖層無效幾何圖形數(shù)據(jù)
select * from v_gbjj
where sdo_geom.validate_geometry_with_context(GEOMETRY,0.0001)<>'TRUE'
//==the==end==