Oracle spatial 空间数据SQL查询操作相关实例

 Oracle spatial 空间数据SQL查询操作相关实例

--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);

//删除空间数据 用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;

//插入空间数据 用SQL语句
insert into spatial(dlbm,geometry)
select dlbm,geometry from v_dltb where dlbm in('011','012','013') ;
commit;

//创建空间字段索引 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;
//======================================

//创建字段索引
//=======================================
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);
//=======================================

//读取空间数据字段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;