3.1 ST_GEOMETRYとSDO_GEOMETRYとの相互運用性

SQL MultimediaのST_GEOMETRYルート・タイプ(サブタイプを含む)とOracle Spatial and GraphのSDO_GEOMETRY型(「SDO_GEOMETRYオブジェクト型」を参照)は、基本的に相互運用できます。ST_GEOMETRYサブタイプは次のとおりです。

• ST_CIRCULARSTRING

• ST_COMPOUNDCURVE

• ST_CURVE

• ST_CURVEPOLYGON

• ST_GEOMCOLLECTION

• ST_LINESTRING

• ST_MULTICURVE

• ST_MULTILINESTRING

• ST_MULTIPOINT

• ST_MULTIPOLYGON

• ST_MULTISURFACE

• ST_POINT

• ST_POLYGON

• ST_SURFACE

ST_GEOMETRY型には、SDO_GEOMETRYオブジェクトを使用してその型のインスタンスを作成するための追加コンストラクタ・メソッド(つまり、ISO標準で定義されたコンストラクタに加え)があります。このコンストラクタには、次の形式があります。

ST_GEOMETRY(geom SDO_GEOMETRY);

例3-1 空間列でのST_GEOMETRY型の使用

例3-1では、SDO_GEOMETRY型のかわりに空間列にST_GEOMETRY型を使用して表を作成し、ST_GEOMETRYコンストラクタを使用して、その表に行を挿入する際のSHAPE列の値を指定します。

CREATE TABLE cola_markets (
  mkt_id NUMBER PRIMARY KEY,
  name VARCHAR2(32),
  shape ST_GEOMETRY);
 
INSERT INTO cola_markets VALUES(
  1,
  'cola_a',
  ST_GEOMETRY(
    SDO_GEOMETRY(
      2003,  -- two-dimensional polygon
      NULL,
      NULL,
      SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior)
      SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to
            -- define rectangle (lower left and upper right) with
            -- Cartesian-coordinate data
    )
  )
);

ST_GEOMETRY型の空間列を持つ表を作成する場合は、SDO_GEOMETRY型を使用して定義される空間データの場合と同様に、その情報をUSER_SDO_GEOM_METADATAビューに追加し、ST_GEOMETRY列に空間索引を作成する必要があります。これらの操作を実行した後に、ST_GEOMETRYデータでOracle Spatial and Graphの演算子(「空間演算子」を参照)を使用できます。標準で定義されている演算子の他に、SDO_NN演算子およびSDO_WITHIN_DISTANCE演算子も使用できます。

例3-2 ST_GEOMETRYデータの作成、索引付け、格納および問合せ

例3-2では、「空間データの挿入、索引付けおよび問合せの例」の例2-1と基本的に同じ操作を実行しますが、空間列に対して、SDO_GEOMETRY型のかわりにST_GEOMETRY型を使用します。

CREATE TABLE cola_markets (
  mkt_id NUMBER PRIMARY KEY,
  name VARCHAR2(32),
  shape ST_GEOMETRY);
 
INSERT INTO cola_markets VALUES(
  1,
  'cola_a',
  ST_GEOMETRY(
    SDO_GEOMETRY(
      2003,  -- two-dimensional polygon
      NULL,
      NULL,
      SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior)
      SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to
            -- define rectangle (lower left and upper right) with
            -- Cartesian-coordinate data
    )
  )
);
 
INSERT INTO cola_markets VALUES(
  2,
  'cola_b',
  ST_GEOMETRY(
    SDO_GEOMETRY(
      2003,  -- two-dimensional polygon
      NULL,
      NULL,
      SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
      SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)
    )
  )
);
 
INSERT INTO cola_markets VALUES(
  3,
  'cola_c',
  ST_GEOMETRY(
    SDO_GEOMETRY(
      2003,  -- two-dimensional polygon
      NULL,
      NULL,
      SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
      SDO_ORDINATE_ARRAY(3,3, 6,3, 6,5, 4,5, 3,3)
    )
  )
);
 
