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 update_receivers():
"""Update the receiver table."""
# get the timestamp of last update
last_update_query = app.session.query(
coalesce(func.max(Receiver.lastseen), "2015-01-01 00:00:00").label("last_entry")
)
last_update = last_update_query.one().last_entry
# get last receiver beacons since last update
last_receiver_beacon_sq = (
app.session.query(ReceiverBeacon.name, func.max(ReceiverBeacon.timestamp).label("lastseen"))
.filter(ReceiverBeacon.timestamp >= last_update)
.group_by(ReceiverBeacon.name)
.subquery()
)
receivers_to_update = (
app.session.query(
ReceiverBeacon.name,
ReceiverBeacon.latitude,
ReceiverBeacon.longitude,
ReceiverBeacon.altitude,
last_receiver_beacon_sq.columns.lastseen,
ReceiverBeacon.version,
ReceiverBeacon.platform,
)
.filter(
and_(
ReceiverBeacon.name == last_receiver_beacon_sq.columns.name,
ReceiverBeacon.timestamp == last_receiver_beacon_sq.columns.lastseen,
)
)
.subquery()
)
# set country code to None if lat or lon changed
count = (
app.session.query(Receiver)
.filter(
and_(
Receiver.name == receivers_to_update.columns.name,
or_(
Receiver.latitude != receivers_to_update.columns.latitude,
Receiver.longitude != receivers_to_update.columns.longitude,
),
)
)
.update(
{
"latitude": receivers_to_update.columns.latitude,
"longitude": receivers_to_update.columns.longitude,
"country_code": null(),
}
)
)
logger.info("Count of receivers who changed lat or lon: {}".format(count))
# update lastseen of known receivers
count = (
app.session.query(Receiver)
.filter(Receiver.name == receivers_to_update.columns.name)
.update(
{
"altitude": receivers_to_update.columns.altitude,
"lastseen": receivers_to_update.columns.lastseen,
"version": receivers_to_update.columns.version,
"platform": receivers_to_update.columns.platform,
}
)
)
logger.info("Count of receivers who where updated: {}".format(count))
# add new receivers
empty_sq = (
app.session.query(
ReceiverBeacon.name,
ReceiverBeacon.latitude,
ReceiverBeacon.longitude,
ReceiverBeacon.altitude,
last_receiver_beacon_sq.columns.lastseen,
ReceiverBeacon.version,
ReceiverBeacon.platform,
)
.filter(
and_(
ReceiverBeacon.name == last_receiver_beacon_sq.columns.name,
ReceiverBeacon.timestamp == last_receiver_beacon_sq.columns.lastseen,
)
)
.outerjoin(Receiver, Receiver.name == ReceiverBeacon.name)
.filter(Receiver.name == null())
.order_by(ReceiverBeacon.name)
)
for receiver_beacon in empty_sq.all():
receiver = Receiver()
receiver.name = receiver_beacon.name
receiver.latitude = receiver_beacon.latitude
receiver.longitude = receiver_beacon.longitude
receiver.altitude = receiver_beacon.altitude
receiver.firstseen = None
receiver.lastseen = receiver_beacon.lastseen
receiver.version = receiver_beacon.version
receiver.platform = receiver_beacon.platform
app.session.add(receiver)
logger.info("{} added".format(receiver.name))
# update firstseen if None
firstseen_null_query = (
app.session.query(Receiver.name, func.min(ReceiverBeacon.timestamp).label("firstseen"))
.filter(Receiver.firstseen == null())
.join(ReceiverBeacon, Receiver.name == ReceiverBeacon.name)
.group_by(Receiver.name)
.subquery()
)
count = (
app.session.query(Receiver)
.filter(Receiver.name == firstseen_null_query.columns.name)
.update({"firstseen": firstseen_null_query.columns.firstseen})
)
logger.info("Total: {} receivers added".format(count))
# update country code if None
unknown_country_query = app.session.query(Receiver).filter(Receiver.country_code == null()).order_by(Receiver.name)
for receiver in unknown_country_query.all():
receiver.country_code = get_country_code(receiver.latitude, receiver.longitude)
if receiver.country_code is not None:
logger.info("Updated country_code for {} to {}".format(receiver.name, receiver.country_code))
app.session.commit()
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))
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 update_receivers():
"""Update the receiver table."""
# get the timestamp of last update
last_update_query = app.session.query(coalesce(func.max(Receiver.lastseen), '2015-01-01 00:00:00').label('last_entry'))
last_update = last_update_query.one().last_entry
# get last receiver beacons since last update
last_receiver_beacon_sq = app.session.query(ReceiverBeacon.name,
func.max(ReceiverBeacon.timestamp).label('lastseen')) \
.filter(ReceiverBeacon.timestamp >= last_update) \
.group_by(ReceiverBeacon.name) \
.subquery()
# update receivers
receivers_to_update = app.session.query(ReceiverBeacon.name,
ReceiverBeacon.location_wkt,
ReceiverBeacon.altitude,
last_receiver_beacon_sq.columns.lastseen,
ReceiverBeacon.version,
ReceiverBeacon.platform) \
.filter(and_(ReceiverBeacon.name == last_receiver_beacon_sq.columns.name,
ReceiverBeacon.timestamp == last_receiver_beacon_sq.columns.lastseen)) \
.subquery()
# ... set country code to None if lat or lon changed
changed_count = app.session.query(Receiver) \
.filter(Receiver.name == receivers_to_update.columns.name) \
.filter(or_(not_(func.ST_Equals(Receiver.location_wkt, receivers_to_update.columns.location)),
and_(Receiver.location_wkt == null(),
receivers_to_update.columns.location != null()))) \
.update({"location_wkt": receivers_to_update.columns.location,
"country_code": null()},
synchronize_session=False)
# ... and update altitude, lastseen, version and platform
update_count = app.session.query(Receiver) \
.filter(Receiver.name == receivers_to_update.columns.name) \
.update({"altitude": receivers_to_update.columns.altitude,
"lastseen": receivers_to_update.columns.lastseen,
"version": receivers_to_update.columns.version,
"platform": receivers_to_update.columns.platform})
# add new receivers
empty_sq = app.session.query(ReceiverBeacon.name,
ReceiverBeacon.location_wkt,
ReceiverBeacon.altitude,
last_receiver_beacon_sq.columns.lastseen,
ReceiverBeacon.version, ReceiverBeacon.platform) \
.filter(and_(ReceiverBeacon.name == last_receiver_beacon_sq.columns.name,
ReceiverBeacon.timestamp == last_receiver_beacon_sq.columns.lastseen)) \
.outerjoin(Receiver, Receiver.name == ReceiverBeacon.name) \
.filter(Receiver.name == null()) \
.order_by(ReceiverBeacon.name)
for receiver_beacon in empty_sq.all():
receiver = Receiver()
receiver.name = receiver_beacon.name
receiver.location_wkt = receiver_beacon.location_wkt
receiver.altitude = receiver_beacon.altitude
receiver.firstseen = None
receiver.lastseen = receiver_beacon.lastseen
receiver.version = receiver_beacon.version
receiver.platform = receiver_beacon.platform
app.session.add(receiver)
logger.info("{} added".format(receiver.name))
# update firstseen if None
firstseen_null_query = app.session.query(Receiver.name,
func.min(ReceiverBeacon.timestamp).label('firstseen')) \
.filter(Receiver.firstseen == null()) \
.join(ReceiverBeacon, Receiver.name == ReceiverBeacon.name) \
.group_by(Receiver.name) \
.subquery()
added_count = app.session.query(Receiver) \
.filter(Receiver.name == firstseen_null_query.columns.name) \
.update({'firstseen': firstseen_null_query.columns.firstseen})
# update country code if None
unknown_country_query = app.session.query(Receiver) \
.filter(Receiver.country_code == null()) \
.filter(Receiver.location_wkt != null()) \
.order_by(Receiver.name)
for receiver in unknown_country_query.all():
location = receiver.location
country_code = get_country_code(location.latitude, location.longitude)
if country_code is not None:
receiver.country_code = country_code
logger.info("Updated country_code for {} to {}".format(receiver.name, receiver.country_code))
logger.info("Added: {}, location changed: {}".format(added_count, changed_count))
app.session.commit()
return update_count