(原) Oracle Spatial: Spatial Data Types and Metadata

1 Simple Example: Inserting, Indexing, and Querying Spatial Data
建立表：
CREATE TABLE cola_markets (
mkt_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape SDO_GEOMETRY);

插入資料：
INSERT INTO cola_markets VALUES(
1,
'cola_a',
SDO_GEOMETRY(
2003, -- two-dimensional polygon
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior)
SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to
-- define rectangle (lower left and upper right) with
-- Cartesian-coordinate data
)
);
INSERT INTO cola_markets VALUES(
2,
'cola_b',
SDO_GEOMETRY(
2003, -- two-dimensional polygon
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)
)
);

INSERT INTO cola_markets VALUES(
3,
'cola_c',
SDO_GEOMETRY(
2003, -- two-dimensional polygon
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,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-dimensional polygon
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,4), -- one circle
SDO_ORDINATE_ARRAY(8,7, 10,9, 8,11)
)
);

更新視圖：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 INDEX cola_spatial_idx
ON cola_markets(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
-- Preceding statement created an R-tree index.

查詢：
    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：維數
    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
    確認coordinate system，此值為SDO_COORD_REF_SYS表中的SRID值。此值也被插入到USER_SDO_GEOM_METADATA視圖中。
2.3 SDO_POINT
    （1）SDO_ELEM_INFO and SDO_ORDINATES are both null
    （2）SDO_POINT attribute is non-null
    結論：儲存座標
2.4 SDO_ELEM_INFO
    用來解釋儲存在SDO_ORDINATES屬性中的座標資訊。
    SDO_STARTING_OFFSET：SDO_ORDINATES中的offset min為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
    長數組，存放空間對象的座標
2.6 Usage Considerations
    SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT 用來檢查幾何對象的一致性。