꿈을 향하여 질주하기

  공간 연산 함수를 이해하기에 앞서 아래 포스트를 먼저 참고하실 것을 권장드립니다.

  공간 연산 함수는 두 공간 객체의 연산 결과로 새로운 공간 객체를 반환해주는 함수입니다.

  MySQL에서 제공해주는 공간 연산 함수 중에서 자주 사용되는 함수는 아래와 같습니다

  MySQL에서 공간 연산 함수의 테스트를 위해 아래 테스트 데이터를 사용자 변수에 저장하도록 하겠습니다.

SET @p0 = POINT(0,0);

SET @p1 = POINT(1,1);

SET @p2 = POINT(2,2);

SET @l1 = ST_GeomFromText('LineString(1 1, 3 3, 5 5, 10 10, 14 14)');

SET @l2 = ST_GeomFromText('LineString(2 2, 8 8)');

SET @b1 = ST_GeomFromText('Polygon((1 1,1 10,10 10,10 1,1 1))');

  위에서 정의한 사용자 변수를 가지고 아래와 같이 공간 연산 함수를 테스트해보았습니다.

SELECT ST_AsText(ST_Intersection(@p0, @p1)), ST_AsText(ST_Intersection(@b1, @p2));

SELECT ST_AsText(ST_Union(@p0, @p1)), ST_AsText(ST_Union(@l1, @p1));

SELECT ST_AsText(ST_Difference(@p0, @p1)), ST_AsText(ST_Difference(@l1, @p2));

SELECT ST_AsText(ST_Buffer(@b1,0));

SELECT ST_AsText(ST_Envelope(@l2));

SELECT ST_AsText(ST_StartPoint(@l1));

SELECT ST_AsText(ST_EndPoint(@l1));

SELECT ST_AsText(ST_PointN(@l1,3));

  테스트 결과 화면은 아래와 같습니다.

  이전 포스팅에서는 공간 데이터 타입에 대해 알아봤습니다.

  이제 공간 데이터 타입으로 저장된 공간 데이터를 이용해 연산을 할 수 있는 공간 함수에 대해 알아보겠습니다.

  공간 함수는 공간 관계 함수와 공간 연산 함수로 구분 질 수 있는데, 이 중에서 공간 관계 함수는 두 공간 객체 간의 관계를 일반 데이터 타입(Boolean 또는 숫자)으로 반환해주는 함수입니다.

  MySQL에서 제공해주는 공간 관계 함수 중에서 자주 사용되는 함수는 아래와 같습니다.

  공간 관계 함수의 테스트를 위해 아래 테스트 데이터를 사용자 변수에 저장하도록 하겠습니다.

SET @p0 = POINT(0,0);

SET @p1 = POINT(1,1);

SET @p2 = POINT(2,2);

SET @l1 = ST_GeomFromText('LineString(1 1, 3 3)');

SET @l2 = ST_GeomFromText('LineString(2 2, 4 4)');

SET @b1 = ST_GeomFromText('Polygon((1 1,1 10,10 10,10 1,1 1))');

  위에서 정의한 사용자 변수를 가지고 아래와 같이 공간 관계 함수를 테스트해보았습니다.

SELECT ST_Equals(@p1, @p1), ST_Equals(@p1, @l1);

SELECT ST_Disjoint(@b1, @p0), ST_Disjoint(@b1, @p2);

SELECT ST_Within(@p2, @b1), ST_Within(@b1, @p2);

SELECT Overlaps(@l1, @l2), Overlaps(@l1, @p1);

SELECT ST_Intersects(@b1, @p2), ST_Intersects(@b1, @p1);

SELECT ST_Contains(@b1, @p2), ST_Contains(@p2, @b1);

SELECT ST_Touches(@b1, @p1), ST_Touches(@b1, @p2);

SELECT ST_Distance(@p1, @p2);

  테스트 결과 화면은 아래와 같습니다.

