파트 12. 공간 데이터

1. 지리정보시스템(GIS)의 개념


지표면과 지상공간에 존재하고 있는 각종 자연물(산,강,토지 등)과 인공물(건물, 도로, 철도 등)에 대한 위치정보와 속성 정보를 컴퓨터에 입력 후, 이를 연계시켜 각종 계획 수립과 의사 결정 및 산업 활동을 효율적으로 지원할 수 있도록 만든 첨단 정보 시스템을 지리정보시스템이라고 한다. 쉽게 말하면, 지도와 그에 따른 텍스트(속성) 정보를 컴퓨터에 입력 한 후에 그 입력된 전자지도를 교통, 환경 등의 다양한 곳에 응용하는 분야이다.


2. 공간 데이터


mysql 에서는 geometry라는 데이터 형식을 지원한다. 이로서 공간 데이터를 저장할 수 있다. 공간 데이터는 점, 선, 면이라는 3개의 개체로서 표현된다. 그림1

예시로 공간 데이터 입력해보자.

create table streamtbl(
    MapNumber char(10),
    StreamName char(20),
    Stream geometry -- 공간 데이터
); 
insert into streamtbl values ('33','한류천',
-- st_geomfromtext 함수를 사용하여 geometry 데이터 삽입
st_geomfromtext('linestring(-10 30,-50 70, 50 70)'));


3. 실습


DROP DATABASE IF EXISTS GisDB;
CREATE DATABASE GisDB;

USE GisDB;
-- 하천 테이블 만들기
CREATE TABLE StreamTbl (
   MapNumber CHAR(10),  -- 지도일련번호
   StreamName CHAR(20),  -- 하천이름
   Stream GEOMETRY ); -- 공간데이터(하천개체)

-- 정보 입력, geometry에 선타입으로 입력
INSERT INTO StreamTbl VALUES ( '330000001' ,  '한류천', 
	ST_GeomFromText('LINESTRING (-10 30, -50 70, 50 70)'));
INSERT INTO StreamTbl VALUES ( '330000001' ,  '안양천', 
	ST_GeomFromText('LINESTRING (-50 -70, 30 -10, 70 -10)'));
INSERT INTO StreamTbl VALUES ('330000002' ,  '일산천', 
	ST_GeomFromText('LINESTRING (-70 50, -30 -30, 30 -60)'));

-- 빌딩 테이블 만들기
CREATE TABLE BuildingTbl (
   MapNumber CHAR(10),  -- 지도일련번호
   BuildingName CHAR(20),  -- 건물이름
   Building GEOMETRY ); -- 공간데이터(건물개체)

-- geometry에 면타입으로 입력
-- 괄호가 하나씩 더 들어가고 면이니까 시작점과 끝점이 같음
INSERT INTO BuildingTbl VALUES ('330000005' ,  '하나은행', 
	ST_GeomFromText('POLYGON ((-10 50, 10 30, -10 10, -30 30, -10 50))'));
INSERT INTO BuildingTbl VALUES ( '330000001' ,  '우리빌딩', 
	ST_GeomFromText('POLYGON ((-50 -70, -40 -70, -40 -80, -50 -80, -50 -70))'));
INSERT INTO BuildingTbl VALUES ( '330000002' ,  '디티오피스텔', 
	ST_GeomFromText('POLYGON ((40 0, 60 0, 60 -20, 40 -20, 40 0))'));

-- select하고 오른쪽에서 아래로 내리다보면 spatial view가 있다.
-- 클릭하고 돋보기+(확대)옆에 전체화면클릭하고
-- 돋보기 왼쪽에 보면 여러개 선택 가능한 것에서 
-- robinson이 되어있을 텐데 equirectangular로 바꾸면
-- 전체적인 위치 확인 가능
SELECT * FROM StreamTbl;

SELECT * FROM BuildingTbl;

-- st_length()공간함수 -> 길이를 구해줌
SELECT * FROM StreamTbl WHERE ST_Length(Stream) > 140 ;

-- st_area() 공간 면적 함수
SELECT BuildingName, ST_AREA(Building) FROM BuildingTbl 
	WHERE ST_AREA(Building) < 500;

-- 같이보고 싶다면 union
SELECT * FROM StreamTbl
UNION ALL
SELECT * FROM BuildingTbl;


4. 공간 데이터 형식의 함수


