插入数据库Geohash（haikou数据）

插入数据库Geohash（haikou数据）

from pygeohash import encode, decode
import plotly
import numpy as np
import pandas as pd
import math
from matplotlib.path import Path
import numpy as np
import plotly.offline as of
import plotly.graph_objs as go
import chart_studio.plotly as py
import numpy as np
import pandas as pd
import folium
import webbrowser
from folium.plugins import HeatMap
import datetime
import time
import pymysql.cursors
import decimal
import geohash


def mysql(id):
    conn = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='xu19931026',
        db='hk_taxi',
        charset='utf8'
    )
    cursor = conn.cursor()  # 获取游标
    sql = "select CAST(starting_lng as CHAR(11)) as BeginLongitude,CAST(starting_lat as CHAR(10)) as BeginLatitude FROM haikou_1 where id=%s"  # sql语句
    cursor.execute(sql,id)
    result=cursor.fetchall()
    df=list(result)  #将元组转换为列表
    lon = []
    lat = []
    conn.close()#关闭数据库连接
    for point in df:
        lon.append(float(point[0]))  #将字符串的经纬度转换为float格式
        lat.append(float(point[1]))
    return lon,lat
def get_geohash(lon, lat):
    #生成Geohash
    geo = geohash.encode(lat, lon)  # precision=9可以加精度
    return geo
def get_geolist(lon,lat):  #根据MySQL出的经纬度生成Geohash
    p = []
    for i in range(len(lon)):
        result = get_geohash(lon[i],lat[i])
        p.append(result)
    return p
def geohashsql(geohash,id):
    conn = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='xu19931026',
        db='hk_taxi',
        charset='utf8'
    )
    geohash=(" ".join(geohash)) #列表转换为字符串
    cursor = conn.cursor()  # 获取游标
    sql = "UPDATE haikou_1 SET geo=%s WHERE id=%s"  # sql语句
    cursor.execute(sql,(geohash,id))
    conn.commit() #不加这一句不执行
    conn.close()  # 关闭数据库连接
#id最大12374604最小是1
#departure_time 从2017-04-29 07:20:00到2017-11-01 07:20:00
if __name__ == "__main__":
    time_start = time.time()
    id = 1000
    while id < 12374605:
        lon,lat=mysql(id) # 获取MySQL里的经度，纬度，经度纬度组成的列表
        geohash1=get_geolist(lon,lat) # 根据经纬度获得geohash1列表
        geohashsql(geohash1,id)
        id += 1
        if id%50000==0:
            time_end = time.time()
            yongshi=time_end-time_start
            t=(12374604-id)/id*yongshi
            print('已经完成'+str(id)+'**已用时**'+str(yongshi)+'还需要'+str(t)+'******')
    print('处理完毕,请在数据库中查看')