Oracle spatial 空间数据SQL查询操作相关实例
来源:互联网 发布:淘宝怎样用图片搜索 编辑:程序博客网 时间:2024/04/29 06:24
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==
--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 spatial 空间数据SQL查询操作相关实例
- Oracle Spatial的空间查询,代码操作
- Oracle 表空间查询相关sql
- Arcgis 访问Oracle spatial空间数据
- oracle spatial 空间数据的获取
- oracle spatial 创建空间字段索引 创建字段索引 完善SQL语句实例
- oracle spatial 创建空间字段索引 创建字段索引 完善SQL语句实例二
- NET下基于OO4O,FME,ODP.NET的Oracle Spatial空间数据读取操作
- Oracle Spatial空间函数
- Oracle Spatial空间类型
- Oracle Spatial 空间分析操作函数——SDO_GEOM.RELATE
- Oracle Spatial中上载GIS空间数据方法研究
- Oracle spatial创建空间数据的字段详解
- Oracle Spatial 实例简介
- Oracle SQL数据操作和查询(一)
- Oracle:重复数据SQL查询+操作
- Spring+SpringMVC+hibernate spatial+oracle spatial 映射存储Geomety空间数据过程记录
- Oracle表空间相关查询
- 关于SDK Manager.exe无法打开的解决办法
- Html.RenderPartial和Html.Partial在Razor视图中的区别
- IOS中截取当前屏幕,并保存图片
- mysql 数据库有选择的删除
- "爱国者"防空
- Oracle spatial 空间数据SQL查询操作相关实例
- 实用监控Linux工具总结
- JavaScript面向对象
- Windows下创建OpenGL窗口
- 几种常见的排序算法---交换排序
- uva-123 - Searching Quickly
- Android输入法原理和疑云
- 黑马程序员_网页爬虫
- MD5