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;
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"
//在内部运算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图层有效几何图形数据
select * from v_gbjj
where sdo_geom.validate_geometry_with_context（GEOMETRY,0.0001）="TRUE"
//提取v_gbjj图层无效几何图形数据
select * from v_gbjj
where sdo_geom.validate_geometry_with_context（GEOMETRY,0.0001）<>"TRUE"
//==the==end==       深入研究Oracle数据库中的临时表Oracle spatial 创建空间字段索引创建字段索引完善SQL语句实例相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）