プライマリ・コンテンツに移動
Oracle® Spatial and Graph開発者ガイド
12cリリース1 (12.1)
B72470-07
目次へ移動
目次 		索引へ移動
索引
前
前 		次
次

2.7.10 3次元のジオメトリ・タイプ

例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');
前
前 		次
次
目次へ移動
目次 		索引へ移動
索引