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)) -- 四点空间化