공간 데이터를 입력/출력/조회하기 위해서는 기존의 sql구문으로 처리할 수 없기 때문에 공간 데이터를 처리하기 위한 공간 함수를 mysql에서 제공해준다.

그림2

SELECT StreamName, BuildingName, Building, Stream
   FROM BuildingTbl , StreamTbl 
   -- 안양천과 교차하는 건물
   WHERE ST_Intersects(Building, Stream) = 1   AND StreamName = '안양천';

-- 강폭을 5만큼 늘려서 보기
SELECT ST_Buffer(Stream,5) FROM StreamTbl;


5. 응용


그림3

체인점을 관리하기 위한 gis프로젝트 구현하기

DROP DATABASE IF EXISTS KingHotDB;
CREATE DATABASE KingHotDB;

USE KingHotDB;
-- [왕매워 짬뽕] 체인점 테이블 (총 매출액 포함)
CREATE TABLE Restaurant
(restID int auto_increment PRIMARY KEY,  -- 체이점 ID
 restName varchar(50),	        -- 체인점 이름
 restAddr varchar(50),	        -- 체인점 주소
 restPhone varchar(15),	        -- 체인점 전화번호
 totSales  BIGINT,		        -- 총 매출액			
 restLocation geometry ) ;	        -- 체인점 위치

-- [왕매워 짬뽕] 1호점~9호점 입력
INSERT INTO Restaurant VALUES
 (NULL, '왕매워 짬뽕 1호점', '서울 강서구 방화동', '02-111-1111', 1000, ST_GeomFromText('POINT(-80 -30)')),
 (NULL, '왕매워 짬뽕 2호점', '서울 은평구 증산동', '02-222-2222', 2000, ST_GeomFromText('POINT(-50 70)')),
 (NULL, '왕매워 짬뽕 3호점', '서울 중랑구 면목동', '02-333-3333', 9000, ST_GeomFromText('POINT(70 50)')),
 (NULL, '왕매워 짬뽕 4호점', '서울 광진구 구의동', '02-444-4444', 250, ST_GeomFromText('POINT(80 -10)')),
 (NULL, '왕매워 짬뽕 5호점', '서울 서대문구 북가좌동', '02-555-5555', 1200, ST_GeomFromText('POINT(-10 50)')),
 (NULL, '왕매워 짬뽕 6호점', '서울 강남구 논현동', '02-666-6666', 4000, ST_GeomFromText('POINT(40 -30)')),
 (NULL, '왕매워 짬뽕 7호점', '서울 서초구 서초동', '02-777-7777', 1000, ST_GeomFromText('POINT(30 -70)')),
 (NULL, '왕매워 짬뽕 8호점', '서울 영등포구 당산동', '02-888-8888', 200, ST_GeomFromText('POINT(-40 -50)')),
 (NULL, '왕매워 짬뽕 9호점', '서울 송파구 가락동', '02-999-9999', 600, ST_GeomFromText('POINT(60 -50)'));

-- 점이 너무 작으므로 buffer로 크기 키워서 출력
SELECT restName, ST_Buffer(restLocation, 3) as '체인점' FROM Restaurant;

-- 지역 관리자 테이블
CREATE TABLE Manager
 (ManagerID int auto_increment PRIMARY KEY,   -- 지역관리자 id
  ManagerName varchar(5),	              -- 지역관리자 이름
  MobilePhone varchar(15),	              -- 지역관리자 전화번호
  Email varchar(40),                      -- 지역관리자 이메일
  AreaName varchar(15),                 -- 담당지역명
  -- srid는 geometry 타입인데 평면지구, 둥근지구 매핑에 사용되는 
  -- 특정 타원면을기준으로 하는 공간 참조 시스템임
  Area geometry SRID 0) ;                       -- 담당지역 폴리곤

-- 매니저 정보
INSERT INTO Manager VALUES
 (NULL, '존밴이', '011-123-4567', 'johnbann@kinghot.com',  '서울 동/북부지역',
   ST_GeomFromText('POLYGON((-90 0, -90 90, 90 90, 90 -90, 0 -90, 0  0, -90 0))')) ,
 (NULL, '당탕이', '019-321-7654', 'dangtang@kinghot.com', '서울 서부지역',
   ST_GeomFromText('POLYGON((-90 -90, -90 90, 0 90, 0 -90, -90 -90))'));

