글

작성일자

[PostGIS] 가장 가까운 위치 찾기

시나리오는 어떤 지점에서 가장 가까운 위치를 찾아야 한다.. 입니다. 그런데 위치에 대한 좌표 타입이 geometry가 아니고 일단 실수형이라는 점입니다. 그리고 기준 좌표와 찾아야 하는 좌표의 좌표 체계가 다릅니다. 하나는 구글 좌표계(EPSG:3857)이고 하나는 경위도 좌표계(EPSG:4326)입니다.

아래의 쿼리가 위의 내용에 대한 솔루션 중 하나입니다.

SELECT 
    station_id,
    address,
    lng,
    lat,
    ST_DISTANCE(
        ST_TRANSFORM(
            ('SRID=4326;POINT(' || lng || ' ' || lat || ')')::geometry,
            3857
        ),
        'SRID=3857;POINT(14128453 4506986)'::geometry
    ) AS distance
FROM 
    weather_station
ORDER BY
    distance

즉, 구글 좌표계로 (14128453, 4506986)인 지점과의 거리를 구하고 있습니다. 거리를 구하는 ST_DISTANCE는 서로 다른 좌표계를 사용할 수 없으므로 ST_TRANSFORM을 통해 경위도 좌표를 X,Y 좌표계로 변경하고 있습니다. 결과는 다음과 같습니다.

보시면 가장 첫번째 Row가 가장 가까운 대상이라는 것을 알 수 있습니다.

작성일자

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;

결과는 다음과 같습니다.

덧붙여 TIMESTAMP 타입의 데이터 값을 문자열로 변환하기 위해서는 TO_CHAR를 사용할 수 있습니다. 활용 예로 2025년 4월에 대한 쿼리의 예는 다음과 같습니다.

SELECT 
    *
FROM 
    project 
WHERE 
    TO_CHAR(date_modified, 'YYYYMM') = '202504';