공간인덱스(Spatial index)들은 PostGIS의 가장 큰 자산들이다. 우리가 이전에 살펴본 ST_DWithin 에서 볼 수 있듯이, 두 개의 지오메트리(geometry) 사이의 관계를 결정하는 것은 먼저 지오메트리의 바운딩박스(bounding box)를 결정함으로써 속도가 향상될 수 있다. 인덱스는 이러한 바운딩 박스와 지오메트리사이의 관계를 정하는 개념과 더불어 지오메트리의 바운딩 박스를 인덱싱하는 절차를 갖게 된다.

PostGIS 에서 공간인덱스를 생성하는 것은 간단하다. 아래의 커맨드들은 jacksonco_streets table의 지오메트리 컬럼에 인덱스를 생성할 것이다.

CREATE INDEX jacksonco_streets_gix ON jacksonco_streets USING GIST (the_geom);

Note
인덱스를 생성할 때 GIST 절을 이용하는 것은 PostgreSQL가 generic index structure (GIST)인덱스를 사용하게 하는 것이다. 이것은 기본 인덱스 타입이 B-Tree 타입이기 때문에 중요하다. B-Tree 인덱스들은 GIST 인덱스가 하는 것과 같은 lossy (inexact)타입이 아니기 때문이다. 이것은 GIST 인덱스가 지오메트리의 바운딩박스를 인덱싱하는 반면에, B-Tree 반드시 인덱스가 처리할 수 있는 것보다 클 수도 있는 모든 지오메트리를 인덱싱하는 것을 의미한다. 만약 인덱스 생성시에 "ERROR: index row requires 11340 bytes, maximum size is 8191" 이라는 에러가 나오면 USING GIST 절을 쓰는 것을 소흘히 해서 생긴 것이다.

우리가 공간인덱스를 사용할 때 실제 무슨 일이 인덱스가 생성될 때 발생하는지 살펴보자.

왼쪽에 있는 그림의 하나의 폴리곤(polygon)과 세 개의 선(line)이다. 오직 빨간선만이 폴리곤을 가로지르고 있음을 확인할 수 있고, 나머지 두 개의 선은 접하거나 겹치지 않는다. 가운데 있는 그림은 같은 오브젝트들이 바운딩 박스(bounding box )라고도 하는 MBR(minimum bounding rectangle)에 있는 것을 보여준다. 마지막으로 오른쪽의 그림은 바운딩 박스이다.

이 경우에서 우리는 바운딩 박스가 필터가 폴리곤과 라인스트링(linestring)이 빨간선과 파랑선에 교차하는가와 녹색선이 교차(intersection )하지 않는 것을 조회한다는 것을 확인할 수 있다. 바운딩박스 교차 연산자는 &&이고, 이것은 아래 보이는 쿼리의 WHERE 절에 추가 되어졌다.

&& 연산자는 그 자체만으로는 지오메트리들끼리 교차되었는지 보장하지는 못하고, 단지 바운딩 박스가 교차되었다는 것만을 보장할 수 있다. 그런 입장에서 && 연산자는 거의 독립적으로 쓰이지는 않는다. 이러한 이유로 대부분의 관련 함수들이 && 연산자를 쿼리의 일부분으로서 포함할 것이다. ST_DWithin 의 예를 보면 두 개의 && 연산자가 다른 것의 바운딩 박스 확장과 각각의 지오메트리의 바운딩 박스와 어울려 사용된다.

R-Tree 인덱스에 바운딩 박스를 저장하므로써, 박스들은 빠르게 위치하고, 비교되고, 지오메트리가 디스크로부터 읽혀지기 전에 비교되어질 전체 지오메트리의 숫자는 줄어든다.

우리의 인덱스는 이제 생성되어질 것이다. 데이터베이스가 쿼리 계획을 세울 때 사용되어지는 통계를 업데이트 하기 위해서는 우리는 반드시 다음과 같은 커맨드를 실행하여야 한다.

VACUUM ANALYZE jacksonco_streets;

ANALYZE 커맨드는 PostgreSQL이 테이블을 가로지르고 쿼리 수행계획 추정에 사용될 내부 통계의 업데이트를 요청한다. VACUUM 커맨드는 PostgreSQL이 레코드에 대한 업데이트와 삭제에 의해 남겨진 테이블 페이지에 있는 사용하지 않는 공간의 개선을 요청한다. VACUUM ANALZE 커맨드는 위의 두 가지를 모드 수행한다.

