MySQL에 행정구역 공간 데이터 shape파일 import하기

대한민국 행정구역 공간 데이터와 MySQL의 공간 함수를 사용하기 위한 과정을 정리하고자 한다.

앞서 미리 행정구역 데이터를 구해놓은 상태이고, 이를 MySQL로 임포트시켜보자.

대한민국 최신 행정구역(SHP) 다운로드

위 링크에서 최신 행정구역의 shape파일을 제공해주어 이를 이용했다.

일단 mysql을 사용할 것이기에, shape파일의 데이터를 mysql에 넣고 쿼리를 날려보는 것부터 시작해보자.

관련 키워드로 검색을 해보니 GDAL 이라는 툴을 사용하여 shape파일을 넣는 방법이 존재하는 것 같다.

GDAL — GDAL documentation

대강 지리 데이터 변환기?라고 생각하면 될 것 같다.

1. GDAL을 사용한 shape파일 import 첫 번째 시도

먼저 실습을 위해 설치부터 해보자

brew install gdal
brew list

사진처럼 설치가 완료된 것을 확인할 수 있다.

이제 아래 문서를 통해 mysql로 데이터를 넣어보자

MySQL — GDAL documentation

문서를 보면 다음과 같은 명령어로 shape파일을 mysql로 전송할 수 있다.

ogr2ogr -f MySQL MySQL:{DB 이름},user={유저} {파일명}.shp -nln {테이블명} \
-update -overwrite -lco GEOMETRY_NAME={컬럼명}

파라미터는 몇 개만 변경해주면 되고, -update, -overwrite 옵션을 주면 테이블까지 모두 만들어준다.

따라서 create database ~~; 로 데이터베이스만 생성한 뒤에 위 명령어를 실행하면 된다.

gisDB 라는 이름의 데이터베이스를 만든 뒤 아래 명령어를 실행해보았다.

ogr2ogr -f MySQL MySQL:gisDB,user=root ctprvn.shp -nln sido \
-update -overwrite -lco GEOMETRY_NAME=geometry

그러나 MySQL 드라이버를 찾지 못하는 문제가 발생했다.

(캡처를 못해서 자료 화면이 없다..)

분명 mysql은 깔려있는데…

터미널에서 mysql 명령어가 인식되지 않아서 PATH 문제인가 싶어 export PATH 로 경로까지 설정해보았지만 역시나 똑같았다.

꽤 긴 시간 구글링을 해본 결과, 같은 문제를 겪는 사람들은 몇몇 보였지만 명확한 해결법에 대한 내용을 찾을 수 없었다.

그러다가 GDAL이 빌드될 때 MySQL 드라이버이 포함되어야 한다는 정보를 듣고, 다음 명령어로 지원 포맷을 확인해보았다.

ogrinfo --formats

사진에 보이는 것 이외에도 많은 지원 포맷들이 있었지만, 정작 MySQL 포맷은 없었다.

---

2. 직접 GDAL에 MySQL을 포함시켜 빌드하기

현재 설치된 GDAL이 MySQL을 지원하지 않는다는 것을 확인했으니, 원인을 파악한 셈이다.

결론적으로 MySQL 드라이버를 포함하여 직접 빌드하기로 했다.

Building GDAL from source — GDAL documentation

GDAL 레퍼런스에 빌드 방법이 설명되어 있었고, 바로 작업을 시작했다.

2-1. 먼저 빌드를 위해 레포지토리를 클론한다.

git clone https://github.com/OSGeo/gdal.git

2-2. 레포 디렉토리에 build 디렉토리를 만들어야 한다.

2-3. 이제 cmake로 빌드를 시작할 차례다.

단, 자동으로 되는진 몰라도 MySQL을 확실하게 빌드에 포함시키기 위해 레퍼런스에 명시되어 있는 다음 옵션을 주었다.

cmake -MYSQL_INCLUDE_DIR=/opt/homebrew/opt/mysql/include \
-MYSQL_LIBRARY=/opt/homebrew/opt/mysql/lib \
..

여기서 갑자기 지원되지 않는 옵션이라고 하길래 “또 공식문서가 업데이트가 안 되어있나..?” 라는 생각과 함께 약간의 삽질을 했다.

그러나 다시 읽어보니 아래 사진처럼 -D 접두사를 붙여줘야 한다는 것이다… 제대로 읽어보자..^^

따라서 다음과 같이

cmake -DMYSQL_INCLUDE_DIR=/opt/homebrew/opt/mysql/include/mysql \
-DMYSQL_LIBRARY=/opt/homebrew/opt/mysql/lib  \
..

드디어 성공했다!

이제 진짜 빌드를 해보자. 8개 코어를 통해 빠르게 빌드를 진행해보았다.

make -j8 # 8-core

한 80%? 정도 완료됐을 때 갑자기 오류가 발생했다.

-- Could NOT find EXPAT (missing: EXPAT_DIR)
-- Found EXPAT: /Library/Developer/CommandLineTools/SDKs/MacOSX15.1.sdk/usr/lib/libexpat.tbd (found version "2.6.1")
-- Failed to find XercesC (missing: XercesC_LIBRARY XercesC_INCLUDE_DIR XercesC_VERSION)
-- Found ZLIB: /Library/Developer/CommandLineTools/SDKs/MacOSX15.1.sdk/usr/lib/libz.tbd (found version "1.2.12")
-- Could NOT find Deflate (missing: Deflate_LIBRARY Deflate_INCLUDE_DIR)
-- Found OpenSSL: /opt/homebrew/Cellar/openssl@3/3.4.0/lib/libcrypto.dylib (found version "3.4.0") found components: SSL Crypto
-- Could NOT find CryptoPP (missing: CRYPTOPP_LIBRARY CRYPTOPP_TEST_KNOWNBUG CRYPTOPP_INCLUDE_DIR)
-- Could NOT find PROJ (missing: PROJ_DIR)

