Oracle 空间表操作记录

删除已建立的空间索引、空间表、空间表对象

drop index testinx;
drop table t_test_spatial;
delete from USER_SDO_GEOM_METADATA t where t.TABLE_NAME='T_TEST_SPATIAL';

 

创建空间数据表

create table t_test_spatial ( s_id varchar2(30) primary key, s_name varchar2(30), shape MDSYS.Sdo_Geometry );

 

定义空间表对像的空间范围

insert into USER_SDO_GEOM_METADATA values('T_TEST_SPATIAL','SHAPE',SDO_DIM_ARRAY( SDO_DIM_ELEMENT('X', -180, 180, 0.005), SDO_DIM_ELEMENT('Y', -90, 90, 0.005)), 8307);

 

建立空间索引
create index testinx on T_TEST_SPATIAL(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

 

导入数据

insert into T_TEST_SPATIAL values('4','西湖路',sdo_geometry('POLYGON ((113.11258241 36.2203193573, 113.112198316 36.2178940792, 113.110130815 36.2180007719, 113.110043022 36.2206280549, 113.11258241 36.2203193573))',8307))

 

根据坐标查询区域
SELECT c.区域名称 FROM 区域表名称 c WHERE SDO_CONTAINS(c.shape,SDO_GEOMETRY(2001, 4326,SDO_POINT_TYPE(经度值, 纬度值, NULL),NULL, NULL)) = 'TRUE';