oracle spatial之空间操作符

空间操作符：

1.        SDO_WITHIN_DISTANCE：该操作符将从中返回在一个查询位置指定距离范围内的所有位置。

如果搜素局限于适当大小的街道上，即不是太大又不是太小，那么需要以下的参数来设置。

Min_resolution= 从查询结果中排除了太小的几何数据

max_resolution= 从查询结果中排除了太小的几何数据

获取竞争对手离商店周围0.25英里内的街道名

SQL> SELECT s.street_name

  2  FROM competitors comp, map_streets s

  3  WHERE comp.id=1

  4  AND SDO_WITHIN_DISTANCE

  5  (s.geometry, comp.location,

  6  'DISTANCE=0.25 UNIT=MILE min_resolution=200 ' )='TRUE'

  7  ORDER BY s.street_name;

 

STREET_NAME

--------------------------------------------------------------

 

18TH ST NW

19TH ST NW

20TH ST NW

21ST ST NW

EYE ST NW

H ST NW

L ST NW

已选择7行。

 

获取竞争对手离商店周围0.25英里内的街道名

 

SQL> SELECT s.street_name

  2  FROM competitors comp, map_streets s

  3  WHERE comp.id=1

  4  AND SDO_WITHIN_DISTANCE

  5  (s.geometry, comp.location,

  6  'DISTANCE=0.25 UNIT=MILE min_resolution=200 max_resolution=500 ' )='TRUE'

  7  ORDER BY s.street_name;

 

STREET_NAME

------------------------------------------------------------------------------

 

21ST ST NW

H ST NW

 

2.        SDO_NN: 该操作符将从中返回在一个查询位置指定距离范围之外的所有位置

SDO_BATCH_SIZE性能调优参数：批量的大小将有索引决定。但可以通过SDO_BATCHE_SIZE参数来设置批量的大小

SDO_NUM_RES性能调优参数，

获取离指定竞争对手最近的5GOLD个客户

SQL> SELECT ct.id, ct.name, ct.customer_grade

  2  FROM competitors comp, customers ct

  3  WHERE comp.id=1

  4  AND ct.customer_grade='GOLD'

  5  AND SDO_NN(ct.location, comp.location, 'SDO_BATCH_SIZE=100' )='TRUE'

  6  AND ROWNUM<=5

  7  ORDER BY ct.id;

 

        ID NAME                                CUSTOMER_GRADE

---------- ----------------------------------- ---------------

       809 LINCOLN SUITES                      GOLD

       810 HOTEL LOMBARDY                      GOLD

      6326 HOTEL LOMBARDY                      GOLD

      7821 RENAISSANCE MAYFLOWER HOTEL         GOLD

      8792 DESTINATION HOTEL & RESORTS         GOLD

 

获取离指定竞争对手最近的5个客户

SQL> SELECT ct.id, ct.name, ct.customer_grade

  2  FROM competitors comp, customers ct

  3  WHERE comp.id=1

  4  AND SDO_NN(ct.location, comp.location, 'SDO_NUM_RES=5')='TRUE' ;

 

        ID NAME                                CUSTOMER_GRADE

---------- ----------------------------------- ---------------

      1044 MUSEUM OF THE THIRD DIMENSION       SILVER

       809 LINCOLN SUITES                      GOLD

      1526 INTERNATIONAL FINANCE               SILVER

      1538 MCKENNA AND CUNEO                   SILVER

      8792 DESTINATION HOTEL & RESORTS         GOLD

 

SQL>

3.        SDO_NN_DISTANCE

获取离指定竞争对手最近的5个客户及其相应距离

SQL> SELECT ct.id, ct.name, ct.customer_grade, SDO_NN_DISTANCE(1) dist

  2  FROM competitors comp, customers ct

  3  WHERE comp.id=1

  4  AND SDO_NN(ct.location, comp.location, 'SDO_NUM_RES=5 UNIT=MILE',1)='TRUE'

  5  ORDER BY ct.id;

 

        ID NAME                                CUSTOMER_GRADE        DIST

---------- ----------------------------------- --------------- ----------

       809 LINCOLN SUITES                      GOLD            .059043167

      1044 MUSEUM OF THE THIRD DIMENSION       SILVER          .086737655

      1526 INTERNATIONAL FINANCE               SILVER          .134083997

      1538 MCKENNA AND CUNEO                   SILVER          .055098188

      8792 DESTINATION HOTEL & RESORTS         GOLD            .090474343

 

搜素空间距离限制在0.1英里范围之内

