def station():
# 连接数据库
SHParking1 = db('/Users/chaidi/Documents/RA-Documents/EVcharge.db')
# 读取数据
coords_tuple = SHParking1.select(table='station_pub')
counter = 0
# 合并经纬度
coords_post = ''
# 记录停车场ID
opid = []
stid = []
# 循环获取新坐标
for coords in coords_tuple:
counter = counter + 1
coords_post = coords_post + '%s,%s' % (coords[5], coords[4])
opid.append(coords[0])
stid.append(coords[2])
# 每100组坐标请求一次
if counter % 100 == 0 or counter == coords_tuple.__len__():
coords_baidu = getBaiduGPS(coords_post)
# 如果获取成功，写入数据库
if coords_baidu:
for i in range(0, coords_baidu.__len__()):
SHParking1.insert(table='stationBaiduGPS',
value=[(opid[i], stid[i],
coords_baidu[i]['y'],
coords_baidu[i]['x'])])
opid = []
stid = []
coords_post = ''
else:
coords_post = coords_post + ';'
def poi2():
# 连接数据库
poi2 = db('/Users/chaidi/Documents/RA-Documents/poi2.db')
# 读取数据
coords_tuple = poi2.select(table='poi')
counter = 0
# 合并经纬度
coords_post = ''
# 循环获取新坐标
for coords in coords_tuple:
counter = counter + 1
coords_post = coords_post + '%s,%s' % (coords[5], coords[4])
# 每100组坐标请求一次
if counter % 100 == 0 or counter == coords_tuple.__len__():
coords_baidu = getBaiduGPS(coords_post)
# 如果获取成功，写入数据库
if coords_baidu:
numbers = coords_baidu.__len__()
for i in range(0, numbers):
tmp = [e for e in coords_tuple[counter - numbers + i]]
tmp.append(coords_baidu[i]['y'])
tmp.append(coords_baidu[i]['x'])
insert_value = tuple(tmp)
# insert_value = (coords[0], coords[1],coords[2],coords[3],coords[4],coords[5],coords[6],
# coords[7],coords[8],coords_baidu[i]['y'], coords_baidu[i]['x'])
poi2.insert(table='poiWithBaiduGPS', value=[insert_value])
print([insert_value])
coords_post = ''
else:
coords_post = coords_post + ';'
def park():
# 连接数据库
SHParking1 = db(os.path.join(BASE_DIR, 'db/SHParking.db'))
# 读取数据
coords_tuple = SHParking1.select(table='ParkStaticData')
counter = 0
# 合并经纬度
coords_post = ''
# 记录停车场ID
parkIDList = []
# 循环获取新坐标
for coords in coords_tuple:
counter = counter + 1
coords_post = coords_post + '%s,%s' % (coords[8], coords[7])
parkIDList.append(coords[0])
# 每100组坐标请求一次
if counter % 100 == 0 or counter == coords_tuple.__len__():
coords_baidu = getBaiduGPS(coords_post)
# 如果获取成功，写入数据库
if coords_baidu:
for i in range(0, coords_baidu.__len__()):
SHParking1.insert(table='ParkBaiduGPS',
value=[
(parkIDList[i], coords_baidu[i]['y'],
coords_baidu[i]['x'])
])
parkIDList = []
coords_post = ''
else:
coords_post = coords_post + ';'
def clear_data():
map_db = db(os.path.join(BASE_DIR, 'db/map.db'))
map_0 = map_db.select(table='map_0', cols='parkID,stid,opid')
for record in map_0:
parkID = record[0]
stid = record[1]
opid = record[2]
test2 = map_db.select(table='map_2', condition="stid='%s' and opid='%s'" % (stid, opid))
test1 = map_db.select(table='map_1', condition="stid='%s' and opid='%s'" % (stid, opid))
if test1.__len__() is not 0 or test2.__len__() is not 0:
map_db.delete(table='map_0', condition="parkID='%s'" % parkID)
map_db.close()
def one_to_many_saveMap():
map_db = db(os.path.join(BASE_DIR, 'db/map.db'))
counter = 0
done = []
for key in parkNearest:
parkID = key
stid = parkNearest[parkID][1]
opid = parkNearest[parkID][2]
distance = float(parkNearest[parkID][0])
if distance < threshold and parkID not in done:
counter += 1
insert_once(map_db, parkID, stid, opid, distance, counter, get_feature_value(parkID))
done.append(parkID)
for i in parkNearest:
if stid == parkNearest[i][1] and opid == parkNearest[i][2] and \
float(parkNearest[i][0]) < threshold and i not in done:
counter += 1
insert_once(map_db, i, parkNearest[i][1], parkNearest[i][2], parkNearest[i][0], counter,
get_feature_value(i))
done.append(i)
map_db.close()
def one_to_one_saveMap():
map_db = db(os.path.join(BASE_DIR, 'db/map.db'))
for key in oto_match_list:
parkID = key
stid = oto_match_list[parkID][0]
opid = oto_match_list[parkID][1]
distance = float(oto_match_list[parkID][2])
if distance < threshold:
parkInfos = park_db.select(table='ParkStaticData', cols='parkName,address,parkType,companyName',
condition="parkID='%s'" % parkID)
parkLocation = park_db.select(table='ParkBaiduGPS',
cols='lat,lng',
condition='parkID="%s"' % parkID)
for location in parkLocation:
parkLat = float(location[0])
parkLng = float(location[1])
for parkInfo in parkInfos:
parkName = parkInfo[0]
parkAddress = parkInfo[1]
parkType = parkInfo[2]
parkCompanyName = parkInfo[3]
stationInfos = station_db.select(table='station_pub', cols='stname,address,constructDesc',
condition="stid='%s' and opid='%s'" % (stid, opid))
stationLocation = station_db.select(table='stationBaiduGPS',
cols='lat,lng',
condition="stid='%s' and opid='%s'" % (stid, opid))
for location in stationLocation:
stationLat = float(location[0])
stationLng = float(location[1])
for stationInfo in stationInfos:
stationName = stationInfo[0]
stationAddress = stationInfo[1]
stationConstruct = stationInfo[2]
insert_value = [(parkID, stid, opid, distance, parkName,
stationName, parkAddress, stationAddress,
parkCompanyName, stationConstruct,
parkLat, parkLng, stationLat, stationLng)]
map_db.insert(table='map', value=insert_value)
occ_hour_weekday['23'], occ_hour_weekend['00'],
occ_hour_weekend['01'], occ_hour_weekend['02'],
occ_hour_weekend['03'], occ_hour_weekend['04'],
occ_hour_weekend['05'], occ_hour_weekend['06'],
occ_hour_weekend['07'], occ_hour_weekend['08'],
occ_hour_weekend['09'], occ_hour_weekend['10'],
occ_hour_weekend['11'], occ_hour_weekend['12'],
occ_hour_weekend['13'], occ_hour_weekend['14'],
occ_hour_weekend['15'], occ_hour_weekend['16'],
occ_hour_weekend['17'], occ_hour_weekend['18'],
occ_hour_weekend['19'], occ_hour_weekend['20'],
occ_hour_weekend['21'], occ_hour_weekend['22'],
occ_hour_weekend['23'])])
# database.insert(table='parkTotalNum', value=[(parkID, matlabID, parkType, totalNum, error1, error2)])
occ_hour_weekday = {}
occ_hour_weekend = {}
counter_weekday = {}
counter_weekend = {}
if __name__ == '__main__':
park = True
if park:
SHParking1 = db(os.path.join(BASE_DIR, 'db/SHParking.db'))
park_occs = SHParking1.select(table='ParkOcc',
condition='occupancy<"1"')
park_analysis = combine_data(park_occs)
store_data_in_matrix(park_analysis, SHParking1)
pass
# -*- coding:utf-8 -*-
import numpy as np
import matplotlib.pyplot as plt
from dbapi import dbShell as db
import os
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
park_db = db(os.path.join(BASE_DIR, 'db/SHParking.db'))
station_db = db(os.path.join(BASE_DIR, 'db/EVcharge.db'))
park_matrix = park_db.select(table='parkMatrix')
park_dataWash = park_db.select(table='data_wash')
park_dataWash = [e[0] for e in park_dataWash]
def normalize(park_occ):
max_occ = max(park_occ)
min_occ = min(park_occ)
park_occ = [((e - min_occ) / (max_occ - min_occ)) for e in park_occ]
return park_occ
for park in park_matrix:
####################################################################################################
# 遍历park
####################################################################################################
# wash data
matlabID = park[1]
if matlabID not in park_dataWash:
condition='parkID="%s"' %
parkID[0])
park_lat = float(park_location[0][0])
park_lon = float(park_location[0][1])
nearby = ''
nearbyNum = 0
for station in stations:
stationID = station[0]
station_lat = float(station[1])
station_lon = float(station[2])
distance = haversine(park_lon, park_lat, station_lon, station_lat)
if distance < threshold:
nearby += '%s:%f;' % (stationID, distance)
nearbyNum += 1
if nearby:
SHParking.insert(table='mappingParkStation',
value=[(parkID[0], threshold, nearbyNum, nearby)])
print([(parkID[0], threshold, nearbyNum, nearby)])
pass
if __name__ == '__main__':
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
SHParking = db(os.path.join(BASE_DIR, 'db/SHParking.db'))
Stations = db(os.path.join(BASE_DIR, 'db/EVcharge.db'))
threshold = 200 # 米
mapping(SHParking, Stations, threshold)
# _*_ coding:utf-8 _*_
import os
from dbapi import dbShell as db
from local_path import dbPath
park_db = db(os.path.join(dbPath + 'SHParking.db'))
parkIDs = [
# 'hk31010900001',
# 'hp31010100048',
# 'hp31010100069',
# 'xh31010400124',
# 'xh31010400247',
# 'xh31010400103',
# 'xh31010400019',
# 'xh31010400250',
'hp31010100134',
]
final_result = {}
for parkID in parkIDs:
records = park_db.select(table='park_similarity', condition="parkID1='%s'" % parkID,
orderby='similarity+0')
result = records[1:6]
final_result[parkID] = result
print("#############################")
for e in result:
parkID2 = e[1]
print("#############################")
# _*_ coding:utf-8 _*_
from dbapi import dbShell as db
import numpy as np
import os
from local_path import dbPath
park_db = db(dbPath + 'SHParking.db')
station_db = db(dbPath + 'EVcharge.db')
parkIDs = [
"hp31010100048",
"hp31010100069",
"xh31010400124",
"xh31010400247",
"xh31010400103",
"xh31010400019",
"xh31010400250",
"hp31010100134",
"hp31010100024",
"ja31010600010",
"ja31010600047",
"pd31011500090",
"pd31011500505",
"pt31010700189",
"pt31010700138",
"3101010167",
"hp31010100205",
"hp31010100124",
"hp31010100128",
"hp31010100154",
]
from dbapi import dbShell as db
import os
# 2017-06-20 2017-08-21
weekendList = [
'2017-06-24', '2017-06-25', '2017-07-01', '2017-07-02', '2017-07-08',
'2017-07-09', '2017-07-15', '2017-07-16', '2017-07-22', '2017-07-23',
'2017-07-29', '2017-07-30', '2017-08-05', '2017-08-06', '2017-08-12',
'2017-08-13', '2017-08-19', '2017-08-20'
]
charge_station = db(
'/Users/chaidi/Documents/RA-Documents/DataAnalysis/db/EVcharge.db')
stations = charge_station.select(table='stationOcc', orderby='stid,opid')
id_dic = {}
id_dic_counter = 0
station_matrix = []
for station in stations:
# station sample
# < class 'tuple'>: ('10007', 'MA1FP0228', '2017-06-20', '13', '0.100', '10')
stid = str(station[0])
opid = str(station[1])
date = station[2]
hour = station[3]
occ = float(station[4])
totalNum = str(station[5])
# 区分周六日
if date in weekendList:
hour = str(24 + int(hour))
if stid + opid not in id_dic:
id_dic[stid + opid] = id_dic_counter
# _*_ coding:utf-8 _*_
# 代码描述:对地图格子上的
# Input:
# Output:
import os
from dbapi import dbShell as db
import math
from local_path import dbPath as dbpath
EVcharge = db(dbpath + 'EVcharge.db')
poi = db(dbpath + 'poi2.db')
SHParking = db(dbpath + 'SHParking.db')
park_obj_GPSs = SHParking.select(table='ParkBaiduGPSMap')
poi_obj_infs = poi.select(table='poi',
cols='baidulat,baidulng,type1,type2,type3')
station_obj_GPSs = EVcharge.select(table='stationBaiduGPS')
lngperkm = 0.009
latperkm = 0.0103
bottom = 30.408258525468
top = 32.408258525468
left = 120.22177414094
right = 122.22177414094
latgridnum = int((top - bottom) / latperkm)
lnggridnum = int((right - left) / lngperkm)
# lng lat
block_location = [
[121.486576, 31.239016],
[121.450356, 31.227901],