-- 각각의 담당 영역 출력
SELECT ManagerName, Area as '당탕이' FROM Manager WHERE ManagerName = '당탕이';
SELECT ManagerName, Area as '존밴이' FROM Manager WHERE ManagerName = '존밴이';

-- 서울시의 도로 테이블
CREATE TABLE Road
 (RoadID int auto_increment PRIMARY KEY,  -- 도로 ID
  RoadName varchar(20),           -- 도로 이름
  RoadLine geometry );              -- 도로 선

INSERT INTO Road VALUES
 (NULL, '강변북로',
   ST_GeomFromText('LINESTRING(-70 -70 , -50 -20 , 30 30,  50 70)'));

-- 버퍼줘서 키워서 보기
SELECT RoadName, ST_BUFFER(RoadLine,1) as '강변북로' FROM Road;

-- 한번에 묶어서 실행하면 union 안해도
-- 쿼리 결과는 따로나오지만 spatial view에서 layer체크시 함께 나옴
SELECT ManagerName, Area as '당탕이' FROM Manager WHERE ManagerName = '당탕이';
SELECT ManagerName, Area as '존밴이' FROM Manager WHERE ManagerName = '존밴이';
SELECT restName, ST_Buffer(restLocation, 3) as '체인점' FROM Restaurant;
SELECT RoadName, ST_BUFFER(RoadLine,1) as '강변북로' FROM Road;

-- 공간데이터 완성했으니 이제 활용

-- 담당자의 면적 영역 확인
SELECT ManagerName, AreaName, ST_Area(Area) as "면적 m2" FROM Manager;

-- 담당자별로 담당하는 체인점 출력
SELECT M.ManagerName,
       R.restName,
       R.restAddr,
       M.AreaName 
FROM Restaurant R, Manager M
-- 매니저의 지역과, 체인점의 위치 포함여부
WHERE ST_Contains(M.AREA, R.restLocation) = 1 
ORDER BY M.ManagerName;

-- 체인점 중에서 1호점과 가까운 거리순
SELECT R2.restName,
       R2.restAddr,
       R2.restPhone, 
       ST_Distance(R1.restLocation, R2.restLocation) AS "1호점에서 거리"
FROM Restaurant R1, Restaurant R2
WHERE R1.restName='왕매워 짬뽕 1호점'
ORDER BY ST_Distance(R1.restLocation, R2.restLocation) ;

-- 관리지역 union으로 합치기
SELECT Area INTO @eastNorthSeoul FROM Manager WHERE AreaName = '서울 동/북부지역';
SELECT Area INTO @westSeoul FROM Manager WHERE AreaName = '서울 서부지역';
SELECT ST_Union(@eastNorthSeoul, @westSeoul) AS  "모든 관리지역을 합친 범위" ;

-- 겹치는 위치에 있는 레스토랑 출력
SELECT  Area INTO @eastNorthSeoul FROM Manager WHERE ManagerName = '존밴이';
SELECT  Area INTO @westSeoul FROM Manager WHERE ManagerName = '당탕이';
SELECT  ST_Intersection(@eastNorthSeoul, @westSeoul) INTO @crossArea ;
SELECT DISTINCT restName AS "중복 관리 지점"
    FROM Restaurant
    WHERE ST_Contains(@crossArea, restLocation) = 1;

-- 도로에서 30미터 안에 있는 지점 찾기
SELECT  ST_Buffer(RoadLine, 30) INTO @roadBuffer FROM Road;
SELECT R.restName AS "강변북로 30M 이내 지점"
   FROM Restaurant R
   WHERE ST_Contains(@roadBuffer,R.restLocation) = 1;

SELECT  ST_Buffer(RoadLine, 30) INTO @roadBuffer FROM Road;
-- 강변북로 내 30m 범위 출력
SELECT  ST_Buffer(RoadLine, 30) as '강변북로 30m' FROM Road;
SELECT  ST_Buffer(R.restLocation, 5) as '체인점' -- 지점을 약간 크게 출력
   FROM Restaurant R
   WHERE ST_Contains(@roadBuffer, R.restLocation) = 1;
-- 그냥 강변북로 출력
SELECT RoadLine as '강변북로' FROM Road;



본 포스팅은 ‘이것이 MySQL이다’를 읽고 공부한 내용을 바탕으로 작성하였습니다.


© 2021. By Backtony