2020 2학기 SSWU DBP 기말 프로젝트
아파트 매매 실거래가 조회 시스템으로 지역과 예산, 평수의 범위를 설정해서 맞춤형으로 아파트 실거래가 변동추이와 평당 가격 비교, 도면, 상세 위치 등 서울시 아파트 정보를 제공하는 웹사이트이다.
최근 치솟는 아파트값 상승 뉴스와 각종 부동산 대책이 끊이지 않고 있다. 이에 따라 사용자 맞춤형 아파트 정보와 아파트 실거래가 변동 추이 그래프와 평당 가격 비교 서비스를 제공하여 합리적인 가격대에 아파트를 매매 및 해당 부동산에 대해 용이한 투자 가치분석을 실현하고자 한다. 뿐만 아니라 아파트 도면과 위치와 같은 시각 정보를 제공하여 이사를 원하는 젊은 층부터 투자 목적으로 아파트 실거래가 정보에 대한 관심이 많은 중년층까지 모두에게 편리하고 직관적인 인터페이스를 제공하고자 한다.
-
해당 프로젝트는 웹의 형태로 제작했다. 아파트 정보는 거래, 투자 등 필요한 그 순간에만 보는 것이기 때문에 설치가 필요 없이 모든 기기와 브라우저에서 접근이 가능한 웹이 더 적합하다고 판단했다.
-
다음으로 웹서버이다. 직접 웹서버를 구축하기에는 24시간 가동하면서 들어가는 유지비용을 지불하기에는 어려운 상황이기 때문에 외부 호스팅이 최적의 선택이라고 생각했다.
-
외부 호스팅 중에서도 웹호스팅을 선택했다. 트래픽이 많이 발생하는 대규모의 사이트가 아니라 소규모의 간단한 웹페이지를 만들 목적이기 때문이다.
-
호스팅 업체는 가격과 안정성, 신뢰도 중 가격을 최우선이었고 위에서 언급했듯이 간단한 웹페이지 제작으로 최소한으로 구동만 되면 되기 때문에 무료 호스팅을 지원하는 닷홈 웹호스팅을 이용하고 있다.
-
닷홈의 무료 웹호스팅 사양은 다음과 같다.
웹서버 PHP DB Apache 2.2 PHP 7.3 MySQL 5.7
-
항목명 항목설명 항목크기 샘플데이터 거래금액 거래금액(만원) 40 82,500 건축년도 건축년도 4 2015 년 계약년도 4 2015 법정동 법정동 40 사직동 아파트 아파트명 40 광화문풍림스페이스본(9-0) 월 계약월 2 12 일 일 6 1 전용면적 전용면적(㎡) 20 94.51 지번 지번 10 9 지역코드 지역코드 5 11110 층 층 4 11
국토교통부에서 제공하는 아파트 매매 실거래 데이터 OpenAPI를 활용해서 아파트 정보를 가져오고, 카카오 Maps API를 통해 지도를 보여준다. 아파트 도면 이미지와 전용면적 데이터는 네이버 아파트 검색 결과를 크롤링했다. 국토 교통부 제공 아파트 매매 실거래 데이터 항목은 위와 같다.
-
xml파싱 파이썬 코드로 지역구별로 csv파일을 생성했다.
-
Convert CSV to SQL 사이트에서 csv파일을 sql파일로 변환한 후, create table 구문에서 id 컬럼을 추가해 25개의 지역구 테이블을 생성했다. insert문으로 해당 지역구 테이블에 아파트데이터를 삽입했다.
-
gfp.py를 작성하여 아파트 도면 이미지를 크롤링했다. 자세한 과정은 다음과 같다. 로컬 저장소에 서울시 25개의 지역구에 대해서 지역구\법정동\아파트명\전용면적.jpg 경로로 도면이미지가 저장되도록 코드를 작성했다.
-
해당 루트 폴더를 ftp서버로 업로드했으나 무료 호스팅 이용에 따른 계정용량 초과로 25개 지역구 중 14개(강남, 강북, 서대문구, 서초구, 성동구, 성북구, 송파구, 양천구, 영등포구, 용산구, 은평구, 종로구, 중구, 중랑구)의 지역에 대해서만 업로드 했다. 아래 사진은 도면이미지가 업로드된 경로이다.
-
아파트 매매 실거래가 조회 시스템으로 이동한다.
-
index.php 페이지에서 원하는 예산과 평수의 범위를 조절한 다음 조회하고 싶은 지역구를 선택한다. 추가로 법정동을 선택할 수도 있다. 선택 완료 버튼을 누르면 다음과 같은 5개의 옵션 값이 search.php페이지로 전달된다.
- 최소, 최대 예산 - $minA, $maxA
- 최소, 최대 평수 - $minB, $maxB
- 지역구 - $gu
- 동 - $dong
-
search.php 에서 아래와 같은 쿼리가 동작한다. 사용자가 검색 조건으로 특정 법정동까지 선택했는지 여부에 따라 where절에서 법정동 = '{$dong}' 을 추가하여 쿼리를 구분했다. 그리고 지도를 츨력할 때와 아파트 목록을 출력할 때 모두 같은 쿼리가 사용되기 때문에 함수(execQuery)로 묶었다.
쿼리에서는 사용자가 입력한 예산과 평수의 범위, 지역 값을 받아와서 사용한다. 여기서 평수에 주목할 부분은 평수이다. DB에 저장된 데이터는 평수가 아닌 전용면적이다. 따라서 사용자가 입력한 최소, 최대 평수 값($minA, $maxA)에 3.305785(1평은 약 3.305785㎡)을 곱해서 전용면적으로 변환한 후 평수 조건에 맞는지 검사한다. 또한 최근 3개월 간 거래 정보이면서 최소, 최대 거래금액 값($minB, $maxB)사이에 포함되는지 등을 검사해서 조건에 맞는 아파트 거래 정보를 가져온다. 이 정보는 지도의 태그와 아파트 목록에서 사용된다.
목록에서 아파트 이름을 클릭하면 해당 아파트로 지도가 이동되는 것을 확인 할 수 있고, 지도의 태그(또는 실거래가 평균)나 목록에서 도면보기를 클릭하면 상세 정보를 조회할 수 있다.
function execQuery($link, $gu, $dong, $minB, $maxB, $minA, $maxA) { if($dong == "전체") { # 동 선택 안함 $query = "SELECT 아파트, CONCAT(ROUND(ANY_VALUE(AVG(거래금액))/10000, 1), '억') AS 거래금액, CONCAT(법정동, ' ', ANY_VALUE(지번)) AS 주소, ANY_VALUE(ROUND(전용면적/3.305785)) AS 평수, ANY_VALUE(전용면적) AS 전용면적, TRUNCATE(ANY_VALUE(전용면적), 2) AS 면적, ANY_VALUE(법정동) AS 법정동, ANY_VALUE(지번) AS 지번, '{$gu}' AS 지역구 FROM {$gu} WHERE ROUND(전용면적) > ROUND({$minA}*3.305785) AND ROUND(전용면적) < ROUND({$maxA}*3.305785) AND 년 = YEAR(sysdate()) AND 월 IN (MONTH(sysdate()) - 3, MONTH(sysdate()) - 2, MONTH(sysdate()) - 1, MONTH(sysdate())) # 3개월치(9-12) AND 거래금액 > {$minB}*10000 AND 거래금액 < {$maxB}*10000 GROUP BY 아파트, 법정동 ORDER BY ROUND(AVG(거래금액)/10000, 1) DESC"; } else { # 동까지 선택 $query = "SELECT 아파트, CONCAT(ROUND(ANY_VALUE(AVG(거래금액))/10000, 1), '억') AS 거래금액, CONCAT(법정동, ' ', ANY_VALUE(지번)) AS 주소, ANY_VALUE(ROUND(전용면적/3.305785)) AS 평수, ANY_VALUE(전용면적) AS 전용면적, TRUNCATE(ANY_VALUE(전용면적), 2) AS 면적, ANY_VALUE(법정동) AS 법정동, ANY_VALUE(지번) AS 지번, '{$gu}' AS 지역구 FROM {$gu} WHERE ROUND(전용면적) > ROUND({$minA}*3.305785) AND ROUND(전용면적) < ROUND({$maxA}*3.305785) AND 년 = YEAR(sysdate()) AND 월 IN (MONTH(sysdate()) - 3, MONTH(sysdate()) - 2, MONTH(sysdate()) - 1, MONTH(sysdate())) # 3개월치(9-12) AND 거래금액 > {$minB}*10000 AND 거래금액 < {$maxB}*10000 AND 법정동 = '{$dong}' GROUP BY 아파트, 법정동 ORDER BY ROUND(AVG(거래금액)/10000, 1) DESC"; } $result = mysqli_query($link, $query); return $result; }
-
도면보기를 클릭한 경우
해당 아파트 평수에 맞는 도면을 확인할 수 있다. 관련 코드는 아래와 같다. images/지역구/법정동/아파트 라는 경로에 해당 아파트의 전용면적.jpg 파일이 존재하면 도면보기 태그가 출력된다. 만약 도면이미지를 제공하지 않는 아파트의 경우나 무료호스팅에 따른 계정용량 제한으로 도면이 업로드 되지 못한 경우는 도면 보기 태그를 출력하지 않는다.
while($row = mysqli_fetch_array($result)){ $list .= '<tr><td>'; $list .= '<h3><p onclick="movePos(\''.$row['주소'].'\')" style="cursor:pointer;">'.$row['아파트'].'</p></h3>'; $list .= '</td><td rowspan="3"><h2><a href="detail.php?name='.$row['아파트'].'&ac='.$row['전용면적'].'&gu='.$gu.'&지번='.$row['지번'].'&법정동='.$row['법정동'].'">'.$row['거래금액'].'</h2></td></tr>'; $path = "images/{$gu}/{$row['법정동']}/{$row['아파트']}/{$row['면적']}.jpg"; if(file_exists($path)){ $list .= '<tr><td><a id="apt" href="images/'.$gu.'/'.$row['법정동'].'/'.$row['아파트'].'/'.$row['면적'].'.jpg">'.$row['평수'].'평 도면보기</td></tr>'; }else{ $list .= '<tr></tr>'; } $list .= '<tr><td style="font-size:15px;">'.$row['주소'].'</td></tr>'; }
-
지도의 태그 또는 실거래가 평균을 클릭한 경우
detail.php 페이지로 이동한다. 앞서 search페이지에서 선택한 아파트의 지역구, 법정동, 지번, 아파트명, 전용면적 값을 전달받아서 4가지의 요소를 출력한다.
-
최근 3년간 실거래가 변동추이를 그래프
해당 아파트명과 전용면적에 해당하는 거래금액과 거래일을 가져와서 x축에는 거래년월, y축에는 거래금액이 출력된다.
-
아파트 내 평수 구분
해당 아파트에 여러 개의 평수가 존재할 경우 해당 평수를 클릭해서 선택한 평수에 맞는 데이터를 재조회할 수 있다. 같은 평수이지만 도면이 다른 경우 B, C등의 알파벳을 통해 구분할 수 있다.
-
최근 3개월 기준 해당 법정동 아파트와 조회 아파트의 평균 평당 가격을 비교 분석한 결과
앞서 언급했듯이 DB에는 전용면적이 저장되어있으므로 평균 평당 가격을 계산하기 위해서는 전용면적을 평수로 변환해야 한다. 따라서 전용면적을 3.3((1평은 약 3.305785㎡))을 나눈 다음 거래금액 평균을 평수로 나누어 평당 가격을 구한다. 쿼리는 지역별, 해당지역 내 아파트 쿼리 2개로 구분했다.
"최근 3개월 기준 삼성동의 평균 평당 가격은 6289만원이며, 삼성동 삼성월드타워의 평당 가격은 4901만원입니다. 평균 평당 가격에 비해 1388만원 낮은 가격입니다." -
최신순으로 거래의 상세 정보를 보여주는 리스트
-
자세한 위치 정보를 보여주는 지도
// 테이블 표기용 쿼리 $query = "SELECT 거래금액 AS price, CONCAT(TRUNCATE(거래금액/10000, 1), '억') AS 거래금액, CONCAT(년, '년 ', 월, '월') AS 거래년월, CONCAT(년, '년 ', 월, '월 ', 일, '일') AS 거래일자, CONCAT(법정동, ' ', 지번) AS 주소, CONCAT(전용면적, '㎡') AS 전용면적, 층, CONCAT(TRUNCATE(전용면적,2), '㎡') AS 면적 FROM {$_GET['gu']} WHERE 아파트 = '{$name2}' AND 전용면적 = {$_GET['ac']} ORDER BY CAST(CONCAT(년,'-',월,'-',일) AS DATE) DESC"; //차트용 쿼리 $chartQuery = "SELECT 거래금액, CONCAT(년, '년 ', 월, '월') AS 거래년월 FROM {$_GET['gu']} WHERE 아파트 = '{$name2}' AND 전용면적 = {$_GET['ac']}"; // 지역별 평균 평당 가격을 구하는 쿼리 $avgAcreageQuery = "SELECT ROUND(AVG(a.평당가)) as 평당 FROM (SELECT 아파트, AVG(거래금액) AS 거래금액, TRUNCATE(거래금액/(전용면적/3.3), 0) AS 평당가 FROM {$_GET['gu']} WHERE 월 IN (MONTH(sysdate()) - 3, MONTH(sysdate()) - 2, MONTH(sysdate()) - 1) AND 법정동 = '{$_GET['법정동']}' GROUP BY 아파트, 평당가) a"; // 해당 아파트의 평당 가격을 구하는 쿼리 $acreageQuery = "SELECT ROUND(AVG(a.평당가)) as 평당 FROM (SELECT 아파트, AVG(거래금액) AS 거래금액, TRUNCATE(거래금액/(전용면적/3.3), 0) AS 평당가 FROM {$_GET['gu']} WHERE 월 IN (MONTH(sysdate()) - 3, MONTH(sysdate()) - 2, MONTH(sysdate()) - 1) AND 아파트 = '{$name}' GROUP BY 아파트, 평당가) a";
-
-
서울시로 지역을 제한한 것을 전국 단위로 확대
-
무료 호스팅 이용에 따른 ftp 계정용량 제한으로 도면 이미지 파일을 모두 업로드 하지 못했으나 DB에 테이블을 별도로 생성해서 도면 이미지를 저장하고 join해서 가져오는 방식 등으로 해결
-
아파트 목록이 3개월치 데이터 중 법정동, 아파트명, 평수 등으로 그룹화해서 평균 거래금액이 내림차 순 정렬으로 고정되어있는 상태인데 정렬 기준을 다양하게 선택할 수 있도록 수정
-
아파트 목록을 보면 동일 아파트 동일 평수에 대해서 거래금액이 다르면 구분되서 출력되는데 detail페이지에서 가격 변동은 그래프로 확인할 수 있는 사항이므로 중복되는 사항을 제거 (수정 완료)
-
특정 아파트 검색 기능 추가
-
김혜민
- search.php 지도 구현
- detail.php 지도 인포윈도우 삽입
-
서채원
- gfp.py 도면이미지, 전용면적 크롤링 구현
- 크롤링 이미지 ftp 서버에 업로드
-
윤예지
- index.php 구현
- search.php 아파트 3개월 평균 실거래가 리스트(도면 제외) 지도 커스텀
- detail.php 아파트 실거래 상세 테이블, 평당 가격 비교
- 화면 구성, api -> csv파일화
-
전민지
- detail.php 지도 구현
-
최영진
- DB 구축, csv -> sql 파일화
- detail.php 아파트 실거래가 그래프
- index.php 지역 목록, search.php 도면이미지 화면 구성
- README.md 작성