def write_to_excel(poilist, cityname, classfield):
# 一个Workbook对象，这就相当于创建了一个Excel文件
book = xlwt.Workbook(encoding='utf-8', style_compression=0)
sheet = book.add_sheet(classfield, cell_overwrite_ok=True)
# 第一行(列标题)
sheet.write(0, 0, 'id')
sheet.write(0, 1, 'name')
sheet.write(0, 2, 'lng')
sheet.write(0, 3, 'lat')
sheet.write(0, 4, 'address')
for i in range(len(poilist)):
# 根据poi的id获取边界数据
# bounlist = getDetail(poilist[i]['id'])
# while (len(bounlist) < 4):
# bounlist.append(0)
# 每一行写入
location = gcj02_to_wgs84(poilist[i]['location'].split(',')).split(',')
sheet.write(i + 1, 0, poilist[i]['id'])
sheet.write(i + 1, 1, poilist[i]['name'])
sheet.write(i + 1, 2, location[0])
sheet.write(i + 1, 3, location[1])
sheet.write(i + 1, 4, poilist[i]['address'])
# 最后，将以上操作保存到指定的Excel文件中
book.save(r'd:\\' + cityname + classfield + '.xls')
def extract(xy):
'''输入xy数组or列表'''
sql = """SELECT * FROM `population_beijing` where `LAT`=""" + str(
xy[1]) + ' and `LON`=' + str(xy[0])
cursor.execute(sql)
result = cursor.fetchall()
try:
count = pd.DataFrame(list(list(r) for r in result)).iloc[:, 3].mean()
sql = """INSERT INTO `beijing_pop` (`LON`,`LAT`,`COUNT`) VALUES(%s, %s, %s)"""
cursor.execute(sql, (gcj02_to_wgs84(
xy[0], xy[1])[0], gcj02_to_wgs84(xy[0], xy[1])[1], count))
db.commit()
except:
db.rollback()
def coordTransform():
with open('trips.json', 'r') as file:
paths = json.load(file)
for i, path in enumerate(paths):
for j, point in enumerate(path['path']):
temp = gcj02_to_wgs84(point[0], point[1])
path['path'][j][0] = temp[0]
path['path'][j][1] = temp[1]
with open('new_trips.json', 'w') as file:
json.dump(paths, file)
def generateOrder():
req = pd.read_csv('req.csv', dtype=str)
orders = []
for index, row in req.iterrows():
temp = {}
temp['timestamp'] = int(eval(row['Tr']))
temp['coordinates'] = gcj02_to_wgs84(float(row['olng']),
float(row['olat']))
orders.append(temp)
orders.sort(key=lambda x: x['timestamp'])
with open('order.json', 'w') as file:
json.dump(orders, file)
def insert_into_db(self, pois):
if len(pois) == 0:
print("pois empty")
return False
for p in pois:
map = DataMap()
# print(p)
print("object_id: ", id(map.data_map))
location = p.get("location").split(",")
map.data_map["locationx"] = location[0]
map.data_map["locationy"] = location[1]
location = gcj02_to_wgs84(float(location[0]), float(location[1]))
map.data_map["wgsx"] = location[0]
map.data_map["wgsy"] = location[1]
entr_location = p.get("entr_location")
entr_location = gcj02_to_wgs84(float(entr_location.split(",")[0]), float(entr_location.split(",")[1])) if len(entr_location) else [None, None]
map.data_map["wgs_entrx"] = entr_location[0]
map.data_map["wgs_entry"] = entr_location[1]
exit_location = p.get("exit_location")
exit_location = gcj02_to_wgs84(float(exit_location.split(",")[0]), float(exit_location.split(",")[1])) if len(exit_location) else [None, None]
map.data_map["wgs_exitx"] = exit_location[0]
map.data_map["wgs_exity"] = exit_location[1]
type = p.get("type")
map.data_map["type"] = type.split(";")[0]
map.data_map["sub_type1"] = type.split(";")[1]
map.data_map["sub_type2"] = type.split(";")[2]
for i in row0:
if i in self.exclude_list:
continue
map.data_map[i] = p.get(i, None)
print(map.data_map)
global start_row
self.collection.insert_one(map.data_map)
return True
def convert_by_type(lng, lat, type):
if type == 'g2b':
return gcj02_to_bd09(lng, lat)
elif type == 'b2g':
return bd09_to_gcj02(lng, lat)
elif type == 'w2g':
return wgs84_to_gcj02(lng, lat)
elif type == 'g2w':
return gcj02_to_wgs84(lng, lat)
elif type == 'b2w':
return bd09_to_wgs84(lng, lat)
elif type == 'w2b':
return wgs84_to_bd09(lng, lat)
else:
print('Usage: type must be in one of g2b, b2g, w2g, g2w, b2w, w2b')
sys.exit()
def tPoint(lng, lat, type):
if type == 'g2b':
tcoord = gcj02_to_bd09(lng, lat)
elif type == 'b2g':
tcoord = bd09_to_gcj02(lng, lat)
elif type == 'w2g':
tcoord = wgs84_to_gcj02(lng, lat)
elif type == 'g2w':
tcoord = gcj02_to_wgs84(lng, lat)
elif type == 'b2w':
tcoord = bd09_to_wgs84(lng, lat)
elif type == 'w2b':
tcoord = wgs84_to_bd09(lng, lat)
else:
tcoord = [lng, lat]
return tcoord
def write_to_excel(poilist, cityname, classfield):
# 一个Workbook对象，这就相当于创建了一个Excel文件
book = xlwt.Workbook(encoding='utf-8', style_compression=0)
sheet = book.add_sheet(classfield, cell_overwrite_ok=True)
# 第一行(列标题)
sheet.write(0, 0, 'id')
sheet.write(0, 1, 'name')
sheet.write(0, 2, 'location')
sheet.write(0, 3, 'pname')
sheet.write(0, 4, 'pcode')
sheet.write(0, 5, 'cityname')
sheet.write(0, 6, 'citycode')
sheet.write(0, 7, 'adname')
sheet.write(0, 8, 'adcode')
sheet.write(0, 9, 'address')
sheet.write(0, 10, 'type')
sheet.write(0, 11, '建筑面积')
sheet.write(0, 12, '容积率')
sheet.write(0, 13, '边界面积')
sheet.write(0, 14, '坐标')
for i in range(len(poilist)):
# 根据poi的id获取边界数据
bounlist = getDetail(poilist[i]['id'])
time.sleep(random.randint(3, 9))
while (len(bounlist) < 4):
bounlist.append(0)
#每一行写入
sheet.write(i + 1, 0, poilist[i]['id'])
sheet.write(i + 1, 1, poilist[i]['name'])
sheet.write(i + 1, 2,
gcj02_to_wgs84(poilist[i]['location'].split(',')))
sheet.write(i + 1, 3, poilist[i]['pname'])
sheet.write(i + 1, 4, poilist[i]['pcode'])
sheet.write(i + 1, 5, poilist[i]['cityname'])
sheet.write(i + 1, 6, poilist[i]['citycode'])
sheet.write(i + 1, 7, poilist[i]['adname'])
sheet.write(i + 1, 8, poilist[i]['adcode'])
sheet.write(i + 1, 9, poilist[i]['address'])
sheet.write(i + 1, 10, poilist[i]['type'])
sheet.write(i + 1, 11, bounlist[0])
sheet.write(i + 1, 12, bounlist[1])
sheet.write(i + 1, 13, bounlist[2])
sheet.write(i + 1, 14, bounlist[3])
time.sleep(random.randint(0, 10))
# 最后，将以上操作保存到指定的Excel文件中
book.save(r'd:\\' + cityname + '.xls')
f = open(filename + '.csv', 'r')
file = f.readlines()
titles = file[:1][0]
contents = file[1:]
cols_num = len(titles.split(','))
f.close()
f = open(filename + '_trans.csv', 'w')
f_err = open(filename + '_err.csv', 'w')
f.writelines(titles[:-1].encode('utf-8') + ',lng_trans,lat_trans\n')
for i in range(len(contents)):
# print contents[i]
# print len(contents[i].split(','))
try:
content = contents[i].split(',')
gcj02lng = content[-2]
gcj02lat = content[-1][:-1]
res = gcj02_to_wgs84(float(gcj02lng), float(gcj02lat))
plng = str(res[0])
plat = str(res[1])
f.writelines(contents[i][:-1] + ',' + plng + ',' + plat + '\n')
process_i = i * 100 / len(contents)
processbar_general(process_i)
except Exception as e:
print str(i + 2)
f_err.writelines(str(i + 2) + '\n')
# exit()
f_err.close()
f.close()
return 0
def point_clean(coords):
'''输入((x,y)形式的数据,输出标记
1：删除该线段
0：保留该线段'''
for xy in coords:
if xy[0][0]>180 or xy[0][0]<-180 or xy[0][1]>90 or xy[0][1]<-90:
return 1
return 0
'''轨迹数据'''
#保存名称为footprint#
i = 1
for path in line_paths[i-1:]:
try:
gdf = gpd.GeoDataFrame.from_file(path)
gdf['xy'] = gdf['geometry'].apply(lambda x: x.coords)
gdf['judge'] = gdf['xy'].apply(line_clean)
gdf = gdf[gdf['judge'] == 0]
gdf.drop(['judge'], inplace=True, axis=1)
gdf['geometry'] = gdf['xy'].apply(lambda x: LineString([gcj02_to_wgs84(x[0],x[1]) for x in x]))
gdf.drop(['xy'], inplace=True, axis=1)
gpd.GeoDataFrame(gdf).to_file('E:\\全国轨迹清洗\\footprint(' + str(i) + ').shp') # 输出的文件为84坐标系
except:
print(i,path)
finally:
i+=1
'User-Agent': 'okhttp/3.10.0',
}
# 偏移量，根据实际情况进行调整，值越小则精度越高，但效率更低
# 0.002 约等于316米
offset = 0.002
bikes = {}
for lng in np.arange(121.484258, 121.500859, offset):
for lat in np.arange(31.233277, 31.249362, offset):
data = [('longitude', lng), ('latitude', lat)]
data = requests.post(
'https://app.mobike.com/api/nearby/v4/nearbyBikeInfo',
headers=headers,
data=data).json()
for bike in data['bike']:
id, x, y = bike['distId'], bike['distX'], bike['distY']
wgs84 = coordTransform_utils.gcj02_to_wgs84(x, y)
# 记录时间，经度、纬度
bikes[id] = (datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
wgs84[0], wgs84[1])
print("Bikes:", len(bikes))
# 按照年月日-时分秒形式存为压缩包
filename = datetime.now().strftime("%Y%m%d-%H%M%S") + ".csv.gz"
with gzip.open(open(filename, 'wb'), 'wt', compresslevel=9) as f:
for id, data in bikes.items():
f.write("%s,%s,%s,%s\n" % (data[0], id, data[1], data[2]))