오라클 DB 의 Spatial ( 스파샬 )


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에 있으므로 이를 참조하기 바란다.


Comments

comments

haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

오라클 DB 의 Spatial ( 스파샬 )”의 21개의 댓글

  • 2018년 10월 18일 11:03 오후
    Permalink

    I don’t know if it’s just me or if perhaps everyone else encountering issues
    with your website. It appears as if some of the written text in your posts are
    running off the screen. Can someone else please comment and let me know if
    this is happening to them as well? This may be a
    problem with my browser because I’ve had this happen before.
    Cheers

    댓글달기
  • 2018년 10월 20일 5:21 오전
    Permalink

    It is actually a great and helpful piece of info. I’m satisfied that you shared this useful information with us.
    Please stay us up to date like this. Thank you for sharing.

    댓글달기
  • 2018년 11월 19일 7:09 오후
    Permalink

    I delight in, result in I discovered exactly what I was looking for.
    You have ended my four day long hunt! God Bless you man. Have a great day.
    Bye

    댓글달기
  • 2018년 11월 20일 2:56 오후
    Permalink

    I feel that is one of the such a lot vital information for
    me. And i’m satisfied reading your article. But want to observation on some basic things, The
    website taste is great, the articles is in reality great : D.
    Excellent task, cheers

    댓글달기
  • 2018년 11월 24일 11:17 오전
    Permalink

    Asking questions are really fastidious thing if you are not understanding anything
    totally, but this paragraph presents nice understanding yet.

    댓글달기
  • 2018년 12월 10일 2:41 오후
    Permalink

    Remarkable! Its actually amazing piece of
    writing, I have got much clear idea concerning from this paragraph.

    댓글달기
  • 2018년 12월 11일 2:11 오후
    Permalink

    Excellent post! We are linking to this great post on our
    site. Keep up the good writing.

    댓글달기
  • 2018년 12월 11일 5:50 오후
    Permalink

    Does your blog have a contact page? I’m having problems locating it but, I’d like to send you an email.
    I’ve got some recommendations for your blog you might
    be interested in hearing. Either way, great site and I look forward to seeing it expand over time.

    댓글달기
  • 2019년 8월 2일 7:02 오전
    Permalink

    Link exchange is nothing else except it is only placing the other person’s webpage link on your page at proper place and other person will also do same in favor of you.

    댓글달기
  • 2019년 8월 9일 8:18 오후
    Permalink

    Hey I know this is off topic but I was wondering if you knew of any widgets I could add to my blog that automatically tweet my newest twitter updates. I’ve been looking for a plug-in like this for quite some time and was hoping maybe you would have some experience with something like this. Please let me know if you run into anything. I truly enjoy reading your blog and I look forward to your new updates.

    댓글달기
  • 2019년 8월 10일 2:48 오전
    Permalink

    Good day I am so grateful I found your weblog, I really found you by mistake, while I was searching on Askjeeve for something else, Anyhow I am here now and would just like to say thank you for a incredible post and a all round enjoyable blog (I also love the theme/design), I don抰 have time to read it all at the minute but I have book-marked it and also added in your RSS feeds, so when I have time I will be back to read a lot more, Please do keep up the great b.

    댓글달기
  • 2019년 8월 10일 4:37 오후
    Permalink

    What i don’t realize is if truth be told how you’re now not actually much more smartly-preferred than you may be now. You’re so intelligent. You realize therefore significantly relating to this subject, made me individually imagine it from a lot of numerous angles. Its like women and men aren’t involved except it is one thing to do with Lady gaga! Your own stuffs nice. At all times handle it up!

    댓글달기
  • 2019년 8월 10일 4:57 오후
    Permalink

    Good day! Do you know if they make any plugins to safeguard against hackers? I’m kinda paranoid about losing everything I’ve worked hard on. Any tips?

    댓글달기
  • 2019년 9월 30일 5:43 오후
    Permalink

    Great article, this post is more approachable and innovative. When I read this stuff, I also appreciate it.

    댓글달기
  • 2019년 9월 30일 5:44 오후
    Permalink

    I also hope you will make more and more excellent post and let’s more and more talk, thank you very much, dear.

    댓글달기
  • 2019년 9월 30일 5:46 오후
    Permalink

    Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! It’s always nice when you can not only be informed, but also entertained!

    댓글달기
  • 2019년 9월 30일 5:47 오후
    Permalink

    Thanks for sharing. I hope it will be helpful for too many people that are searching for this topic.

    댓글달기

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다