MySQL + 공간 데이터

지리 정보 시스템의 개념

지리 정보 시스템이란? (GIS)

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

GIS에서 사용되는 데이터의 기본 구조

속성 데이터

• 기존에 사용해 왔던 문자, 숫자, 날짜 등의 데이터 형식

공간 데이터

• 지구 상에 존재하는 지형 정보를 표현한 데이터
  • 종이지도에 표현된 강, 도로, 나무, 건물, 가로등, 전봇대 등의 모든 표현물을 디지털 지도 상에 옮겨 놓은 데이터
• MySQL 5.0부터 Geometry 라는 데이터 형식으로 공간 데이터를 각각의 개체로 저장/관리/ 검색 지원
  • 기존에는 별도 파일 단위로만 관리
• 국토 지리 정보원에서 배포하고 있는 수치지도 샘플
• 3개의 개체로 표현

• 개체 하나하나를 행 데이터로 취급
  • 하천 이름을 저장하기 위해 CHAR 데이터 형식 열 생성
  • 공간 데이터도 GEOMETRY 데이터 형식 열 생성

 

 

MySQL에서 공간 데이터의 저장

공간 데이터의 관리 방식

CREATE DATABASE GisDB;

USE GisDB;

CREATE TABLE StreamTbl(
	MapNumer	CHAR(10),
    StreamName	CHAR(20),
    Stream		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)'));
    
SELECT * FROM Streamtbl WHERE Streamname = '한류천';

CREATE TABLE BuildingTbl (
	MapNumber	CHAR(10),
    BuildingName	CHAR(20),
    Building 	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 * FROM BuildingTbl;

 

 

공간 데이터 형식의 함수

• ST_GeomFromText()
  • 문자열을 Geometry 형식으로 변환한다.
• ST_AsText()
  • Geometry 형식을 문자열 형식으로 변환한다.
• ST_Length()
  • LineString의 길이를 구한다.
• ST_Area()
  • Polygon의 면적을 구한다.
• ST_Intersects()
  • 두 도형의 교차 여부를 확인한다.
• ST_Buffer(
  • 도형에서부터 주어진 거리만큼 떨어진 좌표 집합을 구한다.
• ST_Contains()
  • 한 도형안에 다른 도형이 들어 있는지 확인한다.

 

프로젝트

체인점 관리

• 짬뽕 전문점 [왕매워 짬뽕] 체인점 본사에서 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)'));
    
    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),                 -- 담당지역명
  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;

 

관리자의 담당 체인점 이름과 주소

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;

 

• 2호점과 5호점 중복 출력

1호점에 배달이 너무 밀려 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);

 

두 관리자의 지역을 모두 합쳐서 출력

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 R.restName AS "중복 관리 지점"
    FROM Restaurant R, Manager M
    WHERE ST_Contains(@crossArea, R.restLocation) = 1;

 

강변북로에서 30m 이내에 있는 짬뽕집 조회

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;
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;