이어서, 다음 포스트에서는 공간 연산 함수에 대해 설명드리도록 하겠습니다.

  이전 포스팅에서 공간 데이터베이스의 필요성에 대해 RDBMS와 비교 설명을 해보았습니다.

  그럼, 본격적으로 공간 데이터베이스에 대해 알아보겠습니다.

  공간 데이터베이스. 

  쉽게 설명하면 X, Y 좌표로 구성된 공간 데이터를 저장하고 연산할 수 있는 기능을 제공해주는 데이터베이스라고 생각하시면 됩니다. 다른 말로, 공간 데이터의 저장 형태를 구분하는 공간 데이터 타입과 공간 데이터를 연산할 수 있는 공간 함수가 제공되는 데이터베이스라고 표현 가능합니다.

  우리가 일반적으로 사용하고 있는 RDBMS인 Oracle, MariaDB, MySQL, PostgreSQL 등에서 공간 데이터를 처리하기 위한 기능을 제공하고 있습니다. 이 솔루션을 이용해 공간 데이터베이스를 생성/관리할 수 있는 겁니다. 심지어 Hive에서도 관련 Library를 설치하면 공간 데이터를 저장하고 공간 연산이 가능합니다.

  그럼, 공간 데이터 타입과 공간 함수를 알면 공간 데이터베이스를 정복하게 되는걸까요? 저는 그렇다고 생각합니다. 가장 기본적인 공간 데이터 타입과 공간 함수를 알면 쉽게 공간 데이터를 처리할 수 있으니까요. 가장 기본적인 기초만 알면 그 후의 응용문제는 쉽게 풀어갈 수 있다고 생각합니다.

  참고로, 공간 데이터 타입과 함수는 Open Geospatial Consortium(OGC, www.ogc.org)에서 표준을 정의하고 있습니다. 아래 링크를 클릭하시면 표준 문서를 다운로드 하실 수 있습니다.
   > 표준 문서 다운로드 : http://portal.opengeospatial.org/files/?artifact_id=25354

  이제 공간 데이터베이스의 기초인 공간 데이터 타입에 대해 알아보겠습니다. 여기서는 MySQL에서 제공하는 공간 데이터 타입을 기준으로 설명을 진행할 것입니다.

  자주 사용되는 공간 데이터 타입은 아래와 같습니다.

  MySQL에서 공간 데이터 타입의 컬럼을 추가하고, 공간 데이터를 입력해보도록 하겠습니다.

CREATE TABLE GEO_TAB
( 
  GID  INT(10),
  PT   POINT,
  LS   LINESTRING
);

  위 SQL은 테스트용 테이블 GEO_TAB을 생성하는 DDL문입니다. GEO_TAB 테이블은 일반 INT형 GID 컬럼, POINT 공간데이터 타입인 PT 컬럼과 LINESTRING 공간데이터 타입인 LS 컬럼으로 구성됩니다.

  이제 위에서 생성한 GEO_TAB 테이블에 데이터를 입력해보도록 하겠습니다. 

SET @g1 = 'POINT(10 10)';
SET @g2 = GeomFromText( 'LINESTRING(5 5, 10 5, 15 10)' );
INSERT INTO GEO_TAB VALUES ( 1, GeomFromText(@g1), @g2 );

  데이터 입력 예제의 가독성을 위해 사용자 변수를 사용하여 데이터를 입력해보았습니다.
  사용자변수 g1에는 POINT형 공간 데이터를 입력하기 위한 공간 데이터 문자열을 저장합니다. 사용자 변수 g2에는 LINESTRING형 공간 데이터를 저장합니다.
  이 사용자 변수의 값을 GEO_TAB 테이블에 입력을하는데 공간 데이터의 입력도 일반 RDBMS처럼 INSERT문을 사용합니다.
  이 때, 공간 데이터 타입은 Binary 형태로 저장되기 때문에 문자열을 저장한 사용자 변수 g1은 공간 데이터 타입으로 변환해주는 GeomFromText 함수를 사용하여 Binary로 변환 후 저장해야 합니다.
(* GeomFromText 함수는 MySQl 8.0에서 Remove되었으며, ST_GeomFromText 함수를 사용하면 됩니다. )

  공간 데이터를 조회하기 위해서는 Binary 데이터를 텍스트로 변환하는 AsText함수를 사용하면 됩니다.
