C.3 SDO_NN Examples

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