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