| Oracle® Spatial and Graph開発者ガイド 12cリリース1 (12.1) B72470-07 |
|
 前 |
 次 |
この項では、座標系変換ファンクションおよびプロシージャを使用する簡単な例を示します。この例では、この章で説明する概要および「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
