def process_flights(sub_batch):
db_conn = get_pg_conn()
insert_lst = []
for fl in sub_batch:
fl_d = pd.DataFrame.from_dict(fl)
fl_d = fl_d[fl_d['alt'] > flight_level_min]
if (fl_d['ts'].max() - fl_d['ts'].min()) < la_time:
# print("Flight too short above selected FL")
continue
else:
fl_d['time_el'] = fl_d['ts'] - fl_d['ts'].min()
# Clean up the dataframe by patching NAN values
fl_d['hdg'] = fl_d['hdg'].fillna(method='ffill')
fl_d['spd'] = fl_d['spd'].fillna(method='ffill')
try:
steps = int(fl_d['time_el'].max() / la_time) + 1
for i in range(steps):
dct = create_projection_dict(
fl_d[(fl_d['time_el'] > i * la_time)
& (fl_d['time_el'] < (i + 1) * la_time)], la_time)
if dct:
insert_lst.append(dct)
except Exception as e:
print(e)
continue
# Flush to DB
flush_stat = flush_to_db(insert_lst, db_conn)
if flush_stat:
print("%d Flights inserted" % len(insert_lst))
return True
def get_hourly_flight_batch(_ep):
fl_start_ep = _ep
ts_offset = 1800
conn_read = get_pg_conn()
cur_read = conn_read.cursor(cursor_factory=RealDictCursor)
cur_read.execute("SELECT ts, lat, lon, alt, spd, hdg, roc, start_ep, \
flight_id \
FROM public.adsb_flights \
WHERE (end_ep - start_ep) > %s \
AND ((end_ep - start_ep) / flight_length) < %s \
AND start_ep BETWEEN %s AND %s;",
(la_time, avg_sec_msg, fl_start_ep - ts_offset,
fl_start_ep + ts_offset))
batch = cur_read.fetchall()
cur_read.close()
conn_read.close()
return batch
def get_conflicts(ep):
fl_start_ep = ep
ts_offset = 1800
max_dst = 5 * 1852
alt_min = 15000
conn = get_pg_conn()
cur_read = conn.cursor(cursor_factory=RealDictCursor)
cur_read.execute(
"SELECT ts, lat, lon, alt, spd, hdg, roc, start_ep, flight_id \
FROM public.adsb_flights WHERE flight_length > 500 \
AND start_ep BETWEEN %s AND %s;",
(fl_start_ep - ts_offset, fl_start_ep + ts_offset))
sql_inj_lst = []
col_lst = [
'td', 'altd', 'dstd', 'hdgd', 'flight_id_1', 'ts_1', 'lat_1', 'lon_1',
'alt_1', 'spd_1', 'hdg_1', 'roc_1', 'flight_id_2', 'ts_2', 'lat_2',
'lon_2', 'alt_2', 'spd_2', 'hdg_2', 'roc_2'
]
batch = cur_read.fetchall()
id_lst = [b['flight_id'] for b in batch]
fset = list(itertools.combinations(id_lst, 2))
cnt = 1
print(len(fset))
for fs in fset:
f1 = next(f for f in batch if f['flight_id'] == fs[0])
f2 = next(f for f in batch if f['flight_id'] == fs[1])
if abs(f1['start_ep'] - f2['start_ep']) < 1800:
f1crd = [(lt, ln, ts) for lt, ln, alt, ts in list(
zip(f1['lat'], f1['lon'], f1['alt'], f1['ts']))
if alt > alt_min]
f2crd = [(lt, ln, ts) for lt, ln, alt, ts in list(
zip(f2['lat'], f2['lon'], f2['alt'], f2['ts']))
if alt > alt_min]
if len(f2crd) > 20 and len(f1crd) > 20:
t1 = time.time()
confl_list = find_flight_intersect(f1crd, f2crd)
if confl_list:
for con in confl_list:
d, c1, c2, i1, i2 = con[0], con[1], con[2], con[
3], con[4]
bdiff = abs(
calc_bearing((f1crd[i1 - 1][0], f1crd[i1 - 1][1]),
(f1crd[i1][0], f1crd[i1][1])) -
calc_bearing((f2crd[i2 - 1][0], f2crd[i2 - 1][1]),
(f2crd[i2][0], f2crd[i2][1])))
tdiff = abs(f1crd[i1][2] - f2crd[i2][2])
if d < max_dst and bdiff > 10: # and tdiff < max_ts
confl = {}
for k in [
'ts', 'lat', 'lon', 'alt', 'spd', 'hdg',
'roc'
]:
confl[('%s_1' % k)] = f1[k][:i1]
for k in [
'ts', 'lat', 'lon', 'alt', 'spd', 'hdg',
'roc'
]:
confl[('%s_2' % k)] = f2[k][:i2]
confln = postprocess_conflict(confl)
if confln:
confln['flight_id_1'] = f1['flight_id']
confln['flight_id_2'] = f2['flight_id']
# cidstr = "%s-%s" % (f1['flight_id'], f2['flight_id'])
# confl['ćid'] = cidstr
# confl['td'] = tdiff
# confl['altd'] = abs(f1['alt'][i1] - f2['alt'][i2])
# confl['dstd'] = d
# confl['hdgd'] = abs(f1['hdg'][i1] - f2['hdg'][i2])
if confln['dstd'][-1] < max_dst:
try:
sql_inj_lst.append(
tuple(confln[kk]
for kk in col_lst))
except Exception as e1:
print(e1)
cnt = cnt + 1
if len(sql_inj_lst) > 100:
cur_inj = conn.cursor()
records_list_template = ','.join(['%s'] * len(sql_inj_lst))
insert_query = 'insert into conflicts_3 (td, altd, dstd, hdgd,\
flight_id_1, ts_1, lat_1, lon_1, alt_1, spd_1, hdg_1, roc_1,\
flight_id_2, ts_2, lat_2, lon_2, alt_2, spd_2, hdg_2, roc_2) \
values {}'.format(
records_list_template)
print('100 records to be added')
cur_inj.execute(insert_query, sql_inj_lst)
conn.commit()
cur_inj.close()
sql_inj_lst = []
cur_read.close()
cur_inj = conn.cursor()
records_list_template = ','.join(['%s'] * len(sql_inj_lst))
insert_query = 'insert into conflicts_3 (td, altd, dstd, hdgd,\
flight_id_1, ts_1, lat_1, lon_1, alt_1, spd_1, hdg_1, roc_1,\
flight_id_2, ts_2, lat_2, lon_2, alt_2, spd_2, hdg_2, roc_2) \
values {}'.format(
records_list_template)
cur_inj.execute(insert_query, sql_inj_lst)
conn.commit()
cur_inj.close()
sql_inj_lst = []
conn.close()
insert_query = 'insert into conflicts_3 (td, altd, dstd, hdgd,\
flight_id_1, ts_1, lat_1, lon_1, alt_1, spd_1, hdg_1, roc_1,\
flight_id_2, ts_2, lat_2, lon_2, alt_2, spd_2, hdg_2, roc_2) \
values {}'.format(
records_list_template)
cur_inj.execute(insert_query, sql_inj_lst)
conn.commit()
cur_inj.close()
sql_inj_lst = []
conn.close()
if __name__ == "__main__":
conn = get_pg_conn()
cur_ts = conn.cursor(cursor_factory=RealDictCursor)
cur_ts.execute("SELECT start_ep, flight_id \
FROM public.adsb_flights;")
batch = cur_ts.fetchall()
x = [b['start_ep'] for b in batch]
daylst = list(
set([
datetime.datetime.fromtimestamp(xx).strftime('%Y-%m-%d %H')
for xx in x
]))
eplist = [(datetime.datetime.strptime(ts, '%Y-%m-%d %H') -
datetime.datetime(1970, 1, 1)).total_seconds() for ts in daylst]
def classify_flight_pairs(ep, ctype='all'):
assert ctype in ['all', 'conflict', 'expected', 'overlapping'], \
"Classification type not recognized"
alt_min = flight_level_min
sql_inj_conflicts = []
sql_inj_overlap = []
sql_inj_expected = []
col_lst = ['td', 'altd', 'dstd', 'hdgd', 'flight_id_1', 'ts_1', 'lat_1',
'lon_1', 'alt_1', 'spd_1', 'hdg_1', 'roc_1', 'flight_id_2',
'ts_2', 'lat_2', 'lon_2', 'alt_2', 'spd_2', 'hdg_2', 'roc_2']
batch = get_hourly_flight_batch(ep)
id_lst = [b['flight_id'] for b in batch]
fset = list(itertools.combinations(id_lst, 2))
fset_len = len(fset)
print('Set length: %d' % fset_len)
cnt = 0
for fs in fset:
overlap_found, conflict_found, conv_found = False, False, False
cnt = cnt + 1
f1 = [f for f in batch if f['flight_id'] == fs[0]][0]
f2 = [f for f in batch if f['flight_id'] == fs[1]][0]
f1crd, f2crd, f1td, f2td = resample_flight_set(f1, f2, alt_min)
if not all(i for i in [f1crd, f2crd, f1td, f2td]):
continue
if f1td > la_time and f2td > la_time:
if check_flight_overlap(f1crd, f2crd):
overlap_found = True
_cflag, c1, c2 = check_conflict(f1crd, f2crd)
if _cflag:
_cstat, f1crd, f2crd = create_artificial_conflict(f1crd,
f2crd,
c1, c2)
if _cstat:
conflict_found = True
if check_flight_convergence(f1crd, f2crd):
conv_found = True
if not any(c for c in [overlap_found, conflict_found,
conv_found]):
continue
f1_df = pd.DataFrame.from_records(f1crd,
columns=['lat', 'lon', 'ts',
'hdg', 'alt',
'roc', 'spd']
)
f2_df = pd.DataFrame.from_records(f2crd,
columns=['lat', 'lon', 'ts',
'hdg', 'alt',
'roc', 'spd']
)
fset_dict = create_flightset_dict(f1_df, f2_df)
try:
fset_aligned, confl_flag = align_flightset(fset_dict)
except Exception as e:
print(e)
continue
if not fset_aligned:
continue
fset_aligned['flight_id_1'] = f1['flight_id']
fset_aligned['flight_id_2'] = f2['flight_id']
if overlap_found and not confl_flag:
sql_inj_overlap.append(fset_aligned)
if confl_flag:
sql_inj_conflicts.append(fset_aligned)
if conv_found and not confl_flag:
sql_inj_expected.append(fset_aligned)
else:
continue
if sql_inj_conflicts:
conn = get_pg_conn()
column_lst = col_lst
sql_inj_list_c = [tuple([d[k] for k in column_lst])
for d in sql_inj_conflicts]
records_list_template = ','.join(['%s'] * len(sql_inj_list_c))
insert_query_c = 'insert into {} {} values {}'.format(
'conflicts', tuple(column_lst),
records_list_template).replace("'", "")
try:
cur = conn.cursor()
cur.execute(insert_query_c, sql_inj_list_c)
cur.close()
conn.commit()
conn.close()
except Exception as e:
print('Saving to DB failed, error: ')
print(e)
conn.close()
print("%d Conflicts inserted" % len(sql_inj_conflicts))
if sql_inj_expected:
conn = get_pg_conn()
column_lst = col_lst
sql_inj_list_e = [tuple([d[k] for k in column_lst])
for d in sql_inj_expected]
records_list_template = ','.join(['%s'] * len(sql_inj_list_e))
insert_query_e = 'insert into {} {} values {}'.format(
'converging_flights', tuple(column_lst),
records_list_template).replace("'", "")
try:
cur = conn.cursor()
cur.execute(insert_query_e, sql_inj_list_e)
cur.close()
conn.commit()
conn.close()
except Exception as e:
print('Saving to DB failed, error: ')
print(e)
conn.close()
print("%d Converging Flights inserted" % len(sql_inj_expected))
if sql_inj_overlap:
conn = get_pg_conn()
column_lst = col_lst
sql_inj_list_o = [tuple([d[k] for k in column_lst])
for d in sql_inj_overlap]
records_list_template = ','.join(['%s'] * len(sql_inj_list_o))
insert_query_o = 'insert into {} {} values {}'.format(
'overlapping_flights', tuple(column_lst),
records_list_template).replace("'", "")
try:
cur = conn.cursor()
cur.execute(insert_query_o, sql_inj_list_o)
cur.close()
conn.commit()
conn.close()
except Exception as e:
print('Saving to DB failed, error: ')
print(e)
conn.close()
print("%d Overlapping flights inserted" % len(sql_inj_overlap))
if not any(i for i in [sql_inj_overlap, sql_inj_expected,
sql_inj_conflicts]):
print('No flight sets found')