この項では、座標系変換ファンクションおよびプロシージャを使用する簡単な例を示します。この例では、この章で説明する概要および「SDO_CSパッケージ(座標系変換)」で説明するファンクションを使用します。
例6-17 座標系変換の簡単な例
例6-17では、主に、「空間データの挿入、索引付けおよび問合せの例」で使用したジオメトリ・データ(cola markets)を使用します(ただし、SDO_SRIDの値はNULLではなく8307です)。つまり、ジオメトリは、SRID値が8307でwell-known nameが「Longitude / Latitude (WGS 84)」である座標系を使用するように定義されます。これは、最も一般的な座標系であり、Global Positioning System (GPS)デバイスで使用されます。ジオメトリは、SRID値が8199でwell-known nameが「Longitude / Latitude (Arc 1950)」の座標系を使用して変換されます。
例6-17では、「空間データの挿入、索引付けおよび問合せの例」に示すジオメトリを使用します(ただし、円弧は測地座標系でサポートされていないため、cola_d
は円弧ではなく矩形(ここでは、正方形)です)。
例6-17では、次の処理を行います。
空間データを格納するための表(COLA_MARKETS_CS)を作成する
SDO_SRID値8307を使用して、4つの対象領域(cola_a
、cola_b
、cola_c
、cola_d
)の行を挿入する
SDO_SRID値8307を使用して、USER_SDO_GEOM_METADATAビューを更新し、その領域の次元を反映する
空間索引(COLA_SPATIAL_IDX_CS)を作成する
(単一ジオメトリおよびレイヤー全体の)変換操作を実行する
-- Create a table for cola (soft drink) markets in a -- given geography (such as city or state). CREATE TABLE cola_markets_cs ( mkt_id NUMBER PRIMARY KEY, name VARCHAR2(32), shape SDO_GEOMETRY); -- The next INSERT statement creates an area of interest for -- Cola A. This area happens to be a rectangle. -- The area could represent any user-defined criterion: for -- example, where Cola A is the preferred drink, where -- Cola A is under competitive pressure, where Cola A -- has strong growth potential, and so on. INSERT INTO cola_markets_cs VALUES( 1, 'cola_a', SDO_GEOMETRY( 2003, -- two-dimensional polygon 8307, -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), -- polygon SDO_ORDINATE_ARRAY(1,1, 5,1, 5,7, 1,7, 1,1) -- All vertices must -- be defined for rectangle with geodetic data. ) ); -- The next two INSERT statements create areas of interest for -- Cola B and Cola C. These areas are simple polygons (but not -- rectangles). INSERT INTO cola_markets_cs VALUES( 2, 'cola_b', SDO_GEOMETRY( 2003, -- two-dimensional polygon 8307, 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_cs VALUES( 3, 'cola_c', SDO_GEOMETRY( 2003, -- two-dimensional polygon 8307, 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 a rectangle (here, square) instead of a circle as in the original, -- because arcs are not supported with geodetic coordinate systems. INSERT INTO cola_markets_cs VALUES( 4, 'cola_d', SDO_GEOMETRY( 2003, -- two-dimensional polygon 8307, -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), -- polygon SDO_ORDINATE_ARRAY(10,9, 11,9, 11,10, 10,10, 10,9) -- All vertices must -- be defined for rectangle with geodetic data. ) ); --------------------------------------------------------------------------- -- 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 (table-column combination; here: cola_markets_cs and shape). INSERT INTO user_sdo_geom_metadata (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ( 'cola_markets_cs', 'shape', SDO_DIM_ARRAY( SDO_DIM_ELEMENT('Longitude', -180, 180, 10), -- 10 meters tolerance SDO_DIM_ELEMENT('Latitude', -90, 90, 10) -- 10 meters tolerance ), 8307 -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system ); ------------------------------------------------------------------- -- CREATE THE SPATIAL INDEX -- ------------------------------------------------------------------- CREATE INDEX cola_spatial_idx_cs ON cola_markets_cs(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX; ------------------------------------------------------------------- -- TEST COORDINATE SYSTEM TRANSFORMATION -- ------------------------------------------------------------------- -- Return the transformation of cola_c using to_srid 8199 -- ('Longitude / Latitude (Arc 1950)') SELECT c.name, SDO_CS.TRANSFORM(c.shape, 8199) FROM cola_markets_cs c WHERE c.name = 'cola_c'; -- Same as preceding, but using to_srname parameter. SELECT c.name, SDO_CS.TRANSFORM(c.shape, 'Longitude / Latitude (Arc 1950)') FROM cola_markets_cs c WHERE c.name = 'cola_c'; -- Transform the entire SHAPE layer and put results in the table -- named cola_markets_cs_8199, which the procedure will create. CALL SDO_CS.TRANSFORM_LAYER('COLA_MARKETS_CS','SHAPE','COLA_MARKETS_CS_8199',8199); -- Select all from the old (existing) table. SELECT * from cola_markets_cs; -- Select all from the new (layer transformed) table. SELECT * from cola_markets_cs_8199; -- Show metadata for the new (layer transformed) table. DESCRIBE cola_markets_cs_8199; -- Use a geodetic MBR with SDO_FILTER. SELECT c.name FROM cola_markets_cs c WHERE SDO_FILTER(c.shape, SDO_GEOMETRY( 2003, 8307, -- SRID for WGS 84 longitude/latitude NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(6,5, 10,10)) ) = 'TRUE';
例6-18に、例6-17のSELECT文の出力結果を示します。元のジオメトリ(SRID 8307)の座標と変換された座標(SRID 8199)の違いに注目してください。たとえば、cola_a
については、(1, 1, 5, 1, 5, 7, 1, 7, 1, 1)と(1.00078604, 1.00274579, 5.00069354, 1.00274488, 5.0006986, 7.00323528, 1.00079179, 7.00324162, 1.00078604, 1.00274579)です。
例6-18 座標系変換の例でのSELECT文の出力
SQL> -- Return the transformation of cola_c using to_srid 8199 SQL> -- ('Longitude / Latitude (Arc 1950)') SQL> SELECT c.name, SDO_CS.TRANSFORM(c.shape, 8199) 2 FROM cola_markets_cs c WHERE c.name = 'cola_c'; NAME -------------------------------- SDO_CS.TRANSFORM(C.SHAPE,8199)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM -------------------------------------------------------------------------------- cola_c SDO_GEOMETRY(2003, 8199, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(3.00074114, 3.00291482, 6.00067068, 3.00291287, 6.0006723, 5.00307625, 4.0007 1961, 5.00307838, 3.00074114, 3.00291482)) SQL> SQL> -- Same as preceding, but using to_srname parameter. SQL> SELECT c.name, SDO_CS.TRANSFORM(c.shape, 'Longitude / Latitude (Arc 1950)') 2 FROM cola_markets_cs c WHERE c.name = 'cola_c'; NAME -------------------------------- SDO_CS.TRANSFORM(C.SHAPE,'LONGITUDE/LATITUDE(ARC1950)')(SDO_GTYPE, SDO_SRID, SDO -------------------------------------------------------------------------------- cola_c SDO_GEOMETRY(2003, 8199, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(3.00074114, 3.00291482, 6.00067068, 3.00291287, 6.0006723, 5.00307625, 4.0007 1961, 5.00307838, 3.00074114, 3.00291482)) SQL> SQL> -- Transform the entire SHAPE layer and put results in the table SQL> -- named cola_markets_cs_8199, which the procedure will create. SQL> CALL SDO_CS.TRANSFORM_LAYER('COLA_MARKETS_CS','SHAPE','COLA_MARKETS_CS_8199',8199); Call completed. SQL> SQL> -- Select all from the old (existing) table. SQL> SELECT * from cola_markets_cs; MKT_ID NAME ---------- -------------------------------- SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- 1 cola_a SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(1, 1, 5, 1, 5, 7, 1, 7, 1, 1)) 2 cola_b SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(5, 1, 8, 1, 8, 6, 5, 7, 5, 1)) 3 cola_c MKT_ID NAME ---------- -------------------------------- SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(3, 3, 6, 3, 6, 5, 4, 5, 3, 3)) 4 cola_d SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(10, 9, 11, 9, 11, 10, 10, 10, 10, 9)) SQL> SQL> -- Select all from the new (layer transformed) table. SQL> SELECT * from cola_markets_cs_8199; SDO_ROWID ------------------ GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- AAABZzAABAAAOa6AAA SDO_GEOMETRY(2003, 8199, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(1.00078604, 1.00274579, 5.00069354, 1.00274488, 5.0006986, 7.00323528, 1.0007 9179, 7.00324162, 1.00078604, 1.00274579)) AAABZzAABAAAOa6AAB SDO_GEOMETRY(2003, 8199, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(5.00069354, 1.00274488, 8.00062191, 1.00274427, 8.00062522, 6.00315345, 5.000 6986, 7.00323528, 5.00069354, 1.00274488)) SDO_ROWID ------------------ GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- AAABZzAABAAAOa6AAC SDO_GEOMETRY(2003, 8199, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(3.00074114, 3.00291482, 6.00067068, 3.00291287, 6.0006723, 5.00307625, 4.0007 1961, 5.00307838, 3.00074114, 3.00291482)) AAABZzAABAAAOa6AAD SDO_GEOMETRY(2003, 8199, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(10.0005802, 9.00337775, 11.0005553, 9.00337621, 11.0005569, 10.0034478, 10.00 SDO_ROWID ------------------ GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- 05819, 10.0034495, 10.0005802, 9.00337775)) SQL> SQL> -- Show metadata for the new (layer transformed) table. SQL> DESCRIBE cola_markets_cs_8199; Name Null? Type ----------------------------------------- -------- ---------------------------- SDO_ROWID ROWID GEOMETRY SDO_GEOMETRY SQL> SQL> -- Use a geodetic MBR with SDO_FILTER SQL> SELECT c.name FROM cola_markets_cs c WHERE 2 SDO_FILTER(c.shape, 3 SDO_GEOMETRY( 4 2003, 5 8307, -- SRID for WGS 84 longitude/latitude 6 NULL, 7 SDO_ELEM_INFO_ARRAY(1,1003,3), 8 SDO_ORDINATE_ARRAY(6,5, 10,10)) 9 ) = 'TRUE'; NAME -------------------------------- cola_c cola_b cola_d