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 用来检查几何对象的一致性。