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