GIS Developer

공간 서버 단에서 kml 문서를 생성해 주는 기능을 개발하면서 Point, Polyline, Polygon 요소에 대한 kml 문서를 생성해 주는 Java 라이브러리를 만들어 사용했는데요. 이에 대해 공개합니다. 단순히 좌표에 대한 형상 정보만을 생성해 주는 라이브러리이고 style과 같은 내용은 아직 지원하지 않습니다. 이 라이브러리의 클래스 구조도는 다음과 같습니다.

이 라이브러리를 이용해 Point에 대한 kml 문서를 생성해 주는 코드의 예는 다음과 같습니다.

import java.util.LinkedList;

import geoservice.kmlwriter.Coordinate;
import geoservice.kmlwriter.Document;
import geoservice.kmlwriter.IGeometry;
import geoservice.kmlwriter.LineString;
import geoservice.kmlwriter.LinearRing;
import geoservice.kmlwriter.Placemark;
import geoservice.kmlwriter.Point;
import geoservice.kmlwriter.Polygon;

public class MainEntry {

    private static String testPointPlaceMarkKML() {
        Document kmlDoc = new Document();

        Coordinate coord = new Coordinate(128, 38); //(경도, 위도)
        IGeometry geom = new Point(coord);
        Placemark placemark = new Placemark(geom, "My Point Placemark");
        kmlDoc.addPlacemark(placemark);

        String kml = kmlDoc.build();
		
        return kml;
    }

    ....

Polyline에 대한 kml 문서를 생성해 주는 코드는 아래와 같습니다.

import java.util.LinkedList;

import geoservice.kmlwriter.Coordinate;
import geoservice.kmlwriter.Document;
import geoservice.kmlwriter.IGeometry;
import geoservice.kmlwriter.LineString;
import geoservice.kmlwriter.LinearRing;
import geoservice.kmlwriter.Placemark;
import geoservice.kmlwriter.Point;
import geoservice.kmlwriter.Polygon;

public class MainEntry {

    private static String testPolylinePlaceMarkKML() {
        Document kmlDoc = new Document();
		
        LinkedList<Coordinate> coords = new LinkedList<Coordinate>();
        coords.add(new Coordinate(128, 38));
        coords.add(new Coordinate(129, 38));
        coords.add(new Coordinate(129, 39));

        IGeometry kmlGeom = new LineString(coords);
        Placemark placemark = new Placemark(kmlGeom, "My Polyline Placemark");
        kmlDoc.addPlacemark(placemark);
		
        String kml = kmlDoc.build();
		
        return kml;
    }

    ....

Polygon에 대한 kml 문서를 생성해 주는 코드입니다.

import java.util.LinkedList;

import geoservice.kmlwriter.Coordinate;
import geoservice.kmlwriter.Document;
import geoservice.kmlwriter.IGeometry;
import geoservice.kmlwriter.LineString;
import geoservice.kmlwriter.LinearRing;
import geoservice.kmlwriter.Placemark;
import geoservice.kmlwriter.Point;
import geoservice.kmlwriter.Polygon;

public class MainEntry {
	
    private static String testPolygonPlaceMarkKML() {
        Document kmlDoc = new Document();
        LinkedList<LinearRing> rings = new LinkedList<LinearRing>();

        LinkedList<Coordinate> outer_coords = new LinkedList<Coordinate>();
        outer_coords.add(new Coordinate(128, 38));
        outer_coords.add(new Coordinate(129, 38));
        outer_coords.add(new Coordinate(129, 39));
        outer_coords.add(new Coordinate(128, 39));
        outer_coords.add(new Coordinate(128, 38));

        LinkedList<Coordinate> inner_coords = new LinkedList<Coordinate>();
        inner_coords.add(new Coordinate(128.25, 38.25));
        inner_coords.add(new Coordinate(128.75, 38.25));
        inner_coords.add(new Coordinate(128.75, 38.75));
        inner_coords.add(new Coordinate(128.25, 38.75));
        inner_coords.add(new Coordinate(128.25, 38.25));

        rings.add(new LinearRing(outer_coords, true));
        rings.add(new LinearRing(inner_coords, false));

        IGeometry kmlGeom = new Polygon(rings);
        Placemark placemark = new Placemark(kmlGeom, "My Polygon Placemark");
        kmlDoc.addPlacemark(placemark);

        String kml = kmlDoc.build();

        return kml;
    }

