프로젝트를 진행하면서 SHP 파일을 import 한 과정을 기록한다.
DBeaver 를 DB 툴로 사용하고 있는 상황이었는데, DBeaver로는 .csv 파일 불러오듯이 바로 .shp 파일을 불러올 수가 없다. postGIS나 qGIS 등을 이용해야 한다. 나는 postGIS를 사용해서 파일을 import 했다.

0. shp 파일 구하기

지역정보를 shp 파일은 여러 곳에서 구할 수 있다.
필자는 GEOSERVICE_WEB 을 사용했다.
회원가입하면 코인이 충전되고 코인으로 shp 파일을 구매할 수 있다.
지역구 파일이 필요해서 시/군/구 파일을 다운로드 받았다.
자세한 사용법은 제작자의 블로그를 참고하자

1. postGIS 설치

shp 데이터베이스가 필요한 건 로컬이 아니고, AWS 에 올라가있는 서버였다. SSH로 연결하는 방식인데 postGIS나 qGIS에서 서버를 연결하는 방법을 모르겠어서 로컬에 일단 올리고, DBeaver를 통해서 DDL과 데이터베이스 내용을 서버로 가져오기로 했다.

그러려면 일단 로컬에 postGIS가 설치되어있어야 한다.
로컬에 postgreSQL 17버전이 세팅되어있었는데, postgis 익스텐션이 설치가 안되어있어서 postgis 설치만 다시 진행하려고 했다.
그런데 17 버전에서 postgis 익스텐션을 다운로드하려고 하면 계속 응답없음이 뜨길래 버전을 다운그레이드 하여 진행했다.

기존에 있던 17버전을 싹 지우고 16.7버전을 설치하니, postgis 익스텐션이 다운로드가 잘 되었다.

2. pgAdmin

이 과정은 생략해도 된다.
다들 pgAdmin을 쓰던데 한 번도 안써봐서 사용해봤다.
어차피 shp 파일을 변환해서 넣는 부분은 postGIS로 진행할 거라, 여기서는 새로운 서버를 연결하기만 할 것이다.

Servers -> Register -> server 순으로 진행하여 서버를 등록해준다.

서버를 등록할 때 필요한 정보는 아래와 같다.

• Name : 서버 이름
• Server group : 서버 그룹 설정
• Backgroud, Foregroupd : 색상 지정
• Connect Now : 설정 후 PostgreSQL에 접속
• Comments : 서버 설명

• Host name/address : PostgreSQL 서버의 주소
• Port : 포트 값 (postgreSQL 기본값 : 5432)
• Maintenance databse : 접속할 Database명
• Username : 사용자 이름
• Password : PostgreSQL 비밀번호

제대로 서버를 연결했다면 왼쪽에 이렇게 추가된다.
테이블은 Schemas > public 안에 있다.

※ 참고

이미 postgis 익스텐션이 실행된 상태에서 해당 쿼리를 작성하면 에러가 발생한다.
postgis가 가능한 상태이면 spatial_ref_sys 테이블이 이미 생성되어 있을 것이다.

3. postGIS

VIEW CONNECTION DETAILS을 클릭하여 진행한다.

pgAdmin에서 만든 새 서버를 넣었다. shp 파일이 필요한 서버정보를 작성하여 연결한다.

연결이 완료되면 이렇게 뜰 것이다.

shp 파일에 한국어가 포함되어있다면 Options에서 UTF-8을 EUC-KR로 바꿔준다.

Options 설정이 끝났다면 add file을 클릭하여 파일을 추가하고 import를 클릭한다.

※ 참고
dbf file (.dbf) can not be opened. Shapefile import failed.
에러가 발생하면 파일의 경로 중 한글이 포함되어 있는 경로가 있을 수 있다.
필자도 이 에러를 겪었고, 이 경우에는 바탕화면/C 로 파일들을 빼서 해결했다.

SRID(공간 참조 식별자, Spatial Reference Identifier)는 필요시 설정해주는 것이 좋다.
사용한 shp 파일은 5179 좌표계를 사용하고 있다. 그래서 5179 좌표계로 설정하여 import 하였다.

4. DBeaver

import 가 정상적으로 완료되었다면 DBeaver를 비롯한 DB 툴에서도 테이블이 조회될 것이다. (DBeaver가 익숙해서 사용했다. 본인에게 맞는 툴을 사용하면 된다.)

