PostgreSQL PostGIS 확장 설치 및 사용법

소개

이 랩에서는 PostGIS 확장 기능을 사용하여 PostgreSQL 데이터베이스에 공간 기능을 추가하는 방법을 배웁니다. PostGIS 는 데이터베이스를 공간 데이터베이스로 변환하여 기하학적 공간에 정의된 데이터를 저장하고 쿼리할 수 있도록 합니다.

새 데이터베이스를 생성하고 PostGIS 확장을 활성화하는 것부터 시작합니다. 그런 다음 위치 데이터를 저장할 GEOMETRY 열이 있는 테이블을 생성합니다. 마지막으로 여러 도시의 데이터를 삽입하고 공간 쿼리를 수행하여 거리를 계산하고 특정 영역 내의 데이터를 찾습니다.

공간 데이터베이스 생성 및 PostGIS 활성화

이 단계에서는 새 데이터베이스를 생성하고 그 안에 PostGIS 확장을 활성화합니다. 공간 데이터를 체계적으로 관리하기 위해 전용 데이터베이스를 사용하는 것이 좋습니다.

먼저 터미널을 열고 psql 대화형 터미널을 사용하여 PostgreSQL 서버에 연결합니다. 기본 postgres 슈퍼유저로 연결합니다.

sudo -u postgres psql

postgres=#와 같은 psql 프롬프트가 표시됩니다.

다음으로 spatial_db라는 새 데이터베이스를 생성합니다. psql의 모든 SQL 명령은 세미콜론 (;) 으로 끝나야 합니다.

CREATE DATABASE spatial_db;

다음과 같은 확인 메시지가 표시됩니다.

CREATE DATABASE

이제 새로 생성된 데이터베이스에 연결합니다. \c 명령은 다른 데이터베이스에 연결하는 데 사용됩니다.

\c spatial_db

프롬프트가 spatial_db에 연결되었음을 나타내도록 변경됩니다.

You are now connected to database "spatial_db" as user "postgres".
spatial_db=#

데이터베이스가 준비되면 PostGIS 확장을 활성화합니다. 이 명령은 모든 PostGIS 공간 유형, 함수 및 테이블을 데이터베이스로 로드합니다.

CREATE EXTENSION postgis;

출력에서 확장이 생성되었음을 확인할 수 있습니다.

CREATE EXTENSION

PostGIS 가 올바르게 설치되었는지 확인하려면 버전을 확인할 수 있습니다.

SELECT PostGIS_full_version();

이 명령은 PostGIS 및 관련 라이브러리에 대한 자세한 버전 정보를 표시하여 성공적인 설정을 확인합니다. 이제 다음 단계로 진행할 수 있습니다. psql 셸을 종료하지 마십시오.

공간 테이블 생성 및 데이터 삽입

이제 공간 데이터베이스가 준비되었으므로 지리 데이터를 저장할 테이블을 생성할 수 있습니다. 이 단계에서는 cities 테이블을 생성하고 몇몇 주요 도시의 좌표를 삽입합니다.

아직 psql 셸에 있으며 spatial_db 데이터베이스에 연결되어 있어야 합니다.

먼저 cities라는 테이블을 생성합니다. 이 테이블에는 표준 id 및 name 열과 함께 GEOMETRY 타입의 location 열이 포함됩니다.

GEOMETRY(Point, 4326) 이해하기

GEOMETRY: 이것은 공간 피처를 저장하는 기본적인 PostGIS 데이터 타입입니다.
Point: 이 지정은 지오메트리 열이 포인트만 저장함을 의미합니다. 다른 타입으로는 LINESTRING 및 POLYGON이 있습니다.
4326: 이것은 공간 참조 시스템 식별자 (SRID) 입니다. SRID 4326 은 GPS 및 Google 지도에서 사용되는 표준 좌표 시스템인 WGS 84 에 해당합니다.

다음 명령을 실행하여 테이블을 생성합니다.

CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    location GEOMETRY(Point, 4326)
);

다음과 같은 확인 메시지가 표시됩니다.

CREATE TABLE

다음으로 cities 테이블에 데이터를 삽입합니다. ST_GeomFromText 함수를 사용하여 Well-Known Text(WKT) 형식의 포인트를 지오메트리 객체로 변환합니다. 형식은 POINT(경도 위도)입니다.

INSERT INTO cities (name, location) VALUES
    ('New York', ST_GeomFromText('POINT(-74.0060 40.7128)', 4326)),
    ('London', ST_GeomFromText('POINT(-0.1278 51.5074)', 4326)),
    ('Tokyo', ST_GeomFromText('POINT(139.6917 35.6895)', 4326));

출력에서 세 개의 행이 삽입되었음을 나타냅니다.

INSERT 0 3

데이터가 올바르게 삽입되었는지 확인하려면 테이블을 쿼리할 수 있습니다. ST_AsText 함수를 사용하여 지오메트리 객체를 사람이 읽을 수 있는 텍스트 형식으로 다시 변환합니다.

