def fetch_trade_status_range(self,
universe: Universe,
start_date: str = None,
end_date: str = None,
dates: Iterable[str] = None,
offset=0):
codes = universe.query(self, start_date, end_date, dates)
if dates:
start_date = dates[0]
end_date = dates[-1]
end_date = advanceDateByCalendar('china.sse', end_date,
str(offset) + 'b').strftime('%Y-%m-%d')
stats = func.lead(Market.isOpen, offset).over(
partition_by=Market.code,
order_by=Market.trade_date).label('is_open')
cte = select([Market.trade_date, Market.code, stats]).where(
and_(
Market.trade_date.between(start_date, end_date),
Market.code.in_(codes.code.unique().tolist())
)
).cte('cte')
query = select([cte]).select_from(cte).order_by(cte.columns['trade_date'], cte.columns['code'])
df = pd.read_sql(query, self.engine)
return pd.merge(df, codes[['trade_date', 'code']], on=['trade_date', 'code'])
def __call__(self, var, minval=0, label=None):
label = label or var
return func.greatest(
func.lead(column(var))
.over(order_by=self.order_by,
partition_by=self.base_columns)
- column(var),
minval).label(label)
def _get_details_query(self, params, **kwargs):
example = super(TestExampleResource, self)._get_details_query(
params, **kwargs)
if example is None:
raise NotFound()
fields = self._get_show_fields(params)
if 'next' in fields or 'previous' in fields:
from sqlalchemy.sql import select, func, text, bindparam
from models import db
filter_params = kwargs.copy()
filter_params.update(self._prepare_filter_params(params))
filter_params.pop('id')
sort_by = params.get('sort_by', None) or 'id'
is_desc = params.get('order', None) == 'desc'
fields_to_select = [TestExample.id]
# TODO: simplify query with specifying WINDOW w
if 'previous' in fields:
fields_to_select.append(
func.lag(TestExample.id).over(
order_by=[sort_by, 'id']).label('prev'))
if 'next' in fields:
fields_to_select.append(
func.lead(TestExample.id).over(
order_by=[sort_by, 'id']).label('next'))
tbl = select(fields_to_select)
for name, val in filter_params.iteritems():
if '->>' in name: # TODO: refactor this
try:
splitted = name.split('->>')
name = "%s->>'%s'" % (splitted[0], splitted[1])
except:
logging.warning('Invalid GET param %s', name)
tbl.append_whereclause("%s='%s'" % (name, val))
tbl = tbl.cte('tbl')
select1 = select(['id', 'prev', 'next']).where(
tbl.c.id == kwargs['id'])
res = db.engine.execute(select1, id_1=kwargs['id'])
id_, example.previous, example.next = res.fetchone()
if not example.is_weights_calculated:
example.calc_weighted_data()
example = super(TestExampleResource, self)._get_details_query(
params, **kwargs)
return example
def fetch_trade_status(self,
ref_date: str,
codes: Iterable[int],
offset=0):
target_date = advanceDateByCalendar('china.sse', ref_date,
str(offset) +
'b').strftime('%Y%m%d')
stats = func.lead(Market.isOpen,
1).over(partition_by=Market.code,
order_by=Market.trade_date).label('is_open')
cte = select([Market.trade_date, Market.code, stats]).where(
and_(Market.trade_date.in_([ref_date, target_date]),
Market.code.in_(codes))).cte('cte')
query = select([
column('code'), column('is_open')
]).select_from(cte).where(column('trade_date') == ref_date).order_by(
column('code'))
return pd.read_sql(query, self.engine).sort_values(['code'])
def compute_logbook_entries(session=None):
logger.info("Compute logbook.")
if session is None:
session = app.session
or_args = [
between(TakeoffLanding.timestamp, '2016-06-28 00:00:00',
'2016-06-28 23:59:59')
]
or_args = []
# 'wo' is the window order for the sql window function
wo = and_(func.date(TakeoffLanding.timestamp), TakeoffLanding.device_id,
TakeoffLanding.timestamp, TakeoffLanding.airport_id)
# 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(order_by=wo).label('device_id_prev'),
func.lead(TakeoffLanding.device_id).over(order_by=wo).label('device_id_next'),
TakeoffLanding.timestamp,
func.lag(TakeoffLanding.timestamp).over(order_by=wo).label('timestamp_prev'),
func.lead(TakeoffLanding.timestamp).over(order_by=wo).label('timestamp_next'),
TakeoffLanding.track,
func.lag(TakeoffLanding.track).over(order_by=wo).label('track_prev'),
func.lead(TakeoffLanding.track).over(order_by=wo).label('track_next'),
TakeoffLanding.is_takeoff,
func.lag(TakeoffLanding.is_takeoff).over(order_by=wo).label('is_takeoff_prev'),
func.lead(TakeoffLanding.is_takeoff).over(order_by=wo).label('is_takeoff_next'),
TakeoffLanding.airport_id,
func.lag(TakeoffLanding.airport_id).over(order_by=wo).label('airport_id_prev'),
func.lead(TakeoffLanding.airport_id).over(order_by=wo).label('airport_id_next')) \
.filter(*or_args) \
.subquery()
# find complete flights (with takeoff and landing on the same day)
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'),
label('duration', sq.c.timestamp_next - sq.c.timestamp)) \
.filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false())) \
.filter(sq.c.device_id == sq.c.device_id_next) \
.filter(func.date(sq.c.timestamp_next) == func.date(sq.c.timestamp))
# split complete flights (with takeoff and landing on different days) into one takeoff and one landing
split_start_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'),
null().label('duration')) \
.filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false())) \
.filter(sq.c.device_id == sq.c.device_id_next) \
.filter(func.date(sq.c.timestamp_next) != func.date(sq.c.timestamp))
split_landing_query = session.query(
sq.c.timestamp_next.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_next.label('landing_timestamp'), sq.c.track_next.label('landing_track'), sq.c.airport_id_next.label('landing_airport_id'),
null().label('duration')) \
.filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false())) \
.filter(sq.c.device_id == sq.c.device_id_next) \
.filter(func.date(sq.c.timestamp_next) != func.date(sq.c.timestamp))
# 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'),
null().label('duration')) \
.filter(sq.c.is_takeoff == false()) \
.filter(or_(sq.c.device_id != sq.c.device_id_prev,
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'),
null().label('duration')) \
.filter(sq.c.is_takeoff == true()) \
.filter(or_(sq.c.device_id != sq.c.device_id_next,
sq.c.is_takeoff_next == true(),
sq.c.is_takeoff_next == null()))
# unite all computated flights
union_query = complete_flight_query.union(
split_start_query,
split_landing_query,
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({"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,
"duration": union_query.c.duration})
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, Logbook.duration), new_logbook_entries)
result = session.execute(ins)
insert_counter = result.rowcount
session.commit()
logger.debug("New logbook entries: {}".format(insert_counter))
return "{}/{}".format(update_counter, insert_counter)
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 compute_logbook_entries(session=None):
logger.info("Compute logbook.")
if session is None:
session = app.session
or_args = [between(TakeoffLanding.timestamp, '2016-06-28 00:00:00', '2016-06-28 23:59:59')]
or_args = []
# 'wo' is the window order for the sql window function
wo = and_(func.date(TakeoffLanding.timestamp),
TakeoffLanding.device_id,
TakeoffLanding.timestamp,
TakeoffLanding.airport_id)
# 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(order_by=wo).label('device_id_prev'),
func.lead(TakeoffLanding.device_id).over(order_by=wo).label('device_id_next'),
TakeoffLanding.timestamp,
func.lag(TakeoffLanding.timestamp).over(order_by=wo).label('timestamp_prev'),
func.lead(TakeoffLanding.timestamp).over(order_by=wo).label('timestamp_next'),
TakeoffLanding.track,
func.lag(TakeoffLanding.track).over(order_by=wo).label('track_prev'),
func.lead(TakeoffLanding.track).over(order_by=wo).label('track_next'),
TakeoffLanding.is_takeoff,
func.lag(TakeoffLanding.is_takeoff).over(order_by=wo).label('is_takeoff_prev'),
func.lead(TakeoffLanding.is_takeoff).over(order_by=wo).label('is_takeoff_next'),
TakeoffLanding.airport_id,
func.lag(TakeoffLanding.airport_id).over(order_by=wo).label('airport_id_prev'),
func.lead(TakeoffLanding.airport_id).over(order_by=wo).label('airport_id_next')) \
.filter(*or_args) \
.subquery()
# find complete flights (with takeoff and landing on the same day)
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'),
label('duration', sq.c.timestamp_next - sq.c.timestamp)) \
.filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false())) \
.filter(sq.c.device_id == sq.c.device_id_next) \
.filter(func.date(sq.c.timestamp_next) == func.date(sq.c.timestamp))
# split complete flights (with takeoff and landing on different days) into one takeoff and one landing
split_start_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'),
null().label('duration')) \
.filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false())) \
.filter(sq.c.device_id == sq.c.device_id_next) \
.filter(func.date(sq.c.timestamp_next) != func.date(sq.c.timestamp))
split_landing_query = session.query(
sq.c.timestamp_next.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_next.label('landing_timestamp'), sq.c.track_next.label('landing_track'), sq.c.airport_id_next.label('landing_airport_id'),
null().label('duration')) \
.filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false())) \
.filter(sq.c.device_id == sq.c.device_id_next) \
.filter(func.date(sq.c.timestamp_next) != func.date(sq.c.timestamp))
# 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'),
null().label('duration')) \
.filter(sq.c.is_takeoff == false()) \
.filter(or_(sq.c.device_id != sq.c.device_id_prev,
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'),
null().label('duration')) \
.filter(sq.c.is_takeoff == true()) \
.filter(or_(sq.c.device_id != sq.c.device_id_next,
sq.c.is_takeoff_next == true(),
sq.c.is_takeoff_next == null()))
# unite all computated flights
union_query = complete_flight_query.union(
split_start_query,
split_landing_query,
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({"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,
"duration": union_query.c.duration})
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,
Logbook.duration),
new_logbook_entries)
result = session.execute(ins)
insert_counter = result.rowcount
session.commit()
logger.debug("New logbook entries: {}".format(insert_counter))
return "{}/{}".format(update_counter, insert_counter)
def compute_takeoff_and_landing(session=None):
logger.info("Compute takeoffs and landings.")
if session is None:
session = app.session
# takeoff / landing detection is based on 3 consecutive points
takeoff_speed = 55 # takeoff detection: 1st point below, 2nd and 3rd above this limit
landing_speed = 40 # landing detection: 1st point above, 2nd and 3rd below this limit
duration = 100 # the points must not exceed this duration
radius = 0.05 # the points must not exceed this radius (degree!) around the 2nd point
# takeoff / landing has to be near an airport
airport_radius = 0.025 # takeoff / landing must not exceed this radius (degree!) around the airport
airport_delta = 100 # takeoff / landing must not exceed this altitude offset above/below the airport
# AircraftBeacon start id and end id
aircraft_beacon_start_id = get_aircraft_beacon_start_id(session)
aircraft_beacon_end_id = aircraft_beacon_start_id + 500000
# 'wo' is the window order for the sql window function
wo = and_(AircraftBeacon.device_id,
AircraftBeacon.timestamp,
AircraftBeacon.receiver_id)
# make a query with current, previous and next position
sq = session.query(
AircraftBeacon.id,
AircraftBeacon.timestamp,
func.lag(AircraftBeacon.timestamp).over(order_by=wo).label('timestamp_prev'),
func.lead(AircraftBeacon.timestamp).over(order_by=wo).label('timestamp_next'),
AircraftBeacon.location_wkt,
func.lag(AircraftBeacon.location_wkt).over(order_by=wo).label('location_wkt_prev'),
func.lead(AircraftBeacon.location_wkt).over(order_by=wo).label('location_wkt_next'),
AircraftBeacon.track,
func.lag(AircraftBeacon.track).over(order_by=wo).label('track_prev'),
func.lead(AircraftBeacon.track).over(order_by=wo).label('track_next'),
AircraftBeacon.ground_speed,
func.lag(AircraftBeacon.ground_speed).over(order_by=wo).label('ground_speed_prev'),
func.lead(AircraftBeacon.ground_speed).over(order_by=wo).label('ground_speed_next'),
AircraftBeacon.altitude,
func.lag(AircraftBeacon.altitude).over(order_by=wo).label('altitude_prev'),
func.lead(AircraftBeacon.altitude).over(order_by=wo).label('altitude_next'),
AircraftBeacon.device_id,
func.lag(AircraftBeacon.device_id).over(order_by=wo).label('device_id_prev'),
func.lead(AircraftBeacon.device_id).over(order_by=wo).label('device_id_next')) \
.filter(between(AircraftBeacon.id, aircraft_beacon_start_id, aircraft_beacon_end_id)) \
.subquery()
# find possible takeoffs and landings
sq2 = session.query(
sq.c.id,
sq.c.timestamp,
case([(sq.c.ground_speed > takeoff_speed, sq.c.location_wkt_prev), # on takeoff we take the location from the previous fix because it is nearer to the airport
(sq.c.ground_speed < landing_speed, sq.c.location)]).label('location'),
case([(sq.c.ground_speed > takeoff_speed, sq.c.track),
(sq.c.ground_speed < landing_speed, sq.c.track_prev)]).label('track'), # on landing we take the track from the previous fix because gliders tend to leave the runway quickly
sq.c.ground_speed,
sq.c.altitude,
case([(sq.c.ground_speed > takeoff_speed, True),
(sq.c.ground_speed < landing_speed, False)]).label('is_takeoff'),
sq.c.device_id) \
.filter(sq.c.device_id_prev == sq.c.device_id == sq.c.device_id_next) \
.filter(or_(and_(sq.c.ground_speed_prev < takeoff_speed, # takeoff
sq.c.ground_speed > takeoff_speed,
sq.c.ground_speed_next > takeoff_speed),
and_(sq.c.ground_speed_prev > landing_speed, # landing
sq.c.ground_speed < landing_speed,
sq.c.ground_speed_next < landing_speed))) \
.filter(sq.c.timestamp_next - sq.c.timestamp_prev < timedelta(seconds=duration)) \
.filter(and_(func.ST_DFullyWithin(sq.c.location, sq.c.location_wkt_prev, radius),
func.ST_DFullyWithin(sq.c.location, sq.c.location_wkt_next, radius))) \
.subquery()
# consider them if they are near a airport
sq3 = session.query(
sq2.c.timestamp,
sq2.c.track,
sq2.c.is_takeoff,
sq2.c.device_id,
Airport.id.label('airport_id')) \
.filter(and_(func.ST_DFullyWithin(sq2.c.location, Airport.location_wkt, airport_radius),
between(sq2.c.altitude, Airport.altitude - airport_delta, Airport.altitude + airport_delta))) \
.filter(between(Airport.style, 2, 5)) \
.order_by(sq2.c.id) \
.subquery()
# consider them only if they are not already existing in db
takeoff_landing_query = session.query(sq3) \
.filter(~exists().where(
and_(TakeoffLanding.timestamp == sq3.c.timestamp,
TakeoffLanding.device_id == sq3.c.device_id,
TakeoffLanding.airport_id == sq3.c.airport_id)))
# ... and save them
ins = insert(TakeoffLanding).from_select((TakeoffLanding.timestamp,
TakeoffLanding.track,
TakeoffLanding.is_takeoff,
TakeoffLanding.device_id,
TakeoffLanding.airport_id),
takeoff_landing_query)
result = session.execute(ins)
counter = result.rowcount
session.commit()
logger.debug("New takeoffs and landings: {}".format(counter))
return counter
def compute_takeoff_and_landing():
logger.info("Compute takeoffs and landings.")
takeoff_speed = 30
landing_speed = 30
# calculate the time where the computation starts
last_takeoff_landing_query = app.session.query(func.max(TakeoffLanding.timestamp))
last_takeoff_landing = last_takeoff_landing_query.one()[0]
if last_takeoff_landing is None:
# if the table is empty
last_takeoff_landing = datetime(2015, 1, 1, 0, 0, 0)
else:
# we get the beacons async. to be safe we delete takeoffs/landings from last 5 minutes and recalculate from then
# alternative: takeoff/landing has a primary key (timestamp,address)
last_takeoff_landing = last_takeoff_landing - timedelta(minutes=5)
app.session.query(TakeoffLanding) \
.filter(TakeoffLanding.timestamp > last_takeoff_landing) \
.delete()
# make a query with current, previous and next position, so we can detect takeoffs and landings
sq = app.session.query(
AircraftBeacon.address,
func.lag(AircraftBeacon.address).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('address_prev'),
func.lead(AircraftBeacon.address).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('address_next'),
AircraftBeacon.timestamp,
func.lag(AircraftBeacon.timestamp).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('timestamp_prev'),
func.lead(AircraftBeacon.timestamp).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('timestamp_next'),
AircraftBeacon.latitude,
func.lag(AircraftBeacon.latitude).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('latitude_prev'),
func.lead(AircraftBeacon.latitude).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('latitude_next'),
AircraftBeacon.longitude,
func.lag(AircraftBeacon.longitude).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('longitude_prev'),
func.lead(AircraftBeacon.longitude).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('longitude_next'),
AircraftBeacon.ground_speed,
AircraftBeacon.track,
func.lag(AircraftBeacon.track).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('track_prev'),
func.lead(AircraftBeacon.track).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('track_next'),
AircraftBeacon.ground_speed,
func.lag(AircraftBeacon.ground_speed).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('ground_speed_prev'),
func.lead(AircraftBeacon.ground_speed).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('ground_speed_next'),
AircraftBeacon.altitude,
func.lag(AircraftBeacon.altitude).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('altitude_prev'),
func.lead(AircraftBeacon.altitude).over(order_by=and_(AircraftBeacon.address, AircraftBeacon.timestamp)).label('altitude_next')) \
.filter(AircraftBeacon.timestamp > last_takeoff_landing) \
.order_by(func.date(AircraftBeacon.timestamp), AircraftBeacon.address, AircraftBeacon.timestamp) \
.subquery()
# find takeoffs and landings (look at the trigger_speed)
takeoff_landing_query = app.session.query(
sq.c.address,
sq.c.timestamp,
sq.c.latitude,
sq.c.longitude,
sq.c.track,
sq.c.ground_speed,
sq.c.altitude,
case([(sq.c.ground_speed > takeoff_speed, True),
(sq.c.ground_speed < landing_speed, False)]).label('is_takeoff')) \
.filter(sq.c.address_prev == sq.c.address == sq.c.address_next) \
.filter(or_(and_(sq.c.ground_speed_prev < takeoff_speed, # takeoff
sq.c.ground_speed > takeoff_speed,
sq.c.ground_speed_next > takeoff_speed),
and_(sq.c.ground_speed_prev > landing_speed, # landing
sq.c.ground_speed < landing_speed,
sq.c.ground_speed_next < landing_speed))) \
.order_by(func.date(sq.c.timestamp), sq.c.timestamp)
# ... and save them
ins = insert(TakeoffLanding).from_select((TakeoffLanding.address, TakeoffLanding.timestamp, TakeoffLanding.latitude, TakeoffLanding.longitude, TakeoffLanding.track, TakeoffLanding.ground_speed, TakeoffLanding.altitude, TakeoffLanding.is_takeoff), takeoff_landing_query)
result = app.session.execute(ins)
counter = result.rowcount
app.session.commit()
logger.debug("New/recalculated takeoffs and landings: {}".format(counter))
return counter
def show(airport_name, latitude, longitude, altitude):
"""Show a logbook for <airport_name> located at given position."""
latitude = float(latitude)
longitude = float(longitude)
altitude = float(altitude)
# get_logbook('Königsdorf', 47.83, 11.46, 601)
latmin = latitude - 0.15
latmax = latitude + 0.15
lonmin = longitude - 0.15
lonmax = longitude + 0.15
max_altitude = altitude + 200
# make a query with current, previous and next "takeoff_landing" event, so we can find complete flights
sq = session.query(
TakeoffLanding.address,
func.lag(TakeoffLanding.address)
.over(
order_by=and_(func.date(TakeoffLanding.timestamp),
TakeoffLanding.address,
TakeoffLanding.timestamp))
.label('address_prev'),
func.lead(TakeoffLanding.address)
.over(order_by=and_(func.date(TakeoffLanding.timestamp),
TakeoffLanding.address,
TakeoffLanding.timestamp))
.label('address_next'),
TakeoffLanding.timestamp,
func.lag(TakeoffLanding.timestamp)
.over(order_by=and_(func.date(TakeoffLanding.timestamp),
TakeoffLanding.address,
TakeoffLanding.timestamp))
.label('timestamp_prev'),
func.lead(TakeoffLanding.timestamp)
.over(order_by=and_(func.date(TakeoffLanding.timestamp),
TakeoffLanding.address,
TakeoffLanding.timestamp))
.label('timestamp_next'),
TakeoffLanding.track,
func.lag(TakeoffLanding.track)
.over(order_by=and_(func.date(TakeoffLanding.timestamp),
TakeoffLanding.address,
TakeoffLanding.timestamp))
.label('track_prev'),
func.lead(TakeoffLanding.track)
.over(order_by=and_(func.date(TakeoffLanding.timestamp),
TakeoffLanding.address,
TakeoffLanding.timestamp))
.label('track_next'),
TakeoffLanding.is_takeoff,
func.lag(TakeoffLanding.is_takeoff)
.over(order_by=and_(func.date(TakeoffLanding.timestamp),
TakeoffLanding.address,
TakeoffLanding.timestamp))
.label('is_takeoff_prev'),
func.lead(TakeoffLanding.is_takeoff)
.over(order_by=and_(func.date(TakeoffLanding.timestamp),
TakeoffLanding.address,
TakeoffLanding.timestamp))
.label('is_takeoff_next')) \
.filter(and_(between(TakeoffLanding.latitude, latmin, latmax),
between(TakeoffLanding.longitude, lonmin, lonmax))) \
.filter(TakeoffLanding.altitude < max_altitude) \
.subquery()
# find complete flights (with takeoff and landing) with duration < 1 day
complete_flight_query = session.query(sq.c.timestamp.label('reftime'), sq.c.address.label('address'), sq.c.timestamp.label('takeoff'), sq.c.track.label('takeoff_track'), sq.c.timestamp_next.label('landing'), sq.c.track_next.label('landing_track'), label('duration', sq.c.timestamp_next - sq.c.timestamp)) \
.filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false())) \
.filter(sq.c.address == sq.c.address_next) \
.filter(sq.c.timestamp_next - sq.c.timestamp < timedelta(days=1))
# split complete flights (with takeoff and landing) with duration > 1 day into one takeoff and one landing
split_start_query = session.query(sq.c.timestamp.label('reftime'), sq.c.address.label('address'), sq.c.timestamp.label('takeoff'), sq.c.track.label('takeoff_track'), null().label('landing'), null().label('landing_track'), null().label('duration')) \
.filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false())) \
.filter(sq.c.address == sq.c.address_next) \
.filter(sq.c.timestamp_next - sq.c.timestamp >= timedelta(days=1))
split_landing_query = session.query(sq.c.timestamp_next.label('reftime'), sq.c.address.label('address'), null().label('takeoff'), null().label('takeoff_track'), sq.c.timestamp_next.label('landing'), sq.c.track_next.label('landing_track'), null().label('duration')) \
.filter(and_(sq.c.is_takeoff == true(), sq.c.is_takeoff_next == false())) \
.filter(sq.c.address == sq.c.address_next) \
.filter(sq.c.timestamp_next - sq.c.timestamp >= timedelta(days=1))
# find landings without start
only_landings_query = session.query(sq.c.timestamp.label('reftime'), sq.c.address.label('address'), null().label('takeoff'), null().label('takeoff_track'), sq.c.timestamp.label('landing'), sq.c.track_next.label('landing_track'), null().label('duration')) \
.filter(sq.c.is_takeoff == false()) \
.filter(or_(sq.c.address != sq.c.address_prev,
sq.c.is_takeoff_prev == false()))
# find starts without landing
only_starts_query = session.query(sq.c.timestamp.label('reftime'), sq.c.address.label('address'), sq.c.timestamp.label('takeoff'), sq.c.track.label('takeoff_track'), null().label('landing'), null().label('landing_track'), null().label('duration')) \
.filter(sq.c.is_takeoff == true()) \
.filter(or_(sq.c.address != sq.c.address_next,
sq.c.is_takeoff_next == true()))
# unite all
union_query = complete_flight_query.union(
split_start_query,
split_landing_query,
only_landings_query,
only_starts_query) \
.subquery()
# get aircraft informations and sort all entries by the reference time
logbook_query = session.query(
union_query.c.reftime,
union_query.c.address,
union_query.c.takeoff,
union_query.c.takeoff_track,
union_query.c.landing,
union_query.c.landing_track,
union_query.c.duration,
Device.registration,
Device.aircraft) \
.outerjoin(Device, union_query.c.address == Device.address) \
.order_by(union_query.c.reftime)
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(registration_object, address):
return '[' + address + ']' if registration_object is None else registration_object
def none_aircraft_replacer(aircraft_object):
return '(unknown)' if aircraft_object is None else aircraft_object
for [reftime, address, takeoff, takeoff_track, landing, landing_track, duration, registration, aircraft] in logbook_query.all():
print('%10s %8s (%2s) %8s (%2s) %8s %8s %s' % (
reftime.date(),
none_datetime_replacer(takeoff),
none_track_replacer(takeoff_track),
none_datetime_replacer(landing),
none_track_replacer(landing_track),
none_timedelta_replacer(duration),
none_registration_replacer(registration, address),
none_aircraft_replacer(aircraft)))
def update_entries(session, start, end, logger=None):
"""Compute takeoffs and landings."""
if logger is None:
logger = app.logger
logger.info("Compute takeoffs and landings.")
# considered time interval should not exceed a complete day
if end - start > timedelta(days=1):
abort_message = "TakeoffLanding: timeinterval start='{}' and end='{}' is too big.".format(
start, end)
logger.warn(abort_message)
return abort_message
# check if we have any airport
airports_query = session.query(Airport).limit(1)
if not airports_query.all():
abort_message = "TakeoffLanding: Cannot calculate takeoff and landings without any airport! Please import airports first."
logger.warn(abort_message)
return abort_message
# takeoff / landing detection is based on 3 consecutive points all below a certain altitude AGL
takeoff_speed = 55 # takeoff detection: 1st point below, 2nd and 3rd above this limit
landing_speed = 40 # landing detection: 1st point above, 2nd and 3rd below this limit
min_takeoff_climb_rate = -5 # takeoff detection: glider should not sink too much
max_landing_climb_rate = 5 # landing detection: glider should not climb too much
duration = 100 # the points must not exceed this duration
radius = 5000 # the points must not exceed this radius around the 2nd point
max_agl = 200 # takeoff / landing must not exceed this altitude AGL
# get beacons for selected time range, one per device_id and timestamp
sq = (session.query(AircraftBeacon).distinct(
AircraftBeacon.device_id, AircraftBeacon.timestamp).order_by(
AircraftBeacon.device_id, AircraftBeacon.timestamp,
AircraftBeacon.error_count).filter(
AircraftBeacon.agl < max_agl).filter(
between(AircraftBeacon.timestamp, start, end)).subquery())
# make a query with current, previous and next position
sq2 = session.query(
sq.c.device_id,
func.lag(sq.c.device_id).over(
partition_by=sq.c.device_id,
order_by=sq.c.timestamp).label("device_id_prev"),
func.lead(sq.c.device_id).over(
partition_by=sq.c.device_id,
order_by=sq.c.timestamp).label("device_id_next"),
sq.c.timestamp,
func.lag(sq.c.timestamp).over(
partition_by=sq.c.device_id,
order_by=sq.c.timestamp).label("timestamp_prev"),
func.lead(sq.c.timestamp).over(
partition_by=sq.c.device_id,
order_by=sq.c.timestamp).label("timestamp_next"),
sq.c.location,
func.lag(sq.c.location).over(
partition_by=sq.c.device_id,
order_by=sq.c.timestamp).label("location_wkt_prev"),
func.lead(sq.c.location).over(
partition_by=sq.c.device_id,
order_by=sq.c.timestamp).label("location_wkt_next"),
sq.c.track,
func.lag(sq.c.track).over(partition_by=sq.c.device_id,
order_by=sq.c.timestamp).label("track_prev"),
func.lead(
sq.c.track).over(partition_by=sq.c.device_id,
order_by=sq.c.timestamp).label("track_next"),
sq.c.ground_speed,
func.lag(sq.c.ground_speed).over(
partition_by=sq.c.device_id,
order_by=sq.c.timestamp).label("ground_speed_prev"),
func.lead(sq.c.ground_speed).over(
partition_by=sq.c.device_id,
order_by=sq.c.timestamp).label("ground_speed_next"),
sq.c.altitude,
func.lag(sq.c.altitude).over(
partition_by=sq.c.device_id,
order_by=sq.c.timestamp).label("altitude_prev"),
func.lead(sq.c.altitude).over(
partition_by=sq.c.device_id,
order_by=sq.c.timestamp).label("altitude_next"),
sq.c.climb_rate,
func.lag(sq.c.climb_rate).over(
partition_by=sq.c.device_id,
order_by=sq.c.timestamp).label("climb_rate_prev"),
func.lead(sq.c.climb_rate).over(
partition_by=sq.c.device_id,
order_by=sq.c.timestamp).label("climb_rate_next"),
).subquery()
# consider only positions with predecessor and successor and limit distance and duration between points
sq3 = (session.query(sq2).filter(
and_(sq2.c.device_id_prev != null(),
sq2.c.device_id_next != null())).filter(
and_(
func.ST_DistanceSphere(sq2.c.location,
sq2.c.location_wkt_prev) < radius,
func.ST_DistanceSphere(sq2.c.location,
sq2.c.location_wkt_next) <
radius)).filter(sq2.c.timestamp_next -
sq2.c.timestamp_prev < timedelta(
seconds=duration)).subquery())
# find possible takeoffs and landings
sq4 = (
session.query(
sq3.c.timestamp,
case([
(
sq3.c.ground_speed > takeoff_speed, sq3.c.location_wkt_prev
), # on takeoff we take the location from the previous fix because it is nearer to the airport
(sq3.c.ground_speed <= takeoff_speed, sq3.c.location),
]).label("location"),
case([
(sq3.c.ground_speed > landing_speed, sq3.c.track),
(sq3.c.ground_speed <= landing_speed, sq3.c.track_prev)
]).label(
"track"
), # on landing we take the track from the previous fix because gliders tend to leave the runway quickly
sq3.c.ground_speed,
sq3.c.altitude,
case([(sq3.c.ground_speed > takeoff_speed, True),
(sq3.c.ground_speed < landing_speed, False)
]).label("is_takeoff"),
sq3.c.device_id,
).filter(
or_(
and_(sq3.c.ground_speed_prev < takeoff_speed,
sq3.c.ground_speed > takeoff_speed,
sq3.c.ground_speed_next > takeoff_speed,
sq3.c.climb_rate > min_takeoff_climb_rate), # takeoff
and_(sq3.c.ground_speed_prev > landing_speed,
sq3.c.ground_speed < landing_speed,
sq3.c.ground_speed_next < landing_speed,
sq3.c.climb_rate < max_landing_climb_rate), # landing
)).subquery())
# consider them if the are near airports ...
sq5 = (session.query(
sq4.c.timestamp, sq4.c.track, sq4.c.is_takeoff, sq4.c.device_id,
Airport.id.label("airport_id"),
func.ST_DistanceSphere(
sq4.c.location,
Airport.location_wkt).label("airport_distance")).filter(
and_(func.ST_Within(sq4.c.location, Airport.border),
between(Airport.style, 2, 5))).subquery())
# ... and take the nearest airport
sq6 = (session.query(sq5.c.timestamp, sq5.c.track, sq5.c.is_takeoff,
sq5.c.device_id, sq5.c.airport_id).distinct(
sq5.c.timestamp, sq5.c.track,
sq5.c.is_takeoff, sq5.c.device_id).order_by(
sq5.c.timestamp, sq5.c.track,
sq5.c.is_takeoff, sq5.c.device_id,
sq5.c.airport_distance).subquery())
# consider them only if they are not already existing in db
takeoff_landing_query = session.query(sq6).filter(~exists().where(
and_(TakeoffLanding.timestamp == sq6.c.timestamp,
TakeoffLanding.device_id == sq6.c.device_id,
TakeoffLanding.airport_id == sq6.c.airport_id)))
# ... and save them
ins = insert(TakeoffLanding).from_select(
(TakeoffLanding.timestamp, TakeoffLanding.track,
TakeoffLanding.is_takeoff, TakeoffLanding.device_id,
TakeoffLanding.airport_id), takeoff_landing_query)
result = session.execute(ins)
session.commit()
insert_counter = result.rowcount
finish_message = "TakeoffLandings: {} inserted".format(insert_counter)
logger.info(finish_message)
return finish_message
def compute_takeoff_and_landing(session=None):
logger.info("Compute takeoffs and landings.")
if session is None:
session = app.session
# check if we have any airport
airports_query = session.query(Airport)
if not airports_query.all():
logger.warn("Cannot calculate takeoff and landings without any airport! Please import airports first.")
return
# takeoff / landing detection is based on 3 consecutive points
takeoff_speed = 55 # takeoff detection: 1st point below, 2nd and 3rd above this limit
landing_speed = 40 # landing detection: 1st point above, 2nd and 3rd below this limit
duration = 100 # the points must not exceed this duration
radius = 0.05 # the points must not exceed this radius (degree!) around the 2nd point
# takeoff / landing has to be near an airport
airport_radius = 0.025 # takeoff / landing must not exceed this radius (degree!) around the airport
airport_delta = 100 # takeoff / landing must not exceed this altitude offset above/below the airport
# 'wo' is the window order for the sql window function
wo = and_(AircraftBeacon.device_id, AircraftBeacon.timestamp, AircraftBeacon.receiver_id)
# make a query with current, previous and next position
sq = (
session.query(
AircraftBeacon.id,
AircraftBeacon.timestamp,
func.lag(AircraftBeacon.timestamp).over(order_by=wo).label("timestamp_prev"),
func.lead(AircraftBeacon.timestamp).over(order_by=wo).label("timestamp_next"),
AircraftBeacon.location_wkt,
func.lag(AircraftBeacon.location_wkt).over(order_by=wo).label("location_wkt_prev"),
func.lead(AircraftBeacon.location_wkt).over(order_by=wo).label("location_wkt_next"),
AircraftBeacon.track,
func.lag(AircraftBeacon.track).over(order_by=wo).label("track_prev"),
func.lead(AircraftBeacon.track).over(order_by=wo).label("track_next"),
AircraftBeacon.ground_speed,
func.lag(AircraftBeacon.ground_speed).over(order_by=wo).label("ground_speed_prev"),
func.lead(AircraftBeacon.ground_speed).over(order_by=wo).label("ground_speed_next"),
AircraftBeacon.altitude,
func.lag(AircraftBeacon.altitude).over(order_by=wo).label("altitude_prev"),
func.lead(AircraftBeacon.altitude).over(order_by=wo).label("altitude_next"),
AircraftBeacon.device_id,
func.lag(AircraftBeacon.device_id).over(order_by=wo).label("device_id_prev"),
func.lead(AircraftBeacon.device_id).over(order_by=wo).label("device_id_next"),
)
.filter(AircraftBeacon.status == null())
.subquery()
)
sq2 = session.query(sq).filter(sq.c.device_id_prev == sq.c.device_id == sq.c.device_id_next).subquery()
# find possible takeoffs and landings
sq3 = (
session.query(
sq2.c.id,
sq2.c.timestamp,
case(
[
(
sq2.c.ground_speed > takeoff_speed,
sq2.c.location_wkt_prev,
), # on takeoff we take the location from the previous fix because it is nearer to the airport
(sq2.c.ground_speed < landing_speed, sq2.c.location),
]
).label("location"),
case(
[
(sq2.c.ground_speed > takeoff_speed, sq2.c.track),
(sq2.c.ground_speed < landing_speed, sq2.c.track_prev),
]
).label(
"track"
), # on landing we take the track from the previous fix because gliders tend to leave the runway quickly
sq2.c.ground_speed,
sq2.c.altitude,
case([(sq2.c.ground_speed > takeoff_speed, True), (sq2.c.ground_speed < landing_speed, False)]).label(
"is_takeoff"
),
sq2.c.device_id,
)
.filter(sq2.c.timestamp_next - sq2.c.timestamp_prev < timedelta(seconds=duration))
.filter(
and_(
func.ST_DFullyWithin(sq2.c.location, sq2.c.location_wkt_prev, radius),
func.ST_DFullyWithin(sq2.c.location, sq2.c.location_wkt_next, radius),
)
)
.filter(
or_(
and_(
sq2.c.ground_speed_prev < takeoff_speed, # takeoff
sq2.c.ground_speed > takeoff_speed,
sq2.c.ground_speed_next > takeoff_speed,
),
and_(
sq2.c.ground_speed_prev > landing_speed, # landing
sq2.c.ground_speed < landing_speed,
sq2.c.ground_speed_next < landing_speed,
),
)
)
.subquery()
)
# consider them if they are near a airport
sq4 = (
session.query(sq3.c.timestamp, sq3.c.track, sq3.c.is_takeoff, sq3.c.device_id, Airport.id.label("airport_id"))
.filter(
and_(
func.ST_DFullyWithin(sq3.c.location, Airport.location_wkt, airport_radius),
between(sq3.c.altitude, Airport.altitude - airport_delta, Airport.altitude + airport_delta),
)
)
.filter(between(Airport.style, 2, 5))
.subquery()
)
# consider them only if they are not already existing in db
takeoff_landing_query = session.query(sq4).filter(
~exists().where(
and_(
TakeoffLanding.timestamp == sq4.c.timestamp,
TakeoffLanding.device_id == sq4.c.device_id,
TakeoffLanding.airport_id == sq4.c.airport_id,
)
)
)
# ... and save them
ins = insert(TakeoffLanding).from_select(
(
TakeoffLanding.timestamp,
TakeoffLanding.track,
TakeoffLanding.is_takeoff,
TakeoffLanding.device_id,
TakeoffLanding.airport_id,
),
takeoff_landing_query,
)
result = session.execute(ins)
counter = result.rowcount
session.commit()
logger.debug("New takeoffs and landings: {}".format(counter))
return counter
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