Oracle Spatial Insert, Spatial Query for 3D Point

3D의 Single Point와 Multi Point의 삽입 및 공간 질의.

--drop table test;

create table test

(

  location number(10), 

  centerPoint sdo_geometry

);

--drop index idx_test_location;

create index idx_test_location on test(location);

--delete from user_sdo_geom_metadata where table_name ='TEST'; -- 삭제할 때, 테이블 이름을 대문자로 적어야함.

insert into user_sdo_geom_metadata values ('test' , 'centerPoint' , 

  sdo_dim_array

  (

    sdo_dim_element('X', 0, 20, 0.005),

    sdo_dim_element('Y', 0, 20, 0.005),

    sdo_dim_element('Z', 0, 20, 0.005)

  )

  ,null

)

--drop index idx_test_centerPoint;

create index idx_test_centerPoint on test(centerPoint) indextype is mdsys.spatial_index parameters('sdo_indx_dims=3');

-- 3D Single point

insert into test values(0001 , SDO_GEOMETRY(3001, NULL, SDO_POINT_TYPE(0 , 0, 0), NULL, NULL) );

-- 3D multi point

insert into test values(0002 , SDO_GEOMETRY(3005, NULL, NULL , 

    sdo_elem_info_array(1,1,1, 4,1,1), -- 4,1,0 으로 했을 때는 방향성을 나타내는 것(orientation vectors)

    sdo_ordinate_array(0,0,0, 5,5,5)

  )

);

select centerPoint as "CENTERPOINT" from test where location = 0001;

SELECT location FROM test c 

WHERE SDO_ANYINTERACT (c.centerpoint , SDO_GEOMETRY(3001, NULL, SDO_POINT_TYPE(0,0,0), NULL, NULL))='TRUE';