(Original) Oracle Spatial: Spatial data types and metadata

1 simple example: inserting, indexing, and querying Spatial Data
Create a table:
Create Table cola_markets (
Mkt_id number primary key,
Name varchar2 (32 ),
Shape sdo_geometry );

Insert data:
Insert into cola_markets values (
1,
'Cola _ ',
Sdo_geometry (
2003, -- two-dimen1_polygon
Null,
Null,
Sdo_elem_info_array (1003, 3), -- one rectangle (= exterior)
Sdo_ordinate_array (1, 1, 5, 7) -- only 2 points needed
-- Define rectangle (lower left and upper right)
-- Cartesian-coordinate data
)
);
Insert into cola_markets values (
2,
'Cola _ B ',
Sdo_geometry (
2003, -- two-dimen1_polygon
Null,
Null,
Sdo_elem_info_array (, 1), -- one polygon (exterior polygon ring)
Sdo_ordinate_array (5, 1, 8, 1, 8, 6, 5, 5, 1)
)
);

Insert into cola_markets values (
3,
'Cola _ C ',
Sdo_geometry (
2003, -- two-dimen1_polygon
Null,
Null,
Sdo_elem_info_array (, 1), -- one polygon (exterior polygon ring)
Sdo_ordinate_array (3,3, 6,3, 6,5, 4,5, 3,3)
)
);

Insert into cola_markets values (
4,
'Cola _ d ',
Sdo_geometry (
2003, -- two-dimen1_polygon
Null,
Null,
Sdo_elem_info_array (1,1003, 4), -- one circle
Sdo_ordinate_array (8, 7, 10, 9, 8, 11)
)
);

Update view: user_sdo_geom_metadata
Insert into user_sdo_geom_metadata
(Table_name,
Column_name,
Diminfo,
SRID)
Values (
'Cola _ markets ',
'Shape ',
Sdo_dim_array (-- 20x20 Grid
Sdo_dim_element ('x', 0, 20, 0.005 ),
Sdo_dim_element ('y', 0, 20, 0.005)
),
Null -- SRID
);

Create a spatial index:
Create index cola_spatial_idx
On cola_markets (SHAPE)
Indextype is MDSYS. spatial_index;
-- Preceding statement created an R-tree index.

Query:
Sdo_geom.sdo_intersection
Sdo_geom.relate
Sdo_geom.sdo_area
Sdo_geom.sdo_distance
Sdo_geom.validate_geometry_with_context

2 sdo_geometry object type
2.1 sdo_gtype dltt
D: dimension
L: Linear referencing system (LRS)
TT: geometry type
00 unknown_geometry
01 point
02 line or curve
03 Polygon
04 collection
05 multipoint
06 multiline or multicurve
07 multipolygon
2.2 sdo_srid
Confirm the coordinate system. The value is the srid value in the sdo_coord_ref_sys table. This value is also inserted into the user_sdo_geom_metadata view.
2.3 sdo_point
(1) sdo_elem_info and sdo_ordinates are both null
(2) sdo_point attribute is non-null
Conclusion: coordinates are stored.
2.4 sdo_elem_info
It is used to explain the coordinate information stored in the sdo_ordinates attribute.
Sdo_starting_offset: the offset min in sdo_ordinates is 1.
Sdo_etype: 1, 2, 1003, and 2003 simple elements; 3 polygon ring; 4, 1005, and 2005 compound elements
Sdo_interpretation
2.5 sdo_ordinates
Long array, storing space object coordinates
2.6 usage considerations
Sdo_geom.validate_geometry_with_context is used to check the consistency of geometric objects.