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

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

## 데이터베이스 구조

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

#### DDL 정보 (PostgreSQL)
```sql
-- 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'));
```



#### 기본 필드
| 필드명 | 타입 | 설명 | 필수 여부 |
|--------|------|------|-----------|
| poi_id | BIGINT | POI 아이디 | 필수 |
| language_code | VARCHAR(10) | 언어 코드 | 필수 |
| title | VARCHAR(200) | 제목 | 필수 |
| summary | TEXT | 요약 정보 | 선택 |
| basic_info | TEXT | 기본 정보 | 선택 |
| address_code | VARCHAR(5) | 주소 코드 | 선택 |
| 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 | 생성일 | 필수 |
| is_deleted | CHAR(1) | 삭제 여부(Y/N) | 필수 |
| is_published | CHAR(1) | 발행 여부(Y/N) | 필수 |
| source_organization | VARCHAR(100) | 출처 기관 | 선택 |
| source_id | VARCHAR(50) | 출처 아이디 | 선택 |

### JSON 구조

#### 상세 정보 (detail_json)
```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
}
```

#### 상세 정보 필드 설명
| 필드 | 설명 | 예시 값 |
|-----------|------|----------|
| source_id | 입력 출처 아이디 | 35399 |
| website | 웹사이트 주소 | "www.example.com" |
| phone | 전화번호 | "02-1111-2222" |
| languages | 지원 언어 목록 | "korean,english" |
| operation_days | 운영일자  | "월,화,수,목,금" |
| operation_hour | 운영일자  | "10:00~18:00" |
| 24service | 24시간 여부  | "없음" |
| closed_days | 휴일  | "없음" |
| stroller_rent | 유모차 렌트  | "가능" |
| accessible_facilities | 장애인시설 | "장애인 화장실 가능" |
| must_see_highlights | 필수 확인 사항 | "엘리베이터는 건물 오른편에 있습니다." |
| food_signature | 대표음식  | "닭갈비" |
| accom_check_in | 숙박 체크인  | "13:00 체크인 " |
| accom_check_out | 숙박 체크아웃  | "11:00 체크아웃" |
| accom_facilities | 부대시설  | "탁구장, 당구장" |
| accom_room_types | 룸유형  | "스위트룸" |
| tour_entrance_fee | 관광 입장료  | "무료(기획 전시 제외)" |
| tour_estimate_time | 관광 예상 소요시간  | "2시간" |
| tour_facility_info | 관광 시설 정보  | "근처 스타벅스 있음" |


#### 검색 필터 정보 (search_filter_json)
```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
}
```

#### 검색 필터 정보 필드 설명
| 상위 필드 | 하위 필드 | 설명 | 가능한 값 |
|-----------|-----------|------|------------|
| search_filter  ||||
|   | restaurant | 음식점| 한식 ex) 한식,양식...가능 |
|   |||양식|
|   |||중식|
|   |||일식|
|   |||동남아식|
|   |||퓨전|
|   |||카페|
|   |||뷔페|
|   |||비건/할랄|
|   |||기타|
|   | tourist_spot | 관광지| 역사 · 고궁 · 문화재 ex)체험 · 공예,안내소,...기타 |
|   |||체험 · 공예|
|   |||전시 · 공연 · 관람|
|   |||자연 · 공원 · 전망대 · 놀이공원|
|   |||휴양 · 캠핑|
|   |||안내소|
|   |||기타|
|   | shopping | 쇼핑| 백화점·쇼핑몰 ex)전통시장,기념품점...기타 |
|   |||전통시장|
|   |||기념품점|
|   |||기타판매점|
|   |||기타|
|   | accommodation | 숙박| 호텔 ex) 콘도,모텔,...기타|
|   |||콘도|
|   |||모텔|
|   |||펜션|
|   |||기타|
|   | tourist_type | 관광약자유형|고령자 ex) 보행장애,시각장애,...청각장애|
|   |||보행장애|
|   |||시각장애|
|   |||영유아 및 동반자|
|   |||청각장애|
|   |||기타|
|   | accessibility_facilities | 관광약자 편의시설 선택|휠체어 접근 가능 안내시설 있음| 
|   |||장애인 엘리베이터 있음| 
|   |||휠체어·유아차 대여 가능| 
|   |||휠체어 접근 가능 매표소 있음| 
|   |||장애인 화장실 있음| 
|   |||유아차 보관 가능| 
|   |||시각장애인 편의시설 있음| 
|   |||가족화장실 있음| 
|   |||유아의자 있음| 
|   |||청각장애인 편의시설 있음| 
|   |||기저귀 교환대 있음| 
|   |||장애인 주차장 있음|
|   |||진입로 접근성 좋음|
|   |||수유실 있음|
|   |||휠체어 객실 있음|
|   |||휠체어 사용자 테이블 접근이 용이함|


## 검색 예시

### 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 를 조회 한다.
```sql
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 를 조회 한다.
```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'
    AND (
    search_filter_json -> 'search_filter' ->> 'tourist_spot' = '자연 · 공원 · 전망대 · 놀이공원'
        OR
    search_filter_json -> 'search_filter' ->> 'tourist_spot' = '역사 · 고궁 · 문화재'
    )
;
```
5. 관광지 리스트 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'
    AND search_filter_json -> 'search_filter' ->> 'tourist_spot' LIKE '%자연 · 공원 · 전망대 · 놀이공원%'
;
```

6. 다누림 POI Migration SQL 
```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, '<[^>]+>|&nbsp;', '', 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 타입 사용 (검색 최적화) 