💁‍♂️CONTENTS

• 지역데이터, 행정동과 법정동에 대한 내용
• MYSQL, 서브쿼리, JOIN, 에 대한 내용이 포함되어있습니다.

최근에 공공데이터 공모전을 참여하면서 여러개의 지역데이터들을 결합(JOIN)해야 하는 일들이 많았습니다. 그런데 결합하고자 하는 지역 데이터들의 key가 통일이 되어있지 않아 데이터를 결합하는데 곤란했던 경험들이 있었는데요 ! 문제를 어떻게 해결 했는지 다시 정리 해보고자 합니다.

먼저! 저희가 결합하려고 했던 데이터들은 다음과 같습니다.

💻데이터

(Main Data) 서울시 법정동별 사고 데이터
(join data) 서울시 법정동별 교차로 개수
(join data) 서울시 행정동별 인구수
(join data) 서울시 행정동별 면적
...

메인데이터에 각 서울시 법정동별 인구수, 도로의복잡도, 면적 등 여러 변수들과 피해자수가 상관성이 있는지 분석하기 위해 여러개의 서울시 데이터들을 결합하려 했습니다!

하지만 데이터의 제목을 보면 알 수 있듯이 메인데이터는 법정동 기준으로 되어있고 나머지 결합해야할 데이터들 중 2개는 행정동 기준으로 정리가 되어있었습니다.

법정동 기준으로 정리된 데이터들이 필요 했지만 서울열린데이터광장 에서 제공되는 대부분의 데이터들은 행정동 기준으로 제공되고 있더군요 ㅠㅠ..

그래서 이들을 결합하기 위해서는 행정동 기준으로 제공된 통계값을 법정동 기준으로 고쳐서 결합해야 했습니다.

👀 행정동과 법정동 ? 뭐지..?

그러기 위해선 먼저 서울시의 법정동과 행정동이 어떤 구조로 되어있는지 알고 있어야 했습니다! 살면서 관심도 없었던 행정동과 법정동의 관계를 몇번의 검색 끝에 요약을 하자보면 ! 법정동과 행정동은
"하나의 법정동에 여러개의 행정동 or 하나의 행정동에 여러개의 법정동 or N개의 법정동에 M개의 행정동" 으로 이루어져 있다는 것이었습니다.

즉, 법정동과 행정동의 관계가 "N:M 관계" 라는 거죠.
아래의 지도와 표를 참고하시면 바로 이해 하실 수 있을겁니다!

[강남구 법정동 지도]

[강남구 행정동 지도]

• 법정동과 행정동 TABLE

시군구	행정동	법정동
강남구	개포1동	개포동
강남구	개포2동	개포동
강남구	개포4동	개포동
강남구	일원2동	개포동
강남구	개포2동	일원동
강남구	일원1동	일원동
강남구	일원2동	일원동
강남구	일원본동	일원동
영등포구	문래동	문래동1가
영등포구	문래동	문래동2가
영등포구	문래동	문래동3가
영등포구	문래동	문래동4가
영등포구	문래동	문래동5가
영등포구	문래동	문래동6가
...	...	...

사진과 표를 보면 알 수 있듯이, 법정동과 행정동은 강남구 개포동(법정동) 처럼 총 4개의 행정동(개포1동, 개포2동, 개포4동, 일원2동)으로 이루어져 있거나, 문래동(행정동) 처럼 하나의 행정동이 여러개의 법정동(문래동1~6가)으로 이루어져 있는 경우도 있습니다. 게다가 개포2동과 일원2동 같은경우는 개포동에도 들어가고 일원동에도 들어갑니다..

맙소사ㅎㅎ... 다시 정리해보면!

1. 하나의 법정동은 여러개의 행정동으로 이루어짐
2. 하나의 행정동은 여러개의 법정동으로 이루어짐
3. 몇몇 행정동은 다른 법정동에 중복으로 들어 갈 수 있다
4. 즉, N:M 관계다.

위의 조건들을 염두해두고 행정동 통계값을 법정동별로 결합해야했습니다.

다음은 저희가 얻은 메인 데이터의 구조입니다. 일부만 가져왔는데요!
법정동별로 사고 피해자수를 집계한 것입니다.

