postgresql18.3 로컬 주소DB 구축 (7) - 위치정보 테이블 적재

주소기반산업서비스에서 제공하는 위치정보요약DB를 PostgreSQL 환경에 적재하는 방법을 알아보겠습니다.

방법은 postgresql18.3 로컬 주소DB 구축 (5) - 도로명 주소 테이블 적재 글에서 설명드린 방법과 동일하게 진행합니다.

postgresql18.3 로컬 주소DB 구축 (5) - 도로명 주소 테이블 적재

---

임시 테이블 생성

-- DROP TABLE juso.tmp_juso_location_summary;

-- 임시테이블 생성 (COPY → 임시 → merge into)
CREATE UNLOGGED TABLE IF NOT EXISTS juso.tmp_juso_location_summary (LIKE juso.juso_location_summary INCLUDING DEFAULTS);

-- 임시 테이블에서 불필요한 PK 제약 제거 (COPY 속도를 위해) : INCLUDING ALL로 부모 pk 만들어진 경우
ALTER TABLE IF EXISTS juso.tmp_juso_location_summary DROP CONSTRAINT IF EXISTS pk_juso_location_summary;

---

전체분 적재 프로시저

CREATE OR REPLACE PROCEDURE juso.prc_load_location_full_data()
LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_start      	TIMESTAMP := clock_timestamp();
    v_cnt_data   	BIGINT;
    v_cnt_location  BIGINT;
    v_sido_files 	TEXT[] := ARRAY[
       'busan', 'chungbuk', 'chungnam', 'daegu', 'daejeon', 'gangwon', 'gwangju',
       'gyeongbuk', 'gyeongnam', 'gyunggi', 'incheon', 'jeju', 'jeonbuk', 'jeonnam',
       'sejong', 'seoul', 'ulsan'
    ];
    v_sido          TEXT;
    v_location_path TEXT;
BEGIN
    RAISE NOTICE '[위치정보요약 전체분] 적재 시작: %', v_start;

    -- 임시 테이블 초기화
    TRUNCATE juso.tmp_juso_location_summary;

    -- --------------------------------------------------------
    -- 1-1. 시도별 위치정보요약 전체분 COPY
    --      파일: entrc_seoul.txt, entrc_busan.txt, ...
    -- --------------------------------------------------------
    FOREACH v_sido IN ARRAY v_sido_files LOOP
        v_location_path := format('/juso/download/202602_location_db_full_data/entrc_%s.txt', v_sido);

        RAISE NOTICE '[위치정보 전체분] % COPY 시작', v_sido;

        EXECUTE format(
            $sql$
            COPY juso.tmp_juso_location_summary (
                sigungu_code,
                ent_serial_no,
                legal_emd_code,
                sido_name,
                sigungu_name,
                emd_name,
                road_name_code,
                road_name,
                is_basement,
                building_main_no,
                building_sub_no,
                building_name,
                zip_code,
                building_usage_type,
                is_building_group,
                admin_emd_name,
                ent_x,
                ent_y
            )
            FROM %L
            WITH (
                FORMAT TEXT,
                DELIMITER '|',
                ENCODING 'UHC',
                NULL ''
            )
            $sql$,
            v_location_path
        );

        -- 좌표 point 생성
        UPDATE juso.tmp_juso_location_summary
        SET 
        -- 1. UTM-K 포인트 생성
        -- geom_utm_k = ST_SetSRID(ST_MakePoint(ent_x, ent_y), 5179),
        -- 2. UTM-K 포인트를 WGS84(위경도)로 변환
           geom_wgs84 = ST_Transform(ST_SetSRID(ST_MakePoint(ent_x, ent_y), 5179), 4326)
        WHERE ent_x > 0 AND ent_y > 0;

    END LOOP;

    -- --------------------------------------------------------
    -- 1-2. 임시 → 본 테이블 UPSERT (전체분이므로 TRUNCATE+INSERT 방식)
    -- --------------------------------------------------------
    RAISE NOTICE '[위치정보 전체분] 본 테이블 적재 시작';

    TRUNCATE juso.juso_location_summary;

    INSERT INTO juso.juso_location_summary
	(
            sigungu_code,
            ent_serial_no,
            legal_emd_code,
            sido_name,
            sigungu_name,
            emd_name,
            road_name_code,
            road_name,
            is_basement,
            building_main_no,
            building_sub_no,
            building_name,
            zip_code,
            building_usage_type,
            is_building_group,
            admin_emd_name,
            ent_x,
            ent_y,
            --geom_utm_k,
            geom_wgs84
	)
    SELECT  sigungu_code,
            ent_serial_no,
            legal_emd_code,
            sido_name,
            sigungu_name,
            emd_name,
            road_name_code,
            road_name,
            is_basement,
            building_main_no,
            building_sub_no,
            building_name,
            zip_code,
            building_usage_type,
            is_building_group,
            admin_emd_name,
            ent_x,
            ent_y,
            --geom_utm_k,
            geom_wgs84
    FROM   juso.tmp_juso_location_summary;
    
    GET DIAGNOSTICS v_cnt_data = ROW_COUNT;

    -- --------------------------------------------------------
    -- 1-3. juso_master 위경도 업데이트
    -- --------------------------------------------------------
    RAISE NOTICE '도로명 주소테이블 위경도정보 업데이트';

    UPDATE juso.juso_master m
    SET
        latitude = ST_Y(l.geom_wgs84),
        longitude = ST_X(l.geom_wgs84),
        geom_wgs84 = l.geom_wgs84
    FROM juso.juso_location_summary l 
    WHERE m.road_name_code     = l.road_name_code
        AND m.building_main_no = l.building_main_no
        AND m.building_sub_no  = l.building_sub_no
        AND m.is_basement      = l.is_basement;

    GET DIAGNOSTICS v_cnt_location = ROW_COUNT;

    -- --------------------------------------------------------
    -- 1-4. 인덱스 갱신 및 통계
    -- --------------------------------------------------------
    ANALYZE juso.juso_location_summary;

    RAISE NOTICE '=====================================================';
    RAISE NOTICE '[위치정보요약 전체분] 적재 완료';
    RAISE NOTICE '  juso_location_summary: % 건', v_cnt_data;
    RAISE NOTICE '  juso_master location : % 건', v_cnt_location;
    RAISE NOTICE '  소요시간: %', clock_timestamp() - v_start;
    RAISE NOTICE '=====================================================';

