ここでは、空間表の作成、データの挿入、空間索引の作成および空間問合せの実行についての例を示します。例では、「Spatialの概念」およびこの章の他の項で説明する内容の概念を示しています。
その内容は、ソフト・ドリンクのメーカーが持っている、数種類の製品(コーラ)に対する市場の関心度についての地域別検証データです。コーラは、その会社、競合他社および数社の共同で生産されています。それぞれの対象領域は、ユーザーが定義した次の基準を表現しています。たとえば、そのコーラが主にマーケットシェアを持つ領域、他社製品よりもシェアが少ない領域、または今後成長が期待できる領域です。それぞれの領域には、都市内の地区、都道府県内の地域、国などがあるとします。
図2-1に、4種類のコーラについての対象領域を示します。
例2-1では、次の処理を実行します。
空間データを格納するための表(COLA_MARKETS)を作成する
4つの対象領域(cola_a
、cola_b
、cola_c
、cola_d
)に行を挿入する
USER_SDO_GEOM_METADATAビューを更新し、その領域の次元情報を反映する
空間索引(COLA_SPATIAL_IDX)を作成する
空間問合せを実行する
例2-1で説明する概念および定義の詳細は、この章の他の項を参照してください。
例2-1 空間データの挿入、索引付けおよび問合せの例
-- Create a table for cola (soft drink) markets in a -- given geography (such as city or state). -- Each row will be an area of interest for a specific -- cola (for example, where the cola is most preferred -- by residents, where the manufacturer believes the -- cola has growth potential, and so on). -- (For restrictions on spatial table and column names, see -- TABLE_NAME and COLUMN_NAME.) CREATE TABLE cola_markets ( 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 VALUES( 1, 'cola_a', 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 ) ); -- 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 VALUES( 2, 'cola_b', 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', 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) ) ); -- Now insert an area of interest for Cola D. This is a -- circle with a radius of 2. It is completely outside the -- first three areas of interest. INSERT INTO cola_markets VALUES( 4, 'cola_d', 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; -- Preceding statement created an R-tree index. ------------------------------------------------------------------- -- PERFORM SOME SPATIAL QUERIES -- ------------------------------------------------------------------- -- Return the topological intersection of two geometries. SELECT SDO_GEOM.SDO_INTERSECTION(c_a.shape, c_c.shape, 0.005) FROM cola_markets c_a, cola_markets c_c WHERE c_a.name = 'cola_a' AND c_c.name = 'cola_c'; -- Do two geometries have any spatial relationship? SELECT SDO_GEOM.RELATE(c_b.shape, 'anyinteract', c_d.shape, 0.005) FROM cola_markets c_b, cola_markets c_d WHERE c_b.name = 'cola_b' AND c_d.name = 'cola_d'; -- Return the areas of all cola markets. SELECT name, SDO_GEOM.SDO_AREA(shape, 0.005) FROM cola_markets; -- Return the area of just cola_a. SELECT c.name, SDO_GEOM.SDO_AREA(c.shape, 0.005) FROM cola_markets c WHERE c.name = 'cola_a'; -- Return the distance between two geometries. SELECT SDO_GEOM.SDO_DISTANCE(c_b.shape, c_d.shape, 0.005) FROM cola_markets c_b, cola_markets c_d WHERE c_b.name = 'cola_b' AND c_d.name = 'cola_d'; -- Is a geometry valid? SELECT c.name, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(c.shape, 0.005) FROM cola_markets c WHERE c.name = 'cola_c'; -- Is a layer valid? (First, create the results table.) CREATE TABLE val_results (sdo_rowid ROWID, result VARCHAR2(2000)); CALL SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('COLA_MARKETS', 'SHAPE', 'VAL_RESULTS', 2); SELECT * from val_results;