• (MAIN data) 서울시 법정동별 사고 데이터

  시군구	법정동	사망자	중상자	경상자
  강남구	개포동	0	0	2
  강남구	논현동	0	10	38
  강남구	대치동	0	5	12
  강남구	도곡동	0	1	5
  강남구	삼성동	0	2	21
  ...	...	...	...	...

아래는 저희가 결합할 행정동별 인구수 데이터의 일부 입니다.

• (join data) 서울시 행정동별 인구수 데이터

  시군구	행정동	인구수
  강남구	개포1동	6489
  강남구	개포2동	27945
  강남구	개포4동	23873
  영등포구	문래동	32817
  ...	...	...

이제 행정동 인구수 데이터를 어떻게 법정동 기준으로 결합했는지 설명드리겠습니다!

🤷‍♂️시나리오?

1. 어쨋든 법정동을 기준으로 표현해야한다.
2. 그렇다면 일단 법정동과 행정동 데이터를 한 테이블에 매칭해야 할듯 한데..? 매칭할 수 있는 재료KEY가 필요하다.
3. ( 법정동1 : N행정동 )인 경우는 같은 법정동에 속한 해당 행정동 인구수를 다 더하면 될 것 같고 (ex. 개포동) ★CASE1★
4. ( 법정동N : 1행정동 )인 경우는 행정동을 포함된 법정동 개수 만큼 나눠서 계산해야 하나? 물론 지역마다 크기도 다르고.. 따질게 많아보이는데.. 음..일단 법정동별 인구데이터가 없으니 N등분하는게 최선일거 같다. (ex.문래동1~6가) ★CASE2★
5. 그리고 여러곳에 들어가는 행정동 법정동들도 생각해야한다.. CASE3?

라는 고민들과 함께 !!
가장 먼저, 법정동과 행정동을 쉽게 매칭하기 위해서는 새로운 변수가 필요했습니다. 그것은 바로..

✔ 표준지역코드

한국은 각각의 지역(시군구/읍면동..) 마다 숫자CODE로 지정되어 있다는 사실을 알게 되었고, 다행히 행정안전부 에서 행정동코드와 법정동코드를 잘 매칭 해놓은 데이터가 있었기 때문에 쉽게 구할 수 있었습니다. 테이블 구조는 다음과 같습니다.

표준지역코드

• H_CODE(행정동코드)
• B_CODE(법정동코드)

재료들을 다 구했으니 데이터 구축을 시작해보겠습니다!

💻SQL query

MySQL을 이용하였습니다.

✔ 메인데이터 + B_CODE

가장먼저, 결합의 key가 될 B_CODE를 메인데이터에 결합해줍니다.
메인데이터에 존재하는 법정동에 해당하는 법정동코드를 붙여주는 작업입니다.
(앞으로 결합할 모든 데이터의 기준은 "B_CODE - 법정동코드" 가 됩니다.)

메인데이터에 있는 시군구+법정동 데이터만 필요하니 메인데이터 기준으로 JOIN을 해줍니다.
(✔ 법정동 이름이 중복되는 지역이 있으니까 꼭 시군구 and 법정동 두개의 key로 결합 해야합니다!)

with main as (SELECT b.B_CODE 
				  , a.*
			  FROM maindata a 
			  	LEFT JOIN 행정법정 b ON a.시군구 = b.시군구  #(table)행정법정 = 표준지역코드
    								AND a.법정동 = b.법정동 )

마찬가지로 이번 문제의 핵심인 "서울시 행정동 인구수 데이터"에도 각 지역코드를 추가해줍니다.

, DF AS (SELECT B.*
    			,A.인구수 AS 행정동인구수
         FROM 행정동인구밀도 A	 # (table)행정동인구밀도 = 서울시 행정동 인구수 데이터
            LEFT JOIN 행정법정 B ON A.자치구 = B.시군구
            					AND A.행정동 = B.행정구역명) 
 # 인구수데이터 행정동 값이 행정구역명으로 되어있길래 행정구역명으로 JOIN 해줍니다. '제1동' '1동' 같이 글자 차이입니다.

output

✔ 각 데이터에 지역코드매칭은 완료했습니다.
이제 ! 본격적으로 법정동별 인구수를 구해야합니다. 먼저 첫번째 CASE !!

✔ CASE1