INSERT INTO cola_markets VALUES(
  4,
  'cola_d',
  ST_GEOMETRY(
    SDO_GEOMETRY(
      2003,  -- two-dimensional polygon
      NULL,
      NULL,
      SDO_ELEM_INFO_ARRAY(1,1003,4), -- one circle
      SDO_ORDINATE_ARRAY(8,7, 10,9, 8,11)
    )
  )
);
 
---------------------------------------------------------------------------
-- UPDATE METADATA VIEW --
---------------------------------------------------------------------------
 
-- Update the USER_SDO_GEOM_METADATA view. This is required before
-- the spatial index can be created. Do this only once for each layer
-- (that is, table-column combination; here: cola_markets and shape).
 
INSERT INTO user_sdo_geom_metadata
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID) 
  VALUES (
    'cola_markets',
    'shape',
    SDO_DIM_ARRAY(   -- 20X20 grid
      SDO_DIM_ELEMENT('X', 0, 20, 0.005),
      SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
       ),
    NULL   -- SRID
  );
 
-------------------------------------------------------------------
-- CREATE THE SPATIAL INDEX --
-------------------------------------------------------------------
 
CREATE INDEX cola_spatial_idx
ON cola_markets(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
 
---------------------------
-- SDO_NN and SDO_WITHIN_DISTANCE
--------------------------
 
-- SDO_NN operator.
 
SELECT  /*+ INDEX(c cola_spatial_idx) */ c.mkt_id, c.name  
   FROM cola_markets c  
   WHERE SDO_NN(c.shape,  sdo_geometry(2001, NULL, 
      sdo_point_type(10,7,NULL), NULL,  NULL),  'sdo_num_res=2') = 'TRUE'; 
 
-- SDO_NN_DISTANCE ancillary operator
 
SELECT   /*+ INDEX(c cola_spatial_idx) */ 
   c.mkt_id, c.name, SDO_NN_DISTANCE(1) dist
   FROM cola_markets c  
   WHERE SDO_NN(c.shape,  sdo_geometry(2001, NULL, 
      sdo_point_type(10,7,NULL), NULL,  NULL),
      'sdo_num_res=2', 1) = 'TRUE' ORDER BY dist; 
 
-- SDO_WITHIN_DISTANCE operator (two examples)
 
SELECT c.name FROM cola_markets c WHERE SDO_WITHIN_DISTANCE(c.shape, 
  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), 
    SDO_ORDINATE_ARRAY(4,6, 8,8)), 
  'distance=10') = 'TRUE';  
 
-- What geometries are within a distance of 10 from a query window
-- (here, a rectangle with lower-left, upper-right coordinates 4,6, 8,8)?
-- But exclude geoms with MBRs with both sides < 4.1, i.e., cola_c and cola_d.
 
SELECT c.name FROM cola_markets c WHERE SDO_WITHIN_DISTANCE(c.shape, 
  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), 
    SDO_ORDINATE_ARRAY(4,6, 8,8)), 
  'distance=10 min_resolution=4.1') = 'TRUE';  
 
-------------------------------------
-- Some ST_GEOMETRY member functions
-------------------------------------
 
SELECT c.shape.GET_WKB() 
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.GET_WKT() 
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.ST_COORDDIM()
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.ST_ISVALID() 
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.ST_SRID() 
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.ST_SRID(8307) 
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.ST_ISEMPTY() 
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.ST_ENVELOPE() 
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.ST_BOUNDARY() 
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.ST_GEOMETRYTYPE() 
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.ST_ISSIMPLE()
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.ST_DIMENSION()
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.ST_CONVEXHULL()
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.ST_CENTROID()
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.ST_GETTOLERANCE()
  FROM cola_markets c WHERE c.name = 'cola_b';
 
-- Some member functions that require a parameter
DECLARE
  cola_a_geom ST_GEOMETRY;
  cola_b_geom ST_GEOMETRY;
  cola_c_geom ST_GEOMETRY;
  cola_d_geom ST_GEOMETRY;
  returned_geom ST_GEOMETRY;
  returned_number NUMBER;
 