SELECT id, name, ST_AsText(location) FROM cities;

출력에는 방금 삽입한 데이터가 표시되어야 합니다.

 id |   name   |          st_astext
----+----------+-----------------------------
  1 | New York | POINT(-74.006 40.7128)
  2 | London   | POINT(-0.1278 51.5074)
  3 | Tokyo    | POINT(139.6917 35.6895)
(3 rows)

공간 테이블을 성공적으로 생성하고 데이터를 채웠습니다.

공간 쿼리 및 분석 수행

PostGIS 의 진정한 힘은 공간 쿼리를 수행하는 능력에 있습니다. 이 단계에서는 PostGIS 함수를 사용하여 두 지점 간의 거리를 계산하고 특정 영역 내에 있는 지점을 찾습니다.

아직 psql 셸에 있으며 spatial_db 데이터베이스에 연결되어 있어야 합니다.

먼저 뉴욕과 런던 간의 거리를 계산해 보겠습니다. 지구 표면에서의 정확한 거리 계산을 위해 geometry 타입을 geography 타입으로 캐스팅하는 것이 가장 좋습니다. geography 타입은 지구의 곡률을 고려하며, ST_Distance는 결과를 미터 단위로 반환합니다.

SELECT ST_Distance(
    (SELECT location::geography FROM cities WHERE name = 'New York'),
    (SELECT location::geography FROM cities WHERE name = 'London')
);

이 쿼리는 미터 단위의 대원 거리 (great-circle distance) 를 반환합니다. 결과는 약 557 만 미터의 큰 숫자가 될 것입니다.

   st_distance
-----------------
 5570299.7888681
(1 row)

다음으로, 런던에서 200km 이내에 있는 도시를 찾기 위한 분석을 수행해 보겠습니다. 이를 위해 먼저 런던 위치 주변에 원형 버퍼 (buffer) 를 생성한 다음, 어떤 도시의 위치가 해당 버퍼와 교차하는지 확인합니다.

ST_Buffer: 소스 지오메트리로부터 지정된 거리 이하인 모든 지점을 나타내는 지오메트리를 생성합니다. geography 타입과 함께 사용될 때 거리는 미터 단위입니다.
ST_Intersects: 두 지오메트리가 "공간적으로 교차"하는 경우, 즉 공통된 공간이 있는 경우 true를 반환합니다.

WITH london_buffer AS (
  SELECT ST_Buffer(location::geography, 200000) AS geom FROM cities WHERE name = 'London'
)
SELECT c.name
FROM cities c, london_buffer lb
WHERE ST_Intersects(c.location::geography, lb.geom);

이 쿼리는 공통 테이블 표현식 (CTE) 을 사용하여 런던 주변에 200,000 미터 (200km) 버퍼를 생성합니다. 그런 다음 이 버퍼와 교차하는 모든 도시를 찾습니다. 예상대로 이 영역 내에는 런던 자체만 있습니다.

  name
--------
 London
(1 row)

이제 기본적인 공간 분석을 수행했습니다. \q를 입력하여 psql 셸을 종료할 수 있습니다.

\q

데이터베이스 정리

작업 완료 후 환경을 정리하는 것은 좋은 습관입니다. 이 단계에서는 spatial_db 데이터베이스를 삭제합니다. 데이터베이스를 삭제하면 데이터베이스 자체와 테이블 및 확장 프로그램과 같은 모든 객체가 함께 제거됩니다.

일반 터미널 프롬프트 (labex@vm:~$) 로 돌아와야 합니다.

데이터베이스를 삭제하려면 다른 데이터베이스에 연결되어 있어야 합니다. 기본 postgres 데이터베이스에 연결합니다.

sudo -u postgres psql

이제 DROP DATABASE 명령을 실행합니다.

DROP DATABASE spatial_db;

다음과 같은 확인 메시지가 표시됩니다.

DROP DATABASE

\l 명령을 사용하여 사용 가능한 모든 데이터베이스를 나열하여 데이터베이스가 제거되었는지 확인할 수 있습니다.

\l

spatial_db가 목록에 더 이상 없는 것을 볼 수 있습니다.

마지막으로 psql 셸을 종료합니다.

\q

요약

이 실습에서는 PostGIS 확장을 사용하여 PostgreSQL 에 공간 데이터 지원을 추가하는 방법을 배웠습니다. 전용 데이터베이스를 성공적으로 생성하고, PostGIS 확장을 활성화하고, 지오메트리 열이 있는 테이블을 정의하고, 위치 데이터를 삽입하고, 거리 계산 및 교차점 찾기를 위한 공간 쿼리를 수행했습니다. 이러한 기술은 PostgreSQL 을 사용하여 위치 기반 애플리케이션을 구축하는 데 견고한 기반을 제공합니다.

PostgreSQL 에서 PostGIS 확장 사용하기

소개

공간 데이터베이스 생성 및 PostGIS 활성화

공간 테이블 생성 및 데이터 삽입

공간 쿼리 및 분석 수행

데이터베이스 정리

요약