" ( 법정동1 : N행정동 )인 경우는 같은 법정동에 속한 해당 행정동 인구수를 다 더하자 "

, DF2 AS (select *
			,COUNT(B_CODE) AS SAME_Bcnt # 해당 법정동이 몇개 있는지 COUNT.
			,SUM(행정동인구수) AS 법정동인구수 # 해당 법정동에 포함된 행정동인구수들을 모두 더한다.
		FROM DF
		GROUP BY B_CODE # 법정동별로 GROUP 

OUTPUT

✔ 개포동을 법정동으로 갖는 행정동이 총4개 있으며, 포함된 행정동들의 인구수를 합친 결과가 75889명 인것을 알 수 있습니다. (개포동 = 개포1동+개포2동+개포4동+일원2동)

✔ CASE2

"( 행정동1 : N법정동 )인 경우는 법정동N 만큼 나눠서 계산해야 하나? 법정동별 인구수 데이터를 알 수 없으니, N등분하는게 최선일거 같다."

예를들어, 한 행정동의 인구수가 200명이고 5개의 법정동으로 이루어져있다면 5로 나누어서 각각의 법정동 인구수를 40명으로 친다는 뜻입니다!

  , DF3 AS( SELECT *
				,COUNT(H_CODE) AS SAME_Hcnt # 같은 행정동을 갖는 법정동의 개수
       	 		,ROUND(행정동인구수/COUNT(H_CODE),2) AS 법정동인구수 # 법정동 개수만큼 나눠 줍니다.  
			FROM DF
            GROUP BY H_CODE)

OUTPUT

영등포구 문래동 행정동에 포함된 법정동 개수가 총 6개이고, 포함된 법정동 수 만큼 인구수를 나눈 결과 5468.67명 인 것을 알 수 있습니다. (하지만 이는 지역의 크기나 특징을 고려하지않고 단순히 균등하게 나눴으므로 당연히 정확한 인구수를 구하는 것에는 한계점이 있습니다.😥)

👀중간 정리

(DATA) 행정동별 인구수 데이터
CASE 1. 한개의 법정동에 여러 행정동으로 이루어져 있는 경우 ✔ 포함된 행정동을 더한다.
CASE 2. 한개의 행정동에 여러 법정동으로 이루어져 있는 경우 ✔ 포함된 법정동만큼 나눈다.
CASE 3. ?

CASE1 과 CASE2 의 경우 위의 방법대로 인구수를 구할 수 있습니다. 하지만 이둘을 제외한 다른CASE도 고려해줘야 했는데 바로 두개이상의 법정동과 두개이상의 행정동이 포함되는 지역입니다.

✔ CASE3

두개이상의 법정동과 행정동을 갖는 지역만 뽑아보면 다음과 같습니다.

    SELECT A.*
		,B.SAME_Hcnt
		,B.법정동인구수 AS 법정동인구수CASE2
	FROM DF2 A
	LEFT JOIN DF5 B ON A.H_CODE = B.H_CODE
    WHERE A.SAME_Bcnt > 1 AND B.SAME_Hcnt > 1

CASE3의 경우 또한 CASE1 ,CASE2 입장으로도 모두 구할 수 있었기 때문에 정확하게 그 법정동의 인구수를 구하기는 어려웠습니다. 그래서 "그래도 이렇게 구하는 것이 더 가 까울것 이다." 라는 최선책으로 정할 수 밖에 없었습니다.

예를들어, 강남구 일원동(법정동) 의 인구수를 구하는 경우
단순히 <CASE1의 방법>으로 구하면 4개의 행정동 으로 이루어져 있기 때문에 해당 행정동을 다더하면 84035명 이며, <CASE2의 방법>으로 구하면 행정동인 개포2동은 일원동과 개포동 2개의 법정동으로 나뉘어 지기 때문에 2로 나눠 13972.5명으로 보게 됩니다.

하지만 일원동의 경우 CASE1의 입장으로 보는 것이 좀 더 가까울 것이라고 생각이 들었습니다. 왜냐하면 행정동에서 나눠지는 법정동수보다 법정동에 포함되는 행정동수가 더 많기 때문입니다. (SAME_Bcnt > SAME_Hcnt)

아니면 CASE3의 해당하는 모든 지역면적을 고려한뒤 case by case로 나눠서 인구수를 구해야 할까?도 생각해봤지만 면적이 넓다고해서 그곳에 인구가 많이 있다고 하기도 어려웠습니다.(좁은 서울에 밀집된 인구수만 봐도 알 수 있듯이..)

그래서 최선책으로 CASE3의 경우 CASE1과 CASE2의 방법을 합쳐서 계산 했습니다.
예를들어, 일원동의 경우 법정동 기준으로 인구수를 합친 84035명 에서 행정동개수인 2개로 나눠서 인구수를 구했는데요! 일단 법정동에 속한 행정동을 다더하고(CASE1) 그안에서 행정동에 속한 법정동 개수(SAME_Hcnt)만큼 균등하게 나누는 것 입니다(CASE2).

CASE1,CASE2에 해당하는 서브쿼리들을 전부 합쳐서 원하는 컬럼만 뽑아보면 다음과 같습니다!

-- -------- <인구수> 행정동 법정동 매핑 코드 -----------

WITH DF AS (SELECT B.*
					,A.인구수 AS 행정동인구수
			FROM 행정동인구밀도 A
				LEFT JOIN 행정법정 B ON A.자치구 = B.시군구
									AND A.행정동 = B.행정구역명)

---------- <인구수> 법정동 1 : N 행정동 ---------

, DF2 AS ( SELECT *
				,COUNT(B_CODE) AS SAME_Bcnt
				,SUM(행정동인구수) AS 법정동인구수CASE1
			FROM DF
			GROUP BY B_CODE) 
            
---------- <인구수> 행정동 1 : N 법정동 ---------   

, DF3 AS ( SELECT *
				,COUNT(H_CODE) AS SAME_Hcnt
                ,ROUND(행정동인구수/COUNT(H_CODE),2) AS 법정동인구수
			FROM DF
            GROUP BY H_CODE)

, DF4 AS ( SELECT A.*
				,B.SAME_Hcnt
				,B.법정동인구수 AS 법정동인구수CASE2	
			FROM DF2 A
				LEFT JOIN DF3 B ON A.H_CODE = B.H_CODE)

----------- CASE3 그리고 CASE1, CASE2 정리 -----------------
SELECT B_CODE
	, 시군구
	, 법정동
	, CASE	WHEN SAME_Hcnt = 1 and SAME_Bcnt > 1 THEN 법정동인구수CASE1 # CASE1 
			WHEN SAME_Bcnt = 1 and SAME_Hcnt > 1 THEN 법정동인구수CASE2  # CASE2
			WHEN SAME_Bcnt > 1 and SAME_Hcnt > 1 THEN ROUND(법정동인구수CASE1/SAME_Hcnt,2) #CASE3
			-- WHEN SAME_Bcnt = SAME_Hcnt THEN ROUND(법정동인구수CASE1/SAME_Hcnt,2)-- 
            ELSE 0 END AS 법정동인구수 
FROM DF4

OUTPUT

✔ 각각의 CASE에 맞게 뽑아낸 법정동별 인구수 데이터 결과는 다음과 같습니다!

🤔결론 및 한계점

이렇게 MySQL을 이용하여 지역데이터를 합쳐보았습니다!
저같은 경우에는 메인데이터가 법정동 기준이었기 때문에 행정동 기준의 통계치를 법정동으로 바꾸는 방법을 선택했습니다. 그러다 보니 행정동 인구수 통계값을 법정동 기준으로 바꾸는데 고려해야할 CASE가 많아 아주 정확하게 법정동 기준으로 인구수를 맞출수 없다는 한계점이 있었습니다. (방법이 떠올르지 않아균등하게 분배해버린😓) 반대로 준비된 메인 데이터(법정동)를 행정동 기준으로 바꾸는 방법도 시도 했다면 좋았겠지만 이미 법정동으로 합쳐져서 피해자 수를 집계한 데이터 이기 때문에 이를 나누는 것도 뭔가 애매했습니다.
저는 SQL을 이용했지만 이 방법이 정확한 방법이 아닐 수도 있고! 다른 언어을 이용한 더 효율적인 방법이 있을 수도 있습니다. 역시 아직 더 공부가 필요한것 같습니다ㅎㅎ.. 마지막으로 참고한 자료를 첨부하고 포스팅을 마무리 하겠습니다!

참고자료

• 공간데이터의 이해