Spatial Overview
Oracle 9i Spatial에서 새롭게 추가된 특징은 Spatial 자체의 enhancement라기 보다는
Domain Index 자체의 enhancement에 기인한 바가 크다.
Oracle 9i Spatial은 데이터를 엑세스하고 다루는 데 있어서
domain index 타입인 spatial indextype을 항상 사용한다고 볼 수 있다.
크게 Oracle 9i Spatial에 새롭게 추가된 특징으로는 사용자가 aggregate 함수를 정의할 수 있고
range-partition 테이블에 각 partition에 spatial index를 생성할 수 있으며
function의 return값에도 spatial index를 생성할 수 있다.
이 장에서 지금부터 하나하나 자세히 살펴 보기로 한다.
Using Partitioned Spatial Indexes
지리 정보 데이터는 일반적으로 매우 커서 하나의 테이블에 몇 백만개의 Row가 존재하거나 하는 상황이 흔할 수 있다.
이러한 경우에 I/O의 로드 분산과 관리의 편의를 위하여 partitioning을 사용하는 경우가 많았다.
그러나 8i 이전 버전에서는 Spatial index의 경우에는 원래의 테이블이 partition되어 있는 경우에도
domain index는 partition되어 있지 않고 하나의 global domain index 만 가능하였으며 partitioning 기법을 사용할 수 없었다.
그러나 9i 부터는 spatial index에 대해서도 partitioning을 지원하는 것이 가능하여 local domain index를 만드는 것이 가능하다.
그러나 모든 경우에 partitioning이 이루어지는 것은 아니며 현재까지는 spatial column이 아닌 스칼라 칼럼에 대해서
range partitioned 된 테이블만 가능하며 hash parition 이나 list partition에 대해서는 지원되지 않는다.
Local domain index를 생성하는 것은 “Local” keyword를 통하여 이루어져서 아래와 같이 일반적인 local partitioned index 처럼
각각의 partition당 storage parameter를 다르게 설정하여 주는 것이 가능하다.
그러나 spatial parameter 에 대해서는 전체 테이블에 대해서 같은 값을 유지해야 한다.
| CREATE INDEX counties_idx on counties(geometry)
INDEXTYPE IS mdsys.spatial_index PARAMETERS (‘sdo_level = 6 tablespace = def_tbs’) LOCAL (PARTITION ip1 PARAMETERS(‘sdo_level = 6 tablespace=local_tbs1’), PARTITION ip2 PARAMETERS(‘sdo_level = 6 tablespace=local_tbs2’); |
Function Based Index
Oracle 9i에 새롭게 추가된 기능으로 function의 output에 domain index를 생성할 수 있는 기능이다.
이 기능을 사용하면 sdo_geometry 타입의 spatial 칼럼이 아닌 일반 relational 칼럼에 있는 데이터에 대해서 spatial index를 설정할 수 있게 된다.
예를 들면 특정 지점에 대한 위도와 경도가 relational column으로 존재하는 다음과 같은 테이블이 존재하고
이를 이용하여 point 타입의 sdo_geometry를 return하는 아래와 같은 함수가 있다고 하면 이 함수 자체에 spatial index를 생성할 수 있는 것이다.
| CREATE TABLE addr_geocode (
id NUMBER PRIMARY KEY, name VARCHAR2(30), longitude NUMBER, latitude NUMBER ) ;
CREATE FUNCTION get_long_lat_pt ( Longitude IN NUMBER, latitude IN NUMBER ) RETURN mdsys.sdo_geometry DETERMINISTIC IS BEGIN RETURN mdsys.sdo_geometry ( 2001, 8307, mdsys.sdo_point_type (longitude, latitude, NULL ), NULL, NULL ); END; |
함수의 return 값이 sdo_geometry이기만 하면 일반 데이터 테이블에 인덱스를 생성하는 것과 같은 방식으로 spatial index를 생성할 수 있게 된다.
| CREATE INDEX addr_geocode_idx on addr_geocode(get_long_lat_pt(longitude, latitude))
INDEXTYPE is mdsys.spatial_index; |
위와 같은 방법으로 생성된 spatial index에는 모든 spatial operator를 문제없이 사용할 수 있다.
Control-0
User defined Aggregate
Oracle 9i는 extensibility의 enhancement로 user defined aggregate fuction 함수를 만들 수 있게 되어 object 타입에 대해서도
sum이나 AVG와 비슷한 방식으로 동작하는 aggregation 함수를 만들 수 있게 되었다.
Oracle 9i Spatial은 이를 이용하여 group by 에 대해서 동작하는 aggregation 함수를 새로 제시하였다.
다음은 이번에 새롭게 등장한 aggregation 함수에 대한 소개이다.
| 함수 이름 | 대응 함수 | 설명 |
| SDO_AGGR_CENTROID | SDO_CENTROID | Group by된 geometries들의 무게중심점을 return한다. |
| SDO_AGGR_CONVEXHULL | SDO_CONVEXHULL | Group by 된 geometries들을 모두 감싸는 볼록 다각형을 return한다. |
| SDO_AGGR_LRS_CONCAT | SDO_LRS_CONCAT | Group by된 LRS geometries들의 모든 concatenation된 LRS geometries들을 return한다. |
| SDO_AGGR_MBR | SDO_MBR | Group by된 모든 geometries들을 감싸는 minimun bounding rectangle을 return한다. |
| SDO_AGGR_UNION | SDO_UNION | Group by된 모든 geometries들의 topological union을 구하여 이를 return한다. |
실제로 위의 함수들이 받아들이는 argument 타입은 mdsys.sdoaggrtype을 받아들인다.
그러나 이는 실제로 사용되는 타입이 아니며 spatial aggregation 함수 호출시 내부적으로 이 타입을 사용한다.
테스트
환경 설정
Spatial index를 생성하기 위해서는 해당 테이블과 테이블 안의 어떤 칼럼이 지리 정보를 담고 있는지에
대한 정보와 이 지리 데이터의 dimension 정보가 user_sdo_geom_metadata 테이블에 하나의 레코드로 들어가 있어야 한다.
User_sdo_geom_metadata에는 아래에서 보듯이 테이블 정보, 칼럼 정보, 그리고 Dimension 정보를 가지고 있다.
일반적으로 vector data는 값들의 쌍으로 구성되어 있고 이차원 정보를 나타내는 경우에는 두 값의 쌍으로 구성된다.
이 값들에 대한 정보가 dimenison 정보인데 아래의 예제에서는 ‘spatial_table’ 칼럼 안의 모든 vector 정보의
첫 번째 값은 ‘longitude’으로 지칭되며 이는 –180 부터 180까지의 값을 가질 수 있다는 의미이다.
그리고 두 번째 값도 ‘latitude’으로 지칭되며 같은 범위를 가진다. 여기에 3개의 dimension에 대한 정보를 입력하면
이는 3차원 vector 정보라는 것을 의미하나 아직 Oracle Spatial은 3차원을 지원하지 않는다.
| SQL> desc user_sdo_geom_metadata
Name Null? Type —————————————– ——– —————————- TABLE_NAME NOT NULL VARCHAR2(32) COLUMN_NAME NOT NULL VARCHAR2(500) DIMINFO MDSYS.SDO_DIM_ARRAY SRID NUMBER SQL> insert into user_sdo_geom_metadata values(‘spatial_table’, ‘spatial_data’, Mdsys.sdo_dim_array( Mdsys.sdo_dim_element(‘longtitude’, -180, 180, 0.005), Mdsys.sdo_dim_element(‘latitude’, -180, 180, 0.005)), 8307) ; |
샘플 프로그램
Using Partitioned Indexes
현재 spatial column을 기준으로 한 partitioning을 지원하지 않고 range partitioning만을 지원하기 때문에 partition 테이블을 만드는 방법과 같다.
이는 위의 예제에서 설명되었기에 여기에서는 생략한다. 실제로 수행을 해보기 위해서는
script 파일 cr_partbls.sql을 참조하기 바란다. 이 script를 수행하면 partition table이 생성되고 sql*loader를 이용하여 테이블로 data가 populate된다.
그리고 인덱스를 생성하면 partition의 수만큼 테이블이 생기는 것을 확인할 수 있다. 인덱스 생성 scirpt는 다음과 같다.
아래에서 local keyword가 local domain index를 생성하라고 하는 키워드이다.
| Create index spatial_partestx on spatial_partest(geoloc)
Indextype is mdsys.spatial_index Parameters(‘sdo_level=6’) Local (partition p parameters(‘sdo_level= 6 tablespace = partest_tbs’), partition p1 parameters(‘sdo_level=6 tablespace = isearch’), partition p2 parameters(‘sdo_level=6 tablespace = users’)); |
Spatial index의 경우에는 spatial index에 대한 데이터를 저장하기 위해서 인덱스가 생성될 때마다 이를 위한 새로운 테이블이 생성된다.
Partitioned된 경우에는 각 partition마다 local index가 생성되어 이를 spatial index의 metadata view인 user_sdo_index_info에서 확인할 수 있다.
| SQL> column index_name format a20
SQL> column table_name format a15 SQL> column sdo_index_table format a15 SQL> select index_name, table_name, sdo_index_table from user_sdo_index_info; INDEX_NAME TABLE_NAME SDO_INDEX_TABLE ——————– ————— ————— SPATIAL_PARTESTX SPATIAL_PARTEST MDRT_7D58$ SPATIAL_PARTESTX SPATIAL_PARTEST MDRT_7D5F$ SPATIAL_PARTESTX SPATIAL_PARTEST MDRT_7D66$ |
Spatial Table에 query하는 경우에 local domain index가 어떻게 사용되는지 확인해 보기 위해 trace를 수행하면 별다른 차이점이 없이
domain index를 사용한다는 정보만 나온다. 그리고 spatial index의 개별 partition table만 사용하는 지, 안 하는지를 확인하기 위하여
sql*trace를 수행하여도 아래와 같이 각각의 global index에 대한 정보만 나타날 뿐 local index에 대한 정보는 나타나지 않는다.
이는 spatial domain index에 대해서는 sql*trace가 상세한 정보를 나타내지 않기 때문이다. 현재로서는 각각 개별 index table이 개별적으로 사용되는 지를 알 수 있는 정보는 없다.
| SQL> select count(*) from spatial_partest partition(p) a, spatial_partest partition (p) b,
User_sdo_geom_metadata m Where sdo_geom.relate(a.geoloc,m.diminfo,’ANYINTERACT’, b.geoloc, m.diminfo) = ‘TRUE’ and b.prinx = 3 and m.table_name = ‘SPATIAL_PARTEST’ and m.column_name = ‘GEOLOC => query 수행 후 execution plan의 결과 Rows Execution Plan ——- ————————————————— 0 SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) 4 MERGE JOIN (CARTESIAN) 5 NESTED LOOPS 2 NESTED LOOPS 2 TABLE ACCESS (BY INDEX ROWID) OF ‘SDO_GEOM_METADATA_TABLE’ 2 INDEX (RANGE SCAN) OF ‘SDO_GEOM_IDX’ (NON-UNIQUE) 2 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF ‘SPATIAL_PARTEST’ PARTITION: START=1 STOP=1 2 INDEX (UNIQUE SCAN) OF ‘SYS_C003917’ (UNIQUE) 5 TABLE ACCESS (FULL) OF ‘SPATIAL_PARTEST’ PARTITION: START= 1 STOP=1 4 BUFFER (SORT) 1 TABLE ACCESS (FULL) OF ‘DUAL’ |
Note : 테이블 생성 및 data population script : cr_partbls.sql
인덱스 생성 script : cr_parindex.sql
Function based Index
Function Based Index를 생성하기 위해서는 사용자에게 “Query Rewrite” 권한이 주어져야 한다.
| 사용자 : mdtest
SQL > conn system/manager SQL> grant query rewrite to mdtest; |
위의 SQL 문을 수행하여 환경을 설정해 준 후의 테스트 시나리오는 매우 간단하다.
먼저 테스트를 위한 테이블을 생성한다. 이 테이블은 특정 지점의 이름과 함께 고도, 경도를 column으로 가지고 있다. 이 테이블에 대해서
수행할 함수를 작성하고 user_sdo_geom_metadata에 이 테이블에 대한 record를 입력한다.
이 경우에는 function-based index이므로 function에 대한 정보가 칼럼 이름 대신 입력된다.
| create table test_long_lat (
id number primary key, name varchar2(20), longtitude number, latitude number ); — data population — — ….. — –….. — insert into user_sdo_geom_metadata values(‘TEST_LONG_LAT’, ‘get_long_lat_pt(longtitude, latitude)’, mdsys.sdo_dim_array( mdsys.sdo_dim_element(‘Longtitude’, -180, 180, 0.005), mdsys.sdo_dim_element(‘Latitude’, -90, 90, 0.005)), 8307); |
그리고 함수를 생성한 후 인덱스를 생성하면 된다.
| create or replace function get_long_lat_pt (
longtitude in number, latitude in number ) return mdsys.sdo_geometry deterministic is begin return mdsys.sdo_geometry( 2001, 8307, mdsys.sdo_point_type(longTitude, latitude, NULL), NULL, NULL); end; / create index test_long_lat_idx on test_long_lat(get_long_lat_pt(longtitude, latitude)) indextype is mdsys.spatial_index; |
인덱스가 생성되면 기존의 GIS query와 같은 방식으로 query를 수행할 수 있다. 이는 attach된 script를 참조하도록 한다.
Note :테이블 생성 및 data population 및 metadata 입력 script : func_tbls.sql
함수 생성 및 인덱스 생성 scirpt : func_index.sql
query script : query1.sql, query2.sql
User Aggregate Function
Oracle 9i Spatial이 제공하는 user aggregate function은 크게 두 가지 타입으로 나눌 수 있다.
하나는 input 으로 mdsys.sdo_geometry를 받아들이는 sdo_aggr_mbr과 다른 하나는 input으로 mdsys.sdoaggrtype을 받아 들이는 나머지 함수들이다. [1]
| CREATE TYPE sdoaggrtype AS OBJECT (
geometry mdsys.sdo_geometry, diminfo mdsys.sdo_dim_array) ; |
sdoaggrtype은 이와 같아서 함수 호출시 이러한 타입을 생성하여 input으로 전달하여야 한다.
sdoaggrtype은 함수의 argument로만 사용되어야 하며 테이블의 칼럼으로는 사용되지 못한다. 다음은 이를 사용하는 sdo_aggr_union 함수의 예이다.
| SELECT SDO_AGGR_MBR(MDSYS.SDOAGGRTYPE( c.geoloc,m.diminfo)), c.loc
FROM sd401 c, user_sdo_geom_metadata m WHERE m.table_name=’SD401′ AND m.column_name=’GEOLOC’ GROUP BY c.loc ; |
mdsys.sdo_geometry를 input으로 받아들이는 sdo_aggr_mbr은 훨씬 쉽게 사용될 수 있다.
단순히 input으로 주기만 하면 된다. 다음은 이 함수의 사용예와 return 결과이다.
| SQL> select sdo_aggr_mbr(loc) from spatial_test ;
SDO_AGGR_MBR(LOC)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES_ARRAY) ——————————————————————————– SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARRAY(10, 10, 20, 20)) |
활용가이드 및 결론
Oracle 9i에서는 좀 더 유용한 spatial의 함수들과 기능들이 추가되었다.
8i 보다는 좀더 편리한 기능들이 제공되어 사용이 편리해지고 좀 더 효과적으로 사용될 수 있는 방법들이 제공되어 졌다고 볼 수 있다.
특히 기존의 데이터가 spatial data가 아닌 경우 이제까지는 이 모든 데이터들을 다 mdsys.sdo-geometry로 변환시키는 작업이 필요했으나
이제는 function based index를 사용함으로써 converting 작업이 필요 없어져서 이러한 application이 요구되는 경우에 편리하게 사용될 수 있다고 본다.
[1] 개별 함수에 대한 설명은 위의 overview에 있으므로 이를 참조하기 바란다.