앞의 글은 아래에 있습니다.
4. 경도(X), 위도(Y) 값 구하기
경도 위도 값을 구할 차례입니다. 저는 카카오 API를 사용했습니다. (SQL과 데이터 분석 중심 설명이기 때문에 카카오API에 대한 자세한 설명은 제외합니다.)
카카오API를 호출해서 위도, 경도 값을 업데이트하기 위해서 파이썬을 사용합니다. 파이썬 소스코드는 아래와 같습니다.(아나콘다3, 파이참 환경입니다. 파이썬 설치가 불가능한 분은 다음 아래의 UPDATE 스크립트를 그대로 카피해서 사용하셔도 됩니다.)
-- 경도, 위도 가져오는 파이썬
import requests
import json
import pymysql
def find_gps():
#DB LINK
conn = pymysql.connect(user='ADMIN_SWEET', passwd='1q2w3e4r', host='127.0.0.1', port=13306, db='SWEET_DATA',charset='utf8')
cur = conn.cursor()
sql_text = "SELECT T1.StationNo, T1.Address FROM M_Station T1"
cur.execute(sql_text);
rows = cur.fetchall();
url = "https://dapi.kakao.com/v2/local/search/address.json";
header = {'Authorization': 'KakaoAK 각자키를 사용'}
for row in rows:
print(row)
_addr = str(row[1]);
_key = row[0]
query = "query=" + _addr;
r = requests.get(url, headers=header, params=query)
json_data = json.loads(r.text)
for i in json_data['documents'] :
# X 좌표값 => longitude
# Y 좌표값 => latitude
sql_update_text = """UPDATE M_Station SET PosXY = POINT(""" +i['x'] + """,""" + i['y'] + """) WHERE StationNo = '""" + str(_key) + """';""";
print(sql_update_text)
cur.execute(sql_update_text)
cur.execute('commit')
find_gps()
아래는 경도, 위도를 바로 업데이트하는 스크립트입니다.
-- 경도, 위도 바로 업데이트 하기 (너무 길어서 줄 번호는 생략합니다.)
UPDATE M_Station SET PosXY = POINT(126.97255256952047,37.557158852433425) WHERE StationNo = '150';
UPDATE M_Station SET PosXY = POINT(126.97698199254849,37.565438157005836) WHERE StationNo = '151';
UPDATE M_Station SET PosXY = POINT(126.98323798089602,37.57021448838956) WHERE StationNo = '152';
UPDATE M_Station SET PosXY = POINT(126.99203100001637,37.570428044296825) WHERE StationNo = '153';
UPDATE M_Station SET PosXY = POINT(127.00191528898299,37.57090762722926) WHERE StationNo = '154';
UPDATE M_Station SET PosXY = POINT(127.01116585737377,37.571779278087966) WHERE StationNo = '155';
UPDATE M_Station SET PosXY = POINT(127.02456540747554,37.57612364917105) WHERE StationNo = '156';
UPDATE M_Station SET PosXY = POINT(127.03469139400376,37.57820060253574) WHERE StationNo = '157';
UPDATE M_Station SET PosXY = POINT(127.04505339058124,37.58022614096847) WHERE StationNo = '158';
UPDATE M_Station SET PosXY = POINT(127.01674914262058,37.573371577168444) WHERE StationNo = '159';
UPDATE M_Station SET PosXY = POINT(126.97538891079977,37.563578179426656) WHERE StationNo = '201';
UPDATE M_Station SET PosXY = POINT(126.98237414667892,37.566051762475745) WHERE StationNo = '202';
UPDATE M_Station SET PosXY = POINT(126.99030643210962,37.56626531530101) WHERE StationNo = '203';
UPDATE M_Station SET PosXY = POINT(126.99785391218619,37.56663690408433) WHERE StationNo = '204';
UPDATE M_Station SET PosXY = POINT(127.0091320641583,37.56558781104745) WHERE StationNo = '205';
UPDATE M_Station SET PosXY = POINT(127.01954091431038,37.56565502277837) WHERE StationNo = '206';
UPDATE M_Station SET PosXY = POINT(127.0290302021847,37.564467359880524) WHERE StationNo = '207';
UPDATE M_Station SET PosXY = POINT(127.0366574155161,37.56122165079314) WHERE StationNo = '208';
UPDATE M_Station SET PosXY = POINT(127.04358539961328,37.55557905450867) WHERE StationNo = '209';
UPDATE M_Station SET PosXY = POINT(127.04736461242203,37.54718750384358) WHERE StationNo = '210';
UPDATE M_Station SET PosXY = POINT(127.05608956590137,37.54450230873498) WHERE StationNo = '211';
UPDATE M_Station SET PosXY = POINT(127.0691712451776,37.54041924881689) WHERE StationNo = '212';
UPDATE M_Station SET PosXY = POINT(127.08618081290898,37.53715364857336) WHERE StationNo = '213';
UPDATE M_Station SET PosXY = POINT(127.09466422420185,37.535164952269845) WHERE StationNo = '214';
UPDATE M_Station SET PosXY = POINT(127.10381323936149,37.52068718042725) WHERE StationNo = '215';
UPDATE M_Station SET PosXY = POINT(127.10031473197408,37.513319997745555) WHERE StationNo = '216';
UPDATE M_Station SET PosXY = POINT(127.0863730771204,37.51156865152352) WHERE StationNo = '217';
UPDATE M_Station SET PosXY = POINT(127.0733561309303,37.51096837383229) WHERE StationNo = '218';
UPDATE M_Station SET PosXY = POINT(127.06307527215017,37.50886235664123) WHERE StationNo = '219';
UPDATE M_Station SET PosXY = POINT(127.0489436033093,37.50450458903165) WHERE StationNo = '220';
UPDATE M_Station SET PosXY = POINT(127.03661876920279,37.50071762069987) WHERE StationNo = '221';
UPDATE M_Station SET PosXY = POINT(127.02830790088069,37.498164651039694) WHERE StationNo = '222';
UPDATE M_Station SET PosXY = POINT(127.0141576077151,37.49357566392058) WHERE StationNo = '223';
UPDATE M_Station SET PosXY = POINT(127.00758070181062,37.491824710937784) WHERE StationNo = '224';
UPDATE M_Station SET PosXY = POINT(126.9976935425379,37.481438109455894) WHERE StationNo = '225';
UPDATE M_Station SET PosXY = POINT(126.9814093612868,37.47652436655696) WHERE StationNo = '226';
UPDATE M_Station SET PosXY = POINT(126.96341086398692,37.47706437373934) WHERE StationNo = '227';
UPDATE M_Station SET PosXY = POINT(126.95264544509422,37.48115113161296) WHERE StationNo = '228';
UPDATE M_Station SET PosXY = POINT(126.94149651644463,37.48255531496967) WHERE StationNo = '229';
UPDATE M_Station SET PosXY = POINT(126.92980421555579,37.48421761465397) WHERE StationNo = '230';
UPDATE M_Station SET PosXY = POINT(126.91317513329555,37.48757633464119) WHERE StationNo = '231';
UPDATE M_Station SET PosXY = POINT(126.9014956225072,37.48531465452861) WHERE StationNo = '232';
UPDATE M_Station SET PosXY = POINT(126.89494460557201,37.49330995650912) WHERE StationNo = '233';
UPDATE M_Station SET PosXY = POINT(126.89108197001643,37.508771317645575) WHERE StationNo = '234';
UPDATE M_Station SET PosXY = POINT(126.89476532263198,37.51792164597545) WHERE StationNo = '235';
UPDATE M_Station SET PosXY = POINT(126.89662780191244,37.52570074348017) WHERE StationNo = '236';
UPDATE M_Station SET PosXY = POINT(126.90272923847847,37.534957406012765) WHERE StationNo = '237';
UPDATE M_Station SET PosXY = POINT(126.91463716307454,37.55011421506363) WHERE StationNo = '238';
UPDATE M_Station SET PosXY = POINT(126.92367442251489,37.556888697557625) WHERE StationNo = '239';
UPDATE M_Station SET PosXY = POINT(126.9361204389213,37.555440075383814) WHERE StationNo = '240';
UPDATE M_Station SET PosXY = POINT(126.94575729679175,37.55679639200007) WHERE StationNo = '241';
UPDATE M_Station SET PosXY = POINT(126.95603329132891,37.557359284038256) WHERE StationNo = '242';
UPDATE M_Station SET PosXY = POINT(126.96447506227653,37.559838065855615) WHERE StationNo = '243';
UPDATE M_Station SET PosXY = POINT(127.05072874381338,37.562005716412244) WHERE StationNo = '244';
UPDATE M_Station SET PosXY = POINT(127.04682212731294,37.56999015446607) WHERE StationNo = '245';
UPDATE M_Station SET PosXY = POINT(127.02456540747554,37.57612364917105) WHERE StationNo = '246';
UPDATE M_Station SET PosXY = POINT(126.88270348752339,37.51444322325958) WHERE StationNo = '247';
UPDATE M_Station SET PosXY = POINT(126.86606566370143,37.51269920666508) WHERE StationNo = '248';
UPDATE M_Station SET PosXY = POINT(126.85298994738748,37.51979797507366) WHERE StationNo = '249';
UPDATE M_Station SET PosXY = POINT(127.03803112066214,37.573939672123046) WHERE StationNo = '250';
UPDATE M_Station SET PosXY = POINT(126.90582369800002,37.65085626552864) WHERE StationNo = '309';
UPDATE M_Station SET PosXY = POINT(126.9187436123905,37.63684295896786) WHERE StationNo = '310';
UPDATE M_Station SET PosXY = POINT(126.9211598597772,37.618961821472446) WHERE StationNo = '311';
UPDATE M_Station SET PosXY = POINT(126.92998497034971,37.610206176235664) WHERE StationNo = '312';
UPDATE M_Station SET PosXY = POINT(126.93601831026713,37.60074200017985) WHERE StationNo = '313';
UPDATE M_Station SET PosXY = POINT(126.94424412257759,37.588708926043225) WHERE StationNo = '314';
UPDATE M_Station SET PosXY = POINT(126.95032164535576,37.58238306587019) WHERE StationNo = '315';
UPDATE M_Station SET PosXY = POINT(126.95774846781482,37.57441035829069) WHERE StationNo = '316';
UPDATE M_Station SET PosXY = POINT(126.97306191821602,37.575860041203214) WHERE StationNo = '317';
UPDATE M_Station SET PosXY = POINT(126.98582213739954,37.57672001005511) WHERE StationNo = '318';
UPDATE M_Station SET PosXY = POINT(126.99199470091352,37.571141630115804) WHERE StationNo = '319';
UPDATE M_Station SET PosXY = POINT(126.99265172209068,37.566276296521835) WHERE StationNo = '320';
UPDATE M_Station SET PosXY = POINT(127.00545974413328,37.55902878277881) WHERE StationNo = '322';
UPDATE M_Station SET PosXY = POINT(127.01082377161588,37.55455946560875) WHERE StationNo = '323';
UPDATE M_Station SET PosXY = POINT(127.01589250869206,37.54811134857417) WHERE StationNo = '324';
UPDATE M_Station SET PosXY = POINT(127.01833055740015,37.54104716078602) WHERE StationNo = '325';
UPDATE M_Station SET PosXY = POINT(127.02848148490598,37.526430913588165) WHERE StationNo = '326';
UPDATE M_Station SET PosXY = POINT(127.02032892209975,37.51643146679401) WHERE StationNo = '327';
UPDATE M_Station SET PosXY = POINT(127.01122040598548,37.51279983559945) WHERE StationNo = '328';
UPDATE M_Station SET PosXY = POINT(127.00444243570533,37.50464799363718) WHERE StationNo = '329';
UPDATE M_Station SET PosXY = POINT(127.0141576077151,37.49357566392058) WHERE StationNo = '330';
UPDATE M_Station SET PosXY = POINT(127.01620479414689,37.48514196837421) WHERE StationNo = '331';
UPDATE M_Station SET PosXY = POINT(127.03454374096688,37.48393428252617) WHERE StationNo = '332';
UPDATE M_Station SET PosXY = POINT(127.04661648311405,37.48696112783275) WHERE StationNo = '333';
UPDATE M_Station SET PosXY = POINT(127.05532122481543,37.49086414202696) WHERE StationNo = '334';
UPDATE M_Station SET PosXY = POINT(127.06342962689939,37.49456140410784) WHERE StationNo = '335';
UPDATE M_Station SET PosXY = POINT(127.07171829171698,37.49671547492159) WHERE StationNo = '336';
UPDATE M_Station SET PosXY = POINT(127.07949534292234,37.4937227596509) WHERE StationNo = '337';
UPDATE M_Station SET PosXY = POINT(127.0844509601109,37.4840749361128) WHERE StationNo = '338';
UPDATE M_Station SET PosXY = POINT(127.10185851088313,37.48738773253322) WHERE StationNo = '339';
UPDATE M_Station SET PosXY = POINT(127.11848381630814,37.49274223205612) WHERE StationNo = '340';
UPDATE M_Station SET PosXY = POINT(127.12406558337433,37.495601702768255) WHERE StationNo = '341';
UPDATE M_Station SET PosXY = POINT(127.12788105549203,37.50226507641803) WHERE StationNo = '342';
UPDATE M_Station SET PosXY = POINT(127.07901229284572,37.67029255404396) WHERE StationNo = '409';
UPDATE M_Station SET PosXY = POINT(127.07357594636086,37.66090424876206) WHERE StationNo = '410';
UPDATE M_Station SET PosXY = POINT(127.06303203591438,37.65626849637051) WHERE StationNo = '411';
UPDATE M_Station SET PosXY = POINT(127.04771291366652,37.65321595105881) WHERE StationNo = '412';
UPDATE M_Station SET PosXY = POINT(127.03488286973543,37.6488488732005) WHERE StationNo = '413';
UPDATE M_Station SET PosXY = POINT(127.02535610786357,37.63774397254115) WHERE StationNo = '414';
UPDATE M_Station SET PosXY = POINT(127.0261044533112,37.62644184231728) WHERE StationNo = '415';
UPDATE M_Station SET PosXY = POINT(127.0301002308641,37.61328646943786) WHERE StationNo = '416';
UPDATE M_Station SET PosXY = POINT(127.02482344269079,37.60318219863949) WHERE StationNo = '417';
UPDATE M_Station SET PosXY = POINT(127.0164771967841,37.592717746005185) WHERE StationNo = '418';
UPDATE M_Station SET PosXY = POINT(127.00595101268726,37.588393983179365) WHERE StationNo = '419';
UPDATE M_Station SET PosXY = POINT(127.00197896746458,37.58180245102366) WHERE StationNo = '420';
UPDATE M_Station SET PosXY = POINT(127.00954468675879,37.57041351149444) WHERE StationNo = '421';
UPDATE M_Station SET PosXY = POINT(127.00755189864863,37.56507976088089) WHERE StationNo = '422';
UPDATE M_Station SET PosXY = POINT(126.99411000772092,37.561173134797826) WHERE StationNo = '423';
UPDATE M_Station SET PosXY = POINT(126.98594737006931,37.560909354360476) WHERE StationNo = '424';
UPDATE M_Station SET PosXY = POINT(126.97823812825729,37.558536761057404) WHERE StationNo = '425';
UPDATE M_Station SET PosXY = POINT(126.97280752153232,37.5532017297038) WHERE StationNo = '426';
UPDATE M_Station SET PosXY = POINT(126.97187348361902,37.54519343558292) WHERE StationNo = '427';
UPDATE M_Station SET PosXY = POINT(126.97301340873837,37.53459433761754) WHERE StationNo = '428';
UPDATE M_Station SET PosXY = POINT(126.96821830742816,37.529616013993916) WHERE StationNo = '429';
UPDATE M_Station SET PosXY = POINT(126.97368292469619,37.52252106548174) WHERE StationNo = '430';
UPDATE M_Station SET PosXY = POINT(126.98026736778657,37.502869646133625) WHERE StationNo = '431';
UPDATE M_Station SET PosXY = POINT(126.98219812464853,37.487552836479765) WHERE StationNo = '432';
UPDATE M_Station SET PosXY = POINT(126.9815946871435,37.476863175208685) WHERE StationNo = '433';
UPDATE M_Station SET PosXY = POINT(126.98865563982616,37.46485899919096) WHERE StationNo = '434';
UPDATE M_Station SET PosXY = POINT(126.8126306092129,37.57700464071319) WHERE StationNo = '2511';
UPDATE M_Station SET PosXY = POINT(126.80599997709584,37.572269120979904) WHERE StationNo = '2512';
UPDATE M_Station SET PosXY = POINT(126.80157133547586,37.56214539460694) WHERE StationNo = '2513';
UPDATE M_Station SET PosXY = POINT(126.81105778784394,37.56147620063578) WHERE StationNo = '2514';
UPDATE M_Station SET PosXY = POINT(126.82554810761461,37.56019746267116) WHERE StationNo = '2515';
UPDATE M_Station SET PosXY = POINT(126.83731235802402,37.55902854821429) WHERE StationNo = '2516';
UPDATE M_Station SET PosXY = POINT(126.83641074062257,37.549001008797624) WHERE StationNo = '2517';
UPDATE M_Station SET PosXY = POINT(126.84049622173754,37.5414346084945) WHERE StationNo = '2518';
UPDATE M_Station SET PosXY = POINT(126.84661226903779,37.53195698666088) WHERE StationNo = '2519';
UPDATE M_Station SET PosXY = POINT(126.85729175247246,37.52520926282623) WHERE StationNo = '2520';
UPDATE M_Station SET PosXY = POINT(126.86474281159968,37.52619832069603) WHERE StationNo = '2521';
UPDATE M_Station SET PosXY = POINT(126.87535270115818,37.524451239657594) WHERE StationNo = '2522';
UPDATE M_Station SET PosXY = POINT(126.88686737317903,37.52551159691278) WHERE StationNo = '2523';
UPDATE M_Station SET PosXY = POINT(126.8952904240174,37.52422552622088) WHERE StationNo = '2524';
UPDATE M_Station SET PosXY = POINT(126.90495781244745,37.52273443371112) WHERE StationNo = '2525';
UPDATE M_Station SET PosXY = POINT(126.91433478893137,37.517667159567864) WHERE StationNo = '2526';
UPDATE M_Station SET PosXY = POINT(126.92448389420136,37.52184392588269) WHERE StationNo = '2527';
UPDATE M_Station SET PosXY = POINT(126.9328638413941,37.52708561146302) WHERE StationNo = '2528';
UPDATE M_Station SET PosXY = POINT(126.94591912537975,37.53958378800099) WHERE StationNo = '2529';
UPDATE M_Station SET PosXY = POINT(126.95136518782198,37.54452723838077) WHERE StationNo = '2530';
UPDATE M_Station SET PosXY = POINT(126.95661507072609,37.553362673930344) WHERE StationNo = '2531';
UPDATE M_Station SET PosXY = POINT(126.96337438660855,37.560994609486485) WHERE StationNo = '2532';
UPDATE M_Station SET PosXY = POINT(126.96663637960322,37.565857352826356) WHERE StationNo = '2533';
UPDATE M_Station SET PosXY = POINT(126.97678991701194,37.571622553368286) WHERE StationNo = '2534';
UPDATE M_Station SET PosXY = POINT(126.99006110477345,37.57256865945283) WHERE StationNo = '2535';
UPDATE M_Station SET PosXY = POINT(126.99805763167915,37.56752348754336) WHERE StationNo = '2536';
UPDATE M_Station SET PosXY = POINT(127.00591742504709,37.564575296239745) WHERE StationNo = '2537';
UPDATE M_Station SET PosXY = POINT(127.0138080439052,37.560293101373034) WHERE StationNo = '2538';
UPDATE M_Station SET PosXY = POINT(127.02006312784418,37.554533029207484) WHERE StationNo = '2539';
UPDATE M_Station SET PosXY = POINT(127.02923112964419,37.55727735595999) WHERE StationNo = '2540';
UPDATE M_Station SET PosXY = POINT(127.0366574155161,37.56122165079314) WHERE StationNo = '2541';
UPDATE M_Station SET PosXY = POINT(127.04316151445926,37.56628305149315) WHERE StationNo = '2542';
UPDATE M_Station SET PosXY = POINT(127.05318118517143,37.566444746492174) WHERE StationNo = '2543';
UPDATE M_Station SET PosXY = POINT(127.0644189574387,37.561501690379174) WHERE StationNo = '2544';
UPDATE M_Station SET PosXY = POINT(127.07901512810203,37.557362654799554) WHERE StationNo = '2545';
UPDATE M_Station SET PosXY = POINT(127.08937161621444,37.55239978385352) WHERE StationNo = '2546';
UPDATE M_Station SET PosXY = POINT(127.10368438183367,37.54527740465725) WHERE StationNo = '2547';
UPDATE M_Station SET PosXY = POINT(127.12374300170981,37.53857988247639) WHERE StationNo = '2548';
UPDATE M_Station SET PosXY = POINT(127.13353161821865,37.535520236233765) WHERE StationNo = '2549';
UPDATE M_Station SET PosXY = POINT(127.13999863971951,37.53781209828301) WHERE StationNo = '2550';
UPDATE M_Station SET PosXY = POINT(127.14296914789105,37.54573012263343) WHERE StationNo = '2551';
UPDATE M_Station SET PosXY = POINT(127.14400274117364,37.55126099421607) WHERE StationNo = '2552';
UPDATE M_Station SET PosXY = POINT(127.1539511120498,37.55504704640015) WHERE StationNo = '2553';
UPDATE M_Station SET PosXY = POINT(127.16582876743654,37.556663567935374) WHERE StationNo = '2554';
UPDATE M_Station SET PosXY = POINT(127.1363739058897,37.528135991320916) WHERE StationNo = '2555';
UPDATE M_Station SET PosXY = POINT(127.13060127102182,37.51608710743359) WHERE StationNo = '2556';
UPDATE M_Station SET PosXY = POINT(127.12585594283948,37.50855626435071) WHERE StationNo = '2557';
UPDATE M_Station SET PosXY = POINT(127.12788105549203,37.50226507641803) WHERE StationNo = '2558';
UPDATE M_Station SET PosXY = POINT(127.13516074116417,37.49781321258245) WHERE StationNo = '2559';
UPDATE M_Station SET PosXY = POINT(127.14403142719337,37.49320759944486) WHERE StationNo = '2560';
UPDATE M_Station SET PosXY = POINT(127.15232084931651,37.49467124867699) WHERE StationNo = '2561';
UPDATE M_Station SET PosXY = POINT(126.91551472523976,37.59874332069344) WHERE StationNo = '2611';
UPDATE M_Station SET PosXY = POINT(126.92296737989116,37.60603923327726) WHERE StationNo = '2612';
UPDATE M_Station SET PosXY = POINT(126.92998497034971,37.610206176235664) WHERE StationNo = '2613';
UPDATE M_Station SET PosXY = POINT(126.93280900514041,37.6183996051419) WHERE StationNo = '2614';
UPDATE M_Station SET PosXY = POINT(126.91718136927429,37.61121059714101) WHERE StationNo = '2616';
UPDATE M_Station SET PosXY = POINT(126.91391017297514,37.591826152095116) WHERE StationNo = '2617';
UPDATE M_Station SET PosXY = POINT(126.91005600501946,37.584193674614255) WHERE StationNo = '2618';
UPDATE M_Station SET PosXY = POINT(126.90192720232601,37.57716305593089) WHERE StationNo = '2619';
UPDATE M_Station SET PosXY = POINT(126.89903434554247,37.56994184911615) WHERE StationNo = '2620';
UPDATE M_Station SET PosXY = POINT(126.90335776672575,37.56342586335406) WHERE StationNo = '2621';
UPDATE M_Station SET PosXY = POINT(126.9100355097372,37.5560573836446) WHERE StationNo = '2622';
UPDATE M_Station SET PosXY = POINT(126.91354286903871,37.54912232079213) WHERE StationNo = '2623';
UPDATE M_Station SET PosXY = POINT(126.92241177988097,37.5477733797523) WHERE StationNo = '2624';
UPDATE M_Station SET PosXY = POINT(126.93194025779533,37.54748733268153) WHERE StationNo = '2625';
UPDATE M_Station SET PosXY = POINT(126.94246411919256,37.54766952522553) WHERE StationNo = '2626';
UPDATE M_Station SET PosXY = POINT(126.95136518782198,37.54452723838077) WHERE StationNo = '2627';
UPDATE M_Station SET PosXY = POINT(126.9613166204824,37.53933395166948) WHERE StationNo = '2628';
UPDATE M_Station SET PosXY = POINT(126.97408110462513,37.53564334162721) WHERE StationNo = '2629';
UPDATE M_Station SET PosXY = POINT(126.98702937858346,37.534830968963725) WHERE StationNo = '2630';
UPDATE M_Station SET PosXY = POINT(126.99372290017801,37.53448192611647) WHERE StationNo = '2631';
UPDATE M_Station SET PosXY = POINT(127.00174704299799,37.54029533464754) WHERE StationNo = '2632';
UPDATE M_Station SET PosXY = POINT(127.00688478144716,37.54799328551464) WHERE StationNo = '2633';
UPDATE M_Station SET PosXY = POINT(127.01012201494936,37.55389278946013) WHERE StationNo = '2634';
UPDATE M_Station SET PosXY = POINT(127.0138080439052,37.560293101373034) WHERE StationNo = '2635';
UPDATE M_Station SET PosXY = POINT(127.01615443943429,37.56627541998749) WHERE StationNo = '2636';
UPDATE M_Station SET PosXY = POINT(127.01568028457561,37.57220043049817) WHERE StationNo = '2637';
UPDATE M_Station SET PosXY = POINT(127.01521096796488,37.580017519973765) WHERE StationNo = '2638';
UPDATE M_Station SET PosXY = POINT(127.01938754998206,37.58532916538653) WHERE StationNo = '2639';
UPDATE M_Station SET PosXY = POINT(127.0362868728223,37.590648244448744) WHERE StationNo = '2641';
UPDATE M_Station SET PosXY = POINT(127.04118420104847,37.60142249412873) WHERE StationNo = '2642';
UPDATE M_Station SET PosXY = POINT(127.04832616862042,37.606263508926446) WHERE StationNo = '2643';
UPDATE M_Station SET PosXY = POINT(127.05642453926866,37.61055222857029) WHERE StationNo = '2644';
UPDATE M_Station SET PosXY = POINT(127.06611877103421,37.61504853500426) WHERE StationNo = '2645';
UPDATE M_Station SET PosXY = POINT(127.07499697577263,37.61790476490005) WHERE StationNo = '2646';
UPDATE M_Station SET PosXY = POINT(127.08374349284219,37.61989547071212) WHERE StationNo = '2647';
UPDATE M_Station SET PosXY = POINT(127.09075903378347,37.61765223007443) WHERE StationNo = '2648';
UPDATE M_Station SET PosXY = POINT(127.05313592571325,37.70011859930464) WHERE StationNo = '2711';
UPDATE M_Station SET PosXY = POINT(127.04652958563797,37.6891222729532) WHERE StationNo = '2712';
UPDATE M_Station SET PosXY = POINT(127.05537345041247,37.67762557714484) WHERE StationNo = '2713';
UPDATE M_Station SET PosXY = POINT(127.05768986266521,37.66508643213007) WHERE StationNo = '2714';
UPDATE M_Station SET PosXY = POINT(127.0605194502251,37.65469489527157) WHERE StationNo = '2715';
UPDATE M_Station SET PosXY = POINT(127.06424615612472,37.64489022881391) WHERE StationNo = '2716';
UPDATE M_Station SET PosXY = POINT(127.06798672392947,37.63645131422817) WHERE StationNo = '2717';
UPDATE M_Station SET PosXY = POINT(127.07300633481204,37.625477910674306) WHERE StationNo = '2718';
UPDATE M_Station SET PosXY = POINT(127.07499697577263,37.61790476490005) WHERE StationNo = '2719';
UPDATE M_Station SET PosXY = POINT(127.07776672574502,37.6106265895414) WHERE StationNo = '2720';
UPDATE M_Station SET PosXY = POINT(127.07946954444269,37.60157951886236) WHERE StationNo = '2721';
UPDATE M_Station SET PosXY = POINT(127.08756682062385,37.58838327587009) WHERE StationNo = '2723';
UPDATE M_Station SET PosXY = POINT(127.08844098507846,37.580796263833484) WHERE StationNo = '2724';
UPDATE M_Station SET PosXY = POINT(127.0870383398057,37.57402903933762) WHERE StationNo = '2725';
UPDATE M_Station SET PosXY = POINT(127.0840719599339,37.565525794437484) WHERE StationNo = '2726';
UPDATE M_Station SET PosXY = POINT(127.07901512810203,37.557362654799554) WHERE StationNo = '2727';
UPDATE M_Station SET PosXY = POINT(127.07455105814755,37.547872644193866) WHERE StationNo = '2728';
UPDATE M_Station SET PosXY = POINT(127.07104541845995,37.540839804010105) WHERE StationNo = '2729';
UPDATE M_Station SET PosXY = POINT(127.06671932273433,37.53158725107477) WHERE StationNo = '2730';
UPDATE M_Station SET PosXY = POINT(127.0520390352376,37.51914278253384) WHERE StationNo = '2731';
UPDATE M_Station SET PosXY = POINT(127.0413007514142,37.51719004644419) WHERE StationNo = '2732';
UPDATE M_Station SET PosXY = POINT(127.031462969986,37.51420534847362) WHERE StationNo = '2733';
UPDATE M_Station SET PosXY = POINT(127.02165763527358,37.511198199421855) WHERE StationNo = '2734';
UPDATE M_Station SET PosXY = POINT(127.0114956748719,37.50812539767412) WHERE StationNo = '2735';
UPDATE M_Station SET PosXY = POINT(127.00444243570533,37.50464799363718) WHERE StationNo = '2736';
UPDATE M_Station SET PosXY = POINT(126.99318861355258,37.487532357103085) WHERE StationNo = '2737';
UPDATE M_Station SET PosXY = POINT(126.98102282962365,37.485015414425256) WHERE StationNo = '2738';
UPDATE M_Station SET PosXY = POINT(126.97170632193176,37.48444411335394) WHERE StationNo = '2739';
UPDATE M_Station SET PosXY = POINT(126.95406103853256,37.4958128895882) WHERE StationNo = '2740';
UPDATE M_Station SET PosXY = POINT(126.94770059895008,37.503209307157675) WHERE StationNo = '2741';
UPDATE M_Station SET PosXY = POINT(126.93874232967946,37.504664630119066) WHERE StationNo = '2742';
UPDATE M_Station SET PosXY = POINT(126.92822455086642,37.499721196936385) WHERE StationNo = '2743';
UPDATE M_Station SET PosXY = POINT(126.92048905018001,37.49991086100452) WHERE StationNo = '2744';
UPDATE M_Station SET PosXY = POINT(126.90984030067348,37.50011585114009) WHERE StationNo = '2745';
UPDATE M_Station SET PosXY = POINT(126.89660991849864,37.4927419893405) WHERE StationNo = '2746';
UPDATE M_Station SET PosXY = POINT(126.8873428283549,37.48616337072886) WHERE StationNo = '2747';
UPDATE M_Station SET PosXY = POINT(126.8825603874058,37.481596008752796) WHERE StationNo = '2748';
UPDATE M_Station SET PosXY = POINT(126.86801218606675,37.47611693573971) WHERE StationNo = '2749';
UPDATE M_Station SET PosXY = POINT(126.85463844176687,37.47936649568283) WHERE StationNo = '2750';
UPDATE M_Station SET PosXY = POINT(126.83871283951193,37.48682059028633) WHERE StationNo = '2751';
UPDATE M_Station SET PosXY = POINT(126.82338161808453,37.492150645572) WHERE StationNo = '2752';
UPDATE M_Station SET PosXY = POINT(126.81139624459261,37.50619860942323) WHERE StationNo = '2753';
UPDATE M_Station SET PosXY = POINT(126.79742821318048,37.505483510808496) WHERE StationNo = '2754';
UPDATE M_Station SET PosXY = POINT(126.78686559116848,37.50364489731701) WHERE StationNo = '2755';
UPDATE M_Station SET PosXY = POINT(126.77649905200991,37.50297341171852) WHERE StationNo = '2756';
UPDATE M_Station SET PosXY = POINT(126.76406251971743,37.504643113710536) WHERE StationNo = '2757';
UPDATE M_Station SET PosXY = POINT(126.75318785519286,37.505810217122445) WHERE StationNo = '2758';
UPDATE M_Station SET PosXY = POINT(126.74190705378871,37.506582594807355) WHERE StationNo = '2759';
UPDATE M_Station SET PosXY = POINT(126.73176253798594,37.50707897171457) WHERE StationNo = '2760';
UPDATE M_Station SET PosXY = POINT(126.72085774246696,37.50760511901552) WHERE StationNo = '2761';
UPDATE M_Station SET PosXY = POINT(127.1275418321658,37.550141060814475) WHERE StationNo = '2811';
UPDATE M_Station SET PosXY = POINT(127.12374300170981,37.53857988247639) WHERE StationNo = '2812';
UPDATE M_Station SET PosXY = POINT(127.12057570947756,37.53067237616664) WHERE StationNo = '2813';
UPDATE M_Station SET PosXY = POINT(127.11242844575287,37.517623092550025) WHERE StationNo = '2814';
UPDATE M_Station SET PosXY = POINT(127.1042170589561,37.51497806972679) WHERE StationNo = '2815';
UPDATE M_Station SET PosXY = POINT(127.10634432937081,37.50594811233585) WHERE StationNo = '2816';
UPDATE M_Station SET PosXY = POINT(127.11217989255323,37.4996861342522) WHERE StationNo = '2817';
UPDATE M_Station SET PosXY = POINT(127.11848381630814,37.49274223205612) WHERE StationNo = '2818';
UPDATE M_Station SET PosXY = POINT(127.1222907853981,37.48626191297808) WHERE StationNo = '2819';
UPDATE M_Station SET PosXY = POINT(127.12642443090823,37.478119655330296) WHERE StationNo = '2820';
UPDATE M_Station SET PosXY = POINT(127.12676975735933,37.47105537138374) WHERE StationNo = '2821';
UPDATE M_Station SET PosXY = POINT(127.14994228203592,37.456525568472756) WHERE StationNo = '2822';
UPDATE M_Station SET PosXY = POINT(127.15980099129938,37.451567893801) WHERE StationNo = '2823';
UPDATE M_Station SET PosXY = POINT(127.15668641533487,37.445142434318456) WHERE StationNo = '2824';
UPDATE M_Station SET PosXY = POINT(127.14810817259715,37.44118542933686) WHERE StationNo = '2825';
UPDATE M_Station SET PosXY = POINT(127.14035387124626,37.43746108672792) WHERE StationNo = '2826';
UPDATE M_Station SET PosXY = POINT(127.12954981193997,37.4338513401459) WHERE StationNo = '2827';
카카오 API로 경도, 위도를 찾았지만, 두 군데(안암역, 상봉역)는 카카오 API로 찾을 수가 없었습니다. 아마도 주소가 약간 안 맞아서 그런 것 아닐까 생각이 듭니다. 구글 지도로 직접 경도와 위도를 찾아서 업데이트합니다.
-- 못 찾은 경도, 위도 수작업 업데이트.
UPDATE M_Station SET PosXY = POINT(127.029137,37.586296) WHERE StationNo = 2640;
UPDATE M_Station SET PosXY = POINT(127.085739,37.595627) WHERE StationNo = 2722;
5. 검색해보기
POINT 자료형에서 데이터를 빨리 찾기 위해서는 SPATIAL 인덱스를 만들어서 활용합니다. 그런데 SPATIAL 인덱스를 만들려면 해당 컬럼이 NOT NULL이어야 합니다. 아래와 같이 PosXY를 NOT NULL로 변경하고 인덱스를 만듭니다.
-- SPATIAL INDEX 생성
ALTER TABLE M_Station MODIFY PosXY POINT NOT NULL;
CREATE SPATIAL INDEX SX_M_Station_1 ON M_Station(PosXY);
경도와, 위도, 거리(미터)를 변수로 받아서 범위를 리턴해주는 함수를 추가합니다. (함수 관련 로직과 검색에 대해서는 https://purumae.tistory.com/198 블로그 내용을 참고해주시면 감사하겠습니다.)
!! mysql8 은 루트로 SET GLOBAL log_bin_trust_function_creators = 1; 실행 필요함.
-- 범위 값을 구하는 함수 생성
DELIMITER $$
CREATE FUNCTION UFN_GetDiagonal(
lon decimal(30,18)
,lat decimal(30,18)
,MBR_length decimal(30,18)
) RETURNS VARCHAR(500)
BEGIN
DECLARE lon_diff decimal(30,18);
DECLARE lat_diff decimal(30,18);
DECLARE diagonal varchar(500);
SET lon_diff = MBR_length / 2 / ST_DISTANCE_SPHERE(POINT(lon, lat), POINT(lon + IF(lon < 0, 1, -1), lat));
SET lat_diff = MBR_length / 2 / ST_DISTANCE_SPHERE(POINT(lon, lat), POINT(lon, lat + IF(lat < 0, 1, -1)));
SET diagonal = CONCAT('LINESTRING(', lon - IF(lon < 0, 1, -1) * lon_diff, ' ', lat - IF(lon < 0, 1, -1) * lat_diff, ',', lon + IF(lon < 0, 1, -1) * lon_diff, ' ', lat + IF(lon < 0, 1, -1) * lat_diff, ')');
RETURN diagonal;
END$$
DELIMITER ;
이제, 검색을 해보겠습니다. 제 지금 위치(경도=127.0624762, 위도=37.6387354)를 이용합니다. 반경 1키로 이내 지하철 역을 조회합니다.
-- 위치 조회
SELECT *
FROM M_Station T1
WHERE MBRCONTAINS(ST_LINESTRINGFROMTEXT(UFN_GetDiagonal(127.0624762,37.6387354,1000)), T1.PosXY);
아래와 같이 잘 조회됩니다.
'데이터분석 > SQL을 이용한 데이터 분석' 카테고리의 다른 글
| 부동산 분석, 비싼 동네에는 뭐가 많지? 2/2 (0) | 2020.11.10 |
|---|---|
| 부동산 분석, 비싼 동네에는 뭐가 많지? 1/2 (0) | 2020.11.09 |
| 인구 데이터 수집 - 어디에 치킨집을 열어야 할까? (0) | 2020.11.04 |
| 상가 데이터 수집 2/2 (0) | 2020.10.30 |
| 상가 데이터 수집 - 1/2 (0) | 2020.10.30 |
| 서울 지하철 경도위도 설정 1/2 (0) | 2020.10.29 |
| 김밥 말러 갑시다 2/2 (0) | 2020.10.25 |
| 김밥 말러 갑시다 1/2 (0) | 2020.10.25 |