def rec(): sq = session.query(ReceiverBeacon.name, func.max(ReceiverBeacon.timestamp).label('lastseen')) \ .group_by(ReceiverBeacon.name) \ .subquery() last_10_minutes = datetime.utcnow() - timedelta(minutes=10) receiver_query = session.query(ReceiverBeacon, case([(sq.c.lastseen > last_10_minutes, True)], else_=False).label('is_online')) \ .filter(and_(ReceiverBeacon.name == sq.c.name, ReceiverBeacon.timestamp == sq.c.lastseen)) \ .order_by(ReceiverBeacon.name) lines = list() lines.append('<?xml version="1.0" encoding="UTF-8"?>') lines.append('<markers>') lines.append('<m e="0"/>') for [receiver, is_online] in receiver_query.all(): lines.append('<m a="{0}" b="{1:.7f}" c="{2:.7f}" d="{3:1d}"/>' .format(receiver.name, receiver.location.latitude, receiver.location.longitude, is_online)) lines.append('</markers>') xml = '\n'.join(lines) return Response(xml, mimetype='text/xml')
def stats(): """Show some devices stats.""" sq_nt = session.query(Device.address) \ .filter(and_(Device.tracked == false(), Device.identified == true())) \ .subquery() sq_ni = session.query(Device.address) \ .filter(and_(Device.tracked == true(), Device.identified == false())) \ .subquery() sq_ntni = session.query(Device.address) \ .filter(and_(Device.tracked == false(), Device.identified == false())) \ .subquery() query = session.query(Device.address_origin, func.count(Device.id), func.count(sq_nt.c.address), func.count(sq_ni.c.address), func.count(sq_ntni.c.address)) \ .outerjoin(sq_nt, sq_nt.c.address == Device.address) \ .outerjoin(sq_ni, sq_ni.c.address == Device.address) \ .outerjoin(sq_ntni, sq_ntni.c.address == Device.address) \ .group_by(Device.address_origin) print('--- Devices ---') for [address_origin, device_count, nt_count, ni_count, ntni_count] in query.all(): print('{:12s} Total:{:5d} - not tracked:{:3d}, not identified:{:3d}, not tracked & not identified: {:3d}' .format(AddressOrigin(address_origin).name, device_count, nt_count, ni_count, ntni_count))
def receiver_plot(receiver_name): import matplotlib.pyplot as plt from io import BytesIO receiver_query = session.query(func.floor(AircraftBeacon.phi), func.max(AircraftBeacon.radius/1000)) \ .filter(AircraftBeacon.receiver_name == receiver_name) \ .filter(AircraftBeacon.radius is not None) \ .group_by(func.floor(AircraftBeacon.phi)) \ .order_by(func.floor(AircraftBeacon.phi)) rows = receiver_query.all() xs, ys = zip(*rows) xs = [x / 180 * 3.141592653 for x in xs] ax = plt.axes(polar=True) ax.set_theta_zero_location('N') ax.set_theta_direction('clockwise') plt.title('Maximum range') plt.plot(xs, ys, 'r') plt.ylim(0, 100) img = BytesIO() plt.savefig(img) plt.close() img.seek(0) return send_file(img, mimetype='image/png')
def hardware_stats(): """Show some statistics of receiver hardware.""" timestamp_24h_ago = datetime.utcnow() - timedelta(days=1) sq = session.query(ReceiverBeacon.name, func.max(ReceiverBeacon.timestamp).label('lastseen')).\ filter(ReceiverBeacon.timestamp > timestamp_24h_ago).\ group_by(ReceiverBeacon.name).\ subquery() platforms = session.query(distinct(ReceiverBeacon.platform), func.count(ReceiverBeacon.platform)).\ filter(and_(ReceiverBeacon.name == sq.c.name, ReceiverBeacon.timestamp == sq.c.lastseen)).\ group_by(ReceiverBeacon.platform).\ order_by(ReceiverBeacon.platform) print('--- Platforms ---') for [platform, count] in platforms.all(): print('%7s: %s' % (platform, count))
def software_stats(): """Show some statistics of receiver software.""" timestamp_24h_ago = datetime.utcnow() - timedelta(days=1) sq = session.query(ReceiverBeacon.name, func.max(ReceiverBeacon.timestamp).label('lastseen')).\ filter(ReceiverBeacon.timestamp > timestamp_24h_ago).\ group_by(ReceiverBeacon.name).\ subquery() versions = session.query(distinct(ReceiverBeacon.version), func.count(ReceiverBeacon.version)).\ filter(and_(ReceiverBeacon.name == sq.c.name, ReceiverBeacon.timestamp == sq.c.lastseen)).\ group_by(ReceiverBeacon.version).\ order_by(ReceiverBeacon.version) print('--- Versions ---') for [version, count] in versions.all(): print('%5s: %s' % (version, count))
def hardware_stats(): """Show stats about hardware version used by devices.""" hardware_query = session.query(Device.hardware_version, func.count(Device.id)) \ .group_by(Device.hardware_version) \ .order_by(func.count(Device.id).desc()) print("\n--- Hardware version ---") for [hardware_version, count] in hardware_query.all(): print("{}: {}".format(hardware_version, count))
def software_stats(): """Show stats about software version used by devices.""" software_query = session.query(Device.software_version, func.count(Device.id)) \ .group_by(Device.software_version) \ .order_by(func.count(Device.id).desc()) print("--- Software version ---") for [software_version, count] in software_query.all(): print("{}: {}".format(software_version, count))
def stealth_stats(): """Show stats about stealth flag set by devices.""" stealth_query = session.query(Device.stealth, func.count(Device.id)) \ .group_by(Device.stealth) \ .order_by(func.count(Device.id).desc()) print("--- Stealth ---") for [is_stealth, count] in stealth_query.all(): print("{}: {}".format(is_stealth, count))
def count_takeoff_and_landings(self): session = self.session query = session.query(TakeoffLanding) i = 0 for takeoff_landing in query.all(): i = i + 1 print("{} {} {} {} {} {}".format(takeoff_landing.id, takeoff_landing.device_id, takeoff_landing.airport_id, takeoff_landing.timestamp, takeoff_landing.is_takeoff, takeoff_landing.track)) return i
def device_plot2(address): import matplotlib.pyplot as plt import numpy as np from matplotlib.mlab import griddata from io import BytesIO sq = session.query(func.floor(case([(AircraftBeacon.theta-AircraftBeacon.track+180 > 0, AircraftBeacon.theta-AircraftBeacon.track+180), (AircraftBeacon.theta-AircraftBeacon.track+180 < 0, AircraftBeacon.theta-AircraftBeacon.track+180+360)])).label('direction'), AircraftBeacon.radius, AircraftBeacon.signal_strength) \ .filter(AircraftBeacon.receiver_name == 'Koenigsdf') \ .filter(AircraftBeacon.address == address) \ .filter(AircraftBeacon.radius is not None).subquery() device_query = session.query(func.floor(sq.c.direction/15)*15, func.floor(sq.c.radius/1000)*1000, func.avg(sq.c.signal_strength)) \ .group_by(func.floor(sq.c.direction/15)*15, func.floor(sq.c.radius/1000)*1000) rows = device_query.all() xs, ys, zs = zip(*rows) xs = [x / 180 * 3.141592653 for x in xs] xi = np.linspace(0, 2*np.pi, 360) yi = np.linspace(0, 10000, 5) zi = griddata(xs, ys, zs, xi, yi, interp='linear') ax = plt.axes(polar=True) ax.set_theta_zero_location('N') ax.set_theta_direction('clockwise') plt.title('Maximum range') c = plt.contourf(xi, yi, zi, 5) cb = plt.colorbar(c) cb.set_label("Signal strength [db]") img = BytesIO() plt.savefig(img) plt.close() img.seek(0) return send_file(img, mimetype='image/png')
def aircraft_type_stats(): """Show stats about aircraft types used by devices.""" aircraft_type_query = session.query(Device.aircraft_type, func.count(Device.id)) \ .group_by(Device.aircraft_type) \ .order_by(func.count(Device.id).desc()) print("--- Aircraft types ---") for [aircraft_type, count] in aircraft_type_query.all(): at = AircraftType(aircraft_type) print("{}: {}".format(at.name(), count))
def list_all(): """Show a list of all receivers.""" timestamp_24h_ago = datetime.utcnow() - timedelta(days=1) sq = session.query(distinct(ReceiverBeacon.name).label('name'), func.max(ReceiverBeacon.timestamp).label('lastseen'), func.count(ReceiverBeacon.name).label('messages_count')) \ .filter(ReceiverBeacon.timestamp > timestamp_24h_ago) \ .group_by(ReceiverBeacon.name).subquery() query = session.query(Receiver, sq.c.messages_count).\ filter(Receiver.name == sq.c.name).\ order_by(Receiver.name) print('--- Receivers ---') for [receiver, messages_count] in query.all(): print('%9s (%2s): %3d%% avail, %s, %s ' % (receiver.name, receiver.country_code, 100.0 * float(messages_count / receiver_beacons_per_day), receiver.version, receiver.platform))
def list_all(): """Show a list of all receivers.""" timestamp_24h_ago = datetime.utcnow() - timedelta(days=1) sq = session.query(distinct(ReceiverBeacon.name).label('name'), func.max(ReceiverBeacon.timestamp).label('lastseen'), func.count(ReceiverBeacon.name).label('messages_count') ).filter(ReceiverBeacon.timestamp > timestamp_24h_ago).group_by(ReceiverBeacon.name).subquery() query = session.query(Receiver, sq.c.messages_count).\ filter(Receiver.name == sq.c.name).\ order_by(Receiver.name) print('--- Receivers ---') for [receiver, messages_count] in query.all(): print('%9s (%2s): %3d%% avail, %s, %s ' % (receiver.name, receiver.country_code, 100.0 * float(messages_count / receiver_beacons_per_day), receiver.version, receiver.platform))
def device_plot(address): import matplotlib.pyplot as plt from io import BytesIO receiver_name = 'Koenigsdf' device_query = session.query(func.floor(AircraftBeacon.radius/1000), func.avg(AircraftBeacon.signal_strength)) \ .filter(AircraftBeacon.address == address) \ .filter(AircraftBeacon.receiver_name == receiver_name) \ .group_by(func.floor(AircraftBeacon.radius/1000)) \ .order_by(func.floor(AircraftBeacon.radius/1000)) rows = device_query.all() xs, ys = zip(*rows) plt.plot(xs, ys, 'b', marker='o', label=address) receiver_query = session.query(func.floor(AircraftBeacon.radius/1000), func.avg(AircraftBeacon.signal_strength)) \ .filter(AircraftBeacon.receiver_name == receiver_name) \ .group_by(func.floor(AircraftBeacon.radius/1000)) \ .order_by(func.floor(AircraftBeacon.radius/1000)) rows = receiver_query.all() xs, ys = zip(*rows) plt.plot(xs, ys, 'g--', label=receiver_name) plt.title('Signal strength over distance') plt.xlim(0, 40) plt.xlabel('Distance [km]') plt.ylabel('Signal strength [db]') plt.legend(loc='upper right') plt.grid(True, color='0.75') img = BytesIO() plt.savefig(img) img.seek(0) return send_file(img, mimetype='image/png')
def get_polyline(address): last_24_hours = datetime.utcnow() - timedelta(hours=10) points = session.query(func.ST_Y(AircraftBeacon.location_wkt), func.ST_X(AircraftBeacon.location_wkt)) \ .filter(AircraftBeacon.address == address) \ .filter(AircraftBeacon.timestamp > last_24_hours) \ .order_by(AircraftBeacon.timestamp).all() if len(points) == 0: return '' else: from polyline.codec import PolylineCodec return PolylineCodec().encode(points)
def get_devices_stats(session): sq_default = session.query(DeviceInfo.address) \ .filter(and_(DeviceInfo.tracked == true(), DeviceInfo.identified == true())) \ .subquery() sq_nt = session.query(DeviceInfo.address) \ .filter(and_(DeviceInfo.tracked == false(), DeviceInfo.identified == true())) \ .subquery() sq_ni = session.query(DeviceInfo.address) \ .filter(and_(DeviceInfo.tracked == true(), DeviceInfo.identified == false())) \ .subquery() sq_ntni = session.query(DeviceInfo.address) \ .filter(and_(DeviceInfo.tracked == false(), DeviceInfo.identified == false())) \ .subquery() query = session.query(DeviceInfo.address_origin, func.count(DeviceInfo.id), func.count(sq_default.c.address), func.count(sq_nt.c.address), func.count(sq_ni.c.address), func.count(sq_ntni.c.address)) \ .outerjoin(sq_default, sq_default.c.address == DeviceInfo.address) \ .outerjoin(sq_nt, sq_nt.c.address == DeviceInfo.address) \ .outerjoin(sq_ni, sq_ni.c.address == DeviceInfo.address) \ .outerjoin(sq_ntni, sq_ntni.c.address == DeviceInfo.address) \ .group_by(DeviceInfo.address_origin) stats = {} for [address_origin, device_count, default_count, nt_count, ni_count, ntni_count] in query.all(): origin = AddressOrigin(address_origin).name() stats[origin] = {'device_count': device_count, 'default_count': default_count, 'nt_count': nt_count, 'ni_count': ni_count, 'ntni_count': ntni_count} return stats
def add_beacon_to_db(beacon): if type(beacon == ReceiverBeacon): # connect beacon with receiver receiver = session.query(Receiver.id).filter(Receiver.name == beacon.name).first() if receiver is None: receiver = Receiver() receiver.name = beacon.name session.add(receiver) beacon.receiver_id = receiver.id elif type(beacon == AircraftBeacon): # connect beacon with device device = session.query(Device).filter(Device.address == beacon.address).first() if device is None: device = Device() device.address = beacon.address session.add(device) beacon.device_id = device.id # update device device.aircraft_type = beacon.aircraft_type device.stealth = beacon.stealth if beacon.hardware_version is not None: device.hardware_version = beacon.hardware_version if beacon.software_version is not None: device.software_version = beacon.software_version if beacon.real_address is not None: device.real_address = beacon.real_address # connect beacon with receiver receiver = session.query(Receiver.id).filter(Receiver.name == beacon.receiver_name).first() if receiver is None: receiver = Receiver() receiver.name = beacon.receiver_name session.add(receiver) beacon.receiver_id = receiver.id session.add(beacon) session.commit()
def get_devices_stats(session): sq_nt = ( session.query(Device.address).filter(and_(Device.tracked == false(), Device.identified == true())).subquery() ) sq_ni = ( session.query(Device.address).filter(and_(Device.tracked == true(), Device.identified == false())).subquery() ) sq_ntni = ( session.query(Device.address).filter(and_(Device.tracked == false(), Device.identified == false())).subquery() ) query = ( session.query( Device.address_origin, func.count(Device.id), func.count(sq_nt.c.address), func.count(sq_ni.c.address), func.count(sq_ntni.c.address), ) .outerjoin(sq_nt, sq_nt.c.address == Device.address) .outerjoin(sq_ni, sq_ni.c.address == Device.address) .outerjoin(sq_ntni, sq_ntni.c.address == Device.address) .group_by(Device.address_origin) ) stats = {} for [address_origin, device_count, nt_count, ni_count, ntni_count] in query.all(): origin = AddressOrigin(address_origin).name() stats[origin] = { "device_count": device_count, "nt_count": nt_count, "ni_count": ni_count, "ntni_count": ntni_count, } return stats
def list_all(country_code=None): """Show a list of all airports.""" or_args = [] if country_code is None: or_args = [between(Airport.style, 2, 5)] else: or_args = [and_(between(Airport.style, 2, 5), Airport.country_code == country_code)] query = session.query(Airport) \ .order_by(Airport.name) \ .filter(*or_args) print('--- Airports ---') for airport in query.all(): print(airport.name)
def list_all(country_code=None): """Show a list of all airports.""" or_args = [] if country_code is None: or_args = [between(Airport.style, 2, 5)] else: or_args = [ and_(between(Airport.style, 2, 5), Airport.country_code == country_code) ] query = session.query(Airport) \ .order_by(Airport.name) \ .filter(*or_args) print('--- Airports ---') for airport in query.all(): print(airport.name)
def dataxml(): code = request.args.get('i', '') address = request.args.get('f') lines = list() lines.append('<?xml version="1.0" encoding="UTF-8"?>') lines.append('<markers>') aircraft_query = session.query(Device) \ .filter(Device.address == address) device = aircraft_query.first() if device: lines.append('<m g="{0}" i="{1}" a="{2}" b="{3}" c="{4}" d="{5}" e="{6}"/>' .format(0, code, '', '', device.aircraft, device.competition, '')) else: lines.append('<m g="{0}" i="{1}"/>'.format(1, '')) lines.append('</markers>') xml = '\n'.join(lines) return Response(xml, mimetype='text/xml')
def update_relations(): """Update AircraftBeacon and ReceiverBeacon relations""" # Create missing Receiver from ReceiverBeacon available_receivers = session.query(Receiver.name) \ .subquery() missing_receiver_query = session.query(distinct(ReceiverBeacon.name)) \ .filter(ReceiverBeacon.receiver_id == null()) \ .filter(~ReceiverBeacon.name.in_(available_receivers)) ins = insert(Receiver).from_select([Receiver.name], missing_receiver_query) session.execute(ins) # Create missing Device from AircraftBeacon available_addresses = session.query(Device.address) \ .subquery() missing_addresses_query = session.query(distinct(AircraftBeacon.address)) \ .filter(AircraftBeacon.device_id == null()) \ .filter(~AircraftBeacon.address.in_(available_addresses)) ins2 = insert(Device).from_select([Device.address], missing_addresses_query) session.execute(ins2) # Update AircraftBeacons upd = session.query(AircraftBeacon) \ .filter(AircraftBeacon.device_id == null()) \ .filter(AircraftBeacon.receiver_id == null()) \ .filter(AircraftBeacon.address == Device.address) \ .filter(AircraftBeacon.receiver_name == Receiver.name) \ .update({AircraftBeacon.device_id: Device.id, AircraftBeacon.receiver_id: Receiver.id}, synchronize_session='fetch') upd2 = session.query(ReceiverBeacon) \ .filter(ReceiverBeacon.receiver_id == null()) \ .filter(ReceiverBeacon.receiver_name == Receiver.name) \ .update({Receiver.name: ReceiverBeacon.receiver_name}, synchronize_session='fetch') session.commit() print("Updated {} AircraftBeacons and {} ReceiverBeacons". format(upd, upd2))
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 show(airport_name, utc_delta_hours=0, date=None): """Show a logbook for <airport_name>.""" airport = session.query(Airport) \ .filter(Airport.name == airport_name) \ .first() if (airport is None): print('Airport "{}" not found.'.format(airport_name)) return or_args = [] if date is not None: date = datetime.strptime(date, "%Y-%m-%d") or_args = [and_(TakeoffLanding.timestamp >= date, TakeoffLanding.timestamp < date + timedelta(hours=24))] # get device info with highes priority sq2 = session.query(DeviceInfo.address, func.max(DeviceInfo.address_origin).label('address_origin')) \ .group_by(DeviceInfo.address) \ .subquery() sq3 = session.query(DeviceInfo.address, DeviceInfo.registration, DeviceInfo.aircraft) \ .filter(and_(DeviceInfo.address == sq2.c.address, DeviceInfo.address_origin == sq2.c.address_origin)) \ .subquery() # get all logbook entries and add device and airport infos takeoff_airport = aliased(Airport, name='takeoff_airport') landing_airport = aliased(Airport, name='landing_airport') logbook_query = session.query(Logbook, Device, sq3.c.registration, sq3.c.aircraft) \ .filter(or_(Logbook.takeoff_airport_id == airport.id, Logbook.landing_airport_id == airport.id)) \ .filter(*or_args) \ .outerjoin(takeoff_airport, Logbook.takeoff_airport_id == takeoff_airport.id) \ .outerjoin(landing_airport, Logbook.landing_airport_id == landing_airport.id) \ .outerjoin(Device, Logbook.device_id == Device.id) \ .outerjoin(sq3, sq3.c.address == Device.address) \ .order_by(Logbook.reftime) # ... and finally print out the logbook print('--- Logbook ({}) ---'.format(airport_name)) def none_datetime_replacer(datetime_object): return '--:--:--' if datetime_object is None else datetime_object.time() def none_track_replacer(track_object): return '--' if track_object is None else round(track_object / 10.0) def none_timedelta_replacer(timedelta_object): return '--:--:--' if timedelta_object is None else timedelta_object def none_registration_replacer(device_object, registration_object): return '[' + device_object.address + ']' if registration_object is None else registration_object def none_aircraft_replacer(device_object, aircraft_object): return '(unknown)' if aircraft_object is None else aircraft_object def airport_marker(takeoff_airport_object, landing_airport_object): if takeoff_airport_object is not None and takeoff_airport_object.name is not airport.name: return ('FROM: {}'.format(takeoff_airport_object.name)) elif landing_airport_object is not None and landing_airport_object.name is not airport.name: return ('TO: {}'.format(landing_airport_object.name)) else: return ('') def none_altitude_replacer(altitude_object, airport_object): return "?" if altitude_object is None else "{:5d}m ({:+5d}m)".format(altitude_object, altitude_object - airport_object.altitude) for [logbook, device, registration, aircraft] in logbook_query.all(): print('%10s %8s (%2s) %8s (%2s) %8s %15s %8s %17s %20s' % ( logbook.reftime.date(), none_datetime_replacer(logbook.takeoff_timestamp), none_track_replacer(logbook.takeoff_track), none_datetime_replacer(logbook.landing_timestamp), none_track_replacer(logbook.landing_track), none_timedelta_replacer(logbook.duration), none_altitude_replacer(logbook.max_altitude, logbook.takeoff_airport), none_registration_replacer(device, registration), none_aircraft_replacer(device, aircraft), airport_marker(logbook.takeoff_airport, logbook.landing_airport)))
def process_beacon(raw_message, reference_date=None, reference_time=None): if raw_message[0] == '#': return try: message = parse_aprs(raw_message, reference_date, reference_time) # symboltable / symbolcodes used by OGN: # I&: used as receiver # /X: helicopter_rotorcraft # /': glider_or_motorglider # \^: powered_aircraft # /g: para_glider # /O: ? # /^: ? # \n: ? # /z: ? # /o: ? if 'symboltable' not in message and 'symbolcode' not in message: # we have a receiver_beacon (status message) message.update(parse_ogn_receiver_beacon(message['comment'])) beacon = ReceiverBeacon(**message) # connect beacon with receiver receiver = session.query(Receiver.id) \ .filter(Receiver.name == beacon.name) \ .first() if receiver is None: receiver = Receiver() receiver.name = beacon.name session.add(receiver) beacon.receiver_id = receiver.id elif message['symboltable'] == "I" and message['symbolcode'] == '&': # ... we have a receiver_beacon message.update(parse_ogn_receiver_beacon(message['comment'])) message = replace_lonlat_with_wkt(message) beacon = ReceiverBeacon(**message) # connect beacon with receiver receiver = session.query(Receiver.id) \ .filter(Receiver.name == beacon.name) \ .first() if receiver is None: receiver = Receiver() receiver.name = beacon.name session.add(receiver) beacon.receiver_id = receiver.id else: # ... we have a aircraft_beacon message.update(parse_ogn_aircraft_beacon(message['comment'])) message = replace_lonlat_with_wkt(message) beacon = AircraftBeacon(**message) # connect beacon with device device = session.query(Device) \ .filter(Device.address == beacon.address) \ .first() if device is None: device = Device() device.address = beacon.address session.add(device) beacon.device_id = device.id # update device device.aircraft_type = beacon.aircraft_type device.stealth = beacon.stealth if beacon.hardware_version is not None: device.hardware_version = beacon.hardware_version if beacon.software_version is not None: device.software_version = beacon.software_version if beacon.real_address is not None: device.real_address = beacon.real_address # connect beacon with receiver receiver = session.query(Receiver.id) \ .filter(Receiver.name == beacon.receiver_name) \ .first() if receiver is None: receiver = Receiver() receiver.name = beacon.receiver_name session.add(receiver) beacon.receiver_id = receiver.id session.add(beacon) session.commit() logger.debug('Received message: {}'.format(raw_message)) except ParseError as e: logger.error('Received message: {}'.format(raw_message)) logger.error('Drop packet, {}'.format(e.message)) except TypeError as e: logger.error('TypeError: {}'.format(raw_message))
def lxml(): show_offline = request.args.get('a', 0, type=int) == 1 lat_max = request.args.get('b', 90, type=int) lat_min = request.args.get('c', -90, type=int) lon_max = request.args.get('d', 180, type=int) lon_min = request.args.get('e', -180, type=int) if show_offline: observation_start = date.today() else: observation_start = datetime.utcnow() - timedelta(minutes=5) sq = session.query(AircraftBeacon.address, func.max(AircraftBeacon.timestamp).label('lastseen')) \ .filter(and_(between(func.ST_Y(AircraftBeacon.location_wkt), lat_min, lat_max), between(func.ST_X(AircraftBeacon.location_wkt), lon_min, lon_max))) \ .filter(AircraftBeacon.timestamp > observation_start) \ .group_by(AircraftBeacon.address) \ .subquery() position_query = session.query(AircraftBeacon, Device) \ .outerjoin(Device, AircraftBeacon.address == Device.address) \ .filter(and_(AircraftBeacon.address == sq.c.address, AircraftBeacon.timestamp == sq.c.lastseen)) lines = list() lines.append('<?xml version="1.0" encoding="UTF-8"?>') lines.append('<markers>') for [aircraft_beacon, device] in position_query.all(): if device and (not device.tracked or not device.identified): continue code = encode(aircraft_beacon.address) if device is None: competition = ('_' + code[-2:]).lower() registration = code address = 0 else: if not device.competition: competition = device.registration[-2:] else: competition = device.competition if not device.registration: registration = '???' else: registration = device.registration address = device.address elapsed_time = datetime.utcnow() - aircraft_beacon.timestamp elapsed_seconds = int(elapsed_time.total_seconds()) lines.append('<m a="{0:.7f},{1:.7f},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13}"/>' .format(aircraft_beacon.location.latitude, aircraft_beacon.location.longitude, competition, registration, aircraft_beacon.altitude, utc_to_local(aircraft_beacon.timestamp).strftime("%H:%M:%S"), elapsed_seconds, int(aircraft_beacon.track), int(aircraft_beacon.ground_speed), int(aircraft_beacon.climb_rate*10)/10, aircraft_beacon.aircraft_type, aircraft_beacon.receiver_name, address, code)) lines.append('</markers>') xml = '\n'.join(lines) return Response(xml, mimetype='text/xml')
def process_beacon(raw_message): if raw_message[0] == '#': return try: message = parse_aprs(raw_message) # symboltable / symbolcodes used by OGN: # I&: used as receiver # /X: helicopter_rotorcraft # /': glider_or_motorglider # \^: powered_aircraft # /g: para_glider # /O: ? # /^: ? # \n: ? # /z: ? # /o: ? if message['symboltable'] == "I" and message['symbolcode'] == '&': # ... we have a receiver_beacon message.update(parse_ogn_receiver_beacon(message['comment'])) message = replace_lonlat_with_wkt(message) beacon = ReceiverBeacon(**message) # connect beacon with receiver receiver = session.query(Receiver.id) \ .filter(Receiver.name == beacon.name) \ .first() if receiver is None: receiver = Receiver() receiver.name = beacon.name session.add(receiver) beacon.receiver_id = receiver.id else: # ... we have a aircraft_beacon message.update(parse_ogn_aircraft_beacon(message['comment'])) message = replace_lonlat_with_wkt(message) beacon = AircraftBeacon(**message) # connect beacon with device device = session.query(Device) \ .filter(Device.address == beacon.address) \ .first() if device is None: device = Device() device.address = beacon.address session.add(device) beacon.device_id = device.id # update device device.aircraft_type = beacon.aircraft_type device.stealth = beacon.stealth if beacon.hardware_version is not None: device.hardware_version = beacon.hardware_version if beacon.software_version is not None: device.software_version = beacon.software_version if beacon.real_address is not None: device.real_address = beacon.real_address # connect beacon with receiver receiver = session.query(Receiver.id) \ .filter(Receiver.name == beacon.receiver_name) \ .first() if receiver is None: receiver = Receiver() receiver.name = beacon.receiver_name session.add(receiver) beacon.receiver_id = receiver.id session.add(beacon) session.commit() logger.debug('Received message: {}'.format(raw_message)) except ParseError as e: logger.error('Received message: {}'.format(raw_message)) logger.error('Drop packet, {}'.format(e.message)) except TypeError as e: logger.error('TypeError: {}'.format(raw_message))