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]))