Oracle Spatial spatial data SQL query related instances

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