地理空間結合述部の例 - Advanced SQL Engine - Teradata Database

例: 地理空間結合述部の使用

次の例は、2番目のテーブルで定義されたポリゴン内にある1つのテーブル内の点を決定するための地理空間結合述語の使用法を示します。このようなクエリーでは、Teradata最適化ルーチンは、データ アクセスを高速化するために入れ子結合の生成を検討します。

CREATE TABLE DB_TEST.T1
(a INTEGER, b char(20000), GeoCol ST_Geometry)
INDEX (GeoCol);

CREATE TABLE DB_TEST.T2
(pkey INTEGER, buffer char(20000), Geom ST_Geometry)
INDEX (Geom);


INSERT INTO DB_TEST.T1 (1, 'Teradata01', 'POINT(10 20)');
INSERT INTO DB_TEST.T1 (1, 'Teradata02', 'POINT(20 30)');

INSERT INTO DB_TEST.T2 (1, 'Teradata01', 'POLYGON((3 3, 3 8, 8 8, 8 3, 3 3))');
INSERT INTO DB_TEST.T2 (1, 'Teradata02', 'POLYGON((15 15, 15 35, 35 35, 35 15, 15 15))');

SELECT a,b (format 'x(12)'), geocol
FROM T2
INNER JOIN T1 ON T1.GeoCol.ST_WITHIN(T2.Geom)= 1;

          a b            GeoCol
----------- ------------ -----------------
          1 Teradata02   POINT (20 30)

テーブルに挿入される行の数と地理空間データ自体の性質により、最適化ルーチンが地理空間インデックスを使用するかどうかが決まります。この簡単な例では、各テーブルに行が2つしかなく、地理空間インデックスを使用することはおそらくないでしょう。非常に大規模なテーブルや、より複雑な図形、データベースの特性および構成によって、最適化ルーチンがクエリー処理を高速化するためにインデックスを使用する可能性があります。インデックスを使用した場合、EXPLAINは次のようになります。ステップ5で、地理空間インデックスを使用したことを示しています。

Explanation
---------------------------------------------------------------------------
  1) First, we lock DB_TEST.T1 for read on a reserved RowHash to
     prevent global deadlock.
  2) Next, we lock DB_TEST.T2 for read on a reserved RowHash to
     prevent global deadlock.
  3) We lock DB_TEST.T1 for read, and we lock DB_TEST.T2 for read.
  4) We do an all-AMPs RETRIEVE step from DB_TEST.T2 by way of an
     all-rows scan with a condition of ("DB_TEST.T2.b > 1") into
     Spool 2 (all_amps), which is duplicated on all AMPs. The size of
     Spool 2 is estimated with no confidence to be 116 rows (1,168,468
     bytes). The estimated time for this step is 0.67 seconds.
  5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
     all-rows scan, which is joined to DB_TEST.T1 by way of Spatial
     index # 4 "Retrieving from CDT indexed column DB_TEST.T1.sp via
     CDT key expression {LeftTable}.sp .ST_MBR ()"extracting row ids
     only. Spool 2 and DB_TEST.T1 are joined using a nested join,
     with a join condition of ("(1=1)"). The result goes into Spool 3
     (all_amps), which is built locally on the AMPs. Then we do a SORT
     to order Spool 3 by field Id 1. The size of Spool 3 is estimated
     with no confidence to be 1 row (10,083 bytes). The estimated time
     for this step is 0.01 seconds.
  6) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an
     all-rows scan, which is joined to DB_TEST.T1 by way of an
     all-rows scan with a condition of ("DB_TEST.T1.b > 1"). Spool
     3 and DB_TEST.T1 are joined using a row id join, with a join
     condition of ("(sp .ST_WITHIN ({RightTable}.sp ))= 1"). The
     result goes into Spool 1 (group_amps), which is built locally on
     the AMPs. Then we do a SORT to order Spool 1 by the sort key in
     spool field1. The size of Spool 1 is estimated with no confidence
     to be 1 row (95 bytes). The estimated time for this step is 0.05 
     seconds.
  7) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
     -> The contents of Spool 1 are sent back to the user as the result of
     statement 1. The total estimated time is 0.73 seconds.

地理空間結合のST_Geometry列と列式の両方の使用

SELECT * FROM geotable g1, geotable2 g2
WHERE g1.geo.ST_Distance (g2.geo.ST_Buffer(1)) < 5
ORDER BY g1.a;