# oracle 创建SDO_Geometry表

Oracle Spatial由一坨的对象数据类型，类型方法，操作子，函数与过程组合而成。一个地理对象作为一个SDO_GEOMETRY对象保存在表的一个字段里。空间索引则由普通的DDL和DML语句来建立与维护。
本章主要说了一些例子演示如何建立，查询，索引空间数据。

本节演示一个很简单建立空间表，插入，建立索引，查询数据的过程。
场景是一个软饮料公司，用地理信息来表示他们的产品（可乐）在各个地区的情况。这些情况可以是：市场份额，竞争压力，增长潜力等等。而地区可以是邻近的市，州，省或国家。
我们要作的是：
1.建立一个表（COLA_MARKETS）来保存空间数据
2.插入四个（cola_a, cola_b, cola_c, cola_d)地区的数据
4.建立空间索引（COLA_SPATIAL_IDX)
5.进行一些空间查询

``````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
)
);
-- The next two INSERT statements create areas of interest for
-- Cola B and Cola C. These areas are simple polygons (but not
-- rectangles).
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)
)
);
-- Now insert an area of interest for Cola D. This is a
-- circle with a radius of 2. It is completely outside the
-- first three areas of interest.
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)
)
);``````

``````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
);``````
``-- SRID``

``````SDO_DIM_ARRAY(   -- 20X20 grid
SDO_DIM_ELEMENT('X', 0, 20, 0.005),
SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
),``````

``````CREATE INDEX cola_spatial_idx
ON cola_markets(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;``````