글

작성일자

PostgreSQL의 TimeStamp 타입에 대한 쿼리

PostgreSQL에서 날짜와 시간을 하나의 필드로 저장할 수 있는 타입은 Timestamp입니다. 프로젝트를 진행하면서 이 TimeStamp에 대한 쿼리에 대해 정리해 보고자 합니다. 사용한 테이블은 여러분과 다를 수 있으니 쿼리 결과에 대해서는 유연하게 참고하시기 바랍니다. 그리고 Timestamp 타입의 필드는 sample_time입니다.

먼저 2021년 8월 21일 12시 30분 이후에 발생한 데이터를 얻어오기 위한 쿼리의 예는 다음과 같습니다.

SELECT 
    * 
FROM 
    earthquake 
WHERE 
    sample_time > TO_TIMESTAMP('20210821 12:30', 'YYYYMMDD HH24:MI');

DB 테이블에는 8월 데이터만 저장되어 있는데, 결과는 다음과 같습니다.

그리고 2021년 8월 2일에 대한 데이터만을 뽑고자 할때의 쿼리 예는 다음과 같습니다.

SELECT 
    *
FROM 
    weather 
WHERE 
    weather_station_id = 170 AND sample_time::DATE = TO_TIMESTAMP('20210802', 'YYYYMMDD');

결과는 다음과 같았습니다.

마지막으로 일자별로 평균을 내기 위한 쿼리는 다음과 같습니다.

SELECT 
    sample_time::DATE, AVG(rainfall), AVG(humidity), AVG(ground_heat)
FROM 
    weather 
WHERE 
    weather_station_id = 170 
GROUP BY 
    sample_time::DATE 
ORDER BY 
    sample_time::DATE;

결과는 다음과 같습니다.

작성일자

PostgreSQL의 문자열 암호화

PostgreSQL 자체 기능 만으로 어떤 문자열을 암호화해서 저장하거나 암호화된 데이터를 다시 원래의 문자열로 복호화 하기 위해서는 pgcrypto라는 확장 기능을 활용하면 됩니다.

먼저 PostgreSQL에서 다음 명령을 통해 확장 기능을 설치할 수 있습니다. 저 같은 경우 PostgreSQL v10을 사용하였습니다.

CREATE EXTENSION pgcrypto;

이제 어떤 문자열을 암호화하는 방법은 다음과 같습니다.

SELECT 
    encode(
        encrypt(
            convert_to('GIS 개발자, 김형준', 'utf8'),
                'encoding_key',
                'aes'
            ),
        'hex'
    );

위의 쿼리는 “GIS 개발자, 김형준” 문자열을 “encoding_key”를 암호화 키로 사용하여 AES 알고리즘으로 암호화하는 것입니다. 결과는 다음과 같습니다.

이제 다시 암호화된 데이터를 원래의 문자열로 복호화 하는 방법은 아래와 같습니다.

SELECT 
    convert_from(
        decrypt(
            decode('12773b7766896ab35aed6b0b5be174d3b4c57c4bde2fc88a982b12946b500943','hex'),
            'decoding_key',
            'aes'
        ),
        'utf8'
    );

결과는 다음과 같습니다.

작성일자

크리깅(Kriging) 분석에 대한 색상 시각화

아래의 결과는 크리깅(Kriging) 분석을 통한 보간 결과(공간상의 숫자값은 보간을 위한 입력 값이 아닌 단순 번호임)입니다. 크리깅 보간의 결과값의 범위가 최대값 7.4, 최소값 0.6이 산출된 경우입니다. 최대값은 빨간색으로 최소값은 초록색으로 표현하고 있습니다.

단순히 시각적인 관점으로 보면 나쁘진 않은데, 문제는.. 빨간색은 위험 범위의 값일 경우 표현에 사용하고자 한다는데 있습니다. 위험 수위 값은 10부터라고 정의 했을때.. 위의 경우 최대값인 7.4는 위험 수위값이 아니므로 빨간색으로 표현되면 사용자에게 혼란을 야기합니다.

위의 문제점을 개선한 동일한 크리깅 결과를 기대한 색상으로 시각화한 결과는 아래와 같습니다.

모든 지점이 10보다 작으므로 위험을 나타내는 빨간색으로 표현되지 않습니다. 바로 이게 원하는 결과입니다.

이러한 결과를 얻기 위한 색상을 정의하는 코드는 다음과 같습니다.

const colors = [
    { value: 10, steps: 10, color: [255, 0, 0, 200] },
    { value: 5, steps: 10, color: [255, 255, 0, 200] },
    { value: 0, steps: 10, color: [0, 255, 0, 200] },
    { value: -5, steps: 10, color: [0, 255, 255, 200] },
    { value: -10, color: [0, 0, 255, 200] },
];

const clrTbl = new Xr.RangesColorTable(colors);
if (clrTbl.build()) {
    gridLyr.updateByRangesColorTable(clrTbl, psd);
    map.update();
}

위의 코드는 공간 데이터를 시각화 해주는 FingerEyes-Xr에 대한 코드입니다. FingerEyes-Xr은 자바스크립트(js) 기반의 웹 GIS 라이브러리입니다.