oracle空间索引

1、索引创建

• 添加元数据

  • INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
    VALUES ('BEIJING_LINK', 'GEOMETRY',
    MDSYS.SDO_DIM_ARRAY
    (MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.00000000050),
    MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.00000000050)),
    8307);

• 创建索引

  • create index idx_beijing_link on beijing_link(geometry)
    indextype is mdsys.spatial_index;

• 查询索引

　　　　select * from user_sdo_index_info where index_name='IDX_BEIJING_LINK' ;

2、空间操作符使用

1、sdo_filter相交

select count(*)
from beijing_link a,
(select link_pid, geometry from beijing_link where rownum = 1) b
where sdo_filter(a.geometry, b.geometry) = 'TRUE'
and a.link_pid != b.link_pid;

2、sdo_anyinteract 和sdo_filter相似

select count(*)
from beijing_link a,
(select link_pid, geometry from beijing_link where rownum = 1) b
where sdo_anyinteract(a.geometry, b.geometry) = 'TRUE'
and a.link_pid != b.link_pid;

3、sdo_nn(geometry a,geometry b,tol c,unit)返回距离最近的前N个

select sdo_geom.sdo_distance(a.geometry, b.geometry, 0.1, 'unit=meter')
from beijing_link a,
(select link_pid, geometry from beijing_link where rownum = 1) b
where sdo_nn(a.geometry, b.geometry, 'sdo_num_res=6') = 'TRUE';

4、sdo_nn_distance返回距离

select a.link_pid,b.link_pid,sdo_nn_distance(1) distance
from beijing_link a,
(select link_pid, geometry from beijing_link where rownum = 1) b
where sdo_nn(a.geometry, b.geometry, 'sdo_num_res=10',1) = 'TRUE'
order by distance;

5、sdo_relate 判断两个几何关系

select count(*)
from beijing_link a,
(select link_pid, geometry from beijing_link where rownum = 1) b
where sdo_relate(a.geometry, b.geometry,'mask=touch') = 'TRUE'
and a.link_pid != b.link_pid;

select count(*)
from beijing_link a,
(select link_pid, geometry from beijing_link where rownum = 1) b
where sdo_relate(a.geometry, b.geometry,'mask=anyinteract') = 'TRUE'
and a.link_pid != b.link_pid;

select count(*)
from beijing_link a,
(select link_pid, geometry from beijing_link where rownum = 1) b
where sdo_relate(a.geometry, b.geometry,'mask=coveredby') = 'TRUE'
and a.link_pid != b.link_pid;

 6、sdo_within_distance两个对象距离在某范围内

select count(*)
from beijing_link a,
(select link_pid, geometry from beijing_link where rownum = 1) b
where sdo_within_distance(a.geometry, b.geometry,'distance=10,unit=km') = 'TRUE'
and a.link_pid != b.link_pid;