이동형 POI 데이터베이스 설계 가이드 ver1.1

개요

이동형 POI(Point of Interest) 데이터베이스는 관광약자를 위한 장소 정보 시스템입니다. 관광지, 숙박시설, 음식점 등의 장소 정보와 함께 관광약자를 위한 편의시설 정보를 포함하고 있습니다.

데이터베이스 구조

POI 테이블 구조

POI 테이블은 장소의 기본 정보와 상세 정보를 저장합니다.

DDL 정보 (PostgreSQL)

-- POI(Point of Interest) 정보를 저장하는 테이블
-- 관광지, 숙박, 음식점 등의 장소 정보와 관광약자를 위한 편의시설 정보를 포함
-- address_code varchar(5) to varchar(10)
-- create_date not null to null able

CREATE TABLE mv_poi (
    poi_id BIGSERIAL NOT NULL,
    language_code VARCHAR(10) NOT NULL,
    title VARCHAR(200) NOT NULL,
    summary TEXT,
    basic_info TEXT,
    address_code VARCHAR(10),
    address_road VARCHAR(200),
    address_detail VARCHAR(200),
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    detail_json JSONB,
    search_filter_json JSONB,
    publish_date TIMESTAMP,
    update_date TIMESTAMP,
    create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted CHAR(1) NOT NULL DEFAULT 'N',
    is_published CHAR(1) NOT NULL DEFAULT 'N',
    source_organization VARCHAR(100),
    source_id VARCHAR(50),
    CONSTRAINT poi_pkey PRIMARY KEY (poi_id)
);

-- 기본 인덱스 생성
CREATE INDEX idx_language_code ON mv_poi(language_code);
CREATE INDEX idx_address_code ON mv_poi(address_code);
CREATE INDEX idx_location ON mv_poi(latitude, longitude);
CREATE INDEX idx_publish_status ON mv_poi(is_published, is_deleted);

-- JSONB 필드에 대한 GIN 인덱스 생성
CREATE INDEX idx_search_filter_json_gin ON mv_poi USING GIN (search_filter_json);
CREATE INDEX idx_search_filter_json_type_path ON mv_poi USING GIN ((search_filter_json -> 'search_filter'));

기본 필드

JSON 구조

상세 정보 (detail_json)

{
  "phone": "02-761-4078",
  "website": "http://parks.seoul.go.kr/template/sub/yeouido.do",
  "24service": null,
  "languages": "한국어",
  "source_id": 35399,
  "closed_days": "없음",
  "stroller_rent": null,
  "accom_check_in": null,
  "food_signature": null,
  "operation_days": "매일",
  "operation_hour": "상시개방",
  "accom_check_out": null,
  "accom_facilities": null,
  "accom_room_types": null,
  "tour_entrance_fee": "무료",
  "tour_estimate_time": null,
  "tour_facility_info": null,
  "must_see_highlights": null,
  "accessible_facilities": null
}

상세 정보 필드 설명

검색 필터 정보 (search_filter_json)

{
  "phone": "0507-1445-1982",
  "website": "http://jokhwang.co.kr/",
  "24service": null,
  "languages": "한국어",
  "source_id": 35855,
  "closed_days": "없음",
  "stroller_rent": null,
  "accom_check_in": null,
  "food_signature": null,
  "operation_days": "매일",
  "operation_hour": "14:00 - 01:00\r\n*00:30 라스트오더",
  "accom_check_out": null,
  "accom_facilities": null,
  "accom_room_types": null,
  "tour_entrance_fee": "오늘삶은 따뜻한 족발(소(500g)) 32,000원\r\n불맛가득 직화양념 족발(족황 시그니쳐)(소(500g)) 33,000원\r\n\r\n※ 2024. 9. 기준으로 실제 메뉴와 가격이 상이할 수 있음\r\n",
  "tour_estimate_time": null,
  "tour_facility_info": null,
  "must_see_highlights": null,
  "accessible_facilities": null
}

검색 필터 정보 필드 설명

검색 예시

PostgreSQL 검색 쿼리

