내비게이션용DB
내비게이션 서비스에 특화하여 건물단위 도로명주소 및 좌표정보를 제공합니다.
내비게이션용DB 구축 및 현행화
- DB 레이아웃
건물정보 DB 레이아웃
순번 | 컬럼명 | 크기 | 형식 | PK | 비고 |
---|---|---|---|---|---|
1 | 주소관할읍면동코드 | 10 | 문자 | 시군구코드(5)+읍면동코드(3)+00 | |
2 | 시도명 | 40 | 문자 | ||
3 | 시군구명 | 40 | 문자 | ||
4 | 읍면동명 | 40 | 문자 | ||
5 | 도로명코드 | 12 | 문자 | 시군구코드(5)+도로명번호(7) | |
6 | 도로명 | 80 | 문자 | ||
7 | 지하여부 | 1 | 문자 | 0:지상, 1:지하, 2:공중 | |
8 | 건물본번 | 5 | 숫자 | ||
9 | 건물부번 | 5 | 숫자 | ||
10 | 우편번호 | 5 | 문자 | ||
11 | 건물관리번호 | 25 | 문자 | PK | |
12 | 시군구용건물명 | 40 | 문자 | ||
13 | 건축물용도분류 | 100 | 문자 | ||
14 | 행정동코드 | 10 | 문자 | ||
15 | 행정동명 | 40 | 문자 | ||
16 | 지상층수 | 3 | 숫자 | ||
17 | 지하층수 | 3 | 숫자 | ||
18 | 공동주택구분 | 1 | 문자 | 0 : 비공동주택, 1 : 공동주택(아파트), 2 : 공동주택(연립/다세대 등) | |
19 | 건물수 | 10 | 숫자 | 같은 주소를 갖는 건물의 수(건물군의 경우 동일한 값) | |
20 | 상세건물명 | 100 | 문자 | ||
21 | 건물명변경이력 | 1000 | 문자 | 건물명이 변경된 경우 최초부터 최종변경 건물명을 나열 | |
22 | 상세건물명변경이력 | 1000 | 문자 | 상세건물명이 변경된 경우 최초부터 최종변경 상세건물명을 나열 | |
23 | 거주여부 | 1 | 문자 | 0 : 비거주, 1 : 거주 | |
24 | 건물중심점_x좌표 | 15,6 | 숫자 | GRS80 UTM-K 좌표계 | |
25 | 건물중심점_y좌표 | 15,6 | 숫자 | GRS80 UTM-K 좌표계 | |
26 | 출입구_x좌표 | 15,6 | 숫자 | GRS80 UTM-K 좌표계 | |
27 | 출입구_y좌표 | 15,6 | 숫자 | GRS80 UTM-K 좌표계 | |
28 | 시도명(영문) | 40 | 문자 | ||
29 | 시군구명(영문) | 40 | 문자 | ||
30 | 읍면동명(영문) | 40 | 문자 | ||
31 | 도로명(영문) | 80 | 문자 | ||
32 | 읍면동구분 | 1 | 문자 | 0 : 읍면, 1 : 동 | |
33 | 이동사유코드 | 2 | 문자 | 31 : 신규, 34 : 변경, 63 : 삭제 |
지번정보 DB 레이아웃
순번 | 컬럼명 | 크기 | 형식 | PK | 비고 |
---|---|---|---|---|---|
1 | 법정동코드 | 10 | 문자 | 시군구코드(5)+읍면동코드(3)+리코드(2) | |
2 | 시도명 | 40 | 문자 | ||
3 | 시군구명 | 40 | 문자 | ||
4 | 읍면동명 | 40 | 문자 | ||
5 | 리명 | 40 | 문자 | ||
6 | 산여부 | 1 | 문자 | 0:대지, 1:산 | |
7 | 지번본번(번지) | 4 | 숫자 | ||
8 | 지번부번(호) | 4 | 숫자 | ||
9 | 도로명코드 | 12 | 문자 | PK1 | 시군구코드(5)+도로명번호(7) |
10 | 지하여부 | 1 | 문자 | PK2 | 0:지상, 1:지하, 2:공중 |
11 | 건물본번 | 5 | 숫자 | PK3 | |
12 | 건물부번 | 5 | 숫자 | PK4 | |
13 | 지번일련번호 | 10 | 숫자 | PK5 | 0 : 대표지번, 그외는 관련지번 |
14 | 시도명(영문) | 40 | 문자 | ||
15 | 시군구명(영문) | 40 | 문자 | ||
16 | 읍면동명(영문) | 40 | 문자 | ||
17 | 리명(영문) | 40 | 문자 | ||
18 | 이동사유코드 | 2 | 문자 | 31 : 신규, 34 : 변경, 63 : 삭제 | |
19 | 건물관리번호 | 25 | 문자 | ||
20 | 주소관할읍면동코드 | 10 | 문자 | PK6 | 시군구코드(5)+읍면동코드(3)+00 |
보조출입구 DB 레이아웃
순번 | 컬럼명 | 크기 | 형식 | PK | 비고 |
---|---|---|---|---|---|
1 | 시군구코드 | 5 | 문자 | PK1 | |
2 | 출입구일련번호 | 10 | 숫자 | PK2 | |
3 | 도로명코드 | 12 | 문자 | 시군구코드(5)+도로명번호(7) | |
4 | 지하여부 | 1 | 문자 | 0:지상, 1:지하, 2:공중 | |
5 | 건물본번 | 5 | 숫자 | ||
6 | 건물부번 | 5 | 숫자 | ||
7 | 법정동코드 | 10 | 문자 | ||
8 | 출입구유형 | 2 | 문자 | 01 : 공용, 02 : 차량용, 03 : 보행자용 | |
9 | 출입구_x좌표 | 15,6 | 숫자 | GRS80 UTM-K 좌표계 | |
10 | 출입구_y좌표 | 15,6 | 숫자 | GRS80 UTM-K 좌표계 | |
11 | 이동사유코드 | 2 | 문자 | 31 : 신규, 34 : 변경, 63 : 삭제 |
※ 건물이 6개 이상인 주소에 대한 보조출입구 정보
※ 주소관할읍면동코드가 추가되었습니다. 레이아웃을 확인하시기 바랍니다.
- 변동자료 현행화
건물정보/지번정보/보조출입구 신규 및 변경 처리
/*
신규 건 처리
- 테이블 관계의 최상위 레벨인 “건물정보"부터 처리
- 신규분외에 누락된 자료가 있을 경우를 위해 UPDATE 후 INSERT하도록 구현
*/
-- 건물정보 처리
UPDATE 건물정보 A
SET (A.법정동코드, A.시도명, A.시군구명, ……) =
(SELECT 법정동코드, 시도명, 시군구명, …… FROM 건물정보변경분 B
WHERE B.건물관리번호 = A.건물관리번호 AND B.이동사유코드 IN ('31', '34') AND ROWNUM = 1)
WHERE EXISTS (SELECT 1 FROM 건물정보변경분 C WHERE C.건물관리번호 = A.건물관리번호 AND C.이동사유코드 IN ('31', '34'))
INSERT INTO 건물정보 (법정동코드, 시도명, 시군구명……)
SELECT 법정동코드, 시도명, 시군구명 …… FROM 건물정보변경분 A
WHERE A.이동사유코드 IN ('31', '34') AND NOT EXISTS (SELECT 1 FROM 건물정보 B WHERE B.건물관리번호 = A.건물관리번호)
-- 지번정보 처리
UPDATE 지번정보 A
SET (A.법정동코드, A.시도명, A.시군구명, ……) =
(SELECT B.법정동코드, B.시도명, B.시군구명, …… FROM 지번정보변경분 B
WHERE B.도로명코드 = A.도로명코드 AND B.지하여부 = A.지하여부 AND B.건물본번 = A.건물본번 AND B.건물부번 = A.건물부번
AND B.지번일련번호= A.지번일련번호 AND B.이동사유코드 IN ('31', '34') AND ROWNUM = 1)
WHERE EXISTS (SELECT 1 FROM 관련지번변경분 C WHERE C.도로명코드 = A.도로명코드 AND C.지하여부 = A.지하여부 AND C.건물본번 = A.건물본번
AND C.건물부번 = A.건물부번 AND C.지번일련번호= A.지번일련번호 AND C.이동사유코드 IN ('31', '34'))
INSERT INTO 지번정보 (법정동코드, 시도명, 시군구명……)
SELECT 법정동코드, 시도명, 시군구명 …… FROM 지번정보변경분 A WHERE A.이동사유코드 IN ('31', '34')
AND NOT EXISTS (SELECT 1 FROM 지번정보 B WHERE B.도로명코드 = A.도로명코드 AND B.지하여부 = A.지하여부 AND B.건물본번 = A.건물본번
AND B.건물부번 = A.건물부번 AND B.지번일련번호= A.지번일련번호)
-- 보조출입구 처리
UPDATE 보조출입구정보 A
SET (A.도로명코드, A.지하여부, A.건물본번, ……) =
(SELECT B.도로명코드, B.지하여부, 건물본번, …… FROM 보조출입구정보변경분 B
WHERE B.시군구코드 = B.시군구코드 AND B.출입구일련번호 = A.출입구일련번호 AND B.이동사유코드 IN ('31', '34') AND ROWNUM = 1)
WHERE EXISTS (SELECT 1 FROM 보조출입구정보변경분 C WHERE C.시군구코드 = A.시군구코드 AND C.출입구일련번호 = A.출입구일련번호 AND C.이동사유코드 IN ('31', '34'))
INSERT INTO 보조출입구정보 (도로명코드, 지하여부, 건물본번 ……)
SELECT 도로명코드, 지하여부, 건물본번 …… FROM 보조출입구변경분 A WHERE A.이동사유코드 IN ('31', '34')
AND NOT EXISTS (SELECT 1 FROM 보조출입구정보 B WHERE B.시군구코드 = A.시군구코드 AND B.출입구일련번호 = A.출입구일련번호 )
/* ORACLE 9i이상 버전에서 MERGE문 사용 예시 */
MERGE INTO 건물정보 A
USING (SELECT 법정동코드, 시도명, 시군구명, 읍면동명...... FROM 건물정보변경분 WHERE 이동사유코드 IN ('31', '34')) B
ON (A.건물관리번호 = B.건물관리번호)
WHEN MATCHED THEN
UPDATE SET A.법정동코드 = B.법정동코드, A.시도명 = B.시도명, A.시군구명 = B.시군구명, ……
WHEN NOT MATCHED THEN
INSERT (A. 법정동코드 , A. 시도명 , A. 시군구명 , A.읍면동명, ……)
VALUES (B. 법정동코드 , B. 시도명 , B. 시군구명 , B.읍면동명, ……)
건물정보/지번정보/보조출입구 폐지 처리
/*
폐지 건 처리
- 테이블 관계의 최하위 레벨인 “지번정보”, “보조출입구정보” 부터 처리
*/
-- 지번처리
DELETE FROM 지번정보 A
WHERE EXISTS (SELECT 1 FROM 지번정보변경분 B WHERE B.도로명코드 = A.도로명코드 AND B.지하여부 = A.지하여부 AND B.건물본번 = A.건물본번
AND B.건물부번 = A.건물부번 AND B.지번일련번호= A.지번일련번호 AND B.이동사유코드 = '63')
-- 보조출입구정보처리
DELETE FROM 보조출입구정보 A
WHERE EXISTS (SELECT 1 FROM 보조출입구정보변경분 B WHERE B.시군구코드 = A.시군구코드 AND B.출입구일련번호 = A.출입구일련번호 AND B.이동사유코드 = '63')
-- 건물정보처리
DELETE FROM 건물정보 A
WHERE EXISTS (SELECT 1 FROM 건물정보변경분 B WHERE B.건물관리번호 = A.건물관리번호 AND B.이동사유코드 = '63')
내비게이션용DB 활용
- 내비게이션용DB 활용 SQL
건물정보와 보조출입구 정보 결합하기
/*
보조출입구와 건물정보(주소정보) 결합 이용 시
- 건물정보의 도로명주소 항목과 보조출입구의 도로명주소 항목을 조인하여 도로명주소별 보조출입구 정보 형태로 이용
- 보조출입구는 건물군에 하나 이상 존재하는 정보이므로 건물정보와 보조츨입구 결합 이용 시 건물정보를 주소 단위로 중복 제거하여(Distinct) 이용
*/
SELECT DISTINCT
A.우편번호,
A.시도명 || ' ' ||
DECODE(A.시군구명,'','', A.시군구명||' ')|| DECODE(A.읍면동구분, '0', A.읍면동명||' ') ||A.도로명 ||' '||
DECODE(A.지하여부, 0, '', '1','지하 ', '2', '공중 ')||A.건물본번 ||DECODE(A.건물부번, 0, '', '-' || A.건물부번) ||
CASE WHEN (A.읍면동구분 = '0' AND A.공동주택여부 = '0') THEN ''
WHEN (A.읍면동구분 = '0' AND A.공동주택여부 IN ('1','2')) THEN DECODE(A.시군구용건물명, '', '', '(' || A.시군구용건물명 || ')' )
WHEN (A.읍면동구분 = '1' AND A.공동주택여부 = '0') THEN '(' || A.읍면동명 || ')'
WHEN (A.읍면동구분 = '1' AND A.공동주택여부 IN ('1','2')) THEN '(' || A.읍면동명 || DECODE(A.시군구용건물명, '', '', ',' || A.시군구용건물명 ) || ')' AS "도로명주소",
A.출입구_x좌표 AS "주출입구_x좌표", A.출입구_y좌표 AS "주출입구_y좌표",
B.출입구_x좌표 AS "보조출입구_x좌표", B.출입구_y좌표 AS "보조출입구_y좌표"
FROM 건물정보 A, 보조출입구정보 B
WHERE A.주소관할읍면동코드 = B.법정동코드
AND A.도로명코드 = B.도로명코드
AND A.지하여부 = B.지하여부
AND A.건물본번 = B.건물본번
AND A.건물부번 = B.건물부번