def igc(address, date):
"""Export igc file for <address> at <date>."""
if not re.match("[0-9A-F]{6}", address):
print(f"Address '{address}' not valid.")
return
try:
sender = db.session.query(Sender).filter(Sender.address == address).one()
except NoResultFound as e:
print(f"No data for '{address}' in the DB")
return
if not re.match(r"\d{4}-\d{2}-\d{2}", date):
print(f"Date {date} not valid.")
return
with open("sample.igc", "wb") as fp:
writer = Writer(fp)
writer.write_headers(
{
"manufacturer_code": "OGN",
"logger_id": "OGN",
"date": datetime.date(1987, 2, 24),
"fix_accuracy": 50,
"pilot": "Unknown",
"copilot": "",
"glider_type": sender.infos[0].aircraft if len(sender.infos) > 0 else '',
"glider_id": sender.infos[0].registration if len(sender.infos) > 0 else '',
"firmware_version": sender.software_version,
"hardware_version": sender.hardware_version,
"logger_type": "OGN",
"gps_receiver": "unknown",
"pressure_sensor": "unknown",
"competition_id": sender.infos[0].competition if len(sender.infos) > 0 else '',
"competition_class": "unknown",
}
)
points = (
db.session.query(SenderPosition)
.filter(db.between(SenderPosition.reference_timestamp, f"{date} 00:00:00", f"{date} 23:59:59"))
.filter(SenderPosition.name == sender.name)
.order_by(SenderPosition.timestamp)
)
for point in points:
writer.write_fix(point.timestamp.time(), latitude=point.location.latitude, longitude=point.location.longitude, valid=True, pressure_alt=point.altitude, gps_alt=point.altitude)
def closest_location(lat,lon,tolerance=0.001,housenumber=None):
closest = []
distance = np.inf
if housenumber == True:
query = Location.query.filter(db.and_(db.between(Location.longitude,lon-tolerance,lon+tolerance),
db.between(Location.latitude,lat-tolerance,lat+tolerance),
Location.housenumber != None
))
elif housenumber == False:
query = Location.query.filter(db.and_(db.between(Location.longitude,lon-tolerance,lon+tolerance),
db.between(Location.latitude,lat-tolerance,lat+tolerance),
Location.housenumber == None
))
else:
query = Location.query.filter(db.and_(db.between(Location.longitude,lon-tolerance,lon+tolerance),
db.between(Location.latitude,lat-tolerance,lat+tolerance)
))
for loc in query.all():
dist = geopy.distance.distance((loc.latitude, loc.longitude),(lat,lon)).meters
if dist < distance:
distance = dist
closest = loc
return closest,distance
def show(airport_name, date=None):
"""Show a logbook for <airport_name>."""
airport = db.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")
(start, end) = date_to_timestamps(date)
or_args = [db.between(Logbook.reftime, start, end)]
# get all logbook entries and add device and airport infos
logbook_query = (db.session.query(
func.row_number().over(order_by=Logbook.reftime).label("row_number"),
Logbook).filter(*or_args).filter(
db.or_(Logbook.takeoff_airport_id == airport.id,
Logbook.landing_airport_id == airport.id)).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):
return "[" + device_object.address + "]" if len(
device_object.infos) == 0 else device_object.infos[0].registration
def none_aircraft_replacer(device_object):
return "(unknown)" if len(
device_object.infos) == 0 else device_object.infos[0].aircraft
def airport_marker(logbook_object):
if logbook_object.takeoff_airport is not None and logbook_object.takeoff_airport.name is not airport.name:
return "FROM: {}".format(logbook_object.takeoff_airport.name)
elif logbook_object.landing_airport is not None and logbook_object.landing_airport.name is not airport.name:
return "TO: {}".format(logbook_object.landing_airport.name)
else:
return ""
def none_altitude_replacer(logbook_object):
return "?" if logbook_object.max_altitude is None else "{:5d}m ({:+5d}m)".format(
logbook_object.max_altitude, logbook_object.max_altitude -
logbook_object.takeoff_airport.altitude)
for [row_number, logbook] in logbook_query.all():
print("%3d. %10s %8s (%2s) %8s (%2s) %8s %15s %8s %17s %20s" %
(
row_number,
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),
none_registration_replacer(logbook.device),
none_aircraft_replacer(logbook.device),
airport_marker(logbook),
))
def lxml(show_offline=False,
lat_max=90,
lat_min=-90,
lon_max=180,
lon_min=-180):
timestamp_range_filter = [
db.between(AircraftBeacon.timestamp, datetime(2018, 7, 31, 11, 55, 0),
datetime(2018, 7, 31, 12, 5, 0))
]
last_seen_query = db.session.query(AircraftBeacon).filter(
*timestamp_range_filter).order_by(AircraftBeacon.device_id,
AircraftBeacon.timestamp).distinct(
AircraftBeacon.device_id)
lines = list()
lines.append('<?xml version="1.0" encoding="UTF-8"?>')
lines.append("<markers>")
for aircraft_beacon in last_seen_query:
device = aircraft_beacon.device
code = encode(device.address)
if device.info:
if not device.info.tracked or not device.info.identified:
continue
if not device.info.competition:
competition = device.info.registration[-2:]
else:
competition = device.info.competition
if not device.info.registration:
registration = "???"
else:
registration = device.info.registration
address = device.address
else:
competition = ("_" + code[-2:]).lower()
registration = code
address = 0
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,
int(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 xml
def update_takeoff_landings(start, end):
"""Compute takeoffs and landings."""
current_app.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)
current_app.logger.warn(abort_message)
return abort_message
# check if we have any airport
airports_query = db.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."
current_app.logger.warn(abort_message)
return abort_message
# get beacons for selected time range (+ buffer for duration), one per name and timestamp
sq = (db.session.query(
SenderPosition.name, SenderPosition.timestamp, SenderPosition.location,
SenderPosition.track,
db.func.coalesce(SenderPosition.ground_speed,
0.0).label("ground_speed"), SenderPosition.altitude,
db.func.coalesce(
SenderPosition.climb_rate, 0.0).label("climb_rate")).distinct(
SenderPosition.name, SenderPosition.timestamp).order_by(
SenderPosition.name, SenderPosition.timestamp,
SenderPosition.error_count).filter(
SenderPosition.agl <= MAX_EVENT_AGL).filter(
db.between(
SenderPosition.reference_timestamp,
start - timedelta(seconds=MAX_EVENT_DURATION),
end + timedelta(
seconds=MAX_EVENT_DURATION))).subquery())
# make a query with current, previous and next position
sq2 = db.session.query(
sq.c.name,
db.func.lag(
sq.c.name).over(partition_by=sq.c.name,
order_by=sq.c.timestamp).label("name_prev"),
db.func.lead(
sq.c.name).over(partition_by=sq.c.name,
order_by=sq.c.timestamp).label("name_next"),
sq.c.timestamp,
db.func.lag(sq.c.timestamp).over(
partition_by=sq.c.name,
order_by=sq.c.timestamp).label("timestamp_prev"),
db.func.lead(sq.c.timestamp).over(
partition_by=sq.c.name,
order_by=sq.c.timestamp).label("timestamp_next"),
sq.c.location,
db.func.lag(sq.c.location).over(
partition_by=sq.c.name,
order_by=sq.c.timestamp).label("location_wkt_prev"),
db.func.lead(sq.c.location).over(
partition_by=sq.c.name,
order_by=sq.c.timestamp).label("location_wkt_next"),
sq.c.track,
db.func.lag(
sq.c.track).over(partition_by=sq.c.name,
order_by=sq.c.timestamp).label("track_prev"),
db.func.lead(
sq.c.track).over(partition_by=sq.c.name,
order_by=sq.c.timestamp).label("track_next"),
sq.c.ground_speed,
db.func.lag(sq.c.ground_speed).over(
partition_by=sq.c.name,
order_by=sq.c.timestamp).label("ground_speed_prev"),
db.func.lead(sq.c.ground_speed).over(
partition_by=sq.c.name,
order_by=sq.c.timestamp).label("ground_speed_next"),
sq.c.altitude,
db.func.lag(sq.c.altitude).over(
partition_by=sq.c.name,
order_by=sq.c.timestamp).label("altitude_prev"),
db.func.lead(sq.c.altitude).over(
partition_by=sq.c.name,
order_by=sq.c.timestamp).label("altitude_next"),
sq.c.climb_rate,
db.func.lag(sq.c.climb_rate).over(
partition_by=sq.c.name,
order_by=sq.c.timestamp).label("climb_rate_prev"),
db.func.lead(sq.c.climb_rate).over(
partition_by=sq.c.name,
order_by=sq.c.timestamp).label("climb_rate_next"),
).subquery()
# consider only positions between start and end and with predecessor and successor and limit distance and duration between points
sq3 = (db.session.query(sq2).filter(
db.and_(sq2.c.name_prev != db.null(), sq2.c.name_next != db.null())
).filter(
db.and_(
db.func.ST_DistanceSphere(
sq2.c.location, sq2.c.location_wkt_prev) < MAX_EVENT_RADIUS,
db.func.ST_DistanceSphere(sq2.c.location, sq2.c.location_wkt_next)
< MAX_EVENT_RADIUS)).filter(
sq2.c.timestamp_next - sq2.c.timestamp_prev < timedelta(
seconds=MAX_EVENT_DURATION)).filter(
db.between(sq2.c.timestamp, start, end)).subquery())
# find possible takeoffs and landings
sq4 = (
db.session.query(
sq3.c.timestamp,
db.case([
(
sq3.c.ground_speed > MIN_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 <= MIN_TAKEOFF_SPEED, sq3.c.location),
]).label("location"),
db.case([
(sq3.c.ground_speed > MAX_LANDING_SPEED, sq3.c.track),
(sq3.c.ground_speed <= MAX_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,
db.case([(sq3.c.ground_speed > MIN_TAKEOFF_SPEED, True),
(sq3.c.ground_speed < MAX_LANDING_SPEED, False)
]).label("is_takeoff"),
sq3.c.name,
).filter(
db.or_(
db.and_(sq3.c.ground_speed_prev < MIN_TAKEOFF_SPEED,
sq3.c.ground_speed > MIN_TAKEOFF_SPEED,
sq3.c.ground_speed_next > MIN_TAKEOFF_SPEED,
sq3.c.climb_rate > MIN_TAKEOFF_CLIMB_RATE), # takeoff
db.and_(sq3.c.ground_speed_prev > MAX_LANDING_SPEED,
sq3.c.ground_speed < MAX_LANDING_SPEED,
sq3.c.ground_speed_next < MAX_LANDING_SPEED,
sq3.c.climb_rate < MAX_LANDING_SINK_RATE), # landing
)).subquery())
# get the sender id instead of the name and consider them if the are near airports ...
sq5 = (db.session.query(
sq4.c.timestamp, sq4.c.track, sq4.c.is_takeoff,
Sender.id.label("sender_id"), Airport.id.label("airport_id"),
db.func.ST_DistanceSphere(
sq4.c.location, Airport.location_wkt).label("airport_distance"),
Airport.country_code).filter(
db.and_(
db.func.ST_Within(sq4.c.location, Airport.border),
db.between(Airport.style, 2,
5))).filter(sq4.c.name == Sender.name).subquery())
# ... and take the nearest airport
sq6 = (db.session.query(sq5.c.timestamp, sq5.c.track, sq5.c.is_takeoff,
sq5.c.sender_id, sq5.c.airport_id,
sq5.c.country_code).distinct(
sq5.c.timestamp, sq5.c.track, sq5.c.is_takeoff,
sq5.c.sender_id).order_by(
sq5.c.timestamp, sq5.c.track,
sq5.c.is_takeoff, sq5.c.sender_id,
sq5.c.airport_distance).subquery())
# ... add the country
takeoff_landing_query = (db.session.query(
sq6.c.timestamp, sq6.c.track, sq6.c.is_takeoff, sq6.c.sender_id,
sq6.c.airport_id, Country.gid).join(Country,
sq6.c.country_code == Country.iso2,
isouter=True).subquery())
# ... and save them
ins = insert(TakeoffLanding) \
.from_select((TakeoffLanding.timestamp, TakeoffLanding.track, TakeoffLanding.is_takeoff, TakeoffLanding.sender_id, TakeoffLanding.airport_id, TakeoffLanding.country_id), takeoff_landing_query) \
.on_conflict_do_nothing(index_elements=[TakeoffLanding.timestamp, TakeoffLanding.sender_id, TakeoffLanding.airport_id])
result = db.session.execute(ins)
db.session.commit()
insert_counter = result.rowcount
finish_message = "TakeoffLandings: {} inserted".format(insert_counter)
current_app.logger.info(finish_message)
return finish_message
# 1. filter_by: filtra semplicemente gli attributi di una riga (solo gli attributi diretti non quelli derivati)
# 2. filter: permette filtri più complicati (ma non ho capito bene come si usa)
print("Tutte le location che hanno il civico 1:",
*Location.query.filter_by(housenumber=1).all(), sep='\n'
)
# Le tabelle si possono unire per filtrare i risultati utilizzando gli attributi derivati
print("Tutte le strade di San Polo:",
*Street.query.join(Neighborhood).filter_by(name="SAN POLO").all(), sep='\n'
)
print("Il numero 1 di San Polo:",
*Location.query.filter_by(housenumber=1).join(Street).join(Neighborhood).filter_by(name="SAN POLO").all(), sep='\n'
)
l = Location.query.filter_by(housenumber=1).join(Street).join(Neighborhood).filter_by(name="SAN POLO").first()
print("Tutte i civici vicini al numero 1 di San Polo:",
*Location.query.filter(db.and_(
db.between(Location.longitude,l.longitude-0.0003,l.longitude+0.0003),
db.between(Location.latitude,l.latitude-0.0003,l.latitude+0.0003)
)).order_by(Location.housenumber).all(), sep="\n"
)
print("Tutte le strade che contengono il nome Rialto:",
*Street.query.filter(Street.name.contains("RIALTO")).all(), sep="\n")
print("Tutte le strade che contengono il nome Forno:",
*Street.query.filter(Street.name.contains("CALLE DEL FORNO")).all(), sep="\n")
r = Street.query.filter(Street.name.contains("RIALTO")).all()
a = r[2].locations.all()
wrong = r[4].locations.first()
[wrong.latitude, wrong.longitude]
#%%### Poi
"""