BEGIN
 
-- Populate geometry variables with cola market shapes.
SELECT c.shape INTO cola_a_geom FROM cola_markets c
  WHERE c.name = 'cola_a';
SELECT c.shape INTO cola_b_geom FROM cola_markets c
  WHERE c.name = 'cola_b';
SELECT c.shape INTO cola_c_geom FROM cola_markets c
  WHERE c.name = 'cola_c';
SELECT c.shape INTO cola_d_geom FROM cola_markets c
  WHERE c.name = 'cola_d';
 
SELECT c.shape.ST_EQUALS(cola_a_geom) INTO returned_number
  FROM cola_markets c WHERE c.name = 'cola_b';
DBMS_OUTPUT.PUT_LINE('Is cola_b equal to cola_a?: ' || returned_number);
 
SELECT c.shape.ST_SYMMETRICDIFFERENCE(cola_a_geom) INTO returned_geom
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.ST_DISTANCE(cola_d_geom) INTO returned_number
  FROM cola_markets c WHERE c.name = 'cola_b';
DBMS_OUTPUT.PUT_LINE('Distance between cola_b equal to cola_d: ' || returned_number);
 
SELECT c.shape.ST_INTERSECTS(cola_a_geom) INTO returned_number
  FROM cola_markets c WHERE c.name = 'cola_b';
DBMS_OUTPUT.PUT_LINE('Does cola_b intersect cola_a?: ' || returned_number);
 
SELECT c.shape.ST_CROSS(cola_a_geom) INTO returned_number
  FROM cola_markets c WHERE c.name = 'cola_b';
DBMS_OUTPUT.PUT_LINE('Does cola_b cross cola_a?: ' || returned_number);
 
SELECT c.shape.ST_DISJOINT(cola_a_geom) INTO returned_number
  FROM cola_markets c WHERE c.name = 'cola_b';
DBMS_OUTPUT.PUT_LINE('Is cola_b disjoint with cola_a?: ' || returned_number);
 
SELECT c.shape.ST_TOUCH(cola_a_geom) INTO returned_number
  FROM cola_markets c WHERE c.name = 'cola_b';
DBMS_OUTPUT.PUT_LINE('Does cola_b touch cola_a?: ' || returned_number);
 
SELECT c.shape.ST_WITHIN(cola_a_geom) INTO returned_number
  FROM cola_markets c WHERE c.name = 'cola_b';
DBMS_OUTPUT.PUT_LINE('Is cola_b within cola_a?: ' || returned_number);
 
SELECT c.shape.ST_OVERLAP(cola_a_geom) INTO returned_number
  FROM cola_markets c WHERE c.name = 'cola_b';
DBMS_OUTPUT.PUT_LINE('Does cola_b overlap cola_a?: ' || returned_number);
 
SELECT c.shape.ST_CONTAINS(cola_a_geom) INTO returned_number
  FROM cola_markets c WHERE c.name = 'cola_b';
DBMS_OUTPUT.PUT_LINE('Does cola_b contain cola_a?: ' || returned_number);
 
SELECT c.shape.ST_INTERSECTION(cola_a_geom) INTO returned_geom
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.ST_DIFFERENCE(cola_a_geom) INTO returned_geom
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.ST_UNION(cola_a_geom) INTO returned_geom
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.ST_SYMDIFFERENCE(cola_a_geom) INTO returned_geom
  FROM cola_markets c WHERE c.name = 'cola_b';
 
SELECT c.shape.ST_TOUCHES(cola_a_geom) INTO returned_number
  FROM cola_markets c WHERE c.name = 'cola_b';
DBMS_OUTPUT.PUT_LINE('Does cola_b touch cola_a?: ' || returned_number);
 
SELECT c.shape.ST_CROSSES(cola_a_geom) INTO returned_number
  FROM cola_markets c WHERE c.name = 'cola_b';
DBMS_OUTPUT.PUT_LINE('Does cola_b cross cola_a?: ' || returned_number);
 
END;
/