Oracle spatial data SQL query related instances

Oracle spatial data SQL query related instances

-- Select dlbm, dlmc, trim (zldwdm) as zldwdm_1 from gzdt where nvl (zldwdm, 'T') = 'T' or zldwdm = '';
Update gzdt set ZLDWDM = '000000' where nvl (zldwdm, 'T') = 'T' or zldwdm = '';
Commit;
Delete from gzdt where ZLDWDM like '20160301'
Commit;
// Obtain the space area
Update gzdt set mj = mdsys. sdo_geom.sdo_area (geometry, 0.0000000005 );
// Obtain the length of the spatial line
Update xzdw set cd = mdsys. sdo_geom.sdo_length (geometry, 0.0000000005 );

// SQL statement used to delete Spatial Data
// SQL insert oracle spatial object farmland
Delete from spatial;
Insert into spatial (dlbm, geometry)
Select dlbm, geometry from v_dltb where dlbm in ('011', '012', '013 ');
Commit;

// Insert space data using SQL statements
Insert into spatial (dlbm, geometry)
Select dlbm, geometry from v_dltb where dlbm in ('011', '012', '013 ');
Commit;

// Create a spatial field index for 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;
// ================================================

// Create a field 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 );
// ================================================ =

// Read the spatial data field 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

// Intersection between two layers
// Any intersection operation 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
V_ytfq as B
Where sdo_relate (a. geometry, B. geometry, 'mask = ANYINTERACT ') = 'true'

// Internally calculate 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
V_ytfq as B
Where sdo_relate (a. geometry, B. geometry, 'mask = INSIDE ') = 'true'

 

// Dltb_jbnt overlay analysis
Select * from v_dltb
Where dlbm in ('011', '012', '013') and dldwdm like '123 ';
//
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 '200' and (d. dlbm in ('021 ') or d. dlbz in ('k', 'k') and
Y. xzqdm like '20140901' and
Mdsys. sdo_geom.relate (d. geometry, 'Inside ', y. geometry, 0.0001) = 'inside ';
// Extract valid geometric data of v_gbjj Layer
Select * from v_gbjj
Where sdo_geom.validate_geometry_with_context (geometry, 0.0001) = 'true'
// Extract invalid geometric data of v_gbjj Layer
Select * from v_gbjj
Where sdo_geom.validate_geometry_with_context (GEOMETRY, 0.0001) <> 'true'
// = The = end =