
import psycopg2


import matplotlib.pyplot as plt
plt.rcParams["font.family"] = 'nanumgothic'
plt.rcParams['axes.unicode_minus'] = False


import tqdm


import os
from cartoframes.viz import *


import json


import pandas as pd
import geopandas as gpd


from shapely.geometry import Point, MultiPoint


import geopandas as gpd
import psycopg2
from sqlalchemy import create_engine
db_connection = psycopg2.connect(
    dbname='urbanflow',
    user='guest',
    password='guest',
    host='localhost',
    port='5432'
)
engine = create_engine('postgresql+psycopg2://', creator=lambda: db_connection)


query = 'SELECT * FROM building_construct;'
agdf = gpd.GeoDataFrame.from_postgis(query, engine, geom_col='geometry')


query = 'SELECT * FROM building_destroy;'
dgdf = gpd.GeoDataFrame.from_postgis(query, engine, geom_col='geometry')


mybasemap = {
    'style': 'mapbox://styles/mapbox/satellite-v9',
    'token': 'pk.eyJ1IjoiaHNtNjkxMSIsImEiOiJjazl0and6aDUwOWF2M2RvemdrYjllczV3In0.qGmaAF6v-1LAF9C-dnMLBg'
}


import tqdm


import numpy as np


import pandas as pd
import geopandas as gpd
from cartoframes.viz import *
subway_df = pd.read_csv('국가철도공단_수도권2호선_역위치_20230430.csv', encoding='euc-kr')
subway_gdf = gpd.GeoDataFrame(
    subway_df, geometry=gpd.points_from_xy(x=subway_df['경도'], y=subway_df['위도'])
)
subway_gdf.crs = 'EPSG:4326'


subway_gdf_5181 = subway_gdf.to_crs('epsg:5181')


DIST_THRES = 500


ym_list = [y*100 + m for y in range(1960, 2023) for m in range(1, 13)]


agdf_5181 = agdf.to_crs('epsg:5181').sort_values('사용승인_일')
agdf_5181 = agdf_5181[agdf_5181['연면적(㎡)'] > 0].copy()

dgdf_5181 = dgdf.to_crs('epsg:5181').sort_values('사용승인_일')
dgdf_5181 = dgdf_5181[dgdf_5181['폐쇄말소_일'] == dgdf_5181['폐쇄말소_일']]
dgdf_5181 = dgdf_5181[dgdf_5181['연면적(㎡)'] > 0].copy()


import pickle


sitem

철도운영기관명                                           서울교통공사
노선                                                   2호선
역명                                                    시청
경도                                            126.975271
위도                                             37.563534
geometry    POINT (197815.18124126745 451555.8993385717)
Name: 0, dtype: object


DIST_THRES=500
pkl_fname = f'station_year_dict_{DIST_THRES}.pkl'
if os.path.isfile(pkl_fname):
    with open(f'station_year_dict_{DIST_THRES}.pkl', 'rb') as f:
        station_year_dict = pickle.load(f)


asdf = pd.DataFrame(station_year_dict).T
asdf.index=asdf.index.rename(['STATION', 'DATE'])
asdf.index = asdf.index.set_levels(asdf.index.levels[1].astype(int), level=1)
asdf = asdf[asdf.sum(0).sort_values(ascending=False).keys().tolist()]


asdf[asdf.index.isin([sname], level=0)].iloc[:, :50].columns

Index(['업무시설', '공동주택', '단독주택', '제2종근린생활시설', '제1종근린생활시설', '판매시설', '숙박시설',
       '교육연구시설', '공장', 'None', '종교시설', '자동차관련시설', '근린생활시설', '문화및집회시설', '의료시설',
       '창고시설', '판매및영업시설', '노유자시설', '운동시설', '방송통신시설', '교육연구및복지시설', '운수시설',
       '위락시설', '공공용시설', '위험물저장및처리시설', '수련시설', '분뇨.쓰레기처리시설', '학원', '연립주택',
       '장례시설', '자원순환관련시설', '시장', '관광휴게시설', '동물및식물관련시설', '관광숙박시설', '기숙사',
       '주유소'],
      dtype='object')


for sname in asdf.index.unique(level=0): #['합정', '홍대입구', '강남', '서울대입구(관악구청)', '서초', '성수', '뚝섬', '잠실(송파구청)']:
    print(f'{sname}역 {DIST_THRES}m 이내')
    asdf[asdf.index.isin([sname], level=0)].iloc[:, :10].plot()
    plt.xticks(range(0, 12*60+1, 12*10), range(1960, 2021, 10), rotation=90)
    plt.vlines((1985-1970)*12, 0, asdf[asdf.index.isin([sname], level=0)].iloc[:, :10].max().max(), colors='grey')
    plt.title(f'{sname}역 {DIST_THRES}m 이내')
    plt.legend(loc='center left', bbox_to_anchor=(1, 0.5))
    plt.show()

시청역 500m 이내

을지로입구역 500m 이내

을지로3가역 500m 이내

을지로4가역 500m 이내

동대문역사문화공원(DDP)역 500m 이내

신당역 500m 이내

상왕십리역 500m 이내

왕십리(성동구청)역 500m 이내

한양대역 500m 이내

뚝섬역 500m 이내