oracl 空间查询

oracle 空间查询

官网资料：

https://docs.oracle.com/cd/B28359_01/appdev.111/b28400/toc.htm
https://docs.oracle.com/cd/B28359_01/appdev.111/b28400/sdo_operat.htm

顺便分享一个 arcgis 的 官方文档
http://resources.arcgis.com/zh-cn/help/main/10.1/index.html#/na/006z000000ws000000

添加空间字段

alter table LINES add geom "MDSYS"."SDO_GEOMETRY";

更新空间字段

update LINES set geom=sdo_geometry(wkt, 4326);

创建相应的元数据

insert into user_sdo_geom_metadata
  (table_name, COLUMN_NAME, DIMINFO, SRID)
values
  ('LINES',
   'GEOM',
   MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.005),
                       MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.005)),
   4326)

创建索引

Create index LINES_SIDX on LINES(GEOM)
Indextype is MDSYS.spatial_index;

空间查询（点在面内）

SELECT *
FROM POINTS
WHERE SDO_GEOM.RELATE(sdo_geometry(
                          'POLYGON((117.05562 40.49515,117.0674 40.49515,117.0674 40.5013,117.05562 40.5013,117.05562 40.49515))',
                          4326),
                      'ANYINTERACT',
                      GEOM,
                      0.0001) = 'TRUE' AND LINE_NAME = '北京';


SELECT *
FROM POINTS
WHERE SDO_RELATE(GEOM,
                 sdo_geometry(
                     'POLYGON((116.15299 40.50499,116.16361 40.50499,116.16361 40.509,116.15299 40.509,116.15299 40.50499))',
                     4326), 'mask=anyinteract') = 'TRUE';


SELECT *
FROM LINES
WHERE SDO_RELATE(GEOM,
                 sdo_geometry(
                     'POLYGON((116.15299 40.50499,116.16361 40.50499,116.16361 40.509,116.15299 40.509,116.15299 40.50499))',
                     4326), 'mask=anyinteract') = 'TRUE';

SELECT *
FROM LINES
WHERE SDO_GEOM.RELATE(sdo_geometry(
                          'POLYGON((116.15299 40.50499,116.16361 40.50499,116.16361 40.509,116.15299 40.509,116.15299 40.50499))',
                          4326), 'ANYINTERACT', GEOM, 0.0001) = 'TRUE';


SELECT *
FROM LINES
WHERE SDO_FILTER(GEOM, sdo_geometry(
    'POLYGON((116.15299 40.50499,116.16361 40.50499,116.16361 40.509,116.15299 40.509,116.15299 40.50499))',
    4326)) = 'TRUE';