GDAL이 의존하는 라이브러리 중 설치되지 않은 것이 몇 개 있는 것 같았다.

빠르게 chatGPT의 도움으로 설치를 수행했다.

brew update
brew install expat xerces-c libdeflate proj

다시 빌드 시작! 을 했으나 실패했다.

아래와 같은 오류가 발생했다;

Undefined symbols for architecture arm64:
  "_mysql_affected_rows", referenced from:
      OGRMySQLDataSource::ExecuteSQL(char const*, OGRGeometry*, char const*) in ogrmysqldatasource.cpp.o
      OGRMySQLTableLayer::DeleteFeature(long long) in ogrmysqltablelayer.cpp.o

흠…오류를 보아하니 MySQL과 관련된 오류인데, 이유를 모르겠다.

일단 GPT에게 물어봤는데, 생각보다 날카로운 지적을 했다.

MySQL 라이브러리 경로를 지정할 때, /opt/homebrew/opt/mysql/lib 처럼 라이브러리 디렉토리가 아니라 클라이언트 라이브러리 파일인 /opt/homebrew/opt/mysql/lib/libmysqlclient.dylib 을 지정해야 한다는 것이다.

그래서 다음과 같이 바꾸고 다시 빌드해보았다.

그리고 ARM 옵션도 하라고 해서 일단 했는데 이것도 중요 요인인지는 잘 모르겠다

cmake -DCMAKE_OSX_ARCHITECTURES=arm64 \
-DMYSQL_INCLUDE_DIR=/opt/homebrew/opt/mysql/include/mysql \
-DMYSQL_LIBRARY=/opt/homebrew/opt/mysql/lib/libmysqlclient.dylib \
..

성공했다!!!

(이 자료 화면도 캡처하지 못했다)

찝찝한 마음에 다시 관련 내용을 좀 찾아보니, MySQL client library가 필요하다는 말이 있기는 했다.

MYSQL_LIBRARY 옵션에는 그냥 라이브러리 경로를 넣으라는 말만 존재해서 다소 헷갈릴 수 있다고 생각한다.

2-4. ogrinfo 실패

드디어 빌드와 설치를 완료했으니, 명령어 실행이 되는지와 MySQL 드라이버가 잘 인식되는지를 확인해볼 차례다.

아…뭔가 안된다..

LC_RPATH를 찾을 수 없다? 뭔가 설정에 문제가 있는 것 같다.

환경 설정만큼 GPT가 잘 알려주는 것도 없다. 바로 물어보았다.

역시 환경 변수의 문제가 맞는 것 같았고, 결과적으로 2번의 환경 변수 추가까지만 했더니 잘 실행되었다 🙂

그리고 다시 명령어를 실행한 결과

성공이다!

MySQL 드라이버도 잘 인식된 것을 확인했다!

2-5. 데이터 저장 실패

MySQL 드라이버가 잘 인식되는 것을 확인했으니, 드디어 데이터를 다시 넣어볼 차례다.

이번에도 안되면 마음이 아플 것 같았다..

ogr2ogr -f MySQL MySQL:gisDB,user=root ctprvn.shp \
-lco GEOMETRY_NAME=geometry -nln sido -update -overwrite

위와 같은 명령어를 작성하고 실행시켰다.

그러자 어마어마한 좌표값들과 함께 에러가 발생하며 실패했다;

워크벤치를 확인해보니 테이블이랑 컬럼까지는 잘 생성이 되었는데, 데이터가 들어가질 못한다…

-skipfailures 옵션을 통해 실패한 것은 제외시키도록 해봤지만 모든 데이터가 실패했는지 하나도 입력되지 않았다.

또 한 번의 삽질과 구글링을 하고 GPT에게도 물어봤지만 나오는 정보들은 다음과 같은 전형적인 것들이었다.

• 테이블 생성 확인
• geometry 컬럼 이름 확인
• MySQL 권한 확인
• 데이터 자체의 문제

이 중에는 문제가 없을 것 같았다.

2-6. 드디어 성공

고민고민하다가 문득 인코딩을 맞게 설정해야 한다는 글을 보았던 어렴풋한 기억을 떠올렸고, 위 오류 로그에서 보이는 깨진 글자들을 보며 혹시나 하는 마음에 인코딩을 확인해보기로 했다.

먼저 해당 데이터의 인코딩 정보가 혹시 있을까 싶어서 데이터 제공 사이트의 댓글들을 확인했다.

아니나 다를까 뭔가 나랑 비슷한 오류를 겪은 사람의 질문을 찾을 수 있었고, EUC-KR이라는 인코딩을 사용해야한다는 정보를 얻을 수 있었다.

분명 현재 설정값은 UTF-8일 것이고 다음과 같이 EUC-KR로 바꿔서 실행시켰다.

ogr2ogr -f MySQL MySQL:gisDB,user=root ctprvn.shp \
-lco GEOMETRY_NAME=geometry -nln sido -update -overwrite \
--config SHAPE_ENCODING "EUC-KR"

와…오류가 뜨지 않았다.

조마조마한 마음에 워크벤치로 들어가서 확인했다.

드디어 성공!

다사다난한 과정이었지만 그래도 어찌저찌 성공했다.

이제 이 공간 데이터를 이용하면서 이런저런 테스트를 해볼 것이다.