(* AsText 함수는 MySQl 8.0에서 Remove되었으며, ST_AsText 함수를 사용하면 됩니다. )

SELECT GID, AsText(PT), AsText(LS) FROM GEO_TAB; 

  아래 그림은 MySQL에서 테스트한 결과 화면입니다.

  이어서, 아래 포스트에서 공간 관계 함수와 공간 연산 함수에 대해 설명드리도록 하겠습니다.

  혹시 아래 나열된 서비스나 시스템을 사용해보신 적이 있으신가요?

버스정보시스템(버스 실시간 도착 알림)
차량 네비게이션 시스템
지하철 앱
지도 앱
차량 호출 서비스
가까운 상점 찾기
지역 쿠폰 수신
SNS 등록(장소 정보 입력)
...

  위 서비스/시스템을 한 번이라도 사용하신 적이 있다면 직/간접적으로 공간 데이터와 조우하신 겁니다.

  공간 데이터란 말 그대로 이 세상에 존재하는 위치 및 영역을 X, Y 좌표로 표현한 데이터입니다.
  예를 들어, 내가 지금 서 있는 위치(점), 지하철 노선(선), 건물 영역(다각형) 등을 X, Y 좌표(예: 경도, 위도)로 표현할 수 있습니다. 이러한 공간 데이터를 활용하여 위와 같은 위치 기반 서비스가 제공될 수 있는 겁니다.

  스마트 폰 없이는 살 수 없는 시대를 살아가고 있는 우리는 매일 공간 데이터를 소비하고 생산하며 살아가고 있습니다. 매일매일 생산되고 있는 대용량의 공간 데이터. 생산되는 데이터의 양이 증가될수록 데이터를 관리하는 기술 또한 발전이 필요합니다. 공간 데이터도 마찬가지입니다. 일반적인 데이터(숫자, 문자, 날짜)와는 표현 방식이나 연산 방식이 상이하기 때문에 별도의 저장/관리 기술이 필요로 합니다.

  우선, 일반 RDBMS에서 공간 데이터를 저장하고 연산하는 방법을 살펴보도록 하겠습니다.

  선이나 다각형 데이터는 연속된 점 좌표로 구성됩니다. 이러한 다수의 점 좌표를 일반 RDBMS에서 저장하는 방법은 두 가지가 존재합니다.
  1) 좌표 엔터티를 별도로 생성하고, 하나의 좌표를 하나의 로우로 저장. 좌표를 구별할 수 있는 별도의 식별자가 필요함.
  2) 점 좌표 구분자를 사용하여 하나의 문자열로 저장

  아래 ERD는 상점 정보와 상점이 위치한 건물의 정보를 저장하기 위한 엔터티를 보여주고 있습니다.
  왼쪽은 1) 번 방식으로 설계한 ERD이며, 오른쪽은 2) 번 방식으로 설계한 ERD입니다.

  1)번 방식으로 설계한 엔터티 중에서 좌표 값을 저장하는 건물영역좌표에는 아래와 같이 좌표 정보가 저장될 것입니다.  

  위와 같이 좌표 값을 저장하기 위한 하위 엔터티를 생성하는 것은 추가적인 저장 공간을 필요로 합니다. 그리고 하나의 건물영역좌표가 다수의 로우로 표현되기에 다수의 블록에 분산되어 저장될 가능성이 높습니다. 이는 관련 테이블 액세스 시 블록 액세스가 증가되어 성능 부하가 존재할 수 있게 됩니다.

  2) 번 방식으로 설계한 엔터티 중에서 좌표 값을 저장하는 건물에는 아래와 같이 좌표 정보가 저장될 것입니다. 연속된 점 좌표를 콤마(,)를 이용하여 구분해 공간 데이터를 저장하고 있습니다.

  위 데이터를 이용해 공간 연산을 하기 위해서는 문자열 형태(x1 y1, x2 y2, … xn yn)인 좌표 값을 점 좌표(x, y)로 변환하는 작업이 선행되어야 합니다.

  아래 사용자 위치에서 가까운 순서대로＇GAS STATION＇목록을 조회하는 SQL (Oracle)을 살펴보겠습니다. 