1. 음식점 리스트 POI 를 조회 한다. sql SELECT (search_filter_json -> 'search_filter' ->> 'restaurant') AS restaurant, mv_poi.* FROM mv_poi WHERE 1 = 1 AND search_filter_json-> 'search_filter' ? 'restaurant' ;
2. 관광지 리스트 POI 를 조회 한다. sql SELECT (search_filter_json -> 'search_filter' ->> 'tourist_spot') AS tourist_spot, mv_poi.* FROM mv_poi WHERE 1 = 1 AND search_filter_json-> 'search_filter' ? 'tourist_spot' ;
3. 쇼핑 리스트 POI 를 조회 한다. sql SELECT (search_filter_json -> 'search_filter' ->> 'shopping') AS shopping, mv_poi.* FROM mv_poi WHERE 1 = 1 AND search_filter_json-> 'search_filter' ? 'shopping' ;

4. 숙박 리스트 POI 를 조회 한다.

   SELECT (search_filter_json -> 'search_filter' ->> 'accommodation') AS accommodation, mv_poi.* FROM mv_poi
   WHERE 1 = 1
   AND search_filter_json-> 'search_filter'  ? 'accommodation'
   ;
   

5. 숙박 리스트 POI 를 조회 한다.

   SELECT (search_filter_json -> 'search_filter' ->> 'tourist_spot') AS tourist_spot, mv_poi.* FROM mv_poi
   WHERE 1=1
   AND search_filter_json-> 'search_filter'  ? 'tourist_spot'
   AND (
   search_filter_json -> 'search_filter' ->> 'tourist_spot' = '자연 · 공원 · 전망대 · 놀이공원'
       OR
   search_filter_json -> 'search_filter' ->> 'tourist_spot' = '역사 · 고궁 · 문화재'
   )
   ;
   

6. 관광지 리스트 POI 를 조회 한다. > 자연 · 공원 · 전망대 · 놀이공원 포함

   SELECT (search_filter_json -> 'search_filter' ->> 'tourist_spot') AS tourist_spot, mv_poi.* FROM mv_poi
   WHERE 1=1
   AND search_filter_json-> 'search_filter'  ? 'tourist_spot'
   AND search_filter_json -> 'search_filter' ->> 'tourist_spot' LIKE '%자연 · 공원 · 전망대 · 놀이공원%'
   ;
   

7. 다누림 POI Migration SQL

   --
   -- 다누림 콘텐츠 조회 SQL Migration 추출
   -- REF
   -- https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html#GUID-F942D202-E4BB-4ED8-997E-AEBD6D8ED8C1
   --
   

SELECT
POI.SOURCE_ORGANIZATION
, POI.SOURCE_ID
, POI.LANGUAGE_CODE
, POI.TITLE
, POI.SUMMARY
, POI.BASIC_INFO
, POI.ADDRESS_CODE
, POI.ADDRESS_ROAD
, POI.ADDRESS_DETAIL
, POI.LATITUDE
, POI.LONGITUDE
-- 상세 정보 JSON
, (SELECT
JSON_OBJECT(
KEY 'source_id' VALUE POST_SN
, KEY 'phone' VALUE CMMN_TELNO
, KEY 'website' VALUE CMMN_HMPG_URL
, KEY 'languages' VALUE CMMN_HMPG_LANG
, KEY 'operation_hour' VALUE CMMN_USE_TIME
, KEY 'operation_days' VALUE CMMN_BSNDE
, KEY 'closed_days' VALUE CMMN_RSTDE
, KEY '24service' VALUE CMMN_SRVC_24H_AT
, KEY 'stroller_rent' VALUE CMMN_BYBGY_LEND_AT
, KEY 'accessible_facilities' VALUE CMMN_DSPSN_CVNTL
, KEY 'must_see_highlights' VALUE CMMN_IMPORTANT
, KEY 'tour_entrance_fee' VALUE TRRSRT_USE_CHRGE_GUIDANCE
, KEY 'tour_facility_info' VALUE TRRSRT_USE_FCLTY_GUIDANCE
, KEY 'tour_estimate_time' VALUE TRRSRT_REQRE_TIME
, KEY 'food_signature' VALUE FD_REPRSNT_MENU
, KEY 'accom_check_in' VALUE STAYNG_CHKIN
, KEY 'accom_check_out' VALUE STAYNG_CHCKT
, KEY 'accom_room_types' VALUE STAYNG_RUM_KND
, KEY 'accom_facilities' VALUE STAYNG_SBRS
) AS DETAIL_INFO
FROM TN_POST_POI_COMM PPC
WHERE PPC.POST_SN = POI.SOURCE_ID ) AS DETAIL_JSON
-- 검색 옵션 정보 JSON
, JSON_OBJECT( KEY 'search_filter' VALUE (
CASE(TO_CHAR(SUBSTR(POI.CTGRY_NM_LST, 1, INSTR(POI.CTGRY_NM_LST, ',') - 1)))
WHEN '음식점' THEN
JSON_OBJECT(
KEY 'restaurant' VALUE SUBSTR(POI.CTGRY_NM_LST,INSTR(POI.CTGRY_NM_LST, ',') + 1)
,KEY 'tourist_type' VALUE POI.WEAK_TYPE_NM_LST
,KEY 'accessibility_facilities' VALUE POI.FAC_TYPE_NM_LST
)
WHEN '관광지' THEN
JSON_OBJECT(
KEY 'tourist_spot' VALUE SUBSTR(POI.CTGRY_NM_LST,INSTR(POI.CTGRY_NM_LST, ',') + 1)
,KEY 'tourist_type' VALUE POI.WEAK_TYPE_NM_LST
,KEY 'accessibility_facilities' VALUE POI.FAC_TYPE_NM_LST
)
WHEN '쇼핑' THEN
JSON_OBJECT(
KEY 'shopping' VALUE SUBSTR(POI.CTGRY_NM_LST,INSTR(POI.CTGRY_NM_LST, ',') + 1)
,KEY 'tourist_type' VALUE POI.WEAK_TYPE_NM_LST
,KEY 'accessibility_facilities' VALUE POI.FAC_TYPE_NM_LST
)
WHEN '숙박' THEN
JSON_OBJECT(
KEY 'accommodation' VALUE SUBSTR(POI.CTGRY_NM_LST,INSTR(POI.CTGRY_NM_LST, ',') + 1)
,KEY 'tourist_type' VALUE POI.WEAK_TYPE_NM_LST
,KEY 'accessibility_facilities' VALUE POI.FAC_TYPE_NM_LST
)
END )) AS SEARCH_FILTER_JSON
, POI.PUBLISH_DATE
, POI.CREATE_DATE
, POI.IS_DELETED
, POI.IS_PUBLISHED