여기서는 VACUUM ANALYZE 커맨드를 이용할 것이며, 이것을 GUI를 통해서 하는 방법도 있다.

테이블에서 오른쪽 마우스를 클릭하고 "관리(M)" 옵션을 선택한다. 선택을 하면 아래 화면과 같이 VACUUM, ANALYZE, REINDEX 커맨드 옵션을 볼 수 있다. VACUUM을 선택하고 ANALYZE 옵션에 체크를 하면 우리가 위에서 했던 쿼리와 같은 것을 수행하게 된다. 이 "관리"화면은 데이터베이스, 테이블, 인덱스 레벨에서 실행될 수 있다.


자 이제 쿼리를 다시 실행 시켜 보자.



이제 우리는 속도의 엄청난 향상을 보았다. 많은 관계 연산자를 PostGIS가 내장형 바운딩 박스 오퍼레이션으로 제공하고 있다. 잠시 다른 공간 컬럼들에도 인덱스를 생성하여 보자

CREATE INDEX jacksonco_schools_gix ON jacksonco_schools USING GIST (the_geom);

CREATE INDEX jacksonco_taxlots_gix ON jacksonco_taxlots USING GIST (the_geom);

CREATE INDEX medford_buildings_gix ON medford_buildings USING GIST (the_geom);

CREATE INDEX medford_citylimits_gix ON medford_citylimits USING GIST (the_geom);

CREATE INDEX medford_hydro_gix ON medford_hydro USING GIST (the_geom);

CREATE INDEX medford_parks_gix ON medford_parks USING GIST (the_geom);

CREATE INDEX medford_planzone_gix ON medford_planzone USING GIST (the_geom);

CREATE INDEX medford_stormdrain_gix ON medford_stormdrain USING GIST (the_geom);

CREATE INDEX medford_wards_gix ON medford_wards USING GIST (the_geom);

CREATE INDEX medford_wetlands_gix ON medford_wetlands USING GIST (the_geom);

CREATE INDEX medford_zoning_gix ON medford_zoning USING GIST (the_geom);

CREATE INDEX tracts_gix ON tracts USING GIST (the_geom);

Vacuuming

PostgreSQL을 효과적으로 사용하려면 인덱스 생성만으로는 부족하다. VACUUM은 새로운 인덱스가 생성되거나 다수의 UPDATE, INSERT, DELETE가 테이블에 생겼을 때, 반드시 수행되어 져야한다. 효율적인 운영을 위해서 PostgreSQL이 "autovacuum" 옵션을 테이블이 업데이트될 때마다 기능을 수행하기 위해서 자동적으로 수행한다는 것은 매우 중요하다.

Autovacuum은 기본 값으로 실행되게 되어 있으며, 활동 레벨에 의해 결정되어 측정되어진 감지할 수 있는 간격마다 vacuum과 analyze(update statistics) 가 테이블에 실행된다. 이것은 많은 트랜젝션이 발생하는 데이터베이스에 필수인 반면에, 인덱스를 추가한 후나, 데이터의 벌크 로딩이 수행된 후 autovacuum을 기다리는 것은 권장하고 싶지 않다.

데이터베이스의 vacuum과 analyzing은 취향에 따라 따로따로 수행되어질 수 있다. VACUUM 커맨드를 수행하는 것은 데이터베이스 통계를 업데이트 시키지는 않는다. 이와 비슷하게 ANALYZE 커맨드를 수행하는 것도 테이블에서 사용하지 않는 행을 복구하는 것은 아니다. 이 두 커맨드 모두 전체 데이터베이스나, 하나의 테이블, 하나의 컬럼에 수행될 수 있다.

Clustering Data

클러스터링(clustering)은 테이블을 인덱스 기반으로 디스크에 저장하게 하는 PostgreSQL의 특징이다. 아래는 숫자나 텍스트 필드 등의 전형적인 B-Tree 인덱스를 이해시키기 위한 간단한 방법이다.

테이블은 필수적으로 초기의 정렬되지 않은 상태에서 정렬된 상태로 재배열 되어 져야 한다. 이것은 유사한 속성들이 같은 페이지에서 찾게 높은 가능성을 부여하고, 특정 쿼리 타입에 메모리에 읽혀져야만 하는 페이지 수를 줄여주는 역할을 해준다. 인덱스가 선형적인 순서를 가지고 있지 않기 때문에, 이것을 공간데이터로 가시화 시키기는 힘들다. 하지만 효과는 같다. 서로 가까이에 있는 지오메트리는 디스크에서도 서로 가까이 있게 된다.