SQL> SELECT ct.id, ct.name, ct.customer_grade, SDO_NN_DISTANCE(1) dist

  2  FROM competitors comp, customers ct

  3  WHERE comp.id=1

  4  AND SDO_NN(ct.location, comp.location, 'SDO_NUM_RES=5 DISTANCE=0.1 UNIT=MIL

E',1)='TRUE'

  5  ORDER BY ct.id;

 

        ID NAME                                CUSTOMER_GRADE        DIST

---------- ----------------------------------- --------------- ----------

       809 LINCOLN SUITES                      GOLD            .059043167

      1044 MUSEUM OF THE THIRD DIMENSION       SILVER          .086737655

      1538 MCKENNA AND CUNEO                   SILVER          .055098188

      8792 DESTINATION HOTEL & RESORTS         GOLD            .090474343

 

4.        SDO_FILTER:操作符返回的结果比实际上与查询几何体相交的结果要多。它的执行速度比较快。

select location

  from customers

 where sdo_filter(location,

                  sdo_geometry(2003,

                               8307,

                               null,

                               sdo_elem_info_array(1, 1003, 3),

                               sdo_ordinate_array(-122.43886,

                                                  37.78284,

                                                  -122.427195,

                                                  37.79284))) = 'TRUE';

 

l   其他的操作符只能作用于二维几何体，而SDO_FILEER可以作用于二维，三维或是四维几何体。通过在CREATE INDEX语句中设置SDO_INDX_DIMS参数可将索引建为三维或是四维。

l  Min_resolution,max_resolution 仅用于SDO_FILTER,SDO_WITHIN_DISTANCE和SDO_RELATE操作符，而不能用于SDO_NN操作符。

5.        SDO_RELATE:该操作符找出与查询几何体有某种特定关系的几何体

获取在竞争对手商店0.25英里缓冲区域所有的客户

SELECT ct.id, ct.name

FROM competitors_sales_regions comp, customers ct

WHERE comp.id=1

AND SDO_RELATE(ct.location, comp.geom, 'MASK=ANYINTERACT ' )='TRUE'

ORDER BY ct.id;

确定与指定销售区域（ID=51）重叠的销售区域，使用多掩码

SQL> SELECT sr1.id

  2  FROM sales_regions sr2, sales_regions sr1

  3  WHERE sr2.id=51

  4  AND sr1.id <> 51

  5  AND SDO_RELATE

  6  (sr1.geom, sr2.geom, 'MASK=OVERLAPBDYDISJOINT+OVERLAPBDYINTERSECT')='TRUE'

;

    针对非大地测量数据表的SDO_RELATE新能参数

查询增加SDO_LEVEL=6参数

SQL> SELECT COUNT(*)

  2  FROM us_states st, competitors_sales_regions comp

  3  WHERE st.state_abrv='DC'

  4  AND SDO_RELATE(comp.geom, st.geom, 'MASK=INSIDE SDO_LEVEL=6' )='TRUE' ;

 

6.        HIT

带有多个HINT的空间操作符在一个含有两张表的SQL语句中

SQL> create index cust_grade on customers (customer_grade);

 

索引已创建。

SQL> set autotrace trace

SQL> SELECT /*+ ORDERED */ ct.id, ct.name

  2  FROM competitors comp, customers ct

  3  WHERE comp.id=1

  4  AND SDO_WITHIN_DISTANCE

  5  (ct.location, comp.location, 'DISTANCE=0.25 UNIT=MILE ' )='TRUE'

  6  ORDER BY ct.id ;

 

已选择23行。

 

 

执行计划

----------------------------------------------------------

Plan hash value: 557546727

 

--------------------------------------------------------------------------------

 

----------------

 

| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%

 

CPU)| Time     |

 

--------------------------------------------------------------------------------

 

----------------

 

|   0 | SELECT STATEMENT              |                |    32 |  9248 |     3

(34)| 00:00:01 |

 

|   1 |  SORT ORDER BY                |                |    32 |  9248 |     3

(34)| 00:00:01 |

 

|   2 |   NESTED LOOPS                |                |    32 |  9248 |     2

 (0)| 00:00:01 |

 

|   3 |    TABLE ACCESS BY INDEX ROWID| COMPETITORS    |     1 |   137 |     2

 (0)| 00:00:01 |

 

|*  4 |     INDEX UNIQUE SCAN         | COMPETITORS_PK |     1 |       |     1

 (0)| 00:00:01 |

 

|   5 |    TABLE ACCESS BY INDEX ROWID| CUSTOMERS      |    32 |  4864 |     2

 (0)| 00:00:01 |

 

|*  6 |     DOMAIN INDEX              | CUSTOMERS_SIDX |       |       |     0

 (0)| 00:00:01 |

 

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("COMP"."ID"=1)

   6 - access("MDSYS"."SDO_WITHIN_DISTANCE"("CT"."LOCATION","COMP"."LOCATION",'D

ISTANCE=

 

              0.25 UNIT=MILE ')='TRUE')

统计信息

----------------------------------------------------------

        153  recursive calls

          2  db block gets

       2958  consistent gets

          0  physical reads

          0  redo size

       1261  bytes sent via SQL*Net to client

        426  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          3  sorts (memory)

          0  sorts (disk)

         23  rows processed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20976446/viewspace-722352/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20976446/viewspace-722352/