def create_country_stats(session, date, logger=None):
if logger is None:
logger = app.logger
(start, end) = date_to_timestamps(date)
# First kill the stats for the selected date
deleted_counter = session.query(CountryStats).filter(
CountryStats.date == date).delete()
country_stats = (session.query(
literal(date), Country.gid,
func.count(AircraftBeacon.timestamp).label("aircraft_beacon_count"),
func.count(func.distinct(
AircraftBeacon.receiver_id)).label("device_count")).filter(
between(AircraftBeacon.timestamp, start, end)).filter(
func.st_contains(Country.geom,
AircraftBeacon.location)).group_by(
Country.gid).subquery())
# And insert them
ins = insert(CountryStats).from_select([
CountryStats.date, CountryStats.country_id,
CountryStats.aircraft_beacon_count, CountryStats.device_count
], country_stats)
res = session.execute(ins)
insert_counter = res.rowcount
session.commit()
def update_max_altitudes(session, date, logger=None):
"""Add max altitudes in logbook when flight is complete (takeoff and landing)."""
if logger is None:
logger = app.logger
logger.info("Update logbook max altitude.")
if session is None:
session = app.session
(start, end) = date_to_timestamps(date)
logbook_entries = (
session.query(Logbook.id)
.filter(and_(Logbook.takeoff_timestamp != null(), Logbook.landing_timestamp != null(), Logbook.max_altitude == null()))
.filter(between(Logbook.reftime, start, end))
.subquery()
)
max_altitudes = (
session.query(Logbook.id, func.max(AircraftBeacon.altitude).label("max_altitude"))
.filter(Logbook.id == logbook_entries.c.id)
.filter(and_(AircraftBeacon.device_id == Logbook.device_id, AircraftBeacon.timestamp >= Logbook.takeoff_timestamp, AircraftBeacon.timestamp <= Logbook.landing_timestamp))
.group_by(Logbook.id)
.subquery()
)
update_logbook = session.query(Logbook).filter(Logbook.id == max_altitudes.c.id).update({Logbook.max_altitude: max_altitudes.c.max_altitude}, synchronize_session="fetch")
session.commit()
finish_message = "Logbook (altitude): {} entries updated.".format(update_logbook)
logger.info(finish_message)
return finish_message
def compute_takeoff_landing(start, end):
"""Compute takeoffs and landings."""
days = get_database_days(start, end)
pbar = tqdm(days)
for single_date in pbar:
pbar.set_description(datetime.strftime(single_date, "%Y-%m-%d"))
(start, end) = date_to_timestamps(single_date)
result = update_takeoff_landings(start=start, end=end)
def create_relation_stats(session, date, logger=None):
"""Add/update relation stats."""
if logger is None:
logger = app.logger
(start, end) = date_to_timestamps(date)
# First kill the stats for the selected date
deleted_counter = session.query(RelationStats).filter(
RelationStats.date == date).delete()
# Calculate stats for selected day
relation_stats = (session.query(
literal(date), AircraftBeacon.device_id, AircraftBeacon.receiver_id,
func.max(AircraftBeacon.quality),
func.count(AircraftBeacon.timestamp)).filter(
and_(
between(AircraftBeacon.timestamp, start, end),
AircraftBeacon.distance > 1000,
AircraftBeacon.error_count == 0,
AircraftBeacon.quality <= MAX_PLAUSIBLE_QUALITY,
AircraftBeacon.ground_speed > 10,
)).group_by(literal(date), AircraftBeacon.device_id,
AircraftBeacon.receiver_id).subquery())
# And insert them
ins = insert(RelationStats).from_select([
RelationStats.date, RelationStats.device_id, RelationStats.receiver_id,
RelationStats.quality, RelationStats.beacon_count
], relation_stats)
res = session.execute(ins)
insert_counter = res.rowcount
session.commit()
logger.warn("RelationStats for {}: {} deleted, {} inserted".format(
date, deleted_counter, insert_counter))
return "RelationStats for {}: {} deleted, {} inserted".format(
date, deleted_counter, insert_counter)
def show(airport_name, date=None):
"""Show a logbook for <airport_name>."""
airport = db.session.query(Airport).filter(
Airport.name == airport_name).first()
if airport is None:
print('Airport "{}" not found.'.format(airport_name))
return
or_args = []
if date is not None:
date = datetime.strptime(date, "%Y-%m-%d")
(start, end) = date_to_timestamps(date)
or_args = [db.between(Logbook.reftime, start, end)]
# get all logbook entries and add device and airport infos
logbook_query = (db.session.query(
func.row_number().over(order_by=Logbook.reftime).label("row_number"),
Logbook).filter(*or_args).filter(
db.or_(Logbook.takeoff_airport_id == airport.id,
Logbook.landing_airport_id == airport.id)).order_by(
Logbook.reftime))
# ... and finally print out the logbook
print("--- Logbook ({}) ---".format(airport_name))
def none_datetime_replacer(datetime_object):
return "--:--:--" if datetime_object is None else datetime_object.time(
)
def none_track_replacer(track_object):
return "--" if track_object is None else round(track_object / 10.0)
def none_timedelta_replacer(timedelta_object):
return "--:--:--" if timedelta_object is None else timedelta_object
def none_registration_replacer(device_object):
return "[" + device_object.address + "]" if len(
device_object.infos) == 0 else device_object.infos[0].registration
def none_aircraft_replacer(device_object):
return "(unknown)" if len(
device_object.infos) == 0 else device_object.infos[0].aircraft
def airport_marker(logbook_object):
if logbook_object.takeoff_airport is not None and logbook_object.takeoff_airport.name is not airport.name:
return "FROM: {}".format(logbook_object.takeoff_airport.name)
elif logbook_object.landing_airport is not None and logbook_object.landing_airport.name is not airport.name:
return "TO: {}".format(logbook_object.landing_airport.name)
else:
return ""
def none_altitude_replacer(logbook_object):
return "?" if logbook_object.max_altitude is None else "{:5d}m ({:+5d}m)".format(
logbook_object.max_altitude, logbook_object.max_altitude -
logbook_object.takeoff_airport.altitude)
for [row_number, logbook] in logbook_query.all():
print("%3d. %10s %8s (%2s) %8s (%2s) %8s %15s %8s %17s %20s" %
(
row_number,
logbook.reftime.date(),
none_datetime_replacer(logbook.takeoff_timestamp),
none_track_replacer(logbook.takeoff_track),
none_datetime_replacer(logbook.landing_timestamp),
none_track_replacer(logbook.landing_track),
none_timedelta_replacer(logbook.duration),
none_altitude_replacer(logbook),
none_registration_replacer(logbook.device),
none_aircraft_replacer(logbook.device),
airport_marker(logbook),
))
def update_device_stats_jumps(session, date, logger=None):
"""Update device stats jumps."""
if logger is None:
logger = app.logger
(start, end) = date_to_timestamps(date)
# speed limits in m/s (values above indicates a unplausible position / jump)
max_horizontal_speed = 1000
max_vertical_speed = 100
max_jumps = 10 # threshold for an 'ambiguous' device
# find consecutive positions for a device
sq = (session.query(
AircraftBeacon.device_id,
AircraftBeacon.timestamp,
func.lead(AircraftBeacon.timestamp).over(
partition_by=AircraftBeacon.device_id,
order_by=AircraftBeacon.timestamp).label("timestamp_next"),
AircraftBeacon.location_wkt,
func.lead(AircraftBeacon.location_wkt).over(
partition_by=AircraftBeacon.device_id,
order_by=AircraftBeacon.timestamp).label("location_next"),
AircraftBeacon.altitude,
func.lead(AircraftBeacon.altitude).over(
partition_by=AircraftBeacon.device_id,
order_by=AircraftBeacon.timestamp).label("altitude_next"),
).filter(
and_(between(AircraftBeacon.timestamp, start, end),
AircraftBeacon.error_count == 0)).subquery())
# calc vertial and horizontal speed between points
sq2 = (session.query(
sq.c.device_id,
(func.st_distancesphere(sq.c.location_next, sq.c.location) /
(func.extract("epoch", sq.c.timestamp_next) -
func.extract("epoch", sq.c.timestamp))).label("horizontal_speed"),
((sq.c.altitude_next - sq.c.altitude) /
(func.extract("epoch", sq.c.timestamp_next) -
func.extract("epoch", sq.c.timestamp))).label("vertical_speed"),
).filter(
and_(sq.c.timestamp != null(), sq.c.timestamp_next != null(),
sq.c.timestamp < sq.c.timestamp_next)).subquery())
# ... and find and count 'jumps'
sq3 = (session.query(
sq2.c.device_id,
func.sum(
case([(or_(
func.abs(sq2.c.horizontal_speed) > max_horizontal_speed,
func.abs(sq2.c.vertical_speed) > max_vertical_speed), 1)],
else_=0)).label("jumps")).group_by(
sq2.c.device_id).subquery())
upd = update(DeviceStats).where(
and_(DeviceStats.date == date,
DeviceStats.device_id == sq3.c.device_id)).values({
"ambiguous":
sq3.c.jumps > max_jumps,
"jumps":
sq3.c.jumps
})
result = session.execute(upd)
update_counter = result.rowcount
session.commit()
logger.warn("Updated {} DeviceStats jumps".format(update_counter))
return "DeviceStats jumps for {}: {} updated".format(date, update_counter)
def create_device_stats(session, date, logger=None):
"""Add/update device stats."""
if logger is None:
logger = app.logger
(start, end) = date_to_timestamps(date)
# First kill the stats for the selected date
deleted_counter = session.query(DeviceStats).filter(
DeviceStats.date == date).delete()
# Since "distinct count" does not work in window functions we need a work-around for receiver counting
sq = (session.query(
AircraftBeacon,
func.dense_rank().over(
partition_by=AircraftBeacon.device_id,
order_by=AircraftBeacon.receiver_id).label("dr")).filter(
and_(between(AircraftBeacon.timestamp, start, end),
AircraftBeacon.device_id != null())).filter(
or_(AircraftBeacon.error_count == 0,
AircraftBeacon.error_count == null())).subquery())
# Calculate stats, firstseen, lastseen and last values != NULL
device_stats = session.query(
distinct(sq.c.device_id).label("device_id"),
literal(date).label("date"),
func.max(
sq.c.dr).over(partition_by=sq.c.device_id).label("receiver_count"),
func.max(sq.c.altitude).over(
partition_by=sq.c.device_id).label("max_altitude"),
func.count(sq.c.device_id).over(
partition_by=sq.c.device_id).label("aircraft_beacon_count"),
func.first_value(sq.c.name).over(
partition_by=sq.c.device_id,
order_by=case(
[(sq.c.name == null(), None)],
else_=sq.c.timestamp).asc().nullslast()).label("name"),
func.first_value(sq.c.timestamp).over(
partition_by=sq.c.device_id,
order_by=case(
[(sq.c.timestamp == null(), None)],
else_=sq.c.timestamp).asc().nullslast()).label("firstseen"),
func.first_value(sq.c.timestamp).over(
partition_by=sq.c.device_id,
order_by=case(
[(sq.c.timestamp == null(), None)],
else_=sq.c.timestamp).desc().nullslast()).label("lastseen"),
func.first_value(sq.c.aircraft_type).over(
partition_by=sq.c.device_id,
order_by=case([(sq.c.aircraft_type == null(), None)],
else_=sq.c.timestamp).desc().nullslast()).label(
"aircraft_type"),
func.first_value(sq.c.stealth).over(
partition_by=sq.c.device_id,
order_by=case(
[(sq.c.stealth == null(), None)],
else_=sq.c.timestamp).desc().nullslast()).label("stealth"),
func.first_value(sq.c.software_version).over(
partition_by=sq.c.device_id,
order_by=case([(sq.c.software_version == null(), None)],
else_=sq.c.timestamp).desc().nullslast()).label(
"software_version"),
func.first_value(sq.c.hardware_version).over(
partition_by=sq.c.device_id,
order_by=case([(sq.c.hardware_version == null(), None)],
else_=sq.c.timestamp).desc().nullslast()).label(
"hardware_version"),
func.first_value(sq.c.real_address).over(
partition_by=sq.c.device_id,
order_by=case([(sq.c.real_address == null(), None)],
else_=sq.c.timestamp).desc().nullslast()).label(
"real_address"),
).subquery()
# And insert them
ins = insert(DeviceStats).from_select(
[
DeviceStats.device_id,
DeviceStats.date,
DeviceStats.receiver_count,
DeviceStats.max_altitude,
DeviceStats.aircraft_beacon_count,
DeviceStats.name,
DeviceStats.firstseen,
DeviceStats.lastseen,
DeviceStats.aircraft_type,
DeviceStats.stealth,
DeviceStats.software_version,
DeviceStats.hardware_version,
DeviceStats.real_address,
],
device_stats,
)
res = session.execute(ins)
insert_counter = res.rowcount
session.commit()
logger.debug("DeviceStats for {}: {} deleted, {} inserted".format(
date, deleted_counter, insert_counter))
return "DeviceStats for {}: {} deleted, {} inserted".format(
date, deleted_counter, insert_counter)
def create_receiver_stats(session, date, logger=None):
"""Add/update receiver stats."""
if logger is None:
logger = app.logger
(start, end) = date_to_timestamps(date)
# First kill the stats for the selected date
deleted_counter = session.query(ReceiverStats).filter(
ReceiverStats.date == date).delete()
# Select one day
sq = session.query(ReceiverBeacon).filter(
between(ReceiverBeacon.timestamp, start, end)).subquery()
# Calculate stats, firstseen, lastseen and last values != NULL
receiver_stats = session.query(
distinct(sq.c.receiver_id).label("receiver_id"),
literal(date).label("date"),
func.first_value(sq.c.timestamp).over(
partition_by=sq.c.receiver_id,
order_by=case(
[(sq.c.timestamp == null(), None)],
else_=sq.c.timestamp).asc().nullslast()).label("firstseen"),
func.first_value(sq.c.timestamp).over(
partition_by=sq.c.receiver_id,
order_by=case(
[(sq.c.timestamp == null(), None)],
else_=sq.c.timestamp).desc().nullslast()).label("lastseen"),
func.first_value(sq.c.location).over(
partition_by=sq.c.receiver_id,
order_by=case([(sq.c.location == null(), None)],
else_=sq.c.timestamp).desc().nullslast()).label(
"location_wkt"),
func.first_value(sq.c.altitude).over(
partition_by=sq.c.receiver_id,
order_by=case(
[(sq.c.altitude == null(), None)],
else_=sq.c.timestamp).desc().nullslast()).label("altitude"),
func.first_value(sq.c.version).over(
partition_by=sq.c.receiver_id,
order_by=case(
[(sq.c.version == null(), None)],
else_=sq.c.timestamp).desc().nullslast()).label("version"),
func.first_value(sq.c.platform).over(
partition_by=sq.c.receiver_id,
order_by=case(
[(sq.c.platform == null(), None)],
else_=sq.c.timestamp).desc().nullslast()).label("platform"),
).subquery()
# And insert them
ins = insert(ReceiverStats).from_select(
[
ReceiverStats.receiver_id,
ReceiverStats.date,
ReceiverStats.firstseen,
ReceiverStats.lastseen,
ReceiverStats.location_wkt,
ReceiverStats.altitude,
ReceiverStats.version,
ReceiverStats.platform,
],
receiver_stats,
)
res = session.execute(ins)
insert_counter = res.rowcount
session.commit()
logger.warn("ReceiverStats for {}: {} deleted, {} inserted".format(
date, deleted_counter, insert_counter))
# Update aircraft_beacon_count, aircraft_count and max_distance
aircraft_beacon_stats = (session.query(
AircraftBeacon.receiver_id,
func.count(AircraftBeacon.timestamp).label("aircraft_beacon_count"),
func.count(func.distinct(
AircraftBeacon.device_id)).label("aircraft_count"),
func.max(AircraftBeacon.distance).label("max_distance"),
).filter(
and_(between(AircraftBeacon.timestamp, start,
end), AircraftBeacon.error_count == 0,
AircraftBeacon.quality <= MAX_PLAUSIBLE_QUALITY,
AircraftBeacon.relay == null())).group_by(
AircraftBeacon.receiver_id).subquery())
upd = (update(ReceiverStats).where(
and_(ReceiverStats.date == date, ReceiverStats.receiver_id ==
aircraft_beacon_stats.c.receiver_id)).values({
"aircraft_beacon_count":
aircraft_beacon_stats.c.aircraft_beacon_count,
"aircraft_count":
aircraft_beacon_stats.c.aircraft_count,
"max_distance":
aircraft_beacon_stats.c.max_distance
}))
result = session.execute(upd)
update_counter = result.rowcount
session.commit()
logger.warn("Updated {} ReceiverStats".format(update_counter))
return "ReceiverStats for {}: {} deleted, {} inserted, {} updated".format(
date, deleted_counter, insert_counter, update_counter)
def update_entries(session, date, logger=None):
"""Create receiver coverage stats for Melissas ognrange."""
if logger is None:
logger = app.logger
logger.info("Compute receiver coverages.")
(start, end) = date_to_timestamps(date)
# Filter aircraft beacons
sq = (session.query(
AircraftBeacon.location_mgrs_short, AircraftBeacon.receiver_id,
AircraftBeacon.signal_quality, AircraftBeacon.altitude,
AircraftBeacon.device_id).filter(
and_(between(AircraftBeacon.timestamp, start,
end), AircraftBeacon.location_mgrs_short != null(),
AircraftBeacon.receiver_id != null(),
AircraftBeacon.device_id != null())).subquery())
# ... and group them by reduced MGRS, receiver and date
query = (session.query(
sq.c.location_mgrs_short,
sq.c.receiver_id,
func.cast(date, Date).label("date"),
func.max(sq.c.signal_quality).label("max_signal_quality"),
func.min(sq.c.altitude).label("min_altitude"),
func.max(sq.c.altitude).label("max_altitude"),
func.count(sq.c.altitude).label("aircraft_beacon_count"),
func.count(func.distinct(sq.c.device_id)).label("device_count"),
).group_by(sq.c.location_mgrs_short, sq.c.receiver_id).subquery())
# if a receiver coverage entry exist --> update it
upd = (update(ReceiverCoverage).where(
and_(
ReceiverCoverage.location_mgrs_short ==
query.c.location_mgrs_short,
ReceiverCoverage.receiver_id == query.c.receiver_id,
ReceiverCoverage.date == date)).values({
"max_signal_quality":
query.c.max_signal_quality,
"min_altitude":
query.c.min_altitude,
"max_altitude":
query.c.max_altitude,
"aircraft_beacon_count":
query.c.aircraft_beacon_count,
"device_count":
query.c.device_count,
}))
result = session.execute(upd)
update_counter = result.rowcount
session.commit()
logger.debug(
"Updated receiver coverage entries: {}".format(update_counter))
# if a receiver coverage entry doesnt exist --> insert it
new_coverage_entries = session.query(query).filter(~exists().where(
and_(
ReceiverCoverage.location_mgrs_short ==
query.c.location_mgrs_short, ReceiverCoverage.receiver_id ==
query.c.receiver_id, ReceiverCoverage.date == date)))
ins = insert(ReceiverCoverage).from_select(
(
ReceiverCoverage.location_mgrs_short,
ReceiverCoverage.receiver_id,
ReceiverCoverage.date,
ReceiverCoverage.max_signal_quality,
ReceiverCoverage.min_altitude,
ReceiverCoverage.max_altitude,
ReceiverCoverage.aircraft_beacon_count,
ReceiverCoverage.device_count,
),
new_coverage_entries,
)
result = session.execute(ins)
insert_counter = result.rowcount
session.commit()
finish_message = "ReceiverCoverage: {} inserted, {} updated".format(
insert_counter, update_counter)
logger.debug(finish_message)
return finish_message
def update_logbook(offset_days=None):
"""Add/update logbook entries."""
current_app.logger.info("Compute logbook.")
# limit time range to given date and set window partition and window order
if offset_days:
(start, end) = date_to_timestamps(datetime.utcnow() -
timedelta(days=offset_days))
else:
(start, end) = date_to_timestamps(datetime.utcnow().date())
pa = TakeoffLanding.sender_id
wo = db.and_(TakeoffLanding.sender_id, TakeoffLanding.timestamp,
TakeoffLanding.airport_id)
# make a query with previous, current and next "takeoff_landing" event, so we can find complete flights
sq = (db.session.query(
TakeoffLanding.sender_id,
db.func.lag(TakeoffLanding.sender_id).over(
partition_by=pa, order_by=wo).label("sender_id_prev"),
db.func.lead(TakeoffLanding.sender_id).over(
partition_by=pa,
order_by=wo).label("sender_id_next"), TakeoffLanding.timestamp,
db.func.lag(TakeoffLanding.timestamp).over(
partition_by=pa, order_by=wo).label("timestamp_prev"),
db.func.lead(TakeoffLanding.timestamp).over(
partition_by=pa,
order_by=wo).label("timestamp_next"), TakeoffLanding.track,
db.func.lag(TakeoffLanding.track).over(
partition_by=pa, order_by=wo).label("track_prev"),
db.func.lead(TakeoffLanding.track).over(
partition_by=pa, order_by=wo).label("track_next"),
TakeoffLanding.is_takeoff,
db.func.lag(TakeoffLanding.is_takeoff).over(
partition_by=pa, order_by=wo).label("is_takeoff_prev"),
db.func.lead(TakeoffLanding.is_takeoff).over(
partition_by=pa, order_by=wo).label("is_takeoff_next"),
TakeoffLanding.airport_id,
db.func.lag(TakeoffLanding.airport_id).over(
partition_by=pa, order_by=wo).label("airport_id_prev"),
db.func.lead(TakeoffLanding.airport_id).over(
partition_by=pa, order_by=wo).label("airport_id_next")).subquery())
# find (new) starts without landing
only_starts_query = (db.session.query(
sq.c.sender_id.label("sender_id"),
sq.c.timestamp.label("takeoff_timestamp"),
sq.c.track.label("takeoff_track"),
sq.c.airport_id.label("takeoff_airport_id")
).filter(sq.c.is_takeoff == db.true()).filter(
db.or_(
sq.c.is_takeoff_next == db.true(),
sq.c.is_takeoff_next == db.null())).filter(~Logbook.query.filter(
db.and_(Logbook.sender_id == sq.c.sender_id,
Logbook.takeoff_timestamp == sq.c.timestamp, Logbook.
takeoff_airport_id == sq.c.airport_id)).exists()))
ins = insert(Logbook).from_select(
(Logbook.sender_id, Logbook.takeoff_timestamp, Logbook.takeoff_track,
Logbook.takeoff_airport_id),
only_starts_query,
)
result = db.session.execute(ins)
current_app.logger.debug(f"Added {result.rowcount} starts")
db.session.commit()
# find (new) landings without start
only_landings_query = (db.session.query(
sq.c.sender_id.label("sender_id"),
sq.c.timestamp.label("landing_timestamp"),
sq.c.track.label("landing_track"),
sq.c.airport_id.label("landing_airport_id"),
).filter(
db.or_(
sq.c.is_takeoff_prev == db.false(),
sq.c.is_takeoff_prev == db.null())).filter(
sq.c.is_takeoff == db.false()).filter(~Logbook.query.filter(
db.and_(Logbook.sender_id == sq.c.sender_id, Logbook.
landing_timestamp == sq.c.timestamp, Logbook.
landing_airport_id == sq.c.airport_id)).exists()))
ins = insert(Logbook).from_select(
(Logbook.sender_id, Logbook.landing_timestamp, Logbook.landing_track,
Logbook.landing_airport_id),
only_landings_query,
)
result = db.session.execute(ins)
current_app.logger.debug(f"Added {result.rowcount} landings")
db.session.commit()
# find complete flights
complete_flight_query = (db.session.query(
sq.c.sender_id.label("sender_id"),
sq.c.timestamp.label("takeoff_timestamp"),
sq.c.track.label("takeoff_track"),
sq.c.airport_id.label("takeoff_airport_id"),
sq.c.timestamp_next.label("landing_timestamp"),
sq.c.track_next.label("landing_track"),
sq.c.airport_id_next.label("landing_airport_id"),
).filter(sq.c.is_takeoff == db.true()).filter(
sq.c.is_takeoff_next == db.false()).subquery())
# insert (new) flights
new_flights_query = (
db.session.query(complete_flight_query).filter(~Logbook.query.filter(
db.and_(
Logbook.sender_id == complete_flight_query.c.sender_id,
Logbook.landing_timestamp == complete_flight_query.c.
landing_timestamp, Logbook.landing_airport_id ==
complete_flight_query.c.landing_airport_id)).exists()).
filter(~Logbook.query.filter(
db.and_(
Logbook.sender_id == complete_flight_query.c.sender_id,
Logbook.takeoff_timestamp == complete_flight_query.c.
takeoff_timestamp, Logbook.takeoff_airport_id ==
complete_flight_query.c.takeoff_airport_id)).exists()))
ins = insert(Logbook).from_select(
(Logbook.sender_id, Logbook.takeoff_timestamp, Logbook.takeoff_track,
Logbook.takeoff_airport_id, Logbook.landing_timestamp,
Logbook.landing_track, Logbook.landing_airport_id), new_flights_query)
result = db.session.execute(ins)
current_app.logger.debug(f"Added {result.rowcount} complete flights")
db.session.commit()
# update existing landing with takeoff from complete flight
upd = db.update(Logbook) \
.where(db.and_(
Logbook.sender_id == complete_flight_query.c.sender_id,
Logbook.takeoff_timestamp == db.null(),
Logbook.takeoff_airport_id == db.null(),
Logbook.landing_timestamp != db.null(),
Logbook.landing_timestamp == complete_flight_query.c.landing_timestamp,
Logbook.landing_airport_id == complete_flight_query.c.landing_airport_id
)) \
.values(takeoff_timestamp=complete_flight_query.c.takeoff_timestamp,
takeoff_track=complete_flight_query.c.takeoff_track,
takeoff_airport_id=complete_flight_query.c.takeoff_airport_id)
result = db.session.execute(upd)
current_app.logger.debug(
f"Updated {result.rowcount} takeoffs to complete flights")
db.session.commit()
# update existing takeoff with landing from complete flight
upd = db.update(Logbook) \
.where(db.and_(
Logbook.sender_id == complete_flight_query.c.sender_id,
Logbook.takeoff_timestamp != db.null(),
Logbook.takeoff_timestamp == complete_flight_query.c.takeoff_timestamp,
Logbook.takeoff_airport_id == complete_flight_query.c.takeoff_airport_id,
Logbook.landing_timestamp == db.null(),
Logbook.landing_airport_id == db.null()
)) \
.values(landing_timestamp=complete_flight_query.c.landing_timestamp,
landing_track=complete_flight_query.c.landing_track,
landing_airport_id=complete_flight_query.c.landing_airport_id)
result = db.session.execute(upd)
current_app.logger.debug(
f"Updated {result.rowcount} landings to complete flights")
db.session.commit()
return
def update_entries(session, date, logger=None):
"""Add/update logbook entries."""
if logger is None:
logger = current_app.logger
logger.info("Compute logbook.")
# limit time range to given date and set window partition and window order
(start, end) = date_to_timestamps(date)
pa = TakeoffLanding.device_id
wo = and_(TakeoffLanding.device_id, TakeoffLanding.airport_id,
TakeoffLanding.timestamp)
# make a query with current, previous and next "takeoff_landing" event, so we can find complete flights
sq = (session.query(
TakeoffLanding.device_id,
func.lag(TakeoffLanding.device_id).over(
partition_by=pa, order_by=wo).label("device_id_prev"),
func.lead(TakeoffLanding.device_id).over(
partition_by=pa, order_by=wo).label("device_id_next"),
TakeoffLanding.timestamp,
func.lag(TakeoffLanding.timestamp).over(
partition_by=pa, order_by=wo).label("timestamp_prev"),
func.lead(TakeoffLanding.timestamp).over(
partition_by=pa, order_by=wo).label("timestamp_next"),
TakeoffLanding.track,
func.lag(TakeoffLanding.track).over(partition_by=pa,
order_by=wo).label("track_prev"),
func.lead(TakeoffLanding.track).over(partition_by=pa,
order_by=wo).label("track_next"),
TakeoffLanding.is_takeoff,
func.lag(TakeoffLanding.is_takeoff).over(
partition_by=pa, order_by=wo).label("is_takeoff_prev"),
func.lead(TakeoffLanding.is_takeoff).over(
partition_by=pa, order_by=wo).label("is_takeoff_next"),
TakeoffLanding.airport_id,
func.lag(TakeoffLanding.airport_id).over(
partition_by=pa, order_by=wo).label("airport_id_prev"),
func.lead(TakeoffLanding.airport_id).over(
partition_by=pa, order_by=wo).label("airport_id_next"),
).filter(between(TakeoffLanding.timestamp, start, end)).subquery())
# find complete flights
complete_flight_query = session.query(
sq.c.timestamp.label("reftime"),
sq.c.device_id.label("device_id"),
sq.c.timestamp.label("takeoff_timestamp"),
sq.c.track.label("takeoff_track"),
sq.c.airport_id.label("takeoff_airport_id"),
sq.c.timestamp_next.label("landing_timestamp"),
sq.c.track_next.label("landing_track"),
sq.c.airport_id_next.label("landing_airport_id"),
).filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false()))
# find landings without start
only_landings_query = (session.query(
sq.c.timestamp.label("reftime"),
sq.c.device_id.label("device_id"),
null().label("takeoff_timestamp"),
null().label("takeoff_track"),
null().label("takeoff_airport_id"),
sq.c.timestamp.label("landing_timestamp"),
sq.c.track.label("landing_track"),
sq.c.airport_id.label("landing_airport_id"),
).filter(sq.c.is_takeoff == false()).filter(
or_(sq.c.is_takeoff_prev == false(), sq.c.is_takeoff_prev == null())))
# find starts without landing
only_starts_query = (session.query(
sq.c.timestamp.label("reftime"),
sq.c.device_id.label("device_id"),
sq.c.timestamp.label("takeoff_timestamp"),
sq.c.track.label("takeoff_track"),
sq.c.airport_id.label("takeoff_airport_id"),
null().label("landing_timestamp"),
null().label("landing_track"),
null().label("landing_airport_id"),
).filter(sq.c.is_takeoff == true()).filter(
or_(sq.c.is_takeoff_next == true(), sq.c.is_takeoff_next == null())))
# unite all computated flights
union_query = complete_flight_query.union(only_landings_query,
only_starts_query).subquery()
# if a logbook entry exist --> update it
upd = (update(Logbook).where(
and_(
Logbook.device_id == union_query.c.device_id,
union_query.c.takeoff_airport_id != null(),
union_query.c.landing_airport_id != null(),
or_(
and_(
Logbook.takeoff_airport_id ==
union_query.c.takeoff_airport_id, Logbook.takeoff_timestamp
== union_query.c.takeoff_timestamp,
Logbook.landing_airport_id == null()),
and_(
Logbook.takeoff_airport_id == null(),
Logbook.landing_airport_id ==
union_query.c.landing_airport_id, Logbook.landing_timestamp
== union_query.c.landing_timestamp),
),
)).values({
"reftime": union_query.c.reftime,
"takeoff_timestamp": union_query.c.takeoff_timestamp,
"takeoff_track": union_query.c.takeoff_track,
"takeoff_airport_id": union_query.c.takeoff_airport_id,
"landing_timestamp": union_query.c.landing_timestamp,
"landing_track": union_query.c.landing_track,
"landing_airport_id": union_query.c.landing_airport_id,
}))
result = session.execute(upd)
update_counter = result.rowcount
session.commit()
logger.debug("Updated logbook entries: {}".format(update_counter))
# if a logbook entry doesnt exist --> insert it
new_logbook_entries = session.query(union_query).filter(~exists().where(
and_(
Logbook.device_id == union_query.c.device_id,
or_(
and_(
Logbook.takeoff_airport_id ==
union_query.c.takeoff_airport_id, Logbook.takeoff_timestamp
== union_query.c.takeoff_timestamp),
and_(Logbook.takeoff_airport_id == null(),
union_query.c.takeoff_airport_id == null()),
),
or_(
and_(
Logbook.landing_airport_id ==
union_query.c.landing_airport_id, Logbook.landing_timestamp
== union_query.c.landing_timestamp),
and_(Logbook.landing_airport_id == null(),
union_query.c.landing_airport_id == null()),
),
)))
ins = insert(Logbook).from_select(
(
Logbook.reftime,
Logbook.device_id,
Logbook.takeoff_timestamp,
Logbook.takeoff_track,
Logbook.takeoff_airport_id,
Logbook.landing_timestamp,
Logbook.landing_track,
Logbook.landing_airport_id,
),
new_logbook_entries,
)
result = session.execute(ins)
insert_counter = result.rowcount
session.commit()
finish_message = "Logbook: {} inserted, {} updated".format(
insert_counter, update_counter)
logger.debug(finish_message)
return finish_message
