MySQL里的经纬度转为Geohash后插回数据库
from pygeohash import encode, decode
import plotly
import plotly. plotly as pyf
import plotly. graph_objs as go
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 plotly. 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 geohashsql ( geohash) :
conn = pymysql. connect(
host= 'localhost' ,
port= 3306 ,
user= 'root' ,
passwd= 'xu19931026' ,
db= 'cd_taxi' ,
charset= 'utf8'
)
if conn:
print ( "2次连接成功!" )
geohash= ( " " . join( geohash) )
cursor = conn. cursor( )
sql = "UPDATE Order_Data SET geo=%s WHERE id=%s"
cursor. execute( sql, ( geohash, id ) )
conn. commit( )
conn. close( )
def mysql ( ) :
conn = pymysql. connect(
host= 'localhost' ,
port= 3306 ,
user= 'root' ,
passwd= 'xu19931026' ,
db= 'cd_taxi' ,
charset= 'utf8'
)
if conn:
print ( "连接成功!" )
cursor = conn. cursor( )
sql = "select CAST(BeginLongitude as CHAR(11)) as BeginLongitude,CAST(BeginLatitude as CHAR(10)) as BeginLatitude FROM Order_Data where id=%s"
cursor. execute( sql, id )
result= cursor. fetchall( )
df= list ( result)
lon = [ ]
lat = [ ]
conn. close( )
for point in df:
lon. append( float ( point[ 0 ] ) )
lat. append( float ( point[ 1 ] ) )
return lon, lat
def get_lonandlat ( geo) :
lat, lon = geohash. decode( geo)
return lat, lon
def get_geohash ( lon, lat) :
geo = geohash. encode( lat, lon)
return geo
def get_geolist ( lon, lat) :
p = [ ]
for i in range ( len ( lon) ) :
result = get_geohash( lon[ i] , lat[ i] )
p. append( result)
return p
def top_geohash ( geohash1, n) :
new_total = [ ]
block_dict = { }
central_dict = { }
for i in geohash1:
new_total. append( i[ : n] )
return new_total
if __name__ == "__main__" :
id = 1
while id < 430455 :
lon, lat= mysql( )
geohash1= get_geolist( lon, lat)
geohashsql( geohash1)
id += 1
print ( id - 1 )
print ( '处理完毕' )