def breakPath(fid, row, db):
br_time = row[0]
new_fid = fid + chr(ord('a') + path_ctr)
qry = "update flight set fid='{0}' where fid='{1}' and rec_time<'{2}'".format(
new_fid, fid, br_time)
print('executing query: ' + qry)
getRes(qry, db)
def getFidsBwStations(src, dest, db):
qry = (
"select fid, ttime from journeys where src={0} and dest={1} order by ttime asc;"
).format(src, dest)
res = getRes(qry, db)
fids = [x[0] for x in res]
ttimes = [x[1] for x in res]
print(fids)
return fids, ttimes
def addRoute(fid, clr, db, m):
query = ("select lat, lon from flight where fid='{0}' order by rec_time;"
).format(fid)
res = getRes(query, db)
# print(res)
for i in range(0, len(res) - 1):
a = [float(x) for x in res[i]]
b = [float(x) for x in res[i + 1]]
# print(i)
m.drawgreatcircle(a[1],
a[0],
b[1],
b[0],
linewidth=1,
color=clr,
label=fid)
print('plotted route of ' + fid + ' with color ' + clr)
query = 'select fid, min(rec_time) as s_time, max(rec_time) as e_time from flight group by fid order by fid limit 1000;'
csr.execute(query)
r = csr.fetchone()
tree = buildTree()
values = []
ctr = 0
while r is not None:
ctr += 1
try:
fid = r[0]
dtime = int((r[2] - r[1]).total_seconds())
# print(dtime)
query = (
'select fid,lat,lon from flight where fid="{0}" AND rec_time="{1}"'
).format(r[0], r[1])
res1 = getRes(query, db)[0]
spos = (float(res1[1]), float(res1[2]))
query = (
'select fid,lat,lon from flight where fid="{0}" AND rec_time="{1}"'
).format(r[0], r[2])
res1 = getRes(query, db)[0]
epos = (float(res1[1]), float(res1[2]))
s_air = getAirport(spos, tree, db)
e_air = getAirport(epos, tree, db)
dist = haversine(spos, epos)
print(ctr, fid, dtime, spos, epos, s_air, e_air, dist)
# query = ('insert into journeys values ("{0}",{1},{2},{3},{4})').format(fid,s_air,e_air,dtime,dist)
query = """insert into journeys values (%s,%s,%s,%s,%s)"""
def getAirport(p, tree, db):
pos=getNrPoint(p, tree)
query=('select * from airports where lat={0} and lon={1}').format(pos[0], pos[1])
res=getRes(query, db)[0]
return int(res[0])
def breakPath(fid, row, db):
br_time = row[0]
new_fid = fid + chr(ord('a') + path_ctr)
qry = "update flight set fid='{0}' where fid='{1}' and rec_time<'{2}'".format(
new_fid, fid, br_time)
print('executing query: ' + qry)
getRes(qry, db)
db = getConn()
thresh = 20 * 60
threshSpeed = 0.05
# fid='AAL1078'
qry = 'select fid from journeys limit 1000;'
res = getRes(qry, db)
fids = ['AAL1366']
for fid in fids:
try:
print('checking for fid: ' + fid)
qry = "select * from flight where fid='{0}' order by rec_time asc;".format(
fid)
res = getRes(qry, db)
path_ctr = 0
for i in range(1, len(res)):
dtime = (res[i][0] - res[i - 1][0]).total_seconds()
ddist = haversine((res[i][4], res[i][5]),
(res[i - 1][4], res[i - 1][5]))
speed = ddist / dtime
if (dtime > thresh and speed < threshSpeed):
print(ddist / dtime)
def getAirport(p, tree, db):
pos = getNrPoint(p, tree)
query = ('select * from airports where lat={0} and lon={1}').format(
pos[0], pos[1])
res = getRes(query, db)[0]
return int(res[0])
def breakPath(fid, row, db):
br_time=row[0]
new_fid=fid+chr(ord('a')+path_ctr)
qry="update flight set fid='{0}' where fid='{1}' and rec_time<'{2}'".format(new_fid, fid, br_time);
print('executing query: '+qry)
getRes(qry, db)
from haversine import haversine
def breakPath(fid, row, db):
br_time=row[0]
new_fid=fid+chr(ord('a')+path_ctr)
qry="update flight set fid='{0}' where fid='{1}' and rec_time<'{2}'".format(new_fid, fid, br_time);
print('executing query: '+qry)
getRes(qry, db)
db=getConn()
thresh=20*60
threshSpeed=0.05
# fid='AAL1078'
qry='select fid from journeys limit 1000;'
res=getRes(qry, db)
fids=['AAL1366']
for fid in fids:
try:
print('checking for fid: '+fid)
qry="select * from flight where fid='{0}' order by rec_time asc;".format(fid)
res=getRes(qry, db)
path_ctr=0
for i in range(1, len(res)):
dtime=(res[i][0]-res[i-1][0]).total_seconds();
ddist=haversine((res[i][4], res[i][5]), (res[i-1][4], res[i-1][5]))
speed=ddist/dtime
if(dtime>thresh and speed<threshSpeed):
print(ddist/dtime)
breakPath(fid, res[i], db)
path_ctr+=1