1. 開発者ガイド
  2. 補足情報
  3. 複雑な空間問合せの例

C 複雑な空間問合せの例

この付録では、リファレンスの章に示した例より複雑な問合せの例について説明します。

SDO_WITHIN_DISTANCE、SDO_NNなど、Spatial and Graphアプリケーションで頻繁に使用される演算子を中心に説明します。

この付録の内容は、Spatial and Graphユーザーのサポートおよびトレーニングを行うオラクル社の担当者からの情報に基づいています。Oracle Spatial and Graphトレーニング・コースでは、ここで示す例の多くに加えて、さらに他の例および説明を提供しています。

この付録に示す例を使用する前に、「概念および使用方法」および「リファレンス情報」に示した関連する演算子またはファンクションの使用方法およびリファレンス情報を理解してください。

トピック:

C.1 例で使用する表

この付録の例では、GEOD_CITIES、GEOD_COUNTIESおよびGEOD_INTERSTATESという表を使用します。これらの表は次のとおり定義されています。

CREATE TABLE GEOD_CITIES(
  LOCATION     SDO_GEOMETRY,
  CITY         VARCHAR2(42),
  STATE_ABRV   VARCHAR2(2),
  POP90        NUMBER,
  RANK90       NUMBER);

CREATE TABLE GEOD_COUNTIES(
  COUNTY_NAME   VARCHAR2(40),
  STATE_ABRV    VARCHAR2(2),
  GEOM          SDO_GEOMETRY);

CREATE TABLE GEOD_INTERSTATES(
  HIGHWAY   VARCHAR2(35),
  GEOM      SDO_GEOMETRY);

C.2 SDO_WITHIN_DISTANCEの例

SDO_WITHIN_DISTANCE演算子は、指定されたオブジェクトから指定された距離内に存在する一連の空間オブジェクトを識別します。距離には、近似値または正確な値を指定できます。querytype=FILTERを指定すると、1次フィルタ処理のみが実行されるため、距離は近似値になります。指定しない場合は、1次と2次の両方のフィルタ処理が実行されるため、距離は正確な値になります。

例C-1 高速道路から一定距離内に存在するすべての都市の検索

例C-1では、州間高速道路I170から15マイル以内に存在するすべての都市を検索します。 

SELECT /*+ ORDERED */ c.city
FROM geod_interstates i, geod_cities c
WHERE i.highway = 'I170'
   AND sdo_within_distance (
        c.location, i.geom,
       'distance=15 unit=mile') = 'TRUE';

例C-1では、指定した高速道路(i.highway = 'I170')から15マイル以内('distance=15 unit=mile')に存在するすべての都市を検索します。デフォルトでは、結果は正確な値となります(querytypeパラメータを使用せず、問合せを1次フィルタ処理に制限しなかったため)。この例のWHERE句の内容は次のとおりです。

  • i.highwayはINTERSTATES表のHIGHWAY列で、I170はHIGHWAY列の値です。

  • c.locationでは、検索列(geometry1)を指定します。これは、GEOD_CITIES表のLOCATION列です。

  • i.geomでは、問合せウィンドウ(aGeom)を指定します。これは、GEOD_INTERSTATES表の、HIGHWAY列に値I170が含まれている行のGEOM列にある空間ジオメトリです。

例C-2 都市から一定距離内に存在するすべての高速道路の検索

例C-2では、Tampa市から15マイル以内に存在するすべての州間高速道路を検索します。 

SELECT /*+ ORDERED */ i.highway
FROM geod_cities c, geod_interstates i
WHERE c.city = 'Tampa'
   AND sdo_within_distance (
        i.geom, c.location,
        'distance=15 unit=mile') = 'TRUE';

例C-2では、指定した都市(c.city = 'Tampa')から15マイル以内('distance=15 unit=mile')に存在するすべての高速道路を検索します。デフォルトでは、結果は正確な値となります(querytypeパラメータを使用せず、問合せを1次フィルタ処理に制限しなかったため)。この例のWHERE句の内容は次のとおりです。

  • c.cityはGEOD_CITIES表のCITY列で、TampaはCITY列の値です。

  • i.geomでは、検索列(geometry1)を指定します。これは、GEOD_INTERSTATES表のGEOM列です。

  • c.locationでは、問合せウィンドウ(aGeom)を指定します。これは、GEOD_CITIES表の、CITY列に値Tampaが含まれている行のLOCATION列にある空間ジオメトリです。

C.3 SDO_NNの例

SDO_NN演算子は、あるジオメトリから最も近くに存在するジオメトリを特定します。ORDER BY句にSDO_NN_DISTANCE補助演算子を指定して結果を昇順で戻す場合を除き、戻される結果の順序を指定することはできません。オプションのパラメータを指定しない場合、最も近くに存在する1つのジオメトリが戻されます。

オプションのsdo_num_resキーワードを指定すると、最も近くに存在するジオメトリをいくつ要求するかを指定できますが、WHERE句内の他の条件は評価されません。たとえば、交差点から最も近い5つの銀行を検索する場合を考えてみます。ここで、名前がCHASEという銀行だけを検索するとします。最も近い5つの銀行の中にCHASEという銀行がない場合、sdo_num_res=5を指定しても、SDO_NNは行を戻しません。これは、sdo_num_resキーワードは近接性のみを考慮し、WHERE句の条件は無視するためです。

