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 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 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 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))
