DBMS에서 정규표현식으로 주소값 다루기

우리 회사는 렛플레이라는 전시, 연극, 뮤지컬, 콘서트와 같은 여가생활을 추천해주는 서비스를 운영하고 있다.

이번에 더 정교한 유저 타겟팅을 위해서 기존의 장소 검색을 세분화해서 핫플레이스 검색을 추가하게 되었다.

기존의 지역검색은 서울, 경기, 부산, 대구와 같이 시,도로 분류를 했었는데.

서울에 한해서 홍대, 대학로, 강남과 같이 특정 번화가로 이벤트들을 검색할 수 있도록 세분화하였다.

이 과정에서 DB에 쌓인 이벤트의 주소 정보를 가공해야 했고 이를 위해서 정규표현식을 이용했다.

그 과정을 블로그에 연재한다.

              핫플레이스 검색이 추가된 모습(http://www.letplay.co.kr)

 

DB에는 각각의 이벤트를 저장하는 테이블이 하나 존재합니다. 이 테이블에는 이벤트의 정보를 나타내는 여러 칼럼들이 있습니다. (장소, 주소, 날짜, 이름, 카테고리 등등) 이 중 ITEM_ADDRESS라는 칼럼이 주소를 저장하는 칼럼입니다.

우선 SQL에서 정규표현식을 이용할 수 있는 REGEXP를 이용해서 서울의 행정구별로 일어나고 있는 이벤트들의 갯수를 알아보겠습니다.

제가 사용한 쿼리는 아래와 같습니다.

 

SELECT DISTINCT TRIM(REGEXP_REPLACE(ITEM_ADDRESS, '(?<=[가-핳]구).+$', '')) AS 주소, count(*) as '이벤트 갯수' FROM TBL_ITEM WHERE ITEM_ADDRESS REGEXP '^.*?서울+.*구' GROUP BY REGEXP_REPLACE(ITEM_ADDRESS, '(?<=구).+$', '');

 

결과는 아래와 같습니다. 종로구와 마포구가 압도적으로 이벤트가 많은데. 종로구에는 대학로가 있고요.

마포구에는 홍대가 있습니다. 그리고 주소를 입력할 떄 서울특별시를 생략하고 서울시 **구, 서울 **구라고 DB에 입력된 경우도 있네요.

주소	이벤트 갯수
서울특별시 종로구	819
서울특별시 마포구	742
서울특별시 강남구	420
서울특별시 중구	260
서울특별시 용산구	241
서울특별시 송파구	195
서울특별시 서초구	139
서울특별시 광진구	125
서울특별시 서대문구	87
서울특별시 영등포구	87
서울특별시 성동구	69
서울특별시 구로구	43
서울특별시 도봉구	43
서울특별시 강동구	38
서울특별시 성북구	35
서울특별시 동대문구	32
서울특별시 강북구	25
서울특별시 노원구	20
서울특별시 강서구	15
서울특별시 종로구	15
서울 종로구	10
서울특별시 관악구	10
서울특별시 금천구	10
서울특별시 서초구	10
서울특별시 양천구	6
서울 서대문구	4
서울특별시 동작구	4
서울특별시 은평구	4
서울시 종로구	3
서울특별시 중랑구	3
서울 강남구	2
서울 양천구	2
서울시 강남구	2
서울 구로구	1
서울 마포구	1
서울시 서초구	1
서울특별시 강남구	1
즐겨찾기에 추가 드림아트센터 거리뷰 서울특별시 종로구	1

 

 

저희 회사는 MariaDB 10.1 버전을 사용하고 있습니다. MariaDB에서는 REGEXP로 정규표현식을 이용한 검색을 제공하고 있습니다. 그리고 REGEXP_REPLACE라는 함수로 정규표현식으로 선택된 텍스트를 다른 텍스트로 바꿀 수 있는 함수도 제공하고 있습니다. 이를 이용해서 DB의 데이터를 조금 더 정교하게 다룰 수 있죠.

 

제가 사용한 정규표현식을 한번 살펴보겠습니다 우선 서울에 위치한 이벤트로 한정 짓고 싶어서 WHERE 조건문에 ITEM_ADDRESS REGEXP '^.*?서울+.*구'를 추가했습니다. ^.*?는 주소값 앞에 공백이 입력된 경우가 있어서 추가한 것입니다. 주소 형식에 상관없이 서울XXXXXXX구라는 문자가 텍스트에 포함되어있다면 검색조건에 들어갑니다.

 

그리고  TRIM(REGEXP_REPLACE(ITEM_ADDRESS, '(?<=[가-핳]구).+$', '')) AS 주소 를 통해서 서울에서 발생하는 이벤트들을 행정구별로 분류할 수 있습니다. 여기서 사용된 정규표현식인 (?<=[가-핳]구).+$ 은 후방탐색자를 사용했습니다. XX구 뒤에 해당하는 텍스트를 선택해서 없애줍니다. 덤으로 앞에 공백이 있으면 TRIM() 함수로 제거해줍니다.

 

서울의 행정구별 이벤트 개수를 검색해봤으니 하나의 행정구에 속한 주소들의 이벤트 빈도수를 검색해보겠습니다. 가장 이벤트 개수가 많던 종로구를 검색해보겠습니다.

 

SELECT ITEM_LOCATION, ITEM_ADDRESS, COUNT(ITEM_ADDRESS) AS '주소 갯수'  FROM TBL_ITEM WHERE ITEM_ADDRESS REGEXP '(서울).*(종로구)' GROUP BY ITEM_ADDRESS;

 

ITEM_LOCATION	ITEM_ADDRESS	주소 갯수
세종문화회관 대극장	서울특별시 종로구 세종대로 175	47
DCF대명문화공장 2관 라이프웨이홀	서울특별시 종로구 대학로12길 21	27
대학로 예그린씨어터	서울특별시 종로구 동숭길 130-5	21
SH아트홀	서울특별시 종로구 동숭길 25	21
홍익대 대학로 아트센터 대극장	서울특별시 종로구 대학로 57	18
대학로 TOM(티오엠) 1관	서울특별시 종로구 대학로8가길 85 대학로문화공간	18
드림아트센터 1관 에스비타운	서울특별시 종로구 동숭길 123	18
대학로예술극장 대극장	서울특별시 종로구 대학로10길 17 동숭동복합건물	17
두산아트센터 연강홀	서울특별시 종로구 종로33길 15	17
대학로 아트원씨어터 3관	서울특별시 종로구 대학로12길 83 SW빌딩	15
JCC아트센터	서울특별시 종로구 창경궁로35길 29 JEIARTCENTER	15
가든씨어터	서울특별시 종로구 대학로10길 15-11	14
대학로 나온씨어터	서울특별시 종로구 혜화로9길 7	13
동양예술극장 1관	서울특별시 종로구 대학로14길 29	11
대학로 아름다운 극장	서울특별시 종로구 성균관로4길 48	11
경복궁 외소주방	서울특별시 종로구 세종로	11
소극장 혜화당 등	서울특별시 종로구 대학로12길 63 석마빌딩	10
대학로 상명아트홀 1관	서울특별시 종로구 동숭길 133 상명대학교예술디자인대학원	10
대림미술관	서울특별시 종로구 자하문로4길 21	10
선돌극장	서울특별시 종로구 혜화로길 5	10
대학로 유니플렉스 2관	서울특별시 종로구 대학로12길 64	9
대학로 노을소극장	서울특별시 종로구 대학로8가길 66	9
대학로 한성아트홀 2관	서울특별시 종로구 창경궁로 254 동원빌딩	9
아르코예술극장 대극장	서울특별시 종로구 대학로8길 7	8
동숭아트센터 동숭홀	서울특별시 종로구 동숭길 122	8
예술공간 서울	서울특별시 종로구 성균관로4길 19 동숭비즈니스센터	8
창경궁 통명전 월대 및 실내	서울특별시 종로구 창경궁로 185	8
대학로 공간아울	서울특별시 종로구 대학로 120	7

 

검색되는 튜플들이 너무 많아서 다 보여드리지는 못합니다.

1위는 광화문에 위치한 세종문화회관이네요. 그리고 2위는 대학로에 위치한 대명문화공장입니다.

이렇게 뽑아낸 주소 정보로 행정구내에 이벤트들이 집중된 스팟들을 대략적으로 알 수 있습니다.

저는 종로구의 연극과 뮤지컬은 모두 대학로에서 발생하는 거라는 생각을 했었는데.

이를 통해서 광화문이 존재한다는 것을 알게 되었습니다. 그래서 대학로의 주소정보를 세팅할 때 더 정교하게 구성할 수 있었죠.

방금 사용한 정규표현식에 대한 설명은 특별히 하지 않아도 될 것같습니다.

 

핫플레이스 검색은 핫플레이스에 해당하는 주소를 등록하고 이 주소를 이용해서 DB를 검색하는 식으로 동작합니다. 예를 들어서 대학로에 '서울특별시 종로구 동숭길', '서울특별시 종로구 대학로'라는 정보를 추가하고 이 주소를 이용해서 대학로에 일어나는 이벤트들을 검색합니다.

이를 위해서는 필요한 작업은 두가지가 있습니다.

 

1. 대학로에 해당하는 주소 정보를 가져와야합니다.
2. 가져온 주소 정보를 가공해야합니다. (EX. 서울특별시 종로구 창경궁로 254 동원빌딩 -> 서울특별시 종로구 창경궁로)

 

1에 해당하는 작업은 생각보다 쉽게 끝냈습니다. ITEM_ADDRESS말고 ITEM_LOCATION이라는 장소 정보를 저장하는 칼럼이 따로 있었습니다.

이벤트를 입력할 떄 이벤트가 일어나는 장소 정보를 입력하는 칼럼이라 이 칼럼을 이용해 핫플레이스에 해당하는 주소정보에 손쉽게 접근할 수 있었습니다.

 

2에 해당하는 작업을 정규표현식을 이용해서 가져와야합니다. 이 경우 제가 사용한 쿼리문은 아래와 같습니다.

 

SELECT DISTINCT REGEXP_SUBSTR(ITEM_ADDRESS, '((서울).*.([가-핳]|\s))*[1-9]?[길|가|동|로]') AS

주소 FROM TBL_ITEM WHERE ITEM_LOCATION REGEXP '대학로' AND ITEM_ADDRESS <> ' ';

 

주소

서울 종로구 동숭동

서울시 종로구 대학로

서울특별시 종로

서울특별시 종로구 낙산길

서울특별시 종로구 대명1길

서울특별시 종로구 대학로

서울특별시 종로구 대학로 112 (동숭동

서울특별시 종로구 대학로10길 17 동숭동

서울특별시 종로구 대학로8가길

서울특별시 종로구 대학로8가길 52 (동숭동

서울특별시 종로구 대학로8가길 85 대학로

서울특별시 종로구 대학로8길

서울특별시 종로구 동숭4길

서울특별시 종로구 동숭길

서울특별시 종로구 동숭동

서울특별시 종로구 명륜4가 113-1 (대학로

서울특별시 종로구 성균관로

서울특별시 종로구 성균관로 87 종로구 명륜동

서울특별시 종로구 성균관로4길

서울특별시 종로구 이화장1길

서울특별시 종로구 이화장길

서울특별시 종로구 창경궁로

서울특별시 종로구 혜화로9길

서울특별시 종로구 혜화로길

 

결과값이 그리 정확하지 않습니다.

주소를 가공할 떄 고려해야할 경우의 수가 너무 많았고 시간은 한정되어 있었기 때문에 이정도에 만족을 하고 작업을 진행했습니다. 물론 이 주소값으로 이벤트를 추출했을 때의 정확성도 검증했습니다. (주소의 정확도는 네이버맵에서 검색해가면서, 그리고 이벤트의 갯수는 더 정교한 검색을 통해서...) 결과적으로 이 방식이 간단하면서 빠르다는 판단했습니다.

물론 더 정교하게 정규표현식을 짰다면 주소 추출을 자동화할 수있었을 거라는 아쉬움이 있습니다.

그러나 빠르고 간단하게 구현하는 것에 초점을 맞추었고 주소 추출은 빈번하게 일어날 작업은이아니기 떄문에 주소 정보 추출은 이정도로 만족했습니다.