0. 들어가기 전에
전국 상가데이터로 부터 전국 편의점의 위치 데이터를 추출하고 NodeJS, MySQL을 이용해 구글맵에 편의점 위치를 찍어, 전국 편의점의 위치분포를 확인해보는 웹 어플리케이션을 만들 것이다.
NodeJS(Express-ejs), MySQL(AWS) 그리고 Google Map development에 대한 약간의 지식이 필요하다.
데이터의 [ 1) 수집 => 2) 저장 => 3) 가공 => 4) 시각화 ] 과정으로 간단하지만 완성도 있는 웹 어플리케이션을 만들 것이다.
이 프로젝트에 사용된 기술 수준으로는 실무에 사용하기엔 매우 부족하다. 그러나 만들어 보는 것에 의미 있다고 생각해 시작하게 됐고, 이정도 수준의 결과만으로도, 마케팅 자료로 활용하기에 손색이 없다고 생각한다.
소스코드를 보고 싶다면 github에서 다운 받을수 있다.
1. 시작하기
1) 수집
상가데이터를 공공데이터 포털이라는 곳에서 .csv형태로 다운 받을 수 있다. 상가업소정보(최신의 것)를 다운받는다. 다운받은 데이터를 프로젝트 디렉토리에 raw-data 라는 디렉토리를 만들어 거기에 넣고 압축을 푼다.
아래 그림처럼, stores1라는 프로젝트 디렉토리에 raw-data라는 디렉토리를 만들어 상가데이터 .csv파일을 넣고 파일명 한글을 없앴다. 한글을 없앤 이유는 csv 파일을 DB와 연결할 때 문제가 생길 수 있기 때문이다.
그리고 notepad를 열어 .csv 파일들을 utf8 형태로 바꾼다.
2) 저장
A. MySQL에 데이터를 저장하기 위해 준비하기
수집된 데이터 셋(.csv)을 DB에 넣을 것이다. MySQL은 RDBMS이므로 NoSQL과 달리 정형화 된 데이터가 저장되야한다. 저장에 앞서 .csv 파일의 데이터 구조를 보자.
메모장으로 열어봤다. 생각보다 칼럼수가 많다. 약 5 Rows 정도만 남겨두고 다른이름으로 저장한다. 저장할 때, utf-8으로 인코딩한다. 이것을 만드는 이유는 DB에 데이터가 잘 저장되나 테스트하기 위함이다.
csv 뷰어로 파일로 열것이다. 많은 csv 뷰어가 있겠지만 사용하기 간단한 portable 파일인 이것 다운받아 이용한다. ex1.csv를 csv 뷰어로 열어본다. 실제 row-data를 DB에 저장하기에 앞서, ex1.csv가 MySQL에 저장이 되는지 시험해본다.
B. AWS MySQL인스턴스 생성하기
위치데이터를 저장하기 위한 저장소로 MySQL을 사용할 것이다. 이 프로젝트를 위해 자신의 컴퓨터에 MySQL을 설치해서 사용해도 되지만, 따로 설치할 필요가 없고, 관리가 쉽기때문에 AWS의 RDS에서 MySQL 인스턴스를 생성해 사용할 것이다. 참고로 프리티어 기간내에 있다면, MySQL을 사용하는 것은 무료이다. AWS의 RDS생성으로 이동한다.
왼쪽에 프리티어만 을 클릭하고 MySQL을 선택한다.
위에 RDS 프리티어가 디원되는 옵션만 표시하기 를 선택하고, default값을 선택한다.
아래쪽 설정에서 DB 인스턴스 식별자, 마스터 사용자 이름, 암호 등을 설정한다. 이 정보들이 나중에 MySQL에 접근할 때 사용된다.
다음 단계로 넘어가면 고급 설정 구간으로 넘어간다. default 값으로 해준다. 단 네트워크 및 보안부분에서 3306 port를 열어줘야 MySQL에 접근가능하다.
DB 인스턴스 시작을 누르면 MySQL인스턴스가 만들어진다. MySQL을 Ubuntu 등에 직접 설치 하는 것 보다 매우 쉽다. 약 5분뒤 MySQL 인스턴스 생성이 아래 그림과 깉이 완료될 것이다.
C. MySQL에 접속하기
MySQL에 .csv 파일을 적재하기 위해 HeidiSQL을 사용할 것이다. 여기를 클릭해 portalbe 버전을 아래 그림과 같이 다운받자.
다운 받은 HeidiSQL을 압출 풀고 .exe 파일을 실행시킨 후 세션 관리자를 연다.
위 그림과 같은 창이 뜨는데, Host명, 사용자명, 암호, port # 를 정확히 입력해준다. 위의 MySQL인스턴스 생성시 설정에서 입력했던 정보들이다. 호스트명은 AWS 콘솔에서 인스턴스를 클릭하면 나오는 상세보기를 통해 확인가능하다. AWS콘솔의 엔드포인트 정보를 호스트명에 입력하면 된다. 열기를 눌러, 접속을 한다.
D. .csv파일의 데이터를 MySQL에 적재하기
HeidiSQL 창에서 왼쪽 흰 화면의 빈곳을 클릭해, 왼쪽클릭해, 새로 생성 > 데이터베이스를 해 새로운 DB를 하나 만든다.
stores1이라는 이름으로 DB를 만들었다. 이때, 주의할 점은 utf8_general_ci 로 인코딩 값을 선택해줘야 한글이 깨지지 않는다!
DB 생성 후 생성된 stores1 DB를 오른쪽 클릭해 테이블을 만든다.
이름을 stores201706으로 짓고, ex1.csv 포맷에 맞게 칼럼을 추가해준다. 예를들어, 상가업소번호의 경우, 8자리의 코드를 가지므로 char(8), 상호명은 varchar(16) 등으로 말이다.
아래와 같이 테이블을 완성시킨다.
테이블이 완성됐다면, 상단에서 도구 > CSV 파일 가져오기를 클릭한다. 그러면 아래와 같이 문서 파일 가져오기 창이 뜬다.
위 그림과 같이, 인코딩과 제어 문자, 옵션 등을 상황에 맞게 값을 주고 가져오기를 클클릭하면 데이터가 데이블에 적재된다. 데이터가 저장됐나 확인하기 위해 쿼리를 날려 확인해보자.
SELECT * FROM stores201706
위와 같은 결과를 에러없이 얻게 된다면 데이터를 적재할 준비가 된 것이다.
이제 다시 테이블의 레코드를 모두 삭제하고 raw-data를 삽입하자.
DELETE FROM stores201706
아까와 같이 문서 파일 가져오기를 열어 이번에는 raw-data를 넣는다. raw-data는 총 4개로 분할되어 있으므로 4번 시행한다. 이때, utf-8으로 인코딩된 .csv를 열어야 한다.
4개의 .csv 파일을 적재한 후 데이터가 잘 들어갔는지 확인한다.
SELECT * FROM stores201706 LIMIT 10;
얼핏 봤을 때는 잘 들어간 것 같다. 그렇다면 이 테이블에는 상가 데이터가 몇 개나 저장 됐을까?
SELECT COUNT(st_no) FROM stores201706
약 313만 개의 데이터(2.5GiB)가 조회된다.
이 무의미해 보이는 313만 개의 데이터로부터 이제 의미있어 보이는 데이터를 추출해 보자.
3) 가공
진도를 나가기 앞서, 왜? 편의점 위치가 의미있는 데이터가 될 수 있는지 먼저 고민해보자. 그냥 쉽게 생각해도, 편의점은 인구밀도가 높거나 유동인구가 높은 지역에 많이 분포되어 있음을 알 수 있다. 데이터 분석 작업을 하기에 앞서, 반드시 이런 식의 가정이 선행되야 할 것이다.
그러나 여기서는 전문적인 데이터마이닝을 할 목적이 아닌, 단순히 편의점의 위치분포를 구글맵에서 보기 위해 시작한 프로젝트이므로 이 과정을 생략했지만 이러한 가정은 중요하다.
다시 본론으로 돌아와서, 저장된 레코드들을 분석해보면 cat3(상권업종소분류명)에서 편의점 데이터만 가져올 수 있다. 이때, 구글맵에 위치를 표시하기 위해 필요한 정보들의 속성만 가져온다.
SELECT st_name, lng, lat
FROM stores201706
WHERE cat3_name like '편의점' ;
그 결과는 아래와 같고 약 5만 건 이다. st_name을 통해 편의점 종류를 구분하고, lng와 lat를 통해 편의점 위치를 구글맵에 찍을 수 있을 것 같다.
그런데, 한 가지 문제점이 있다. 조회 속도가 너무 느리다(약 60초). MySQL(AWS)의 성능이 매우 낮기 때문이다.
중복의 문제는 발생하나, 이 결과를 자주 조회할 것이기 때문에 따로 테이블을 만들어 관리하자. 데이터베이스에 테이블을 하나 더 만들자.
아까의 결과를 convenient_stores201706 테이블에 저장하자.
INSERT INTO convenient_stores201706
(
SELECT st_name, lng, lat
FROM stores201706
WHERE cat3_name like '편의점'
);저장 결과를 조회해보면, 약 0.3 초걸린다. 이정도 퍼포먼스면 웹에서 편의점 위치 데이터를 매번 조회하는데 무리가 없을 것이다. 그리고 아까 약60초 걸리던 것에 비해 약 200배 정도의 시간을 줄인 것이다.
SELECT * FROM convenient_stores201706;
이제 구글맵에 뿌릴 데이터는 준비됐다.
4) 시각화
A. NodeJS를 이용해 웹 서버 띄우기
NodeJS를 이용해 웹 서버를 만들 것이다. 미들웨어로 Express를, 뷰 템플릿으로 ejs를 사용할 것인데, 이것에 대한 설치법은 글이 장황해질 수 있으므로 다른 곳에서 설명하겠다.
프로젝트 폴더로 이동 후, express(ejs) 프로젝트를 하나 생성한다
$ cd path/stores1
$ express --ejsbin 폴더를 지우고, app.js의 하단에 다음과 같은 코드를 입력해 준다. 아래 코드는 웹 서버를 3333번 포트에 띄우겠다는 의미히다.
app.listen(3333, ()=> {
console.log('Web server statred at port #3333');
});
$ node app.js
Web server statred at port #3333
B. MySQL로 부터 데이터 가져오기
프로젝트 디렉토리에 models 폴더를 만들어 준다. 그 폴더내에 db.js를 만든다. mysql연결에 필요한 모듈인 'mysql'을 설치한다.
$ npm install mysql --save
// db.js
const mysql = require('mysql');
const cnf = require('../cnf.js').AWSMYSQL
const pool = mysql.createPool({
connectionLimit : cnf.connectionLimit,
host : cnf.host,
user : cnf.user,
password : cnf.password,
database : cnf.database
});
exports.getLngLat = (fail, done) => {
pool.getConnection((err, conn) => {
if(err) {
return fail(err);
}
let sql = 'SELECT * FROM convenient_stores201706';
conn.query(sql, (err, rows) => {
if(err) {
return fail(err);
}
conn.release();
done(rows);
});
});
}여기서 cnf.js를 require하고 있는데, mysql에 대한 정보를 담고 있다. 참고로 cnf.js 파일은 이러한 형태이다. 상황에 맞게 적절한 정보를 입력해야 한다.
// cnf.js
module.exports = {
AWSMYSQL: {
connectionLimit : 50,
host : /*mysql-aws 엔드포인드 주소*/,
user : /*유저id*/,
password : /*유저pwd*/,
database : 'stores1'/*접근할 DB 이름*/
}
}
// index.js
const express = require('express');
const db = require('../models/db');
const router = express.Router();
router.get('/', function(req, res, next) {
db.getLngLat(
(err)=> {
return next(err);
},
(rows)=> {
console.log('rows:',rows);
res.send('ok');
});
});
module.exports = router;
다시 콘솔에서 app.js 를 실행시키고, 웹 브라우저에서 접속 후, 콘솔에 다음과 같은 결과를 볼 수 있다면 MySQL로 부터 데이터를 가져오는 것을 성공한 것이다.
C. Google Map에 위치분포 찍기
Google Map을 이용해 위치 분포를 찍어 볼 것이다. 사용할 방법은 마커 클러스터링. 자세한 정보는 링크를 참조하기 바란다. 이번 프로젝트도 링크를 참고해 만들었기 때문에 링크의 문서를 봐도 무방할 것이다. 링크에서 마커 클러스터링이 무엇인지 보고 오자.
그 외에도 지도에 위치를 표시하는 법이 더 있으니 따로 살펴두면 좋을 것 같다.
아래 소스코드는 구글에서 제공하는 예제이다. 이것을 수정해서 사용하면 바로 사용 가능 할 것이다. 먼저, 구글의 예제를 우리의 프로젝트 소스코드에 넣고, router랑 연결해보자.
views/index.ejs를 다음과 같이 입력한다. 단 API키는 각자의 것을 넣어야 한다. API키를 발급 받기 위해서는 여기를 참고하자.
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="initial-scale=1.0, user-scalable=no">
<meta charset="utf-8">
<title>Marker Clustering</title>
<style>
/* Always set the map height explicitly to define the size of the div
* element that contains the map. */
#map {
height: 100%;
}
/* Optional: Makes the sample page fill the window. */
html, body {
height: 100%;
margin: 0;
padding: 0;
}
</style>
</head>
<body>
<div id="map"></div>
<script>
function initMap() {
var map = new google.maps.Map(document.getElementById('map'), {
zoom: 3,
center: {lat: -28.024, lng: 140.887}
});
// Create an array of alphabetical characters used to label the markers.
var labels = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
// Add some markers to the map.
// Note: The code uses the JavaScript Array.prototype.map() method to
// create an array of markers based on a given "locations" array.
// The map() method here has nothing to do with the Google Maps API.
var markers = locations.map(function(location, i) {
return new google.maps.Marker({
position: location,
label: labels[i % labels.length]
});
});
// Add a marker clusterer to manage the markers.
var markerCluster = new MarkerClusterer(map, markers,
{imagePath: 'https://developers.google.com/maps/documentation/javascript/examples/markerclusterer/m'});
}
var locations = [
{lat: -31.563910, lng: 147.154312},
{lat: -33.718234, lng: 150.363181},
{lat: -33.727111, lng: 150.371124},
{lat: -33.848588, lng: 151.209834},
{lat: -33.851702, lng: 151.216968},
{lat: -34.671264, lng: 150.863657},
{lat: -35.304724, lng: 148.662905},
{lat: -36.817685, lng: 175.699196},
{lat: -36.828611, lng: 175.790222},
{lat: -37.750000, lng: 145.116667},
{lat: -37.759859, lng: 145.128708},
{lat: -37.765015, lng: 145.133858},
{lat: -37.770104, lng: 145.143299},
{lat: -37.773700, lng: 145.145187},
{lat: -37.774785, lng: 145.137978},
{lat: -37.819616, lng: 144.968119},
{lat: -38.330766, lng: 144.695692},
{lat: -39.927193, lng: 175.053218},
{lat: -41.330162, lng: 174.865694},
{lat: -42.734358, lng: 147.439506},
{lat: -42.734358, lng: 147.501315},
{lat: -42.735258, lng: 147.438000},
{lat: -43.999792, lng: 170.463352}
]
</script>
<script src="https://developers.google.com/maps/documentation/javascript/examples/markerclusterer/markerclusterer.js">
</script>
<script async defer
src="https://maps.googleapis.com/maps/api/js?key=(API 키를 여기에 입력)&callback=initMap">
</script>
</body>
</html>
routes/app.js를 다음과 같이 수정한다. 아래 코드의 의미는 views/index.ejs를 해당 라우터로 접속하면 렌더링 하겠다는 뜻이다.
// res.send('ok');
res.render('index');
이제 저장 후 다시 app.js를 재실행 시켜 웹에서 localhost:3333 으로 접속해보자.
구글맵이 잘 뜬다. 위의 locations 부분만 조금 수정하면 이제, 편의점 위치를 찍을 수 있다.
app.js 소스 코드에서, 편의점 위치 정보를 가지고 있는 부분은 rows이다. rows를 index.ejs가 렌더링 될 때, 데이터를 넘겨주면 된다. 소스 코드를 아래와 같이 res.render 부분에 rows를 json 형태로 추가해준다.
res.render('index', {rows:rows});index.ejs 에서 <script>를 다음과 같이 고쳐준다.
<script>
var rows = <%- JSON.stringify(rows) %>;
function initMap() {
var map = new google.maps.Map(document.getElementById('map'), {
zoom: 7,
center: {lat: 35.5, lng: 128.0}
});
var markers = rows.map(function(location, i) {
return new google.maps.Marker({
position: {lat:location.lat, lng:location.lng},
label: location.cs_name
});
});
var markerCluster = new MarkerClusterer(map, markers,
{imagePath: 'https://developers.google.com/maps/documentation/javascript/examples/markerclusterer/m'});
}
</script>
드디어, 다시 localhost:3333 으로 접속하면 전국 편의점 위치가 찍힌 지도가 보일 것이다!
2. 마치며
이번 프로젝트를 응용해 편의점이 아닌 다른 데이터도 지도에 입력할 수 있을 것 같다.