オプションのsdo_batch_sizeキーワードを指定すると、sdo_num_res仕様が無視され、SDO_NNは、WHERE句に指定された数を満たすまでジオメトリを距離順に戻し続けます。WHERE句でbank_name = 'CHASE' AND rownum < 6と指定すると、bank_name = 'CHASE'を満たす最も近い5つの銀行が戻されます。

SDO_NN_DISTANCEは、SDO_NN演算子の補助演算子です。この演算子は、SDO_NN演算子によって戻されたオブジェクトの距離を戻します。SDO_NNへのコール内のみで有効です。

「パーティション空間索引の使用」の例5-1も参照してください。

例C-3 高速道路に最も近い都市の検索

例C-3では、州間高速道路I170に最も近い5都市と、高速道路からそれらの各都市までの距離(マイル)を距離順に戻します。 

SELECT /*+ ORDERED */
       c.city,
       sdo_nn_distance (1) distance_in_miles
FROM geod_interstates i, 
     geod_cities c 
WHERE i.highway = 'I170'
  AND sdo_nn(c.location, i.geom,
             'sdo_num_res=5 unit=mile', 1) = 'TRUE'
ORDER BY distance_in_miles;

例C-3では/*+ ORDERED*/オプティマイザ・ヒントが使用されているため、GEOD_INTERSTATES.HIGHWAY列に索引を作成することが重要です。この例の問合せでは、このヒントによって、最も近いジオメトリの検索が試行される前に、強制的に高速道路I170が検索されます。この例のWHERE句の内容は次のとおりです。

  • i.highwayはGEOD_INTERSTATES表のHIGHWAY列で、I170はHIGHWAY列の値です。

  • c.locationでは、検索列(geometry1)を指定します。これは、GEOD_CITIES表のLOCATION列です。

  • i.geomでは、問合せウィンドウ(geometry2)を指定します。これは、GEOD_INTERSTATES表の、HIGHWAY列に値I170が含まれている行のGEOM列にある空間ジオメトリです。

  • sdo_num_res=5では、検索する最も近いジオメトリの数を指定します。

  • unit=mileでは、SDO_NN_DISTANCE補助演算子によって戻される距離の計測単位を指定します。

  • sdo_nn_distance (1)および'sdo_num_res=5 unit=mile', 1の1は、SDO_NNのコールとSDO_NN_DISTANCEのコールを関連付けるnumberパラメータ値です。

例C-3では、WHERE句で得られる結果が、ORDER BY distance_in_milesによって距離(マイル)の順に戻されます。

例C-3の文によって、次の出力結果が得られます(読みやすくするために若干変更が加えられています)。 

CITY                    DISTANCE_IN_MILES
----------------------  ------------------------------
St Louis                  5.36297295
Springfield              78.7997464
Peoria                  141.478022
Evansville              158.22422
Springfield             188.508631

例C-4 高速道路に最も近く、指定の人口を超える都市の検索

例C-4では、例C-3の検索を拡張して、1990年の人口が特定数を超える都市を戻すように結果を制限しています。ここでは、1990年の人口が30万人を超える州間高速道路I170に最も近い5都市、各都市の1990年の人口、および高速道路からそれらの各都市までの距離(マイル)を距離順に戻します。 

SELECT /*+ ORDERED  NO_INDEX(c pop90_idx) */
       c.city, pop90,
       sdo_nn_distance (1) distance_in_miles
FROM geod_interstates i,
     geod_cities c
WHERE i.highway = 'I170'
  AND sdo_nn(c.location, i.geom,
            'sdo_batch_size=10 unit=mile', 1) = 'TRUE'
  AND c.pop90 > 300000
  AND rownum < 6
ORDER BY distance_in_miles;

例C-4ではORDEREDオプティマイザ・ヒントが使用されているため、GEOD_INTERSTATES.HIGHWAY列に索引を作成することが重要です。この例の問合せでは、このヒントによって、最も近いジオメトリの検索が試行される前に、強制的に高速道路I170が検索されます。

正確な結果を得るために、SDO_NN検索列(geometry1)と同じ表の列のすべての非空間索引を無効にします。この例では、NO_INDEX(c pop90_idx)オプティマイザ・ヒントによってPOP90列の非空間索引を無効にします。

この例のWHERE句の内容は次のとおりです。

  • sdo_batch_size=10では、ジオメトリが継続的に戻され(距離順に、一度に10ジオメトリずつ)、WHERE句の他の条件を満たしているかどうかが確認されます。

  • c.pop90 > 300000では、POP90列の値が300000より大きい行に結果が制限されます。

  • rownum < 6では、戻される結果の数が5に制限されます。

例C-4では、WHERE句で得られる結果が、ORDER BY distance_in_milesによって距離(マイル)の順に戻されます。

例C-4の文によって、次の出力結果が得られます(読みやすくするために若干変更が加えられています)。 

CITY               POP90    DISTANCE_IN_MILES
-----------------  -------  ---------------------
St Louis            396685     5.36297295
Kansas City         435146   227.404883
Indianapolis        741952   234.708666
Memphis             610337   244.202072
Chicago            2783726   253.547961