def __call__(self, var, minval=0, label=None): label = label or var return func.greatest( func.sum(column(var)) - func.lag(func.sum(column(var))) .over(order_by=self.order_by), minval ).label(label)
def fetch_factor_range_forward(self, universe: Universe, factors: Union[Transformer, object], start_date: str = None, end_date: str = None, dates: Iterable[str] = None): if isinstance(factors, Transformer): transformer = factors else: transformer = Transformer(factors) dependency = transformer.dependency factor_cols = _map_factors(dependency, factor_tables) codes = universe.query(self, start_date, end_date, dates) total_codes = codes.code.unique().tolist() total_dates = codes.trade_date.astype(str).unique().tolist() big_table = Market joined_tables = set() joined_tables.add(Market.__table__.name) for t in set(factor_cols.values()): if t.__table__.name not in joined_tables: if dates is not None: big_table = outerjoin( big_table, t, and_(Market.trade_date == t.trade_date, Market.code == t.code, Market.trade_date.in_(dates))) else: big_table = outerjoin( big_table, t, and_(Market.trade_date == t.trade_date, Market.code == t.code, Market.trade_date.between(start_date, end_date))) joined_tables.add(t.__table__.name) stats = func.lag(list(factor_cols.keys())[0], -1).over(partition_by=Market.code, order_by=Market.trade_date).label('dx') query = select([Market.trade_date, Market.code, Market.chgPct, stats]).select_from(big_table).where( and_(Market.trade_date.in_(total_dates), Market.code.in_(total_codes))) df = pd.read_sql(query, self.engine) \ .replace([-np.inf, np.inf], np.nan) \ .sort_values(['trade_date', 'code']) return pd.merge(df, codes[['trade_date', 'code']], how='inner').drop_duplicates(['trade_date', 'code'])
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 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_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