| Oracle® Spatial開発者ガイド 11gリリース2 (11.2) B72087-03 |
|
 前 |
 次 |
この付録では、第III部「リファレンス情報」のリファレンスの章に示した例より複雑な問合せの例について説明します。SDO_WITHIN_DISTANCE、SDO_NNなど、Spatialアプリケーションで頻繁に使用される演算子を中心に説明します。
この付録の内容は、Spatialユーザーのサポートおよびトレーニングを行うオラクル社の担当者からの情報に基づいています。Oracle Spatialトレーニング・コースでは、ここで示す例の多くに加えて、さらに他の例および説明を提供しています。
この付録に示す例を使用する前に、第I部「概念および使用方法」および第III部「リファレンス情報」に示した関連する演算子またはファンクションの使用方法およびリファレンス情報を理解してください。
この付録の内容は次のとおりです。
この付録の例では、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);
SDO_WITHIN_DISTANCE演算子は、指定されたオブジェクトから指定された距離内に存在する一連の空間オブジェクトを識別します。距離には、近似値または正確な値を指定できます。querytype=FILTERを指定すると、1次フィルタ処理のみが実行されるため、距離は近似値になります。指定しない場合は、1次と2次の両方のフィルタ処理が実行されるため、距離は正確な値になります。
例C-1では、州間高速道路I170から15マイル以内に存在するすべての都市を検索します。
例C-1 高速道路から一定距離内に存在するすべての都市の検索
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では、Tampa市から15マイル以内に存在するすべての州間高速道路を検索します。
例C-2 都市から一定距離内に存在するすべての高速道路の検索
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列にある空間ジオメトリです。
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へのコール内のみで有効です。
例C-3では、州間高速道路I170に最も近い5都市と、高速道路からそれらの各都市までの距離(マイル)を距離順に戻します。
例C-3 高速道路に最も近い都市の検索
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-3の検索を拡張して、1990年の人口が特定数を超える都市を戻すように結果を制限しています。ここでは、1990年の人口が30万人を超える州間高速道路I170に最も近い5都市、各都市の1990年の人口、および高速道路からそれらの各都市までの距離(マイル)を距離順に戻します。
例C-4 高速道路に最も近く、指定の人口を超える都市の検索
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
多くの行を集計する場合、単一のSDO_AGGR_UNION集計ファンクションの実行に時間がかかることがあります。これは、SDO_AGGR_UNIONが、渡された最初の2つのジオメトリを集計し、その結果と次のジオメトリを集計し、さらにその結果と次のジオメトリを集計するという処理を行うためです。この実行に最終的に時間がかかるようになる理由は、結果としてのジオメトリが短時間に多くの頂点で構成されるようになってしまい、後続の集計すべてに、この非常に複雑なジオメトリが含まれるためです。
多くの行を集計する際により良いパフォーマンスを得るには、集計をグループに分割し、各グループのジオメトリ数が常に50以下になるようにします。例C-5は、次の内容を示しています。
集計するジオメトリ(この場合、米国カリフォルニア州、オレゴン州およびワシントン州の郵便番号ジオメトリ)には、50のグループがいくつ必要か(2の累乗)を決定するためのSELECT文が最初に必要です。このようなジオメトリが5000ある場合、POWERファンクションの結果は128になり、これを次のSELECT文の最も内側のGROUP BY句に使用する必要があります。
1つのSELECT文で、複数のネストされた集計とGROUP BY句を使用します。最高のパフォーマンスを得るには、GROUP BY句で2の累乗値をスキップしながら、2に到達するようにします。この例の句では、128、32、8および2を使用しますが、64、16または4は使用しません。
例C-5 行が多数ある場合のグルーピングを使用した集計和集合
-- Determine how many groupings of 50 are needed. Assume 5000 rows in
-- all. 5000/50 = 100; and 128 is the next higher power of 2.
SELECT POWER(2, CEIL( LOG(2, COUNT(*)/50)))
FROM ZIP_CODES z
WHERE z.state_code IN ('CA', 'OR', 'WA');
-- Perform the aggregate union operation, using 128 in the
-- innermost GROUP BY clause.
SELECT sdo_aggr_union(sdoaggrtype(aggr_geom,0.5)) aggr_geom
FROM
(SELECT sdo_aggr_union(sdoaggrtype(aggr_geom,0.5)) aggr_geom
FROM
(SELECT sdo_aggr_union(sdoaggrtype(aggr_geom,0.5)) aggr_geom
FROM
(SELECT sdo_aggr_union(sdoaggrtype(aggr_geom,0.5)) aggr_geom
FROM
(SELECT sdo_aggr_union(mdsys.sdoaggrtype(geom,0.5)) aggr_geom
FROM ZIP_CODES z
WHERE z.state_code IN ('CA', 'OR', 'WA')
GROUP BY mod(rownum,128))
GROUP BY mod (rownum, 32))
GROUP BY mod (rownum, 8))
GROUP BY mod (rownum, 2)
);
