def save_txt():
fp = open("../data/yhtl_2.txt", 'w')
conn = cx_Oracle.connect('hz/[email protected]/orcl')
cursor = conn.cursor()
lp = [120.129157, 30.297211]
rp = [120.083538, 30.291544]
x0, y0 = bl2xy(lp[1], lp[0])
x1, y1 = bl2xy(rp[1], rp[0])
sql = "select vehicle_num, px, py, direction, speed_time from tb_gps_1805" \
" where speed_time > :1 and speed_time < :2 and carstate = '0' order by speed_time"
bt = datetime(2018, 5, 1, 12)
et = bt + timedelta(hours=6)
tup = (bt, et)
cursor.execute(sql, tup)
traces = defaultdict(list)
for item in cursor:
veh = item[0]
px, py = map(float, item[1:3])
angle = int(item[3])
st = item[4].strftime("%Y-%m-%d %H:%M:%S")
if rp[0] < px < lp[0] and rp[1] < py < lp[1]:
x, y = bl2xy(py, px)
line = "{2},{0:.2f},{1:.2f},{4},{3}\n".format(x, y, veh, st, angle)
fp.write(line)
traces[veh].append([x, y])
cursor.close()
conn.close()
fp.close()
def dst():
conn = oracle_util.get_connection()
cursor = conn.cursor()
sql = "select * from tb_road_point order by rid, seq"
cursor.execute(sql)
road = [[] for x in range(5432)]
for item in cursor:
lng, lat = map(float, item[2:4])
rid, seq = map(int, item[0:2])
px, py = bl2xy(lat, lng)
road[rid].append([px, py])
tup_list = []
upd_sql = "update tb_road_state set road_desc = :1 where rid = :2"
for rid in range(5432):
last_point = None
tot = 0
for point in road[rid]:
if last_point is not None:
tot += calc_dist(last_point, point)
last_point = point
tup_list.append((tot, rid))
cursor.executemany(upd_sql, tup_list)
conn.commit()
cursor.close()
conn.close()
def main():
tree = ET.ElementTree(file="./map_data/map")
root = tree.getroot()
point_dict = {}
line_dict = {}
for elem in tree.iter():
if elem.tag == 'node':
att = elem.attrib
pid, lng, lat = int(att['id']), float(att['lon']), float(
att['lat'])
x, y = bl2xy(lat, lng)
point = OSMPoint(pid, lat, lng, x, y)
point_dict[pid] = point
if elem.tag == 'way':
att = elem.attrib
lid = int(att['id'])
point_list = []
for child in elem:
if child.tag == 'nd':
att = child.attrib
pid = int(att['ref'])
point_list.append(point_dict[pid])
if child.tag == 'tag':
att = child.attrib
k, v = att['k'], att['v']
if k == 'highway':
line = OSMLine(point_list, v)
line_dict[lid] = line
draw_map(line_dict)
def main_show2(xy_list, seg_list0, seg_list1):
"""
显示地图
:return:
"""
fig1 = plt.figure(figsize=(12, 6))
ax = fig1.add_subplot(111)
filename = './road/raw.txt'
data = load_model(filename)
for road in data:
pl = road['polyline']
xy_items = pl.split(';')
x_list, y_list = [], []
for xy in xy_items:
lng, lat = map(float, xy.split(',')[0:2])
x, y = bl2xy(lat, lng)
x_list.append(x)
y_list.append(y)
# plt.plot(x_list, y_list, marker='o', markersize=5)
plt.plot(x_list, y_list)
center_x, center_y = zip(*xy_list)
plt.plot(center_x, center_y, marker='o', linestyle='-', markersize=2)
draw_seg(seg_list0)
draw_seg(seg_list1)
# plt.xlim(75550, 78948)
# plt.ylim(83080, 84958)
plt.subplots_adjust(left=0.06, right=0.98, bottom=0.05, top=0.96)
# plt.savefig("road.png", dpi=200)
plt.show()
def process_data():
f = open("../data/tb_bike_gps_1802.csv", 'r')
fp1 = open('../data/bike_08.txt', 'w')
fp2 = open('../data/bike_18.txt', 'w')
cnt = 0
for line in f.readlines():
if cnt == 0:
cnt = 1
continue
items = line.split(',')
lng, lat = float(items[4].strip('"')), float(items[5].strip('"'))
if lng > 121 or lng < 119 or lat > 31 or lat < 29:
continue
x, y = bl2xy(lat, lng)
state = int(items[7].strip('"'))
stime = items[3].strip('"')
dtime = datetime.strptime(stime, '%Y/%m/%d %H:%M:%S')
if state == 0:
new_str = "{0},{1},{2}\n".format(x, y, stime)
if 6 <= dtime.hour < 9 and dtime.day <= 5:
fp1.write(new_str)
elif 16 <= dtime.hour < 19 and dtime.day <= 5:
fp2.write(new_str)
cnt += 1
fp1.close()
fp2.close()
def load_sqlite_road():
"""
从sqlite3中读取路网
:return:
"""
conn = sqlite3.connect('./data/hz.db3')
cursor = conn.cursor()
cursor.execute(
"select s_id, seq, longti, lati from tb_seg_point order by s_id, seq")
xy_dict = {}
for items in cursor:
rid = int(items[0])
l, b = map(float, items[2:4])
x, y = bl2xy(b, l)
pt = Point(x, y)
try:
xy_dict[rid].append(pt)
except KeyError:
xy_dict[rid] = [pt]
road_list = []
# cnt0, cnt1 = 0, 0
for rid, items in xy_dict.iteritems():
r = Road("", 0, rid)
r.set_point_list(items)
# cnt0 += len(r.point_list)
road_list.append(r)
road_simplify(r)
# cnt1 += len(r.point_list)
cursor.close()
conn.close()
return road_list
def store_node(tree):
p = tree.find('meta')
nds = p.findall('node')
for x in nds:
node_dic = x.attrib
nodeid = node_dic['id']
dx, dy = bl2xy(float(node_dic['lat']), float(node_dic['lon']))
node = MapNode([dx, dy], nodeid)
map_node_dict[nodeid] = node
def load_map(self, map_level):
conn = oracle_util.get_connection()
sql = "select * from tb_road_state where map_level = :1"
tup = (map_level, )
cursor = conn.cursor()
cursor.execute(sql, tup)
for item in cursor:
road_name, direction, level = item[1:4]
r = MapRoad(road_name, direction, level)
rid = item[0]
self.map_road[rid] = r
map_temp_node = {} # 维护地图节点
road_point = {}
sql = "select rp.* from tb_road_point rp, tb_road_state" \
" rs where rp.rid = rs.rid and rs.map_level = :1 and rp.map_level = :2 order by rp.rid, rp.seq "
tup = (map_level, map_level)
cursor.execute(sql, tup)
for item in cursor:
rid = item[0]
lng, lat = map(float, item[2:4])
try:
road_point[rid].append([lng, lat])
except KeyError:
road_point[rid] = [[lng, lat]]
# 维护节点dict
# 每一个点都单独列入dict，边用两点表示，道路用若干点表示
for rid, bllist in road_point.iteritems():
r = self.map_road[rid]
last_nodeid = -1
lastx, lasty = None, None
for i, bl in enumerate(bllist):
lng, lat = bl[0:2]
str_bl = "{0},{1}".format(lng, lat)
x, y = bl2xy(lat, lng)
try:
nodeid = map_temp_node[str_bl]
nd = self.map_node[nodeid]
except KeyError:
nodeid = len(map_temp_node)
map_temp_node[str_bl] = nodeid
nd = MapNode([x, y], nodeid)
self.map_node[nodeid] = nd
if i > 0:
edge_length = calc_dist([x, y], [lastx, lasty])
edge = MapEdge(self.map_node[last_nodeid],
self.map_node[nodeid], True,
len(self.map_edge), edge_length, rid)
self.map_edge.append(edge)
r.add_node(nd)
last_nodeid, lastx, lasty = nodeid, x, y
conn.close()
def get_gps_data(conn, begin_time, veh):
str_bt = begin_time.strftime('%Y-%m-%d %H:%M:%S')
end_time = begin_time + timedelta(minutes=5)
str_et = end_time.strftime('%Y-%m-%d %H:%M:%S')
sql = "select px, py, speed_time, state, speed, carstate, direction from " \
"TB_GPS_1805 t where speed_time >= to_date('{1}', 'yyyy-mm-dd hh24:mi:ss') " \
"and speed_time < to_date('{2}', 'yyyy-MM-dd hh24:mi:ss')" \
" and vehicle_num = '{0}'".format(veh, str_bt, str_et)
cursor = conn.cursor()
cursor.execute(sql)
trace = []
last_point = None
veh = veh[-6:]
for item in cursor.fetchall():
lng, lat = map(float, item[0:2])
if 119 < lng < 121 and 29 < lat < 31:
px, py = bl2xy(lat, lng)
state = int(item[3])
stime = item[2]
speed = float(item[4])
carstate = int(item[5])
ort = float(item[6])
taxi_data = TaxiData(veh, px, py, stime, state, speed, carstate,
ort)
trace.append(taxi_data)
# print len(trace)
trace.sort(cmp1)
# new_trace = []
# for data in trace:
# cur_point = data
# if last_point is not None:
# dist = calc_dist([cur_point.px, cur_point.py], [last_point.px, last_point.py])
# del_time = (cur_point.stime - last_point.stime).total_seconds()
# if data.speed > 140 or del_time < 5: # 速度阈值 时间间隔阈值
# continue
# elif dist > 100 / 3.6 * del_time: # 距离阈值
# continue
# elif data.speed == last_point.speed and data.speed > 0 and data.direction == last_point.direction:
# # 非精确
# continue
# else:
# data.dist = dist
# # del_list.append(del_time)
# new_trace.append(data)
# else:
# data.dist = 0
# new_trace.append(data)
# last_point = cur_point
# # gps_point, gps_mean, gps_med = exclude_abnormal(del_list)
return trace
def load_double_road():
conn = cx_Oracle.connect("hz/[email protected]/orcl")
cursor = conn.cursor()
cursor.execute(
"select sp.s_id, sp.seq, sp.longti, sp.lati, s.s_name, s.direction, s.begin_id, s.end_id"
", s.length from tb_seg_point sp, tb_segment s where sp.s_id = s.s_id and s.rank = '快速路'"
" order by sp.s_id, sp.seq")
xy_dict = {}
info_dict = defaultdict(list)
road_name = {}
for items in cursor:
rid = int(items[0])
l, b = map(float, items[2:4])
l, b = wgs84togcj02(l, b)
x, y = bl2xy(b, l)
pt = Point(x, y)
name = items[4]
uname = name.decode('utf-8')
road_name[rid] = uname
# if name is not None:
# name = name.decode('utf-8')
if name != '艮山西路':
continue
direction = items[5]
if direction == '3':
continue
bid, eid = items[6:8]
length = items[8]
if direction == '2':
bid, eid = eid, bid
try:
xy_dict[rid].append(pt)
except KeyError:
xy_dict[rid] = [pt]
rs = RoadSegment(rid, bid, eid, direction, name, length)
info_dict[name].append(rs)
# add rid
rid_list = []
for name, road_list in info_dict.iteritems():
rid_list.extend(gene_one_road(road_list))
road_list = []
for road in rid_list:
rid = road.rid
pt_list = xy_dict[rid]
new_road = Road(road_name[rid], 0, rid)
new_road.set_point_list(pt_list)
road_list.append(new_road)
return road_list
def load_oracle_road():
"""
从oracle中读取路网
:return: list of Road
"""
conn = cx_Oracle.connect("hz/[email protected]/orcl")
cursor = conn.cursor()
cursor.execute(
"select sp.s_id, sp.seq, sp.longti, sp.lati, s.s_name, s.direction"
" from tb_seg_point sp, tb_segment s "
"where sp.s_id = s.s_id and (s.rank = '快速路' or s.rank = '高速公路' or s.s_name = '天目山路'"
" or s.s_name = '艮山西路' or s.s_name = '文一路' or s.s_name = '文一西路')"
" order by s_id, seq")
xy_dict = {}
road_name = {}
for items in cursor:
rid = int(items[0])
l, b = map(float, items[2:4])
l, b = wgs84togcj02(l, b)
x, y = bl2xy(b, l)
pt = Point(x, y)
name = items[4]
if name is not None:
name = name.decode('utf-8')
if name != u'艮山西路':
continue
direction = items[5]
if direction != '3':
continue
road_name[rid] = name
try:
last_pt = xy_dict[rid][-1]
if last_pt == pt:
continue
xy_dict[rid].append(pt)
except KeyError:
xy_dict[rid] = [pt]
road_list = []
# cnt0, cnt1 = 0, 0
for rid, items in xy_dict.iteritems():
r = Road(road_name[rid], 0, rid)
r.set_point_list(items)
# cnt0 += len(r.point_list)
road_list.append(r)
# road_simplify(r)
# cnt1 += len(r.point_list)
cursor.close()
conn.close()
return road_list
def load_taxi(index):
traj_list = []
fp = open('data/trace/lishui_{0}.txt'.format(index))
cnt = 0
for line in fp.readlines():
items = line.strip('\n').split(',')
# _, px, py, speed, azi, state, speed_time = new_items
longi, lati, speed = map(float, items[1:4])
if longi > max_lon or longi < min_lon or lati > max_lat or lati < min_lat:
continue
px, py = bl2xy(lati, longi)
speed_time = datetime.strptime(items[5], "%Y-%m-%d %H:%M:%S")
data = TaxiData(px, py, speed, speed_time)
traj_list.append(data)
traj_list.sort(cmp1)
return traj_list
def show_amap():
fig1 = plt.figure(figsize=(12, 6))
ax = fig1.add_subplot(111)
line_dic = {}
fp = open('./2.txt')
fp_lines = fp.readlines()
global line_point, line_line
for i, line in enumerate(fp_lines[1:len(fp_lines):2]):
name = get_name(fp_lines[i * 2])
# if name != '新天地街东':
# continue
line_point[name] = []
line_line[name] = []
pts = line.strip('\n').split(';')
pt_list = []
for pt in pts:
lng, lat = pt.split(',')[0:2]
px, py = geo.bl2xy(lat, lng)
pt_list.append([px, py])
if name not in line_dic:
line_dic[name] = []
line_dic[name].append(pt_list)
line_dict_pro(line_dic)
for name in line_line:
segs = line_line[name]
cnt = 0
print name
if name in rem_dic:
rem_num = rem_dic[name]
for i in segs:
cnt += 1
if cnt in rem_num:
continue
plt.plot([i[0][0], i[1][0]], [i[0][1], i[1][1]], color='k')
plt.text(i[0][0], i[0][1], '{0}'.format(cnt))
plt.subplots_adjust(left=0.06, right=0.98, bottom=0.05, top=0.96)
plt.show()
else:
for i in segs:
cnt += 1
plt.plot([i[0][0], i[1][0]], [i[0][1], i[1][1]], color='k')
plt.text(i[0][0], i[0][1], '{0}'.format(cnt))
plt.subplots_adjust(left=0.06, right=0.98, bottom=0.05, top=0.96)
plt.show()
def simplify(point_dict):
pid = 0
for sid, pt_list in point_dict.iteritems():
xy_list = []
for pt in pt_list:
x, y = bl2xy(pt.lati, pt.longti)
xy_list.append([x, y])
path = dog_last(xy_list)
new_pt_list = []
for i, xy in enumerate(path):
x, y = xy[:]
lat, lng = xy2bl(x, y)
pt = Point(pid, sid, i, lng, lat)
pid += 1
new_pt_list.append(pt)
point_dict[sid] = new_pt_list
return point_dict
def load_oracle_road():
"""
从原始做好的oracle路网中读取路网
:return: list of Road
"""
conn = cx_Oracle.connect("hz/[email protected]/orcl")
cursor = conn.cursor()
cursor.execute(
"select sp.s_id, sp.seq, sp.longti, sp.lati, s.s_name, s.direction"
" from tb_seg_point sp, tb_segment s "
"where sp.s_id = s.s_id and s.rank != '次要道路' and s.rank != '连杆道路'"
" order by s_id, seq")
xy_dict = {}
name_dic = {} # name
dir_dict = {} # direction
for items in cursor:
rid = int(items[0])
l, b = map(float, items[2:4])
l, b = wgs84togcj02(l, b)
x, y = bl2xy(b, l)
pt = Point(x, y)
name = items[4]
ort = int(items[5])
try:
xy_dict[rid].append(pt)
except KeyError:
xy_dict[rid] = [pt]
name_dic[rid] = name
dir_dict[rid] = ort
road_list = []
# cnt0, cnt1 = 0, 0
for rid, items in xy_dict.iteritems():
ort = dir_dict[rid]
if ort == 2:
items.reverse()
r = Road(name_dic[rid], ort, rid)
r.set_point_list(items)
# cnt0 += len(r.point_list)
road_list.append(r)
road_simplify(r)
# cnt1 += len(r.point_list)
cursor.close()
conn.close()
return road_list
def get_gps_data_from_redis():
bt = clock()
conn = redis.Redis(host="192.168.11.229", port=6300, db=1)
keys = conn.keys()
new_trace = {}
if len(keys) != 0:
m_res = conn.mget(keys)
et = clock()
veh_trace = {}
static_num = {}
for data in m_res:
try:
js_data = json.loads(data)
lng, lat = js_data['longi'], js_data['lati']
veh, str_time = js_data['isu'], js_data['speed_time']
speed = js_data['speed']
stime = datetime.strptime(str_time, "%Y-%m-%d %H:%M:%S")
state = 0
car_state = 0
ort = js_data['ort']
if 119 < lng < 121 and 29 < lat < 31:
px, py = bl2xy(lat, lng)
taxi_data = TaxiData(veh, px, py, stime, state, speed,
car_state, ort)
try:
veh_trace[veh].append(taxi_data)
except KeyError:
veh_trace[veh] = [taxi_data]
try:
static_num[veh] += 1
except KeyError:
static_num[veh] = 1
except TypeError:
pass
print "redis cost ", et - bt
for veh, trace in veh_trace.iteritems():
trace.sort(cmp1)
new_trace[veh] = trace
# print "get all gps data {0}".format(len(veh_trace))
# print "all car:{0}, ave:{1}".format(len(static_num), len(trace) / len(static_num))
return new_trace
def build_road(road, raw_path_list):
"""
:param road: 路名
:param raw_path_list: 原始路径list
:return:
"""
# print road
node_id = {} # 用经度做hash记录每个点的id
xy_table = {} # 记录每个id代表的经纬度
prev_table = {}
# 对确定的点对重建反向链表，即每一对能确定道路的点作为最小单元segment
# 将路径拆成segment，对于prev->id的拓扑关系，记录每个id的prev_id
# 并使得当prev_id == -1时，该点为合并后新路径的起点
next_table = {}
# 使用正向链表重建序列
for crds in raw_path_list:
prev_id = -1
for crd in crds:
x, y = bl2xy(crd[1], crd[0])
uuid = str(crd[0]) + ',' + str(crd[1])
# node id
try:
nid = node_id[uuid]
except KeyError:
nid = len(node_id)
node_id[uuid] = nid
xy_table[nid] = [x, y]
# 当已经存在拓扑关系时，跳过
if nid not in prev_table or prev_table[nid] == -1:
prev_table[nid] = prev_id
# if prev_id != -1 and prev_id not in next_table:
next_table[prev_id] = nid
prev_id = nid
path_list = []
for nid, pid in prev_table.iteritems():
if pid == -1:
path = build_road_list(next_table, xy_table, nid)
path_list.append(path)
return path_list, xy_table
def split_into_cars(data_list):
"""
:param data_list: vehicle num, px, py, state, stime
:return: taxi_trace_map
"""
taxi_trace_map = {}
for data in data_list:
veh, lng, lat, state, speed, stime = data[0:6]
veh = veh[-6:]
state = int(state)
if lng > 121 or lng < 119 or lat > 31 or lat < 29:
continue
px, py = bl2xy(lat, lng)
taxi_data = TaxiData(px, py, stime, state, speed)
try:
taxi_trace_map[veh].append(taxi_data)
except KeyError:
taxi_trace_map[veh] = [
taxi_data,
]
return taxi_trace_map
def get_main_road():
conn = cx_Oracle.connect("hz/[email protected]/orcl")
cursor = conn.cursor()
sql = "select sp.s_id, sp.seq, sp.longti, sp.lati, s.s_name, s.direction " \
"from tb_segment s, tb_seg_point sp where sp.s_id = s.s_id and " \
"s.s_name like '%德胜%' and (s.rank = '快速路')" \
" order by s_id, seq"
cursor.execute(sql)
xy_dict = {}
name_dic = {} # name
dir_dict = {} # direction
for items in cursor:
rid = int(items[0])
l, b = map(float, items[2:4])
x, y = bl2xy(b, l)
pt = Point(x, y)
name = items[4]
ort = int(items[5])
try:
xy_dict[rid].append(pt)
except KeyError:
xy_dict[rid] = [pt]
name_dic[rid] = name
dir_dict[rid] = ort
road_list = []
# cnt0, cnt1 = 0, 0
for rid, items in xy_dict.iteritems():
ort = dir_dict[rid]
if ort == 2:
items.reverse()
r = Road(name_dic[rid], ort, rid)
r.set_point_list(items)
r.gene_segment()
# cnt0 += len(r.point_list)
road_list.append(r)
conn.close()
return road_list
def trans():
c2 = sqlite3.connect("./hz2.db")
cur = c2.cursor()
sql = "select * from tb_seg_point"
cur.execute(sql)
tup_list = []
for item in cur:
pid, sid, seq, lng, lat = item[:]
mlng, mlat = wgs84_to_gcj02(lng, lat)
x, y = bl2xy(mlat, mlng)
tup_list.append((pid, sid, seq, x, y))
cur.close()
c2.close()
c3 = sqlite3.connect("./hz3.db")
cur = c3.cursor()
cur.executemany(
"insert into tb_seg_point(point_id, s_id, seq, px, py) "
"values (?,?,?,?,?)", tup_list)
c3.commit()
cur.close()
c3.close()
def split_road():
conn1 = sqlite3.connect("../map_info/hz1.db")
cursor = conn1.cursor()
sql = "select * from TB_SEG_POINT order by s_id, seq;"
cursor.execute(sql)
roads = defaultdict(list)
for item in cursor:
s_id, seq, lng, lat = item[1:]
x, y = bl2xy(lat, lng)
roads[s_id].append([x, y])
pt_tup_list = []
for sid, pt_list in roads.iteritems():
last_pt = None
new_pt_list = []
for pt in pt_list:
if last_pt is not None:
dist = calc_dist(pt, last_pt)
if dist > DIST_THREAD:
for n_pt in add_pt(last_pt, pt):
new_pt_list.append(n_pt)
last_pt = pt
new_pt_list.append(pt)
roads[sid] = new_pt_list
for i, pt in enumerate(new_pt_list):
b, l = xy2bl(pt[0], pt[1])
t = (sid, i + 1, l, b)
pt_tup_list.append(t)
cursor.close()
conn1.close()
conn2 = sqlite3.connect("../map_info/hz2.db")
cursor = conn2.cursor()
sql2 = "insert into tb_seg_point(point_id, s_id, seq, longi, lati) values(0,?,?,?,?)"
cursor.executemany(sql2, pt_tup_list)
conn2.commit()
cursor.close()
def on_message(self, headers, message):
data = json.loads(message)
try:
lati, longi = float(data['lati']), float(data['longi'])
x, y = bl2xy(lati, longi)
speed = float(data['speed'])
str_time = data['speed_time']
speed_time = datetime.strptime(str_time, "%Y-%m-%d %H:%M:%S")
isu = data['isu'].encode('utf-8')
except KeyError:
return
data = TaxiData(isu, x, y, speed_time, 0, speed, 0, 0)
_, _, speed_list, _ = match2road(isu, data, self.cnt)
for edge, spd in speed_list:
try:
road_temp[edge.way_id].append([spd, edge.edge_length])
except KeyError:
road_temp[edge.way_id] = [[spd, edge.edge_length]]
global data_cnt
data_cnt += 1
if data_cnt % 1000 == 0:
self.on_cnt(time.clock() - self.ticker)
self.ticker = time.clock()
# @简介 :
# @File : plotRoad.py
from geo import bl2xy
import matplotlib.pyplot as plt
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
fp = open("./road/road3.txt")
while True:
line = fp.readline().strip('\n')
if line == '':
break
if line[0] != '#':
items = line.split(',', 1)
road = items[1]
line = fp.readline().strip('\n')
seg = []
crds = line.split(';')
for crd in crds:
l, b = map(float, crd.split(','))
x, y = bl2xy(b, l)
seg.append([x, y])
x, y = zip(*seg)
plt.plot(x, y)
# plt.text(x[0], y[0], road)
plt.show()
def get_all_gps_data():
"""
从数据库中获取一段时间的GPS数据
:return:
"""
end_time = datetime(2018, 5, 8, 6, 0, 0)
conn = cx_Oracle.connect('hz/[email protected]:1521/orcl')
bt = clock()
begin_time = end_time + timedelta(minutes=-5)
# sql = "select px, py, speed_time, state, speed, carstate, direction, vehicle_num from " \
# "TB_GPS_1805 t where speed_time >= :1 " \
# "and speed_time < :2 and vehicle_num = '浙AT7902' order by speed_time "
sql = "select px, py, speed_time, state, speed, carstate, direction, vehicle_num from " \
"TB_GPS_1805 t where speed_time >= :1 and speed_time < :2"
tup = (begin_time, end_time)
cursor = conn.cursor()
cursor.execute(sql, tup)
veh_trace = {}
static_num = {}
for item in cursor.fetchall():
lng, lat = map(float, item[0:2])
if 119 < lng < 121 and 29 < lat < 31:
px, py = bl2xy(lat, lng)
state = int(item[3])
stime = item[2]
speed = float(item[4])
car_state = int(item[5])
ort = float(item[6])
veh = item[7][-6:]
# if veh != 'AT0956':
# continue
taxi_data = TaxiData(veh, px, py, stime, state, speed, car_state,
ort)
try:
veh_trace[veh].append(taxi_data)
except KeyError:
veh_trace[veh] = [taxi_data]
try:
static_num[veh] += 1
except KeyError:
static_num[veh] = 1
et = clock()
new_dict = {}
for veh, trace in veh_trace.iteritems():
trace.sort(cmp1)
new_trace = []
last_data = None
i = 0
for data in trace:
esti = True
dist = 0
if last_data is not None:
dist = calc_dist([data.px, data.py],
[last_data.px, last_data.py])
dt = (data.stime - last_data.stime).total_seconds()
if data.state == 0:
esti = False
# 过滤异常
if dt <= 10 or dt > 180:
esti = False
elif dist > 100 / 3.6 * dt: # 距离阈值
esti = False
elif data.car_state == 1: # 非精确
esti = False
elif data.speed == last_data.speed and data.direction == last_data.direction:
esti = False
elif dist < 20: # GPS的误差在10米，不准确
esti = False
last_data = data
if esti:
new_trace.append(data)
# print i, dist
# i += 1
new_dict[veh] = new_trace
print "get all gps data", et - bt
# print "all car:{0}, ave:{1}".format(len(static_num), len(trace) / len(static_num))
cursor.close()
conn.close()
return new_dict
def get_gps_data_from_redis():
bt = clock()
conn = redis.Redis(host="192.168.11.229", port=6300, db=1)
keys = conn.keys()
new_trace = {}
if len(keys) != 0:
m_res = conn.mget(keys)
et = clock()
veh_trace = {}
static_num = {}
for data in m_res:
try:
js_data = json.loads(data)
lng, lat = js_data['longi'], js_data['lati']
veh, str_time = js_data['isu'], js_data['speed_time']
speed = js_data['speed']
stime = datetime.strptime(str_time, "%Y-%m-%d %H:%M:%S")
state = 1
car_state = 0
ort = js_data['ort']
if 119 < lng < 121 and 29 < lat < 31:
px, py = bl2xy(lat, lng)
taxi_data = TaxiData(veh, px, py, stime, state, speed,
car_state, ort)
try:
veh_trace[veh].append(taxi_data)
except KeyError:
veh_trace[veh] = [taxi_data]
try:
static_num[veh] += 1
except KeyError:
static_num[veh] = 1
except TypeError:
pass
print "redis cost ", et - bt
cnt = {}
for veh, trace in veh_trace.iteritems():
try:
cnt[len(trace)] += 1
except KeyError:
cnt[len(trace)] = 1
print cnt
for veh, trace in veh_trace.iteritems():
trace.sort(cmp1)
filter_trace = []
last_data = None
for data in trace:
esti = True
if last_data is not None:
dist = calc_dist([data.px, data.py],
[last_data.px, last_data.py])
dt = (data.stime - last_data.stime).total_seconds()
if data.state == 0:
esti = False
# 过滤异常
if dt <= 10 or dt > 180:
esti = False
elif dist > 100 / 3.6 * dt: # 距离阈值
esti = False
elif data.car_state == 1: # 非精确
esti = False
# elif data.speed == last_data.speed and data.direction == last_data.direction:
# esti = False
elif dist < 10: # GPS的误差在10米，不准确
esti = False
last_data = data
if esti:
filter_trace.append(data)
if 4 <= len(filter_trace) <= 40:
new_trace[veh] = filter_trace
# cnt static
cnt = {}
for veh, trace in new_trace.iteritems():
try:
cnt[len(trace)] += 1
except KeyError:
cnt[len(trace)] = 1
# if len(trace) == 13:
# print veh
# print "get all gps data {0}".format(len(veh_trace))
# print "all car:{0}, ave:{1}".format(len(static_num), len(trace) / len(static_num))
return new_trace
def get_all_gps_data(end_time):
"""
从数据库中获取一段时间的GPS数据
:param end_time
:return:
"""
# end_time = datetime(2018, 5, 1, 5, 0, 0)
conn = cx_Oracle.connect('hz/[email protected]:1521/orcl')
begin_time = end_time + timedelta(hours=-3)
# sql = "select px, py, speed_time, state, speed, carstate, direction, vehicle_num from " \
# "TB_GPS_1805 t where speed_time >= :1 " \
# "and speed_time < :2 and vehicle_num = '浙AT7484' order by speed_time "
sql = "select px, py, speed_time, state, speed, carstate, direction, vehicle_num from " \
"TB_GPS_1805 t where speed_time >= :1 and speed_time < :2"
tup = (begin_time, end_time)
cursor = conn.cursor()
cursor.execute(sql, tup)
veh_trace = {}
static_num = {}
for item in cursor.fetchall():
lng, lat = map(float, item[0:2])
if 119 < lng < 121 and 29 < lat < 31:
px, py = bl2xy(lat, lng)
state = int(item[3])
stime = item[2]
speed = float(item[4])
car_state = int(item[5])
ort = float(item[6])
veh = item[7][-6:]
# if veh != 'AT0956':
# continue
taxi_data = TaxiData(veh, px, py, stime, state, speed, car_state,
ort)
try:
veh_trace[veh].append(taxi_data)
except KeyError:
veh_trace[veh] = [taxi_data]
try:
static_num[veh] += 1
except KeyError:
static_num[veh] = 1
new_dict = {}
record_cnt = 0
for veh, trace in veh_trace.iteritems():
trace.sort(cmp1)
new_trace = []
last_data = None
for data in trace:
esti = True
if last_data is not None:
dist = calc_dist([data.px, data.py],
[last_data.px, last_data.py])
dt = (data.stime - last_data.stime).total_seconds()
if data.state == 0:
esti = esti
# 过滤异常
if dt <= 10:
esti = False
elif dist > 100 / 3.6 * dt: # 距离阈值
esti = False
elif data.car_state == 1: # 非精确
esti = False
elif data.speed == last_data.speed and data.direction == last_data.direction:
esti = False
elif dist < 15: # GPS的误差在10米，不准确
esti = False
last_data = data
if esti:
new_trace.append(data)
# print i, dist
# i += 1
if len(new_trace) >= 5:
new_dict[veh] = new_trace
record_cnt += len(new_trace)
# print "all car records", record_cnt, "car number", len(new_dict)
cursor.close()
conn.close()
# check_itv(new_dict)
return new_dict
for e, _ in node.link_list:
edge_set.add(e.edge_index)
fp_node = open('./map/node.csv')
fp_edge = open('./map/edge.csv')
fp = open('./data/point.txt')
node_list = [None]
edge_list = [None]
for line in fp_node.readlines():
items = line.strip('\n').split(',')
nid = items[0]
lng, lat = map(float, items[1:3])
px, py = bl2xy(lat, lng)
node = MapNode([px, py], nid)
node_list.append(node)
for line in fp_edge.readlines():
items = line.strip('\n').split(',')
eid = items[0]
nid0, nid1 = map(int, items[1:3])
node0, node1 = node_list[nid0], node_list[nid1]
edge = MapEdge(node0, node1, True, int(eid), 0, 0)
edge_list.append(edge)
node0.add_link(edge, node1)
node1.add_link(edge, node0)
edge_set = set()
idx = 0
def gene_from_gps_data():
"""
从gps点记录里面获取轨迹，并生成出发点和到达点
:return:
"""
try:
gps_data = {}
# gps_data 用于存放每辆车的轨迹
fp = open('./road/road.txt', 'w')
conn = cx_Oracle.connect('hz', 'hz', '192.168.11.88/orcl')
sql = "select vehicle_num, px, py from tb_gps_1805 where speed_time > to_date(" \
"'2018-05-01 9:00:00', 'yyyy-mm-dd hh24:mi:ss')" \
"and speed_time < to_date('2018-05-01 10:00:00', 'yyyy-mm-dd hh24:mi:ss')"
cursor = conn.cursor()
bt = time.clock()
cursor.execute(sql)
cnt = 0
for item in cursor:
data = map(float, item[1:3])
lng, lat = data[0:2]
if lng > 121 or lng < 119 or lat > 31 or lat < 29:
continue
x, y = bl2xy(lat, lng)
veh = item[0][-6:]
try:
gps_data[veh].append([x, y])
except KeyError:
gps_data[veh] = [[x, y]]
cnt += 1
conn.close()
et = time.clock()
print et - bt
s = 0
req_list = []
for veh, data_list in gps_data.iteritems():
s += len(data_list)
use_list = data_list[::10]
for i in range(len(use_list) - 1):
req_list.append([data_list[i][0], data_list[i][1], data_list[i + 1][0], data_list[i + 1][1]])
print s
pa_list, cnt, idx = [], 0, 0
for req in req_list:
x0, y0, x1, y1 = req[0:4]
if calc_dist([x0, y0], [x1, y1]) > 2000:
b0, l0 = xy2bl(x0, y0)
b1, l1 = xy2bl(x1, y1)
pa_list.append([l0, b0, l1, b1])
idx += 1
if idx >= 20:
batch_path_fetch(pa_list, fp)
time.sleep(0.2)
pa_list = []
idx = 0
cnt += 1
print "batch" + str(cnt)
if cnt >= 1000:
break
fp.close()
except Exception as e:
print e.message