-- , POI.*
FROM (SELECT POST_SN AS SOURCE_ID
, LANG_CODE_ID AS LANGUAGE_CODE
, POST_SJ AS TITLE
, SUMRY AS SUMMARY
-- , REGEXP_REPLACE(REGEXP_REPLACE(CN, '<[^>]+>', ''), '&[A-ZA-Z0-9#]+;', '') AS BASIC_INFO
, REGEXP_REPLACE(CN, '<[^>]+>| ', '', 1, 0, 'i') AS BASIC_INFO
, NEW_ZIP_CODE AS ADDRESS_CODE
, NEW_ADRES AS ADDRESS_ROAD
, NEW_ADRES2 AS ADDRESS_DETAIL
, MAP_POSITION_Y AS LATITUDE
, MAP_POSITION_X AS LONGITUDE
, PBLICTE_DT AS PUBLISH_DATE
, POST_CREAT_DT AS CREATE_DATE
, 'N' AS IS_DELETED
, 'Y' AS IS_PUBLISHED
, 'DANURIM' AS SOURCE_ORGANIZATION
--
-- 카테고리 리스트 정보 콤마 구분으로 표시함
--
, (SELECT RTRIM(
EXTRACT(XMLAGG(XMLELEMENT(e, CTGRY_SN_NM || ',') ORDER BY CTGRY_SN_NM), '//text()').GetClobVal(),
',') AS CTGRY_SN_NM
FROM (SELECT (SELECT CTGRY_NM
FROM TD_TOUR_CTGRY TTC
WHERE TTC.CTGRY_SN = TTCM.CTGRY_SN) AS CTGRY_SN_NM
FROM TD_TOUR_CTGRY_MAPNG TTCM
WHERE TTCM.MAPNG_NO = TNP.POST_SN))
AS CTGRY_NM_LST

-- , (SELECT RTRIM(EXTRACT(XMLAGG(XMLELEMENT(e, CTGRY_SN || ',') ORDER BY CTGRY_SN), '//text()').GetClobVal(),
-- ',') AS CTGRY_SN
-- FROM TD_TOUR_CTGRY_MAPNG TTCM
-- WHERE TTCM.MAPNG_NO = TNP.POST_SN) AS CTGRY_NM_LST

                --
                -- 여행자 유형  리스트 정보 콤마 구분으로 표시함
                --
                , (SELECT RTRIM(
                                  EXTRACT(XMLAGG(XMLELEMENT(e, WEAK_TYPE_NM || ',') ORDER BY WEAK_TYPE_NM), '//text()').GetClobVal(),
                                  ',') AS WEAK_TYPE_NM
                   FROM (SELECT (SELECT CODE_NM
                                 FROM TC_CODE TCD
                                 WHERE 1 = 1
                                   AND TCD.CODE_GROUP_ID = 'TOUR_WEAK_TYPE_CD'
                                   AND TCD.UPPER_CODE_ID = 'TOUR_WEAK_TYPE'
                                   AND TCD.CODE_ID = TIWT.WEAK_TYPE_CD) AS WEAK_TYPE_NM
                         FROM TD_TOUR_INFO_WEAK_TYPE TIWT
                         WHERE TIWT.TOUR_INFO_ID = TNP.POST_SN))                           AS WEAK_TYPE_NM_LST

-- , (SELECT RTRIM(EXTRACT(XMLAGG(XMLELEMENT(e, WEAK_TYPE_CD || ',') ORDER BY WEAK_TYPE_CD), '//text()').GetClobVal(),
-- ',') AS WEAK_TYPE_CD
-- FROM TD_TOUR_INFO_WEAK_TYPE TIWT
-- WHERE TIWT.TOUR_INFO_ID = TNP.POST_SN) AS WEAK_TYPE_CD_LST

                --
                -- 시설 유형  리스트 정보 콤마 구분으로 표시함
                --
                , (SELECT RTRIM(
                                  EXTRACT(XMLAGG(XMLELEMENT(e, FAC_TYPE_NM || ',') ORDER BY FAC_TYPE_NM), '//text()').GetClobVal(),
                                  ',') AS FAC_TYPE_NM
                   FROM (SELECT (SELECT CODE_NM
                                 FROM TC_CODE TCD
                                 WHERE 1 = 1
                                   AND TCD.CODE_GROUP_ID = 'TOUR_FAC_TYPE_CD'
                                   AND TCD.UPPER_CODE_ID = 'TOUR_FAC_TYPE'
                                   AND TCD.CODE_ID = TIFT.FAC_TYPE_CD) AS FAC_TYPE_NM
                         FROM TD_TOUR_INFO_FAC_TYPE TIFT
                         WHERE TIFT.TOUR_INFO_ID = TNP.POST_SN))                           AS FAC_TYPE_NM_LST

-- , (SELECT RTRIM(EXTRACT(XMLAGG(XMLELEMENT(e, FAC_TYPE_CD || ',') ORDER BY FAC_TYPE_CD), '//text()').GetClobVal(),
-- ',') AS FAC_TYPE_CD
-- FROM TD_TOUR_INFO_FAC_TYPE TIFT
-- WHERE TIFT.TOUR_INFO_ID = TNP.POST_SN) AS FAC_TYPE_CD_LST

           FROM TN_POST TNP
           WHERE 1 = 1
             AND POST_TY IN ('D', 'DS')
             AND LANG_CODE_ID = 'ko'
             AND PBLICTE_STTUS_TY = 'P'
             AND ((USE_AT = 'Y') OR (USE_AT = 'P' AND PBLICTE_RESV_DT <= CURRENT_DATE))
             AND POST_SN in (SELECT TOUR_INFO_ID
                             FROM TD_TOUR_INFO
                                      LEFT JOIN TD_TOUR_CTGRY_MAPNG ttcm on TOUR_INFO_ID = ttcm.MAPNG_NO
                             WHERE 1 = 1
                               AND (USE_YN = 'Y' OR USE_YN is null))

-- AND ROWNUM <= 10
) AS POI
;

## 인덱스 정보
- 기본 인덱스
  - `idx_language_code`: 언어 코드
  - `idx_address_code`: 주소 코드
  - `idx_location`: 위도/경도
  - `idx_publish_status`: 발행/삭제 상태

- JSON 검색 인덱스 (GIN)
  - `idx_search_filter_json_gin`: 전체 검색 필터
  - `idx_search_filter_json_type_path`: 함목 검색

## 참고사항
- 모든 날짜/시간은 UTC 기준으로 저장
- 좌표계는 WGS84 사용
- 문자셋은 UTF-8 사용
- JSON 필드는 JSONB 타입 사용 (검색 최적화)