아래 커맨드는 매우 간단하다.
CLUSTER jacksonco_streets USING jacksonco_streets_gix;
데이터베이스통계는 테이블 정렬순서에 대한 정보를 가지고 있기 때문에, 클러스터링 후에 테이블을 Analyze하는 것을 매우 권장되는 바이다. Vacuuming은 불필요 하다.
ANALYZE jacksonco_streets;

공간조인(Spatial join)은 공간데이터베이스(spatial database)와 뗄 수 없는 관계이다. 이번 섹션은 관계 연산자(relation operator)와 약간의 일반적인 사용 예제를 서론으로 제공한다.

공간조인은 두 개 혹은 그 이상의 데이터셋을 공간 릴레이션(spatial relationship)과 관련하여 조합하기 위한 연산이다.

(medford_citylimits)Medford city 에 한정된 jacksonco_schools 테이블의 학교 목록을 얻기 위해서는 다음과 같은 쿼리를 사용할 것이다:

이 쿼리는 각각의 학교가 도시 경계 폴리곤 안에 있는 없는지 확인한다. ST_Within 함수는 각각의 학교가 도시 경계 폴리곤 안에 완전하게 있는지 확인하는 꽤 엄격한 정의를 가지고 있다. 이것은 학교가 경계선에 있으면 기술적으로 폴리곤 안에 있지 않는 것을 의미한다. 이것은 폴리곤과 선을 비교할 때 매우 흥미롭게 한다. 선의 끝점이 경계영역의 에 위치하게 되거나, 선의 일부분이 경계영역에 접하게 되는 것은 false 값을 갖게 됨에 충분하다. ST_Within 함수는 바운딩 박스(bounding box) 중첩 연산자(overlap operator: &&)를 포함하고 있음을 명심하여라.

다음으로는 ST_Length 함수를 이용하여 Medford 도시 도로의 총 길이를 계산해 본다. 우리는 ST_Within 연산자를 한번 더 사용할 수도 있지만, 이것은 완벽하게 포함되었는지 판단하는 요청으로 도로의 길이를 더 짧게 계산할 수도 있다. 차라리 ST_Intersects를 사용하여 도로의 길이를 더 길게 계산하여 본다.

이번에는 공간데이터를 다른 데이터들과 함께 사용해 본다. race 테이블은 인구통계치를 tracts 테이블에 있는 인종에 따라 분석해 놓았다. Jackson Country의 각각의 학교마다 인종별 비율을 결정해 보자.

이번에는 릴레이션 연산자의 성능과 유연성을 볼 차례이다.

Note
인구수는 integer로 저장되기 때문에, 우리는 decimal로 계산하기 위해서 floating 포인트로 변환을 하였다. 이것은 CAST white_pop_1race AS DOUBLE PRECSION과 같이 형변환을 하는 것과 같이 많은 방법으로 수행될 수 있다. 나는 위에서 decimal 값(1.0)을 사용하므로써 변환을 하였다.

아래는 릴레이션 연산자와 그에 대한 짧은 설명이다. 각각의 함수들은 참(true) 혹은 거짓(false) 값을반환할 것이다. 나중 섹션에서 이 함수들을 이용하여 더 좋은 사례를 보여줄 것이다. 이 함수들에 대한 완벽한 설명을 보려면 PostGIS 문서를 참조하여라[1].

ST_Contains(A, B)

Returns true if no points in B lie outside of A, and the interiors of A and B share at least one point, otherwise false.

ST_ContainsProperly(A, B)

Returns true if B intersects the interior of A but not the boundary, otherwise false.

ST_Covers(A, B)

Returns true if no point in B is outside of A, otherwise false.

ST_CoveredBy(A, B)

Returns true if no point in A is outside of B, otherwise false.

ST_Crosses(A, B)

Returns true if A and B share some but not all points in common, otherwise false.

ST_Disjoint(A, B)

Returns true if there are no points in common between A and B, otherwise false.

ST_Intersects(A, B)

Returns true if there are any points in common between A and B, otherwise false.

ST_Overlaps(A, B)

Returns true if the geometries intersect, but are not contained and are of the same dimension (i.e. both lines, both points or both polygons), otherwise false.

ST_Touches(A, B)

Returns true if A and B have at least one point in common but their interiors don't overlap, otherwise false.

ST_Within(A, B)

Returns true if A is completely inside B, otherwise false.