gid : 지역구 인덱스
sig_cd : 지역구 아이디
sig_eng_nm : 지역구 영문명
sig_kor_nm : 지역구 한글명
geom : 지역구 범위 (Multipolygon, 5179)

제대로 테이블이 들어왔음을 확인했으면, 값을 실 사용하기 전에 중요한 과정이 남았다. 이 테이블은 5179좌표계를 사용하고 있으므로 필요한 좌표계가 다르다면 그에 맞춰 수정을 해줘야한다.

진행중인 프로젝트는 4326 좌표계를 필요로 했다. 좌표계 변경에 앞서 아래의 쿼리로 테스트를 했다.

SELECT ST_AsText(ST_Transform(ST_PointOnSurface(geom), 4326)) as transformed_coord,
       ST_AsText(ST_PointOnSurface(geom)) as original_coord
FROM sig_5179 LIMIT 1;

ST_PointOnSurface : 범위의 중앙점을 반환한다.
ST_AsText : 지오메트리 값을 매개변수로 받으면 텍스트로 반환한다.
ST_Transform(좌표, 좌표계) : 지오메트리 값을 특정 좌표계로 변환한다.

실행 결과가 이렇게 나왔다. 얼추 맞게 변환된듯 하다.

ST_Transform() 함수가 제대로 동작하는 걸 확인했으니 원래 테이블을 복사해서 4326 좌표계로 변환할 것이다.

-- 1. 기존 테이블 이름 변경 (백업용)
ALTER TABLE public.sig RENAME TO sig_5179;

-- 2. 새로운 테이블 생성 (EPSG:4326 좌표계 사용)
CREATE TABLE public.sig (
    gid serial4 NOT NULL,
    sig_cd varchar(5) NULL,
    sig_eng_nm varchar(40) NULL,
    sig_kor_nm varchar(40) NULL,
    geom public.geometry(multipolygon, 4326) NULL,
    CONSTRAINT sig_pkey2 PRIMARY KEY (gid)
);

-- 3. 데이터 변환하여 복사
INSERT INTO public.sig (sig_cd, sig_eng_nm, sig_kor_nm, geom)
SELECT sig_cd, sig_eng_nm, sig_kor_nm, ST_Transform(geom, 4326)
FROM sig_5179;

-- 4. 공간 인덱스 생성
CREATE INDEX sig_geom_idx2 ON public.sig USING gist (geom);

1. 기존 테이블 이름을 5179로 변경하고 4326좌표계를 사용하는 새로운 테이블을 생성했다.(구조는 같음)
2. 그리고 기존 테이블의 데이터를 ST_Transform() 함수를 사용해서 4326 좌표계로 변환하여 그대로 복사했다.
3. 공간 인덱스도 빠트리지 않고 똑같이 생성해주었다.

※ 참고
Multipolygon 타입은 이런 식으로 값이 들어가는데 굉장히 길기 때문에 DBeaver에서 컬럼값을 전부 명시해주는 INSERT 문을 복사해서 사용하지 않길 바란다. 무조건 렉이 걸린다.

MULTIPOLYGON (((956615.4532424484 1953567.1989686124, 956621.5787365452 1953565.2711694315, .... )))

5. 지역구 계산하는 쿼리 작성

유저의 위치가 특정 지역구에 속하는 경우에만 서비스 이용이 가능하게끔 제한해야했다. 그래서 쿼리를 아래와 같이 작성했다.

SELECT EXISTS (
    SELECT 1
    FROM sig
    WHERE (sig_cd = '11650' or sig_cd = '11680' or sig_cd = '11710' or sig_cd = '11740')
    and ST_Contains(geom, ST_SetSRID(ST_MakePoint(127.02800140627488, 37.49808633653005), 4326))
);

ST_MakePoint : x좌표, y좌표를 매개변수로 넣으면 공간 데이터로 반환한다.
ST_SetSRID : 공간데이터에 원하는 좌표계값을 넣어준다.
ST_Contains : 한 지오메트리가 다른 지오메트리에 완전히 포함되는지를 확인한다.

sig_cd값에 원하는 지역구를 넣었다.
필요한 sig_cd 조건을 or 로 연결하고 있으므로 괄호를 작성해주어야 한다. 그렇지 않으면 and 가 먼저 실행되어 다른 값이 나오게 되므로 유의하자.