로컬 베이스, 지역기반 프로그램을 하다 보면 공간 데이터 베이스에 대한 개념이 필요하다. 여러모로 필요해서 정리해 보았다.
공간 데이터베이스 & 함수 in MariaDB (혹은 MySQL)
공간 함수는 공간 관계 함수와 공간 연산 함수로 구분 질 수 있다.
두 함수를 알아 보기 전 공간 Data Type을 먼저 알아본다. MySQL에는 문자형, 숫자 형, 날짜 형 외에 Geometry(공간 형)이 있다.
공간 데이터 타입 ( Spatial Data Type )
쉽게 설명하면 X, Y 좌표로 구성된 공간 데이터를 저장하고 연산할 수 있는 기능을 제공해주는 데이터베이스. 다른 말로, 공간 데이터의 저장 형태를 구분하는 공간 데이터 타입과 공간 데이터를 연산할 수 있는 공간 함수가 제공되는 데이터베이스라고 표현 가능.
우리가 일반적으로 사용하고 있는 RDBMS인 Oracle, MariaDB, MySQL, PostgreSQL 등에서 공간 데이터를 처리하기 위한 기능을 제공한다. 이 솔루션을 이용해 공간 데이터베이스를 생성/관리할 수 있다.
참고로, 공간 데이터 타입과 함수는 Open Geospatial Consortium(OGC, www.ogc.org)에서 표준을 정의하고 있다. 아래 링크를 클릭하시면 표준 문서를 다운로드 할 수 있다.
> 표준 문서 다운로드 : http://portal.opengeospatial.org/files/?artifact_id=25354
이 문서를 언젠가는 완독 하자 !!! (영어라 힘들겠지만…….)
이제 공간 데이터베이스의 기초인 공간 데이터 타입에 대해 알아보자. 여기서는 MySQL에서 제공하는 공간 데이터 타입을 기준으로 설명한다. 우리는 MySQL혹은 MariaDB위주로 사용할거니깐…………
자주 사용되는 공간 데이터 타입은 아래와 같다.
| 공간데이터타입 | 정의 | SQL 예 | |
| 1 | Point | 좌표 공간에서 한 지점의 위치를 표시 | POINT (10 10) |
| 2 | LineString | 다수의 Point를 연결해주는 선분 | LINESTRING (10 10, 20 25, 15 40) |
| 3 | Polygon | 다수의 선분들이 연결되어 닫혀 있는 상태인 다각형 | POLYGON ((10 10, 10 20, 20 20, 20 10, 10 10)) |
| 4 | Multi-Point | 다수 개의 Point 집합 | MULTIPOINT (10 10, 30 20) |
| 5 | Multi-LineString | 다수 개의 LineString 집합 | MULTILINESTRING ((10 10, 20 20), (20 15, 30 40)) |
| 6 | Multi-Polygon | 다수 개의 Polygon 집합 | MULTIPOLYGON ((( 10 10, 15 10, 20 15, 20 25, 15 20, 10 10 )) , (( 40 25, 50 40, 35 35, 25 10, 40 25 )) ) |
| 7 | GeomCollection | 모든 공간 데이터들의 집합 | GEOMETRYCOLLECTION ( POINT (10 10), LINESTRING (20 20, 30 40), POINT (30 15) ) |
공간데이터타입 - TEST
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;공간 연산 함수 ( Spatial Operator Functions )
공간 연산 함수는 두 공간 객체의 연산 결과로 새로운 공간 객체를 반환해주는 함수이다.
MySQL에서 제공해주는 공간 연산 함수 중 우리가 필요할 함수는 아래와 같다
| 공간 연산 함수 | 설명 | |
| 1 | ST_Intersection (g1 Geometry, g2 Geometry) : Geometry | g1과 g2의 교집합인 공간 객체를 반환 |
| 2 | ST_Union (g1 Geometry, g2 Geometry) : Geometry | g1과 g2의 합집합인 공간 객체를 반환 |
| 3 | ST_Difference (g1 Geometry, g2 Geometry) : Geometry | g1과 g2의 차집합인 공간 객체를 반환 |
| 4 | ST_Buffer (g1 Geometry, d Double ) : Geometry | g1에서 d 거리만큼 확장된 공간 객체를 반환 |
| 5 | ST_Envelope (g1 Geometry) : Polygon | g1을 포함하는 최소 MBR인 Polygon을 반환 |
| 6 | ST_StartPoint (l1 LineString) : Point | l1의 첫 번째 Point를 반환 |
| 7 | ST_EndPoint (l1 LineString) : Point | l1의 마지막 Point를 반환 |
| 8 | ST_PointN (l1 LineString) : Point | l1의 n 번째 Point를 반환 |
공간연산함수 - TEST
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))');
SET @b2 = ST_GeomFromText('Polygon((8 4,8 6,12 6,12 4,8 4))');
위에서 정의한 사용자 변수를 가지고 아래와 같이 공간 연산 함수를 테스트해본다.
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));
온닫이 플랫폼에서 사용할 것 같은 응용 테스트
#b1폴리곤과 b2폴리곤의 겹치는 영역 추출
SELECT ST_ASTEXT(ST_Intersection(@b1, @b2)) AS intersectionPolygon;
#b1폴리곤과 b2폴리곤의 겹치는 영역에 대한 면적을 계산
SELECT (ST_AREA(ST_Intersection(@b1, @b2))) AS intersectionArea;
공간 관계 함수 ( Spatial Relation Functions )
이제 공간 데이터 타입으로 저장된 공간 데이터를 이용해 연산을 할 수 있는 공간 함수에 대해 알아본다.
공간 관계 함수는 두 공간 객체 간의 관계를 일반 데이터 타입(Boolean 또는 숫자)으로 반환해주는 함수다.
MySQL에서 제공해주는 공간 관계 함수 중에서 자주 사용되는 함수는 아래와 같다.
| 공간 관계 함수 | 설명 | |
| 1 | ST_Equals (g1 Geometry, g2 Geometry) : Boolean |
g1과 g2가 동일하면 True를 반환하고 상이하다면 False를 반환 |
| 2 | ST_Disjoint (g1 Geometry, g2 Geometry) : Boolean |
g1과 g2가 겹치는 곳 없다면 True를 반환하고, 겹치는 곳이 있으면 False를 반환 |
| 3 | ST_Within (g1 Geometry, g2 Geometry) : Boolean |
g1이 g2 영역 안에 포함된 경우 True를 반환하고 그렇지 않은 경우 False를 반환 |
| 4 | ST_Overlaps (g1 Geometry, g2 Geometry) : Boolean |
g1과 g2 영역 중 교집합 영역이 존재하는 경우 True를 반환하고 존재하지 않는 경우 False를 반환 |
| 5 | ST_Intersects (g1 Geometry, g2 Geometry) : Boolean |
g1과 g2 영역 간에 교집합이 존재하는 경우 True를 반환하고 그렇지 않은 경우 False를 반환 |
| 6 | ST_Contains (g1 Geometry, g2 Geometry) : Boolean |
g2가 g1 영역 안에 포함된 경우 True를 반환하고 그렇지 않은 경우 False를 반환 |
| 7 | ST_Touches (g1 Geometry, g2 Geometry) : Boolean |
g1과 g2가 경계 영역에서만 겹치는 경우 결과 값으로 True를 반환하며 경계 영역 외에서 겹치거나 겹치는 곳이 없다면 False를 반환 |
| 8 | ST_Distance (g1 Geometry, g2 Geometry) | Double g1과 g2간의 거리를 반환 |
공간관계함수 - TEST
공간 관계 함수의 테스트를 위해 아래 테스트 데이터를 사용자 변수에 저장.
테스트를 위해 변수 지정.
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);
대한민국 - 지리정보
대한민국 지도에 관한 일반정보의 경도범위는 124 – 132, 위도범위는 33 – 43 이다.
- 대한민국 위도와 경도
- 대한민국 전체
- 극동: 경상북도 울릉군의 독도 동단 동경 131° 52′20" → 131.87222222
- 극서: 평안북도 용천군 신도면 마안도 서단 동경 124° 11′45" → 124.19583333
- 극남: 제주도 남제주군 대정읍 마라도 남단 북위 33° 06′40" → 33.11111111
- 극북: 함경북도 온성군 남양면 북단 북위 43° 00′35" → 43.00972222
- 북한 제외
- 극동: 경상북도 울릉군의 독도(獨島)로 동경 131° 52′20“, → 131.87222222
- 극서: 전라남도 신안군의 소흑산도(小黑山島)로 동경 125° 04′, → 125.06666667
- 극북: 강원도 고성군 현내면 송현진으로 북위 38° 27′00, → 38.45000000
- 극남: 제주도 남제주군 마라도(馬羅島)로 북위 33° 06′00" → 33.10000000
- 대한민국 전체
- 섬 포함 우리나라의 중심점은 강원도 양구군 남면 도촌리 산48번지
- 북위 38도 03분 37.5초, 동경 128도 02분 2.5초 → 38.05138889, 128.03388889
- 섬을 제외하고 육지만을 놓고 한반도의 중심점을 계산하면 북한에 위치한 강원도 회양군 현리 인근
- 북위(lon): 38도 39분 00초, 동경(lat) 127도 28분 55초 → 33.10000000, 127.48194444
- 대한민국
- 도분초: 37° 34′ 8″ N, 126° 58′ 36″ E
- 소수점 좌표: 37.568889, 126.976667
DB에 저장된 폴리곤 타입은 바이너리 형태여서 사람눈으로 알아보기 위해 ASTEXT라는 함수를 사용한다.
조회방법
SELECT sggnm, adm_nm, ASTEXT(area_bound_poly) FROM hangjeongdong;
DB에 행정구역을 폴리곤 타입으로 저장해 놓고 내위치와 겹쳐진 영역의 면적을 추출
내 위치 :: 선정릉 역 (37.510303, 127.043840 )
SET @lon = 127.043840;
SET @lat = 37.510303; #lat 위도 위선
#2제곱km MBR생성 ( Minimum Bounding Rectangle )
SET @currentMbr = POLYGONFROMTEXT('POLYGON((37.50117365896353 127.03250312463376,
37.50117365896353 127.05504087536625,
37.51936234103648 127.05504087536625,
37.51936234103648 127.03250312463376,
37.50117365896353 127.03250312463376))');
#15제곱km
SET @currentMbr = POLYGONFROMTEXT('POLYGON((37.44206044222648 126.95925543475322,
37.44206044222648 127.12828856524679,
37.57847555777353 127.12828856524679,
37.57847555777353 126.95925543475322,
37.44206044222648 126.95925543475322))');
#현재 위치에 2제곱km에 걸쳐지는 행정구역 검색 TEST
SELECT
hang_name from area_polygon_hang
WHERE
ST_INTERSECTS(@currentMbr, polygon)
#겹쳐진 행정구역의 면적 추출 TEST
SELECT
hang_name, ST_AREA( ST_INTERSECTION(@currentMbr, polygon)) AS AREA2 FROM area_polygon_hang
WHERE
ST_AREA( ST_INTERSECTION(@currentMbr, polygon)) > 0.0
#MBR에 겹쳐진 행정구역과 면적과 percentage까지 출력한다..
SELECT
ap_id, hang_code, hang_name, sgg_name, sido_name, tgc_code, ST_AREA( ST_INTERSECTION(@currentMbr, polygon))*1000000000 AS overlapArea, ST_AREA( ST_INTERSECTION(@currentMbr, polygon))*1000000000 *100.0/ SUM(ST_AREA( ST_INTERSECTION(@currentMbr, polygon))*1000000000) over() AS percentage
FROM
area_polygon_hang
WHERE
ST_AREA( ST_INTERSECTION(@currentMbr, polygon)) > 0.0
ORDER BY
overlapArea DESC결과~~~~~~
아... 뭔가 두서 없이 마구 나열한거 같다....
공간DB에 대해 이번기회에 많이 공부 했다. 지역기반,위치기반 서비스를 구현하려면 공간DB를 잘 다뤄야 할것 같다.
여러분들도 화이팅~~