Oracle Spatial table creation learning steps

Oracle Spatial table creation LearningThe steps are as follows:

Step 1. Create a table where shape is used to store space data.

 
 

  
  
CREATE TABLE mylake (  
  
  
feature_id NUMBER PRIMARY KEY,  
  
  
name VARCHAR2(32),  
  
  
shape MDSYS.SDO_GEOMETRY); 
 
 

Step2. in user_sdo_geom_metadata Insert a new record to the table to describe the space field.

 
 

  
  
Insert into user_sdo_geom_metadata VALUES (
  
  
'Mylake ', // --- table name
  
  
'Shape', // --- field name
  
  
MDSYS. SDO_DIM_ARRAY (
  
  
MDSYS. SDO_DIM_ELEMENT ('x', 0,100, 0.05), // --- minimum, maximum, and tolerance of X dimension.
  
  
MDSYS. SDO_DIM_ELEMENT ('y', 0,100, 0.05) // --- minimum Y dimension, maximum value and tolerance NULL // --- coordinate system, Cartesian coordinate system by default
  
  
);
 
 

Step 3: Create a spatial index

 
 

  
  
CREATE INDEX mylake_idx ON mylake(shape)   
  
  
INDEXTYPE IS MDSYS.SPATIAL_INDEX 
 
 

Step 4. insert Space Data

Oracle Spatial uses MDSYS. SDO_GEOMETRY to store Spatial data, defined:

 
 

  
  
CREATE TYPE sdo_geometry AS OBJECT (  
  
  
SDO_GTYPE NUMBER,  
  
  
SDO_SRID NUMBER,  
  
  
SDO_POINT SDO_POINT_TYPE,  
  
  
SDO_ELEM_INFO MDSYS.SDO_ELEM_INFO_ARRAY,  
  
  
SDO_ORDINATES MDSYS.SDO_ORDINATE_ARRAY); 
 
 

SDO_SRID: Coordinate System. NULL is the Cartesian coordinate system. SDO_POINT: Oracle Spatial can also define a single point. SDO_POINT defines create type sdo_point_type as object (x number, y number, z number). If it is two-dimensional, Z is NULL. SDO_ELEM_INFO: each three values describe an element. Value 1: The first vertex starts at SDO_ORDINATES_ARR. Second value: Element type. Third value: vertex connection method: 1-through a straight line connection, 2-through an Arc Connection: defined as create type sdo_elem_info_array as varray (1048576) of NUMBER; SDO_ORDINATES: list of all vertices in the ry. It is defined as create type sdo_ordinate_array as varray (1048576) of NUMBER;

// Insert a lake containing an island

 
 

  
  
INSERT INTO mylake VALUES(  
  
  
10,   
  
  
'Lake Calhoun',   
  
  
MDSYS.SDO_GEOMETRY(  
  
  
2003,  
  
  
NULL,  
  
  
NULL,  
  
  
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1),  
  
  
MDSYS.SDO_ORDINATE_ARRAY(0,0, 10,0, 10,10, 0,10, 0,0, 4,4, 6,4, 6,6, 4,6, 4,4)  
  
  
)); 
 
 

// Insert two boats

 
 

  
  
INSERT INTO mylake VALUES(  
  
  
11,   
  
  
'The Windswept',   
  
  
MDSYS.SDO_GEOMETRY(  
  
  
2003,  
  
  
NULL,  
  
  
NULL,  
  
  
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),  
  
  
MDSYS.SDO_ORDINATE_ARRAY(2,2, 3,2, 3,2, 2,3, 2,2) ); INSERT INTO mylake VALUES(  
  
  
12,   
  
  
'Blue Crest',   
  
  
MDSYS.SDO_GEOMETRY(  
  
  
2003,  
  
  
NULL,  
  
  
NULL,  
  
  
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),  
  
  
MDSYS.SDO_ORDINATE_ARRAY(7,7, 8,7, 8,7, 7,8, 7,7) ); 
 
 

Step 4. QueryOracle Spatial queries data in two processing processes: 1. query candidate items only through the index. Implemented through the SDO_FILTER function: SDO_FILTER (geometry1 MDSYS. SDO_GEOMETRY, geometry2 MDSYS. SDO_GEOMETRY, params VARCHAR2) geometry1: it must be the indexed geometric data geometry2: it is not necessarily a space field in the table, nor is it required to be indexed params: Filter Type querytype = WINDOW: geometry2 does not require querytype = JOIN: geometry2 must be from the table.

 
 

  
  
SELECT name boat_name  
  
  
FROM mylake t  
  
  
WHERE feature_id = 12 
  
  
AND SDO_FILTER(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,  
  
  
mdsys.sdo_elem_info_array(1,1003,1),  
  
  
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),'querytype=WINDOW') = 'TRUE';  
 
 

// Select all boats in the defined rectangle

 
 

  
  
SELECT name boat_name  
  
  
FROM mylake t  
  
  
WHERE feature_id = 12 
  
  
AND SDO_FILTER(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,  
  
  
mdsys.sdo_elem_info_array(1,1003,1),  
  
  
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),  
  
  
'querytype=WINDOW') = 'TRUE'  
  
  
AND SDO_RELATE(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,  
  
  
mdsys.sdo_elem_info_array(1,1003,1),  
  
  
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),  
  
  
'masktype=INSIDE querytype=WINDOW') = 'TRUE' 
 
 

// Masktype can be used together

 
 

  
  
SELECT feature_id id  
  
  
FROM mylake t  
  
  
WHERE feature_id = 12 
  
  
AND SDO_FILTER(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,  
  
  
mdsys.sdo_elem_info_array(1,1003,1),  
  
  
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),  
  
  
'querytype=WINDOW') = 'TRUE'  
  
  
AND SDO_RELATE(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,  
  
  
mdsys.sdo_elem_info_array(1,1003,1),  
  
  
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),  
  
  
'masktype=INSIDE+TOUCH querytype=WINDOW') = 'TRUE' 
 
 

This article introduces the knowledge of Oracle Spatial table creation and learning!