Exemplo n.º 1
0
    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'])
Exemplo n.º 2
0
 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)
Exemplo n.º 3
0
    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
Exemplo n.º 4
0
    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'])
Exemplo n.º 5
0
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)
Exemplo n.º 6
0
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)
Exemplo n.º 7
0
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)
Exemplo n.º 8
0
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
Exemplo n.º 9
0
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
Exemplo n.º 10
0
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)))
Exemplo n.º 11
0
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
Exemplo n.º 12
0
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
Exemplo n.º 13
0
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