    ....

폴리곤의 외부와 내부에 대한 부분은 35번과 36번처럼 LinearRing 클래스의 생성자의 두번째 인자에서 지정할 수 있습니다. 위의 Polygon에 대한 kml 생성 예제 코드를 통해 생성된 kml 문서를 구글어스에서 살펴보면 아래 화면에 같습니다.

이 라이브러리의 소스코드는 아래의 링크를 통해 다운로드 받을 수 있습니다.

PL/pgSQL로 만든 함수가 질의(Query)의 결과로써 테이블(Table)을 반환할 수 있습니다. 함수가 테이블을 반환한다는 의미가 어떤 것인지 실제 코드 예를 통해 보면 쉽게 이해할 수 있습니다. 먼저 예제 코드에서 사용할 person이라는 테이블이 아래와 같다고 합시다.

person 테이블에서 나이값을 지정하면 지정된 나이 이상인 레코드를 질의하여 그 결과를 반환하는 함수를 만들어 보겠습니다.

CREATE OR REPLACE FUNCTION get_persons(v INT)
RETURNS TABLE (f_name CHAR(20), f_age INT)
AS $$
BEGIN
    RETURN QUERY SELECT 
        name, age
    FROM person
    WHERE age >= v;
END; $$
LANGUAGE 'plpgsql';

위의 코드를 실행한 화면이 아래와 같은데요. get_persons(30)을 호출했으므로 30세 이상인 사람만 검색되는 것을 볼 수 있습니다.

코드에 대해 살펴보면, 이 함수는 v라는 인자를 받고 있으며 2번 코드에 반환 타입이 TABLE이라고 명시되어 있습니다. TABLE 구문 바로 뒤어 테이블의 스키마를 정의하고 있는데요. 반환하는 테이블의 스키마가 CHAR(20) 타입의 f_name과 INT 타입의 f_age 필드를 갖습니다. 실제 쿼리는 5번 코드의 QUERY 구분 바로 다음에 SELECT 구문을 지정하고 있습니다.

사실, 질의한 쿼리 결과를 그대로 반환하기 보다는 쿼리 결과에 무언가 연산을 수행해 재가공한 결과를 반환하는 것이 일반적입니다.

CREATE OR REPLACE FUNCTION get_persons(v INT)
RETURNS TABLE (f_name CHAR(20), f_age INT)
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT name, age 
             FROM person
             WHERE age >= v)
     LOOP
         f_name := r.name || '(' || r.age || ')';
         f_age := r.age - 1;
         RETURN NEXT;
     END LOOP;
END; $$
LANGUAGE 'plpgsql';

위의 함수를 보면 이름과 나이를 조합한 문자열에 대한 필드와 실제 나이에서 한 살을 뺀 나이값 필드에 대한 테이블을 반환하는 함수입니다. 실행 결과는 다음과 같습니다.

안녕하세요, GIS Developer 김형준입니다. 이번 글에서는 PL/pgSQL에서 반복문에 대해 살펴보겠습니다. PL/pgSQL에서 제공하는 반복문은 LOOP, WHILE, FOR 문이 있는데요. 하나씩 살펴보도록 하겠습니다.

먼저 예제 코드를 통해 LOOP 문을 살펴 보도록 하겠습니다.

CREATE OR REPLACE FUNCTION adder(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    res INTEGER := 0;
    i INTEGER;
BEGIN
    i := 1;

    LOOP
        res := res + i;
        
        EXIT WHEN i = n;
        
        SELECT i+1 INTO i;
    END LOOP;

    RETURN res;
END;
$$ LANGUAGE plpgsql;

위의 adder 함수는 1부터 인자로 주어진 정수값까지의 합한 누적값을 반환하는 함수입니다. 9번~15번 까지가 LOOP 반복문인데요. 필수 조건은 아니지만 반복문은 반복을 끝내기 위한 조건이 필요합니다. 12번 코드가 바로 반복문을 종료하기 위한 조건 코드로 i 값과 n 값이 같으면 반복문을 종료하게 됩니다. 여기서 14번 코드가 재미있는데요. 선택한(SELECT) 값(i+1)을 원하는 변수(i) 안으로(INTO) 대입시켜 주는 구문입니다. 이 SELECT i+1 INTO i; 문은 i := i + 1; 과 같습니다.

실행 결과는 아래와 같습니다.

다음은 WHILE 반복문을 통해 위의 adder 함수를 재작성해 보도록 하겠습니다. 아래와 같습니다.

CREATE OR REPLACE FUNCTION adder(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    result INTEGER := 0;
    i INTEGER;
BEGIN
    i := 1;
    
    WHILE i <= n LOOP
        SELECT result + i INTO result;

        SELECT i+1 INTO i;
    END LOOP;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

WHILE 반복문에 해당하는 코드는 9번 ~ 13번인데요. WHILE 반복문은 반복문의 시작과 함께 반복 조건이 지정됩니다. 이 반복 조건이 참(true)일 때 반복문 안의 코드(10번~12번)이 실행되다가, 반복 조건이 거짓(false)가 될때 반복문을 탈출합니다.

다음은 반복문 중 가장 유연한 FOR 문입니다. FOR 반복문으로 여기 adder 함수를 재작성해 보도록 하겠습니다. 아래와 같습니다.

CREATE OR REPLACE FUNCTION adder(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    result INTEGER := 0;
BEGIN
    FOR i IN 1..n LOOP
        RAISE NOTICE 'Iterator: %', i;
        result := result + i;
    END LOOP;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

위의 코드에서 반복문에 해당하는 코드는 6번~9번입니다. 반복 조건을 FOR 문에서 지정하고 있는데요. i 변수를 1..n까지, 즉 만약 n 인자값을 10이라고 한다면 i 변수는 총 10번 반복되며 각 반복에서 i의 값은 1씩 증가되어 각각 1, 2, 3, 4, 5, 6, 7, 8, 9, 10이 됩니다. 아래의 실행을 통해 이러한 내용을 살펴볼 수 있습니다.

만약 FOR 문의 반복 조건이 큰 값에서 작은 값으로 진행된다면 다음처럼 FOR 문에 REVERSE를 붙여줘야 합니다.

CREATE OR REPLACE FUNCTION adder(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    result INTEGER := 0;
BEGIN
    FOR i IN REVERSE n..1 LOOP
        RAISE NOTICE 'Iterator: %', i;
        result := result + i;
    END LOOP;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

실행해 보면 아래와 같은 결과를 볼 수 있습니다.

위의 결과를 보면, i 값에 대한 Iteration이 10부터 1까지 감소하면서 반복되는 것을 볼 수 있습니다.

이처럼 FOR 문의 증감값은 1인데요. 이 값은 변경할 수 있습니다. 다음 코드는 증가값을 2로 지정하고 있는 FOR 문입니다.

CREATE OR REPLACE FUNCTION adder(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    result INTEGER := 0;
BEGIN
    FOR i IN 1..n BY 2 LOOP
        RAISE NOTICE 'Iterator: %', i;
        result := result + i;
    END LOOP;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

실행해 보면, i 값의 반복이 1부터 시작해서 2씩 증가함으로써 3, 5, 7, 9가 되며, 아래의 실행 결과를 통해 확인할 수 있습니다.

PG/pgSQL에서 반복문의 백미는 데이타베이스의 테이블에 대한 쿼리에 대한 반복입니다.

CREATE OR REPLACE FUNCTION avg_ages()
RETURNS NUMERIC AS $$
DECLARE
    r RECORD;
    total NUMERIC := 0;
    count INTEGER := 0;
BEGIN
    FOR r IN SELECT age FROM person
    LOOP
        total := total + r.age;
        count := count + 1;
    END LOOP;

    RETURN total / count;
END;
$$ LANGUAGE plpgsql;

SELECT 문을 통한 결과셋의 반복을 위해 8번 코드의 FOR 문에서 RECORD 타입의 r 변수와 쿼리문이 필요합니다. 반복문 안에서 쿼리 결과에 대한 Row의 필드값 접근을 위해 {RECORD Type 객체}.{필드명}과 같이 접근할 수 있는데요. 위의 코드에서는 r.age와 같이 접근하고 있습니다. 실행 결과는 아래와 같습니다.

SELECT 문을 동적 쿼리로 실행할 수 있습니다. 예를 들어 아래의 코드를 살펴보면..

CREATE OR REPLACE FUNCTION avg_ages(n INTEGER)
RETURNS NUMERIC AS $$
DECLARE
    r RECORD;
    total NUMERIC := 0;
    query TEXT;
BEGIN
    query := 'SELECT age FROM person LIMIT $1';
    FOR r IN EXECUTE query USING n
    LOOP
        total := total + r.age;
    END LOOP;

    RETURN total / n;
END;
$$ LANGUAGE plpgsql;

쿼리문에 해당하는 문자열에 대한 변수가 8번 코드입니다. 즉, $1이 동적으로 변하는 부분인데요. 쿼리문 실행 후 Row의 개수를 제한하려고 하는 것입니다. 이러한 동적 쿼리를 FOR 문에서 실행하는 것은 9번 코드입니다. %1에 해당하는 값은 FOR 문에서 USING 문을 사용해 지정할 수 있습니다.

안녕하세요, GIS Developer 김형준입니다. 이번 포스트에서는 조건문 중 CASE 문에 대해 살펴 보겠습니다. CASE 문의 설명을 위해 실제 DBMS에 저장된 Table을 활용해 설명하겠습니다. 사용할 테이블은 다음과 같습니다.

PERSON이라는 이름의 테이블에 총 5개의 Row가 저장되어 있는 간단한 데이터입니다.

CASE 문의 설명을 위해 CASE를 활용한 아래와 같은 함수를 만들어 보겠습니다.

CREATE OR REPLACE FUNCTION get_surname(part_name CHAR(20)) 
RETURNS CHAR(2) AS $$
DECLARE 
    full_name CHAR(20);
    surname CHAR(1);
BEGIN
    SELECT INTO full_name name
    FROM person
    WHERE name LIKE '%' || part_name || '%';
    
    IF full_name IS NOT NULL THEN
        surname = substr(full_name, 1, 1);
        
        CASE surname
        WHEN '김' THEN
            RETURN '金氏';
        WHEN '이' THEN
            RETURN '理氏';
        WHEN '박' THEN
            RETURN '博氏';
        WHEN '최' THEN
            RETURN '崔氏';
        ELSE
            RETURN '모름'; 
        END CASE;
        
    	RETURN surname;
    ELSE
		RETURN NULL;
	END IF;
END; $$
LANGUAGE plpgsql;

위의 함수에 대한 코드 중 14번에 CASE 조건절이 보입니다. surname 변수에는 조회된 이름의 성에 대한 1개의 문자가 할당됩니다. 이 문자에 따라 한자로 그 결과를 반환해 주게 되는데요. 14번의 CASE에 조건과 비교할 대상 값을 담고 있는 변수를 지정하고, 15번과 17번 등의 WHEN에 일치하는 값을 지정합니다. 조건과 일치하는 WHEN을 만나면 WHEN의 밑에 존재하는 코드 부분이 실행됩니다. 다음은 실행 결과입니다.

위의 경우처럼 정확한 값의 일치에 대한 조건 비교가 아닌 어떤 값의 범위에 대한 조건 비교에도 CASE와 WHEN을 사용할 수 있습니다. 이에 대한 다음의 코드는 아래와 같습니다.

CREATE OR REPLACE FUNCTION get_age_level(part_name CHAR(20)) 
RETURNS CHAR(20) AS $$
DECLARE 
    the_age INTEGER;
BEGIN
    SELECT INTO the_age age
    FROM person
    WHERE name LIKE '%' || part_name || '%';
    
    CASE
        WHEN the_age < 10 THEN
            RETURN '어린아이';
        WHEN the_age >= 10 AND the_age < 20 THEN
            RETURN '10대 사춘기';
        WHEN the_age >= 20 AND the_age < 30 THEN
            RETURN '20대 취준생';
        WHEN the_age >= 30 AND the_age < 40 THEN
            RETURN '30대 청춘';        
        ELSE
            RETURN '불노장생';
    END CASE;
END; $$
LANGUAGE plpgsql;

get_age_level은 인자로 지정한 이름과 유사한 Row의 age 필드값의 범위에 따라 각기 그 나이대를 표현하는 문자열을 반환합니다. 이때 나이 범위에 대한 조건을 CASE WHEN 구문으로 분류하고 있습니다. 아래는 실행 결과입니다.