Oracle_spatial的空间操作符介绍

Oracle_spatial的空间操作符介绍

空间操作符

一、主要空间操作符
1、SDO_FILTER
SDO_FILTER(geometry1, geometry2, param);判断两个几何体是否有相交
SELECT c.mkt_id, c.name
FROM cola_markets c
WHERE SDO_FILTER(c.shape,
SDO_GEOMETRY(2003,
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1, 1003, 3),
SDO_ORDINATE_ARRAY(4, 6, 8, 8))) = 'TRUE';
2、SDO_JOIN
SDO_JOIN(table_name1, column_name1, table_name2, column_name2, params,preserve_join_order) RETURN SDO_ROWIDSET;
SELECT /*+ ordered */
a.name, b.name
FROM TABLE(SDO_JOIN('COLA_MARKETS',
'SHAPE',
'COLA_MARKETS',
'SHAPE',
'mask=ANYINTERACT')) c,
cola_markets a,
cola_markets b
WHERE c.rowid1 = a.rowid
AND c.rowid2 = b.rowid
ORDER BY a.name;
3、SDO_NN
SDO_NN(geometry1, geometry2, param [, number]);在指定的距离内，按顺序返回离的最近的。
性能调优参数
sdo_num_res 指定返回离指定点最近的两个市场
sdo_batch_size 指定了一次批量提取多少条记录进行对比
SELECT /*+ INDEX(c cola_spatial_idx) */
c.mkt_id, c.name
FROM cola_markets c
WHERE SDO_NN(c.shape,
sdo_geometry(2001,
NULL,
sdo_point_type(10, 7, NULL),
NULL,
NULL),
'sdo_num_res=2') = 'TRUE';
SELECT /*+ INDEX(c cola_spatial_idx) */
c.mkt_id, c.name
FROM cola_markets c
WHERE SDO_NN(c.shape,
sdo_geometry(2001,
NULL,
sdo_point_type(10, 7, NULL),
NULL,
NULL),
'sdo_batch_size=3') = 'TRUE'
AND c.name < 'cola_d'
AND ROWNUM <= 2;
4、SDO_NN_DISTANCE
SDO_NN_DISTANCE(number);Number必须与sdo_nn操作符的最后一个参数保持一致。
Sdo_nn操作符是通过计算距离来识别客户的，我们可以通过sdo_nn_distance辅助操作符来获取这些距离。
在使用该操作符时，必须指定一个性能调优参数，sdo_num_res或sdo_batch_size，如果不知道如何设置sdo_batch_size的值，就将其设为0，索引会在内部使用合适的值。
SELECT /*+ INDEX(c cola_spatial_idx) */
c.mkt_id, c.name, SDO_NN_DISTANCE(1) dist
FROM cola_markets c
WHERE SDO_NN(c.shape,
sdo_geometry(2001,
NULL,
sdo_point_type(10, 7, NULL),
NULL,
NULL),
'sdo_num_res=2',
1) = 'TRUE'
ORDER BY dist;
5、SDO_RELATE
SDO_RELATE(geometry1, geometry2, param);判断两个几何体的关系
SELECT a.gid
FROM polygons a, query_polys B
WHERE B.gid = 1
AND SDO_RELATE(A.Geometry, B.Geometry, 'mask=touch') = 'TRUE'
UNION ALL
SELECT a.gid
FROM polygons a, query_polys B
WHERE B.gid = 1
AND SDO_RELATE(A.Geometry, B.Geometry, 'mask=coveredby') = 'TRUE';
SELECT c.mkt_id, c.name
FROM cola_markets c
WHERE SDO_RELATE(c.shape,
SDO_GEOMETRY(2003,
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1, 1003, 3),
SDO_ORDINATE_ARRAY(4, 6, 8, 8)),
'mask=anyinteract') = 'TRUE';
6、SDO_WITHIN_DISTANCE
SDO_WITHIN_DISTANCE(geometry1, aGeom, params);返回与指定点相距指定的距离内的某表的几何对象
SELECT c.name
FROM cola_markets c
WHERE SDO_WITHIN_DISTANCE(c.shape,
SDO_GEOMETRY(2003,
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1, 1003, 3),
SDO_ORDINATE_ARRAY(4, 6, 8, 8)),
'distance=10') = 'TRUE';
二、关系操作符
语法：操作符OVERLAPBDYDISJOINT(geometry1, geometry2);
1、SDO_ANYINTERACT
2、SDO_CONTAINS
3、SDO_COVEREDBY
4、SDO_COVERS
5、SDO_EQUAL
6、SDO_INSIDE
7、SDO_ON
8、SDO_OVERLAPBDYDISJOINT
9、SDO_OVERLAPBDYINTERSECT
10、SDO_OVERLAPS
11、SDO_TOUCH
空间函数与操作符的对比
1、 空间操作符使用的表必须有空间索引；
空间操作符执行速度比函数快，因此可以采用操作符的情况下就使用操作符 
2016-03-08 09:18 本站整理 浏览(25)
空间操作符
一、主要空间操作符
1、SDO_FILTER
SDO_FILTER(geometry1, geometry2, param);判断两个几何体是否有相交
SELECT c.mkt_id, c.name
FROM cola_markets c
WHERE SDO_FILTER(c.shape,
SDO_GEOMETRY(2003,
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1, 1003, 3),
SDO_ORDINATE_ARRAY(4, 6, 8, 8))) = 'TRUE';
2、SDO_JOIN
SDO_JOIN(table_name1, column_name1, table_name2, column_name2, params,preserve_join_order) RETURN SDO_ROWIDSET;
SELECT /*+ ordered */
a.name, b.name
FROM TABLE(SDO_JOIN('COLA_MARKETS',
'SHAPE',
'COLA_MARKETS',
'SHAPE',
'mask=ANYINTERACT')) c,
cola_markets a,
cola_markets b
WHERE c.rowid1 = a.rowid
AND c.rowid2 = b.rowid
ORDER BY a.name;
3、SDO_NN
SDO_NN(geometry1, geometry2, param [, number]);在指定的距离内，按顺序返回离的最近的。
性能调优参数
sdo_num_res 指定返回离指定点最近的两个市场
sdo_batch_size 指定了一次批量提取多少条记录进行对比
SELECT /*+ INDEX(c cola_spatial_idx) */
c.mkt_id, c.name
FROM cola_markets c
WHERE SDO_NN(c.shape,
sdo_geometry(2001,
NULL,
sdo_point_type(10, 7, NULL),
NULL,
NULL),
'sdo_num_res=2') = 'TRUE';
SELECT /*+ INDEX(c cola_spatial_idx) */
c.mkt_id, c.name
FROM cola_markets c
WHERE SDO_NN(c.shape,
sdo_geometry(2001,
NULL,
sdo_point_type(10, 7, NULL),
NULL,
NULL),
'sdo_batch_size=3') = 'TRUE'
AND c.name < 'cola_d'
AND ROWNUM <= 2;
4、SDO_NN_DISTANCE
SDO_NN_DISTANCE(number);Number必须与sdo_nn操作符的最后一个参数保持一致。
Sdo_nn操作符是通过计算距离来识别客户的，我们可以通过sdo_nn_distance辅助操作符来获取这些距离。
在使用该操作符时，必须指定一个性能调优参数，sdo_num_res或sdo_batch_size，如果不知道如何设置sdo_batch_size的值，就将其设为0，索引会在内部使用合适的值。
SELECT /*+ INDEX(c cola_spatial_idx) */
c.mkt_id, c.name, SDO_NN_DISTANCE(1) dist
FROM cola_markets c
WHERE SDO_NN(c.shape,
sdo_geometry(2001,
NULL,
sdo_point_type(10, 7, NULL),
NULL,
NULL),
'sdo_num_res=2',
1) = 'TRUE'
ORDER BY dist;
5、SDO_RELATE
SDO_RELATE(geometry1, geometry2, param);判断两个几何体的关系
SELECT a.gid
FROM polygons a, query_polys B
WHERE B.gid = 1
AND SDO_RELATE(A.Geometry, B.Geometry, 'mask=touch') = 'TRUE'
UNION ALL
SELECT a.gid
FROM polygons a, query_polys B
WHERE B.gid = 1
AND SDO_RELATE(A.Geometry, B.Geometry, 'mask=coveredby') = 'TRUE';
SELECT c.mkt_id, c.name
FROM cola_markets c
WHERE SDO_RELATE(c.shape,
SDO_GEOMETRY(2003,
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1, 1003, 3),
SDO_ORDINATE_ARRAY(4, 6, 8, 8)),
'mask=anyinteract') = 'TRUE';
6、SDO_WITHIN_DISTANCE
SDO_WITHIN_DISTANCE(geometry1, aGeom, params);返回与指定点相距指定的距离内的某表的几何对象
SELECT c.name
FROM cola_markets c
WHERE SDO_WITHIN_DISTANCE(c.shape,
SDO_GEOMETRY(2003,
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1, 1003, 3),
SDO_ORDINATE_ARRAY(4, 6, 8, 8)),
'distance=10') = 'TRUE';
二、关系操作符
语法：操作符OVERLAPBDYDISJOINT(geometry1, geometry2);
1、SDO_ANYINTERACT
2、SDO_CONTAINS
3、SDO_COVEREDBY
4、SDO_COVERS
5、SDO_EQUAL
6、SDO_INSIDE
7、SDO_ON
8、SDO_OVERLAPBDYDISJOINT
9、SDO_OVERLAPBDYINTERSECT
10、SDO_OVERLAPS
11、SDO_TOUCH
空间函数与操作符的对比
1、 空间操作符使用的表必须有空间索引；
空间操作符执行速度比函数快，因此可以采用操作符的情况下就使用操作符
 
 
1解决oracle12c安装报“[INS-30131]执行安装程序验证所需的初始设置失败（原因：无法访问临时位置）”方法
2解决 c#连接Oracle 提示system.data.oracleclient 需要 oracle 客户端软件 8.1.7 或更高版本
3SAP和Oracle的对比
4使用Powerdesigner的物理数据模型生成SQL脚本中开头处多出来的create or replace package PDTypes
5ORA-12801: 并行查询服务器 P000, instance dacsdb1:dacsdb1 (1) 中发出错误信号
6oracle job有定时执行的功能，可以在指定的时间点或每天的某个时间点自行执行任务。
7plsql无法连接oracle,oracle64位，问题：ORA-12514: TNS:listener does not currently know of service requ
8Oracle 11g EM安全证书问题无法访问的解决办法
9漏洞分析---SSLv3降级加密协议Padding Oracle攻击（POODLE）技术分析
10在Linux下安装Oracle
11有关Oracle数据库的备份情况
12Oracle SQL Developer显示的时间包含时分秒的设置方法
13ORACLE8的分区管理
14Oracle 数据库远程设置与客户端配置（PL/SQL, Navicat，DbVisualizer）
15Oracle 12C ORA-65096: 公用用户名或角色名无效