WITH BLDS AS (
     SELECT S.STORE_ID
          , S.STORE_NM
          , B.BLD_ID
          , B.BLD_ADDR
          , B.BLD_AREA_CRD
       FROM STORE S
          , BLD B
      WHERE S.STORE_TYPE = 'GAS STATION'
        AND S.BLD_ID = B.BLD_ID
)
SELECT STORE_ID
     , STORE_NM
     , BLD_ID
     , BLD_ADDR
     , DIST 
  FROM (
        SELECT STORE_ID
             , STORE_NM
             , BLD_ID
             , BLD_ADDR
             , DIST
             , ROW_NUMBER() OVER(PARTITION BY BLD_ID ORDER BY DIST) CRD_RN
          FROM (
                SELECT STORE_ID
                     , STORE_NM
                     , BLD_ID
                     , BLD_ADDR
                     , SQRT(ABS(POWER(REGEXP_SUBSTR(BLD_CRD, '[^ ]+', 1, 1) - :USER_X_CRD, 2))+ ABS(POWER(REGEXP_SUBSTR(BLD_CRD, '[^ ]+', 1, 2) - :USER_Y_CRD, 2))) AS DIST
                  FROM (
                        SELECT STORE_ID
                             , STORE_NM
                             , BLD_ID
                             , BLD_ADDR
                             , BLD_CRD_SEQ
                             , REGEXP_SUBSTR(BLD_AREA_CRD, '[^,]+', 1, BLD_CRD_SEQ) AS BLD_CRD
                          FROM BLDS
                             , (SELECT LEVEL AS BLD_CRD_SEQ
                                  FROM DUAL
                               CONNECT BY LEVEL <= ( SELECT MAX( LENGTH(BLD_AREA_CRD) - LENGTH(REPLACE(BLD_AREA_CRD, ',', ''))) + 1 FROM BLDS ) ) L
                     ) A
                 WHERE BLD_CRD IS NOT NULL
             ) B
     ) C
 WHERE C.CRD_RN = 1
 ORDER BY DIST;

  건물영역좌표(BLD_AREA_CRD)를 다수의 로우로 변경하기 위해 CONNECT BY LEVEL을 사용해 카테시안조인을 하였습니다. 그리고 점 좌표를 콤마(,) 기준으로 분리하기 위해 정규식인 REGEXP_SUBSTR 함수를 사용하였습니다.
  위와 같은 방식으로 쿼리를 작성하다 보니 가독성이 떨어지고 공간 연산을 위한 수식이 복잡해집니다.

  그럼 공간 데이터베이스에서 공간 데이터를 저장하고 연산하는 방법을 살펴보겠습니다.

  아래 ERD는 건물영역의 좌표 값을 POLYGON 데이터 타입인 건물Polygon 속성에 저장하는 건물 엔터티를 보여주고 있습니다.

  건물 엔터티에는 아래와 같이 데이터가 저장될 것입니다.

  그리고 두 공간 객체 간의 거리를 계산하는 ST_DISTANCE 함수를 사용하여, 사용자 위치에서 가까운 순서대로＇GAS STATION＇목록을 조회하는 SQL (MySQL)을 살펴보겠습니다. 

SELECT S.STORE_ID
     , S.STORE_NM
     , B.BLD_ID
     , B.BLD_ADDR
     , B.BLD_POLYGON
     , ST_DISTANCE(B.BLD_POLYGON, POINT(@USER_X_CRD, @USER_Y_CRD) ) DIST
  FROM STORE S
     , BLD B
 WHERE S.STORE_TYPE = 'GAS STATION'
   AND S.BLD_ID = B.BLD_ID
 ORDER BY DIST;

  일반 RDBMS에서의 예와 비교하여 쿼리가 단순해지고 가독성이 좋아진 것을 확인하실 수 있습니다. 또한, 다양한 공간 함수를 제공하여 복잡한 연산도 쉽게 처리가 가능합니다.