Oracle Spacial（空间数据库）查询空间数据1

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

  FROMTABLE(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

 ORDERBY 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'

   ANDROWNUM <= 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'

 ORDERBY 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'

UNIONALL

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' ;