oracle spatial 入门

1.创建空间化字段类型为“MDSYS.SDO_GEOMETRY”
2.建立空间化表关联

INSERT INTO USER_SDO_GEOM_METADATA
(TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES
('tablename',
'GEOM',
MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X', -90, 180, 0.000000050),
MDSYS.SDO_DIM_ELEMENT('Y', -90, 180, 0.000000050)),
4326);


3.创建空间索引

CREATE Index indexname ON tablename(GEOM) INDEXTYPE is MDSYS.SPATIAL_INDEX;--创建空间索引


4.空间化查询语句

select *
from tablename
WHERE sdo_relate(tablename.geom,
SDO_geometry(2003,
4326,
NULL,
SDO_elem_info_array(1, 1003, 3),
SDO_ordinate_array(-8.3671,
-6.5117,
46.125,
27.0625)),
'mask=anyinteract') = 'TRUE'; --空间化数据查询

5.空间化插入语句

INSERT INTO tablename
(SCENEID, SATELLITEID, SENSORID, GEOM)
VALUES
('111',
'222',
'333',
MDSYS.Sdo_Geometry(2003,
4326,
NULL,
SDO_elem_info_array(1, 1003, 3),
SDO_ordinate_array(-8.3671, -6.5117, 46.125, 27.0625)));--空间化数据插入
6.空间化更新语句

update tablename t
set t.geom = MDSYS.Sdo_Geometry(2003,
4326,
NULL,
SDO_elem_info_array(1, 1003, 3),
SDO_ordinate_array(-8.3671,
-6.5117,
46.125,
27.0625));
--空间化数据更新

以上是基于空间化面数据的操作


update tablename t
set t.geom = MDSYS.Sdo_Geometry(2003,
4326,
NULL,
SDO_elem_info_array(1, 1003, 3),
SDO_ordinate_array(t.bottomrightlatitude,
t.topleftlatitude,
t.bottomrightlongitude,
t.topleftlongitude))--两点空间化


update tablename t
set
t.geom = MDSYS.Sdo_Geometry(2003,
4326,
NULL,
SDO_elem_info_array(1, 1003, 1),
SDO_ordinate_array(t.bottomleftlongitude,
t.bottomleftlatitude,
t.bottomrightlongitude,
t.bottomrightlatitude,
t.toprightlongitude,
t.toprightlatitude,
t.topleftlongitude,
t.topleftlatitude,
t.bottomleftlongitude,
t.bottomleftlatitude)) -- 四点空间化