다운로드 (18.9 KB)

2025/20.개발산출물/01.DB 설계 » 이동형POI DB설계v1.2_0910.md

이환희, 2025-12-19 05:00

이동형 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'));

기본 필드

필드명	타입	설명	필수 여부
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)

{
  "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)

{
  "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 검색 쿼리

음식점 리스트 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' ;
관광지 리스트 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' ;
쇼핑 리스트 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' ;

숙박 리스트 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'
;

숙박 리스트 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' = '역사 · 고궁 · 문화재'
)
;

관광지 리스트 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 '%자연 · 공원 · 전망대 · 놀이공원%'
;

다누림 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 타입 사용 (검색 최적화)

« 뒤로
1
2
3
…
11
다음 »

(1-1/11)

Project

일반

사용자정보

[IITP] 장애인 데이터 과제 2차년도