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

核心提示：insert into gzdt(dlbm,geometry)select a.dlbm,SDO_GEOM.SDO_INTERSECTION(a.geometry, b.geometry, 0.0001) as geometryfrom v_dltb as av_ytfq as bwhere sdo_relate(a.

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==