例2-18では、複数の表(POINTS3D、LINES3DおよびPOLYGONS3D)を作成し、それぞれの表に対応する3次元オブジェクトを(点はPOINTS3Dに、線はLINES3Dに、ポリゴン、面およびソリッドはPOLYGONS3Dに)挿入します。続いて例2-19では、各表のメタデータと空間索引を作成します。
3次元ジオメトリのサポートの詳細は、「3次元の空間オブジェクト」を参照してください。
例2-18 3次元のジオメトリを挿入するSQL文
create table points3d(id number, geometry sdo_geometry); insert into points3d values(1, sdo_geometry(3001,null, sdo_point_type(0,0,0), null, null)); insert into points3d values(2, sdo_geometry(3001,null, sdo_point_type(1,1,1), null, null)); insert into points3d values(3, sdo_geometry(3001,null, sdo_point_type(0,1,1), null, null)); insert into points3d values(4, sdo_geometry(3001,null, sdo_point_type(0,0,1), null, null)); insert into points3d values(5, sdo_geometry(3001,null, sdo_point_type(1,1,0), null, null)); insert into points3d values(6, sdo_geometry(3001,null, sdo_point_type(1,0,1), null, null)); insert into points3d values(7, sdo_geometry(3001,null, sdo_point_type(1,0,0), null, null)); insert into points3d values(8, sdo_geometry(3001,null, sdo_point_type(0,1,0), null, null)); insert into points3d values(9, sdo_geometry(3005,null, null, sdo_elem_info_array(1,1,1, 4,1,1), sdo_ordinate_array(1,1,1, 0,0,0))); create table lines3d(id number, geometry sdo_geometry); insert into lines3d values(1, sdo_geometry(3002,null, null, sdo_elem_info_array(1,2,1), sdo_ordinate_array(1,1,1, 0,0,0))); insert into lines3d values(2, sdo_geometry(3002,null, null, sdo_elem_info_array(1,2,1), sdo_ordinate_array(1,0,1, 0,1,0))); insert into lines3d values(2, sdo_geometry(3002,null, null, sdo_elem_info_array(1,2,1), sdo_ordinate_array(0,1,1, 1,0,0))); insert into lines3d values(3, sdo_geometry(3002,null, null, sdo_elem_info_array(1,2,1), sdo_ordinate_array(0,1,1, 1,0,0))); insert into lines3d values(4, sdo_geometry(3002,null, null, sdo_elem_info_array(1,2,1), sdo_ordinate_array(0,1,0, 1,0,1))); create table polygons3d(id number, geometry sdo_geometry); -- Simple Polygon -- All points have to be on the same plane. insert into polygons3d values(1, SDO_Geometry (3003,NULL,NULL , SDO_Elem_Info_Array(1,1003,1), SDO_Ordinate_Array(0.5,0.0,0.0, 0.5,1.0,0.0, 0.0,1.0,1.0, 0.0,0.0,1.0, 0.5,0.0,0.0 ))); insert into polygons3d values(2, SDO_Geometry (3003,NULL,NULL , SDO_Elem_Info_Array(1,1003,1), SDO_Ordinate_Array(6.0,6.0,6.0, 5.0,6.0,10.0, 3.0,4.0,8.0, 4.0,4.0,4.0, 6.0,6.0,6.0 ))); insert into polygons3d values(3, SDO_Geometry (3007,NULL,NULL , SDO_Elem_Info_Array(1,1003,1,16,1003,1), SDO_Ordinate_Array(6.0,6.0,6.0, 5.0,6.0,10.0, 3.0,4.0,8.0, 4.0,4.0,4.0, 6.0,6.0,6.0, 0.5,0.0,0.0, 0.5,1.0,0.0, 0.0,1.0,1.0, 0.0,0.0,1.0, 0.5,0.0,0.0 ))); -- Polygon with a Hole (same rules as 2D) plus all points on the same plane insert into polygons3d values(4, SDO_Geometry (3003,NULL,NULL , SDO_Elem_Info_Array(1,1003,1,16,2003,1), SDO_Ordinate_Array(0.5,0.0,0.0, 0.5,1.0,0.0, 0.0,1.0,1.0, 0.0,0.0,1.0, 0.5,0.0,0.0, 0.25,0.5,0.5, 0.15,0.5,0.7, 0.15,0.6,0.7, 0.25,0.6,0.5, 0.25,0.5,0.5 ))); -- Surface with 2 3D polygons (on same plane) insert into polygons3d values(5, SDO_Geometry (3003,NULL,NULL , SDO_Elem_Info_Array(1,1006,2,1,1003,1,16,1003,1), SDO_Ordinate_Array(0.5,0.0,0.0, 0.5,1.0,0.0, 0.0,1.0,0.0, 0.0,0.0,0.0, 0.5,0.0,0.0, 1.5,0.0,0.0, 2.5,1.0,0.0, 1.5,2.0,0.0, 0.5,2.0,0.0, 0.5,0.0,0.0, 1.5,0.0,0.0 ))); -- Surface with 2 3D polygons (on two planes) insert into polygons3d values(5, SDO_Geometry(3003,NULL,NULL , SDO_Elem_Info_Array(1,1006,2,1,1003,3,7,1003,3), SDO_Ordinate_Array(2,2,2, 4,4,2, 2,2,2, 4,2,4 ))); -- Surface with 2 3D polygons -- First polygon has one ext and one int. insert into polygons3d values(6, SDO_Geometry (3003,NULL,NULL , SDO_Elem_Info_Array(1,1006,2,1,1003,1,16,2003,1,31,1003,1), SDO_Ordinate_Array(0.5,0.0,0.0, 0.5,1.0,0.0, 0.0,1.0,1.0, 0.0,0.0,1.0, 0.5,0.0,0.0, 0.25,0.5,0.5, 0.15,0.5,0.7, 0.15,0.6,0.7, 0.25,0.6,0.5, 0.25,0.5,0.5, 1.5,0.0,0.0, 2.5,1.0,0.0, 1.5,2.0,0.0, 0.5,2.0,0.0, 0.5,0.0,0.0, 1.5,0.0,0.0 ))); --3D Surface with 3 3D polygons insert into polygons3d values(7, SDO_Geometry (3003,NULL,NULL , SDO_Elem_Info_Array(1,1006,3,1,1003,1,16,1003,1,34,1003,1), SDO_Ordinate_Array(0.5,0.0,0.0, 0.5,1.0,0.0, 0.0,1.0,1.0, 0.0,0.0,1.0, 0.5,0.0,0.0, 1.5,0.0,0.0, 2.5,1.0,0.0, 1.5,2.0,0.0, 0.5,2.0,0.0, 0.5,0.0,0.0, 1.5,0.0,0.0, 1.5,0.0,0.0, 2.5,0.0,0.0, 2.5,1.0,0.0, 1.5,0.0,0.0 ))); -- 3D surface with 3 3D polygons insert into polygons3d values(8, SDO_Geometry (3003,NULL,NULL , SDO_Elem_Info_Array(1,1006,3,1,1003,1,16,2003,1,31,1003,1,49,1003,1), SDO_Ordinate_Array(0.5,0.0,0.0, 0.5,1.0,0.0, 0.0,1.0,1.0, 0.0,0.0,1.0, 0.5,0.0,0.0, 0.25,0.5,0.5, 0.15,0.5,0.7, 0.15,0.6,0.7, 0.25,0.6,0.5, 0.25,0.5,0.5, 1.5,0.0,0.0, 2.5,1.0,0.0, 1.5,2.0,0.0, 0.5,2.0,0.0, 0.5,0.0,0.0, 1.5,0.0,0.0, 0.5,1.0,0.0, 0.5,2.0,0.0, 0.0,2.0,0.0, 0.0,1.0,0.0, 0.5,1.0,0.0 ))); -- Simple 3D polygon insert into polygons3d values(9, SDO_Geometry (3003,NULL,NULL , SDO_Elem_Info_Array(1,1003,1), SDO_Ordinate_Array(0.0,-4.0,1.0, 4.0,-4.0,1.0, 5.0,-3.0,1.0, 5.0,0.0,1.0, 3.0,1.0,1.0, -1.0,1.0,1.0, -3.0,0.5,1.0, 0.0,0.0,1.0, -6.0,-2.0,1.0, -6.0,-3.5,1.0, -2.0,-3.5,1.0, 0.0,-4.0,1.0 ))); -- SOLID with 6 polygons insert into polygons3d values(10, SDO_Geometry (3008,NULL,NULL , SDO_Elem_Info_Array(1,1007,1,1,1006,6,1,1003,1,16,1003,1,31,1003,1,46,1003,1,61,1003,1,76,1003,1), SDO_Ordinate_Array(1.0,0.0,-1.0, 1.0,1.0,-1.0, 1.0,1.0,1.0, 1.0,0.0,1.0, 1.0,0.0,-1.0, 1.0,0.0,1.0, 0.0,0.0,1.0, 0.0,0.0,-1.0, 1.0,0.0,-1.0, 1.0,0.0,1.0, 0.0,1.0,1.0, 0.0,1.0,-1.0, 0.0,0.0,-1.0, 0.0,0.0,1.0, 0.0,1.0,1.0, 1.0,1.0,-1.0, 0.0,1.0,-1.0, 0.0,1.0,1.0, 1.0,1.0,1.0, 1.0,1.0,-1.0, 1.0,1.0,1.0, 0.0,1.0,1.0, 0.0,0.0,1.0, 1.0,0.0,1.0, 1.0,1.0,1.0, 1.0,1.0,-1.0, 1.0,0.0,-1.0, 0.0,0.0,-1.0, 0.0,1.0,-1.0, 1.0,1.0,-1.0 ))); -- Simple SOLID with 6 polygons -- All polygons are described using the optimized rectangle representation. insert into polygons3d values(11, SDO_Geometry (3008,NULL,NULL , SDO_Elem_Info_Array(1,1007,1,1,1006,6,1,1003,3,7,1003,3,13,1003,3,19,1003,3,25,1003,3,31,1003,3), SDO_Ordinate_Array(1.0,0.0,-1.0, 1.0,1.0,1.0, 1.0,0.0,1.0, 0.0,0.0,-1.0, 0.0,1.0,1.0, 0.0,0.0,-1.0, 0.0,1.0,-1.0, 1.0,1.0,1.0, 0.0,0.0,1.0, 1.0,1.0,1.0, 1.0,1.0,-1.0, 0.0,0.0,-1.0 ))); -- Multi-Solid -- Both solids use optimized representation. insert into polygons3d values(12, SDO_Geometry (3009,NULL,NULL , SDO_Elem_Info_Array(1,1007,3,7,1007,3), SDO_Ordinate_Array(-2.0,1.0,3.0, -3.0,-1.0,0.0, 0.0,0.0,0.0, 1.0,1.0,1.0 ))); -- Multi-Solid - like multipolygon in 2D -- disjoint solids insert into polygons3d values(13, SDO_Geometry (3009,NULL,NULL , SDO_Elem_Info_Array(1,1007,1,1,1006,6,1,1003,1,16,1003,1,31,1003,1,46,1003,1,61,1003,1,76,1003,1,91,1007,1,91,1006,7,91,1003,1,106,1003,1,121,1003,1,136,1003,1,151,1003,1,166,1003,1,184,1003,1), SDO_Ordinate_Array(1.0,0.0,4.0, 1.0,1.0,4.0, 1.0,1.0,6.0, 1.0,0.0,6.0, 1.0,0.0,4.0, 1.0,0.0,6.0, 0.0,0.0,6.0, 0.0,0.0,4.0, 1.0,0.0,4.0, 1.0,0.0,6.0, 0.0,1.0,6.0, 0.0,1.0,4.0, 0.0,0.0,4.0, 0.0,0.0,6.0, 0.0,1.0,6.0, 1.0,1.0,4.0, 0.0,1.0,4.0, 0.0,1.0,6.0, 1.0,1.0,6.0, 1.0,1.0,4.0, 1.0,1.0,6.0, 0.0,1.0,6.0, 0.0,0.0,6.0, 1.0,0.0,6.0, 1.0,1.0,6.0, 1.0,1.0,4.0, 1.0,0.0,4.0, 0.0,0.0,4.0, 0.0,1.0,4.0, 1.0,1.0,4.0, 2.0,0.0,3.0, 2.0,0.0,0.0, 4.0,2.0,0.0, 4.0,2.0,3.0, 2.0,0.0,3.0, 4.5,-2.0,3.0, 4.5,-2.0,0.0, 2.0,0.0,0.0, 2.0,0.0,3.0, 4.5,-2.0,3.0, 4.5,-2.0,3.0, -2.0,-2.0,3.0, -2.0,-2.0,0.0, 4.5,-2.0,0.0, 4.5,-2.0,3.0, -2.0,-2.0,3.0, -2.0,2.0,3.0, -2.0,2.0,0.0, -2.0,-2.0,0.0, -2.0,-2.0,3.0, 4.0,2.0,3.0, 4.0,2.0,0.0, -2.0,2.0,0.0, -2.0,2.0,3.0, 4.0,2.0,3.0, 2.0,0.0,3.0, 4.0,2.0,3.0, -2.0,2.0,3.0, -2.0,-2.0,3.0, 4.5,-2.0,3.0, 2.0,0.0,3.0, 2.0,0.0,0.0, 4.5,-2.0,0.0, -2.0,-2.0,0.0, -2.0,2.0,0.0, 4.0,2.0,0.0, 2.0,0.0,0.0 ))); -- SOLID with a hole -- etype = 1007 exterior solid -- etype = 2007 is interior solid -- All polygons of etype=2007 are described as 2003's. insert into polygons3d values(14, SDO_Geometry (3008,NULL,NULL , SDO_Elem_Info_Array(1,1007,1,1,1006,7,1,1003,1,16,1003,1,31,1003,1,46,1003,1,61,1003,1,76,1003,1,94,1003,1,112,2006,6,112,2003,1,127,2003,1,142,2003,1,157,2003,1,172,2003,1,187,2003,1), SDO_Ordinate_Array(2.0,0.0,3.0, 2.0,0.0,0.0, 4.0,2.0,0.0, 4.0,2.0,3.0, 2.0,0.0,3.0, 4.5,-2.0,3.0, 4.5,-2.0,0.0, 2.0,0.0,0.0, 2.0,0.0,3.0, 4.5,-2.0,3.0, 4.5,-2.0,3.0, -2.0,-2.0,3.0, -2.0,-2.0,0.0, 4.5,-2.0,0.0, 4.5,-2.0,3.0, -2.0,-2.0,3.0, -2.0,2.0,3.0, -2.0,2.0,0.0, -2.0,-2.0,0.0, -2.0,-2.0,3.0, 4.0,2.0,3.0, 4.0,2.0,0.0, -2.0,2.0,0.0, -2.0,2.0,3.0, 4.0,2.0,3.0, 2.0,0.0,3.0, 4.0,2.0,3.0, -2.0,2.0,3.0, -2.0,-2.0,3.0, 4.5,-2.0,3.0, 2.0,0.0,3.0, 2.0,0.0,0.0, 4.5,-2.0,0.0, -2.0,-2.0,0.0, -2.0,2.0,0.0, 4.0,2.0,0.0, 2.0,0.0,0.0, 1.0,1.0,2.5, -1.0,1.0,2.5, -1.0,1.0,0.5, 1.0,1.0,0.5, 1.0,1.0,2.5, -1.0,1.0,2.5, -1.0,-1.0,2.5, -1.0,-1.0,0.5, -1.0,1.0,0.5, -1.0,1.0,2.5, -1.0,-1.0,2.5, 1.0,-1.0,2.5, 1.0,-1.0,0.5, -1.0,-1.0,0.5, -1.0,-1.0,2.5, 1.0,-1.0,2.5, 1.0,1.0,2.5, 1.0,1.0,0.5, 1.0,-1.0,0.5, 1.0,-1.0,2.5, -1.0,-1.0,2.5, -1.0,1.0,2.5, 1.0,1.0,2.5, 1.0,-1.0,2.5, -1.0,-1.0,2.5, 1.0,1.0,0.5, -1.0,1.0,0.5, -1.0,-1.0,0.5, 1.0,-1.0,0.5, 1.0,1.0,0.5 ))); -- Gtype = SOLID -- The elements make up one composite solid (non-disjoint solids) like a cube -- on a cube on a cube. -- This is made up of two solid elements. -- Each solid element here is a simple solid. insert into polygons3d values(15, SDO_Geometry (3008,NULL,NULL , SDO_Elem_Info_Array(1,1008,2,1,1007,1,1,1006,6,1,1003,1,16,1003,1,31,1003,1,46,1003,1,61,1003,1,76,1003,1,91,1007,1,91,1006,7,91,1003,1,106,1003,1,121,1003,1,136,1003,1,151,1003,1,166,1003,1,184,1003,1), SDO_Ordinate_Array(-2.0,1.0,3.0, -2.0,1.0,0.0, -3.0,1.0,0.0, -3.0,1.0,3.0, -2.0,1.0,3.0, -3.0,1.0,3.0, -3.0,1.0,0.0, -3.0,-1.0,0.0, -3.0,-1.0,3.0, -3.0,1.0,3.0, -3.0,-1.0,3.0, -3.0,-1.0,0.0, -2.0,-1.0,0.0, -2.0,-1.0,3.0, -3.0,-1.0,3.0, -2.0,-1.0,3.0, -2.0,-1.0,0.0, -2.0,1.0,0.0, -2.0,1.0,3.0, -2.0,-1.0,3.0, -2.0,-1.0,3.0, -2.0,1.0,3.0, -3.0,1.0,3.0, -3.0,-1.0,3.0, -2.0,-1.0,3.0, -2.0,1.0,0.0, -2.0,-1.0,0.0, -3.0,-1.0,0.0, -3.0,1.0,0.0, -2.0,1.0,0.0, 2.0,0.0,3.0, 2.0,0.0,0.0, 4.0,2.0,0.0, 4.0,2.0,3.0, 2.0,0.0,3.0, 4.5,-2.0,3.0, 4.5,-2.0,0.0, 2.0,0.0,0.0, 2.0,0.0,3.0, 4.5,-2.0,3.0, 4.5,-2.0,3.0, -2.0,-2.0,3.0, -2.0,-2.0,0.0, 4.5,-2.0,0.0, 4.5,-2.0,3.0, -2.0,-2.0,3.0, -2.0,2.0,3.0, -2.0,2.0,0.0, -2.0,-2.0,0.0, -2.0,-2.0,3.0, 4.0,2.0,3.0, 4.0,2.0,0.0, -2.0,2.0,0.0, -2.0,2.0,3.0, 4.0,2.0,3.0, 2.0,0.0,3.0, 4.0,2.0,3.0, -2.0,2.0,3.0, -2.0,-2.0,3.0, 4.5,-2.0,3.0, 2.0,0.0,3.0, 2.0,0.0,0.0, 4.5,-2.0,0.0, -2.0,-2.0,0.0, -2.0,2.0,0.0, 4.0,2.0,0.0, 2.0,0.0,0.0 )));
例2-19 3次元ジオメトリのメタデータの更新と索引の作成
例2-19では、例2-18で作成した表(POINTS3D、LINES3DおよびPOLYGONS3D)に関する必要な情報を使用してUSER_SDO_GEOM_METADATAビューを更新し、各表のジオメトリ列(GEOMETRY)に空間索引を作成します。索引の作成にはPARAMETERS ('sdo_indx_dims=3')
句を使用し、3次元ジオメトリをサポートする演算に3つの次元すべてが使用されるようにします。
INSERT INTO user_sdo_geom_metadata VALUES('POINTS3D', 'GEOMETRY', sdo_dim_array( sdo_dim_element('X', -100,100, 0.000005), sdo_dim_element('Y', -100,100, 0.000005), sdo_dim_element('Z', -100,100, 0.000005)), NULL); CREATE INDEX points3d_sidx on points3d(geometry) INDEXTYPE IS mdsys.spatial_index PARAMETERS ('sdo_indx_dims=3'); INSERT INTO user_sdo_geom_metadata VALUES('LINES3D', 'GEOMETRY', sdo_dim_array( sdo_dim_element('X', -100,100, 0.000005), sdo_dim_element('Y', -100,100, 0.000005), sdo_dim_element('Z', -100,100, 0.000005)), NULL); CREATE INDEX lines3d_sidx on lines3d(geometry) INDEXTYPE IS mdsys.spatial_index PARAMETERS ('sdo_indx_dims=3'); INSERT INTO user_sdo_geom_metadata VALUES('POLYGONS3D', 'GEOMETRY', sdo_dim_array( sdo_dim_element('X', -100,100, 0.000005), sdo_dim_element('Y', -100,100, 0.000005), sdo_dim_element('Z', -100,100, 0.000005)), NULL); CREATE INDEX polygons3d_sidx on polygons3d(geometry) INDEXTYPE IS mdsys.spatial_index PARAMETERS ('sdo_indx_dims=3');