EXCEPTION WHEN OTHERS THEN
    RAISE EXCEPTION '[위치정보요약 전체분] 오류 발생: % / %', SQLERRM, SQLSTATE;
END;
$procedure$
;

--COMMENT ON PROCEDURE juso.prc_load_location_full_data() IS
--'위치정보요약DB 전체분 적재. /juso/download/202602_location_db_full_data/경로의 시도별 txt 파일을 읽어 juso_location_summary에 적재';

---

변동분 적재 프로시저

CREATE OR REPLACE PROCEDURE juso.prc_load_location_delta_data()
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_start         TIMESTAMP := clock_timestamp();
    v_cnt_data      BIGINT;
    v_cnt_location  BIGINT;

    v_location_path TEXT := '/juso/download/202602_location_db_delta_data/entrc_mod.txt';
BEGIN
    RAISE NOTICE '[위치정보요약 변동분] 적재 시작: %', v_start;

    -- 임시 테이블 초기화
    TRUNCATE juso.tmp_juso_location_summary;

    -- --------------------------------------------------------
    -- 2-1. 위치정보 변동분 COPY
    -- --------------------------------------------------------
    RAISE NOTICE '[위치정보 변동분] COPY 시작';

    EXECUTE format(
        $sql$
        COPY juso.tmp_juso_location_summary (
            sigungu_code,
            ent_serial_no,
            legal_emd_code,
            sido_name,
            sigungu_name,
            emd_name,
            road_name_code,
            road_name,
            is_basement,
            building_main_no,
            building_sub_no,
            building_name,
            zip_code,
            building_usage_type,
            is_building_group,
            admin_emd_name,
            ent_x,
            ent_y,
            move_reason_code
        )
        FROM %L
        WITH (
            FORMAT TEXT,
            DELIMITER '|',
            ENCODING 'UHC',
            NULL ''
        )
        $sql$,
        v_location_path
    );
    
    -- 좌표 point 생성
    UPDATE juso.tmp_juso_location_summary
    SET 
    -- 1. UTM-K 포인트 생성
    -- geom_utm_k = ST_SetSRID(ST_MakePoint(ent_x, ent_y), 5179),
    -- 2. UTM-K 포인트를 WGS84(위경도)로 변환
       geom_wgs84 = ST_Transform(ST_SetSRID(ST_MakePoint(ent_x, ent_y), 5179), 4326)
    WHERE ent_x > 0 AND ent_y > 0;

    -- --------------------------------------------------------
    -- 2-2. juso_location_summary 변동분 처리
    --      신규(31)  : INSERT
    --      변경(34)  : UPDATE
    --      폐지(63)  : DELETE
    -- --------------------------------------------------------
    MERGE INTO juso.juso_location_summary t
    USING (
        SELECT
            sigungu_code,
            ent_serial_no,
            legal_emd_code,
            sido_name,
            sigungu_name,
            emd_name,
            road_name_code,
            road_name,
            is_basement,
            building_main_no,
            building_sub_no,
            building_name,
            zip_code,
            building_usage_type,
            is_building_group,
            admin_emd_name,
            ent_x,
            ent_y,
            move_reason_code,
            --geom_utm_k,
            geom_wgs84
        FROM juso.tmp_juso_location_summary
        WHERE move_reason_code IN ('31', '34', '63')
    ) AS s 
	ON (
            t.road_name_code = s.road_name_code
        AND t.is_basement = s.is_basement
        AND t.building_main_no = s.building_main_no
        AND t.building_sub_no = s.building_sub_no
        AND t.legal_emd_code = s.legal_emd_code)
    WHEN MATCHED AND s.move_reason_code = '34' THEN -- 변경
        UPDATE SET 
	        sigungu_code = s.sigungu_code,
            ent_serial_no = s.ent_serial_no,
            legal_emd_code = s.legal_emd_code,
            sido_name = s.sido_name,
            sigungu_name = s.sigungu_name,
            emd_name = s.emd_name,
            road_name_code = s.road_name_code,
            road_name = s.road_name,
            is_basement = s.is_basement,
            building_main_no = s.building_main_no,
            building_sub_no = s.building_sub_no,
            building_name = s.building_name,
            zip_code = s.zip_code,
            building_usage_type = s.building_usage_type,
            is_building_group = s.is_building_group,
            admin_emd_name = s.admin_emd_name,
            ent_x = s.ent_x,
            ent_y = s.ent_y,
            move_reason_code = s.move_reason_code,
            --geom_utm_k = s.geom_utm_k,
            geom_wgs84 = s.geom_wgs84,
	        updated_at = NOW()
    WHEN MATCHED AND s.move_reason_code = '63' THEN -- 폐지
        DELETE
    WHEN NOT MATCHED AND s.move_reason_code = '31' THEN -- 신규
        INSERT (
            sigungu_code,
            ent_serial_no,
            legal_emd_code,
            sido_name,
            sigungu_name,
            emd_name,
            road_name_code,
            road_name,
            is_basement,
            building_main_no,
            building_sub_no,
            building_name,
            zip_code,
            building_usage_type,
            is_building_group,
            admin_emd_name,
            ent_x,
            ent_y,
            move_reason_code,
            --geom_utm_k,
            geom_wgs84
        )
        VALUES (
            s.sigungu_code,
            s.ent_serial_no,
            s.legal_emd_code,
            s.sido_name,
            s.sigungu_name,
            s.emd_name,
            s.road_name_code,
            s.road_name,
            s.is_basement,
            s.building_main_no,
            s.building_sub_no,
            s.building_name,
            s.zip_code,
            s.building_usage_type,
            s.is_building_group,
            s.admin_emd_name,
            s.ent_x,
            s.ent_y,
            s.move_reason_code,
            --geom_utm_k,
            geom_wgs84);
            
    GET DIAGNOSTICS v_cnt_data = ROW_COUNT;
    
    -- --------------------------------------------------------
    -- 2-3. juso_master 위경도 업데이트
    -- --------------------------------------------------------
    RAISE NOTICE '도로명 주소테이블 위경도정보 업데이트';

    UPDATE juso.juso_master m
    SET
        latitude = ST_Y(l.geom_wgs84),
        longitude = ST_X(l.geom_wgs84),
        geom_wgs84 = l.geom_wgs84
    FROM juso.tmp_juso_location_summary l 
    WHERE m.road_name_code     = l.road_name_code
        AND m.building_main_no = l.building_main_no
        AND m.building_sub_no  = l.building_sub_no
        AND m.is_basement      = l.is_basement;

    GET DIAGNOSTICS v_cnt_location = ROW_COUNT;

    -- --------------------------------------------------------
    -- 2-4. 통계 갱신
    -- --------------------------------------------------------
    ANALYZE juso.juso_location_summary;

    RAISE NOTICE '=====================================================';
    RAISE NOTICE '[위치정보요약 변동분] 적재 완료';
    RAISE NOTICE '  juso_location_summary : % 건', v_cnt_data;
    RAISE NOTICE '  juso_master location  : % 건', v_cnt_location;
    RAISE NOTICE '  소요시간: %', clock_timestamp() - v_start;
    RAISE NOTICE '=====================================================';

EXCEPTION WHEN OTHERS THEN
    RAISE EXCEPTION '[위치정보요약 변동분] 오류 발생: % / %', SQLERRM, SQLSTATE;
END;
$procedure$
;

--COMMENT ON PROCEDURE juso.prc_load_location_delta_data() IS
--'위치정보요약DB 변동분 적재. /juso/download/202602_location_db_delta_data\ 변경분 txt 파일을 읽어 juso_location_summary를 MERGE INTO';