def __get_result(model, flight_field, **kw):
subq = (
DBSession.query(
getattr(Flight, flight_field),
func.count("*").label("count"),
func.sum(Flight.index_score).label("total"),
)
.group_by(getattr(Flight, flight_field))
.outerjoin(Flight.model)
)
if "year" in kw:
try:
year = int(kw["year"])
except:
raise HTTPBadRequest
year_start = date(year, 1, 1)
year_end = date(year, 12, 31)
subq = subq.filter(Flight.date_local >= year_start).filter(Flight.date_local <= year_end)
subq = subq.subquery()
result = DBSession.query(
model, subq.c.count, subq.c.total, over(func.rank(), order_by=desc("total")).label("rank")
).join((subq, getattr(subq.c, flight_field) == model.id))
result = result.order_by(desc("total"))
return result
def guess_model(self):
from skylines.model.flight import Flight
from skylines.model.model import Model
# first try to find the reg number in the database
if self.registration is not None:
glider_reg = self.registration
result = DBSession.query(Flight) \
.filter(func.upper(Flight.registration) == func.upper(glider_reg)) \
.order_by(desc(Flight.id)) \
.first()
if result and result.model_id:
return result.model_id
# try to find another flight with the same logger and use it's aircraft type
if self.logger_id is not None \
and self.logger_manufacturer_id is not None:
logger_id = self.logger_id
logger_manufacturer_id = self.logger_manufacturer_id
result = DBSession.query(Flight).outerjoin(IGCFile) \
.filter(func.upper(IGCFile.logger_manufacturer_id) == func.upper(logger_manufacturer_id)) \
.filter(func.upper(IGCFile.logger_id) == func.upper(logger_id)) \
.filter(Flight.model_id != None) \
.order_by(desc(Flight.id))
if self.logger_manufacturer_id.startswith('X'):
result = result.filter(Flight.pilot == self.owner)
result = result.first()
if result and result.model_id:
return result.model_id
if self.model is not None:
glider_type = self.model.lower()
# otherwise, try to guess the glider model by the glider type igc header
text_fragments = ['%{}%'.format(v) for v in re.sub(r'[^a-z]', ' ', glider_type).split()]
digit_fragments = ['%{}%'.format(v) for v in re.sub(r'[^0-9]', ' ', glider_type).split()]
if not text_fragments and not digit_fragments:
return None
glider_type_clean = re.sub(r'[^a-z0-9]', '', glider_type)
result = DBSession.query(Model) \
.filter(and_( \
func.regexp_replace(func.lower(Model.name), '[^a-z]', ' ').like(func.any(text_fragments)), \
func.regexp_replace(func.lower(Model.name), '[^0-9]', ' ').like(func.all(digit_fragments)))) \
.order_by(func.levenshtein(func.regexp_replace(func.lower(Model.name), '[^a-z0-9]', ''), glider_type_clean))
if result.first():
return result.first().id
# nothing found
return None
def clubs(self):
subq = DBSession.query(Flight.club_id,
func.count('*').label('count'),
func.sum(Flight.olc_plus_score).label('total')) \
.group_by(Flight.club_id).subquery()
result = DBSession.query(Club, subq.c.count, subq.c.total) \
.join((subq, subq.c.club_id == Club.id))
result = result.order_by(desc('total'))
result = result.limit(20)
return dict(tab='clubs', result=result)
def pilots(self):
subq = DBSession.query(Flight.pilot_id,
func.count('*').label('count'),
func.sum(Flight.olc_plus_score).label('total')) \
.group_by(Flight.pilot_id).subquery()
result = DBSession.query(User, subq.c.count, subq.c.total) \
.join((subq, subq.c.pilot_id == User.user_id))
result = result.order_by(desc('total'))
result = result.limit(20)
return dict(tab='pilots', result=result)
def airports(self):
subq = DBSession.query(Flight.takeoff_airport_id,
func.count('*').label('count'),
func.sum(Flight.olc_plus_score).label('total')) \
.group_by(Flight.takeoff_airport_id).subquery()
result = DBSession.query(Airport, subq.c.count, subq.c.total) \
.join((subq, subq.c.takeoff_airport_id == Airport.id))
result = result.order_by(desc('total'))
result = result.limit(20)
return dict(tab='airports', result=result)
def trafficRequestReceived(self, host, port, key, payload):
if len(payload) != 8: return
data = struct.unpack('!II', payload)
pilot = User.by_tracking_key(key)
if pilot is None:
log.err("No such pilot: %d" % key)
return
flags = data[0]
or_filters = []
if flags & TRAFFIC_FLAG_FOLLOWEES:
subq = DBSession.query(Follower.destination_id) \
.filter(Follower.source_id == pilot.id) \
.subquery()
or_filters.append(TrackingFix.pilot_id.in_(subq))
if flags & TRAFFIC_FLAG_CLUB:
subq = DBSession.query(User.id) \
.filter(User.club_id == pilot.club_id) \
.subquery()
or_filters.append(TrackingFix.pilot_id.in_(subq))
if len(or_filters) == 0:
return
query = DBSession.query(TrackingFix) \
.distinct(TrackingFix.pilot_id) \
.filter(and_(TrackingFix.time >= datetime.datetime.utcnow() - datetime.timedelta(hours=2),
TrackingFix.pilot_id != pilot.id,
TrackingFix.location_wkt != None,
TrackingFix.altitude != None,
or_(*or_filters))) \
.order_by(TrackingFix.pilot_id, desc(TrackingFix.time)) \
.limit(32)
response = ''
count = 0
for fix in query:
location = fix.location
if location is None: continue
t = fix.time
t = t.hour * 3600000 + t.minute * 60000 + t.second * 1000 + t.microsecond / 1000
response += struct.pack('!IIiihHI', fix.pilot_id, t,
int(location.latitude * 1000000),
int(location.longitude * 1000000),
int(fix.altitude), 0, 0)
count += 1
response = struct.pack('!HBBI', 0, 0, count, 0) + response
response = struct.pack('!IHHQ', MAGIC, 0, TYPE_TRAFFIC_RESPONSE, 0) + response
response = set_crc(response)
self.transport.write(response, (host, port))
def get_last_year_statistics(self):
query = DBSession.query(func.count('*').label('flights'),
func.sum(Flight.olc_classic_distance).label('distance'),
func.sum(Flight.duration).label('duration')) \
.filter(Flight.pilot == self.user) \
.filter(Flight.date_local > (date.today() - timedelta(days=365))) \
.first()
last_year_statistics = dict(flights=0,
distance=0,
duration=timedelta(0),
speed=0)
if query and query.flights > 0:
duration_seconds = query.duration.days * 24 * 3600 + query.duration.seconds
if duration_seconds > 0:
last_year_statistics['speed'] = float(query.distance) / duration_seconds
last_year_statistics['flights'] = query.flights
last_year_statistics['distance'] = query.distance
last_year_statistics['duration'] = query.duration
last_year_statistics['average_distance'] = query.distance / query.flights
last_year_statistics['average_duration'] = query.duration / query.flights
return last_year_statistics
def update_params(self, d):
models = DBSession.query(Model) \
.order_by(Model.name)
options = [(None, '[unspecified]')] + \
[(model.id, model) for model in models]
d['options'] = options
return SingleSelectField.update_params(self, d)
def get_optimised_contest_trace(self, contest_type, trace_type):
from skylines.model.trace import Trace
query = DBSession.query(Trace) \
.filter(Trace.contest_type == contest_type) \
.filter(Trace.trace_type == trace_type) \
.filter(Trace.flight == self).first()
return query
def _my_update_params(self, d, nullable=False):
query = DBSession.query(User.id, User.display_name) \
.filter(User.club_id == request.identity['user'].club_id) \
.order_by(User.display_name)
options = [(None, 'None')] + query.all()
d['options'] = options
return d
def remove_country(country_code):
print "removing all entries for country_code " + country_code
query = DBSession.query(Airspace) \
.filter(Airspace.country_code == country_code)
query.delete(synchronize_session=False)
DBSession.flush()
transaction.commit()
def get_flight_path2(pilot, last_update = None):
query = DBSession.query(TrackingFix)
query = query.filter(and_(TrackingFix.pilot == pilot,
TrackingFix.location != None,
TrackingFix.altitude != None,
TrackingFix.time >= datetime.utcnow() - timedelta(hours=12)))
if pilot.tracking_delay > 0 and not pilot.is_readable():
query = query.filter(TrackingFix.time <= datetime.utcnow() - timedelta(minutes=pilot.tracking_delay))
query = query.order_by(TrackingFix.time)
start_fix = query.first()
if not start_fix:
return None
start_time = start_fix.time.hour * 3600 + start_fix.time.minute * 60 + start_fix.time.second
if last_update:
query = query.filter(TrackingFix.time >= \
start_fix.time + timedelta(seconds=(last_update - start_time)))
result = []
for fix in query:
location = fix.location
if location is None:
continue
time_delta = fix.time - start_fix.time
time = start_time + time_delta.days * 86400 + time_delta.seconds
result.append((time, location.latitude, location.longitude,
fix.altitude, fix.engine_noise_level))
return result
def update_params(self, d):
users = DBSession.query(User) \
.filter(User.club_id == request.identity['user'].club_id) \
.order_by(User.display_name)
options = [(None, '[unspecified]')] + \
[(user.id, user) for user in users]
d['options'] = options
return SingleSelectField.update_params(self, d)
def analysis(self):
"""Hidden method that restarts flight analysis."""
for flight in DBSession.query(Flight):
analyse_flight(flight)
DBSession.flush()
return redirect('/flights/')
def today(self, **kw):
query = DBSession.query(Flight).filter(Flight.takeoff_time < datetime.utcnow())
query = query.from_self(func.max(Flight.takeoff_time).label('date'))
date = query.one().date
if not date:
raise HTTPNotFound
return self.date(date, today = True, **kw)
def __get_result(model, flight_field):
subq = (
DBSession.query(
getattr(Flight, flight_field),
func.count("*").label("count"),
func.sum(Flight.index_score).label("total"),
)
.group_by(getattr(Flight, flight_field))
.outerjoin(Flight.model)
.subquery()
)
result = DBSession.query(
model, subq.c.count, subq.c.total, over(func.rank(), order_by=desc("total")).label("rank")
).join((subq, getattr(subq.c, flight_field) == model.id))
result = result.order_by(desc("total"))
return result
def by_location(cls, location, distance_threshold = 0.025):
airport = DBSession.query(cls, functions.distance(cls.location_wkt, location.to_wkt()).label('distance'))\
.order_by(functions.distance(cls.location_wkt, location.to_wkt())).first()
if airport is not None and (distance_threshold is None or
airport.distance < distance_threshold):
return airport.Airport
else:
return None
def today(self, **kw):
query = DBSession.query(func.max(Flight.date_local).label('date')) \
.filter(Flight.takeoff_time < datetime.utcnow())
date = query.one().date
if not date:
date = datetime.utcnow()
return self.date(date, today=True, **kw)
def get_elevations(flight, encoder):
# Prepare column expressions
locations = Flight.locations.ST_DumpPoints()
location_id = extract_array_item(locations.path, 1)
location = locations.geom
# Prepare subquery
subq = DBSession.query(location_id.label('location_id'),
location.label('location')) \
.filter(Flight.id == flight.id).subquery()
# Prepare column expressions
timestamp = literal_column('timestamps[location_id]')
elevation = Elevation.rast.ST_Value(subq.c.location)
# Prepare main query
q = DBSession.query(timestamp.label('timestamp'),
elevation.label('elevation')) \
.filter(and_(Flight.id == flight.id,
subq.c.location.ST_Intersects(Elevation.rast),
elevation != None)).all()
if len(q) == 0:
return [], []
# Assemble elevation data
elevations_t = []
elevations_h = []
start_time = q[0][0]
start_midnight = start_time.replace(hour=0, minute=0, second=0, microsecond=0)
for time, elevation in q:
time_delta = time - start_midnight
time = time_delta.days * 86400 + time_delta.seconds
elevations_t.append(time)
elevations_h.append(elevation)
# Encode lists
elevations_t = encoder.encodeList(elevations_t)
elevations_h = encoder.encodeList(elevations_h)
return elevations_t, elevations_h
def generate_keys(self):
"""Hidden method that generates missing tracking keys."""
for user in DBSession.query(User):
if user.tracking_key is None:
user.generate_tracking_key()
DBSession.flush()
return redirect('/users/')
def get_near_flights(flight, location, time, max_distance=1000):
# calculate max_distance in degrees at the earth's sphere (approximate,
# cutoff at +-85 deg)
max_distance_deg = (max_distance / METERS_PER_DEGREE) / \
math.cos(math.radians(min(abs(location.latitude), 85)))
# the distance filter is geometric only, so max_distance must be given in
# SRID units (which is degrees for WGS84). The filter will be more and more
# inaccurate further to the poles. But it's a lot faster than the geograpic
# filter...
result = DBSession.query(Flight) \
.filter(Flight.id != flight.id) \
.filter(Flight.takeoff_time <= time) \
.filter(Flight.landing_time >= time) \
.filter(func.ST_DWithin(Flight.locations,
WKTElement(location.to_wkt(), srid=4326),
max_distance_deg))
flights = []
for flight in result:
# find point closest to given time
closest = min(range(len(flight.timestamps)),
key=lambda x: abs((flight.timestamps[x] - time).total_seconds()))
trace = to_shape(flight.locations).coords
if closest == 0 or closest == len(trace) - 1:
point = trace[closest]
else:
# interpolate flight trace between two fixes
next_smaller = closest if flight.timestamps[closest] < time else closest - 1
next_larger = closest if flight.timestamps[closest] > time else closest + 1
dx = (time - flight.timestamps[next_smaller]).total_seconds() / \
(flight.timestamps[next_larger] - flight.timestamps[next_smaller]).total_seconds()
point_next = trace[closest]
point_prev = trace[closest]
point = [point_prev[0] + (point_next[0] - point_prev[0]) * dx,
point_prev[1] + (point_next[1] - point_prev[1]) * dx]
point_distance = location.geographic_distance(
Location(latitude=point[1], longitude=point[0]))
if point_distance > max_distance:
continue
flights.append(flight)
# limit to 5 flights
if len(flights) == 5:
break
return flights
def index(self, **kw):
subq = DBSession.query(TrackingFix,
over(func.rank(),
partition_by=TrackingFix.pilot_id,
order_by=desc(TrackingFix.time)).label('rank')) \
.outerjoin(TrackingFix.pilot) \
.filter(TrackingFix.time >= datetime.utcnow() - timedelta(hours=6)) \
.filter(TrackingFix.location_wkt != None) \
.subquery()
query = DBSession.query(TrackingFix) \
.filter(TrackingFix.id == subq.c.id) \
.filter(subq.c.rank == 1) \
.order_by(desc(TrackingFix.time))
tracks = []
for track in query.all():
airport = Airport.by_location(track.location, None)
distance = airport.distance(track.location)
tracks.append([track, airport, distance])
return dict(tracks=tracks)
def get_latest_fixes(self, max_age=timedelta(hours=6), **kw):
row_number = over(func.row_number(),
partition_by=TrackingFix.pilot_id,
order_by=desc(TrackingFix.time))
tracking_delay = cast(cast(User.tracking_delay, String) + ' minutes', Interval)
subq = DBSession.query(TrackingFix.id,
row_number.label('row_number')) \
.outerjoin(TrackingFix.pilot) \
.filter(TrackingFix.time >= datetime.utcnow() - max_age) \
.filter(TrackingFix.time <= datetime.utcnow() - tracking_delay) \
.filter(TrackingFix.location_wkt != None) \
.subquery()
query = DBSession.query(TrackingFix) \
.options(joinedload(TrackingFix.pilot)) \
.filter(TrackingFix.id == subq.c.id) \
.filter(subq.c.row_number == 1) \
.order_by(desc(TrackingFix.time))
return query
def create_pilot(self, email_address, display_name, **kw):
if not self.club.is_writable():
raise HTTPForbidden
pilot = User(display_name=display_name,
email_address=email_address, club=self.club)
DBSession.add(pilot)
pilots = DBSession.query(Group).filter(Group.group_name == 'pilots').first()
if pilots:
pilots.users.append(pilot)
redirect('pilots')
def select_pilot(self, pilot, co_pilot, **kwargs):
if not self.flight.is_writable(request.identity):
raise HTTPForbidden
if self.flight.pilot_id != pilot:
self.flight.pilot_id = pilot
if pilot:
self.flight.club_id = DBSession.query(User).get(pilot).club_id
self.flight.co_pilot_id = co_pilot
self.flight.time_modified = datetime.utcnow()
DBSession.flush()
redirect('.')
def igc_headers(self):
"""Hidden method that parses all missing IGC headers."""
igc_files = DBSession.query(IGCFile)
igc_files = igc_files.filter(or_(IGCFile.logger_manufacturer_id == None,
IGCFile.logger_id == None,
IGCFile.model == None,
IGCFile.registration == None))
for igc_file in igc_files:
igc_file.update_igc_headers()
DBSession.flush()
return redirect('/flights/')
def update(self, **kw):
flight_id_list = kw.get('flight_id')
model_list = kw.get('model')
registration_list = kw.get('registration')
if not isinstance(flight_id_list, list):
flight_id_list = [flight_id_list]
if not isinstance(model_list, list):
model_list = [model_list]
if not isinstance(registration_list, list):
registration_list = [registration_list]
if flight_id_list is None \
or len(flight_id_list) != len(model_list) \
or len(flight_id_list) != len(registration_list):
flash(_('Sorry, some error happened when updating your flight(s). Please contact a administrator for help.'), 'warning')
return redirect('/flights/today')
for index, id in enumerate(flight_id_list):
try:
id = int(id)
except ValueError:
continue
try:
model_id = int(model_list[index])
except ValueError:
model_id = None
registration = registration_list[index]
if registration is not None:
registration = registration.strip()
if not 0 < len(registration) < 32:
registration = None
flight = DBSession.query(Flight).get(id)
if not flight.is_writable():
continue
flight.model_id = model_id
flight.registration = registration
flight.time_modified = datetime.utcnow()
DBSession.flush()
flash(_('Your flight(s) have been successfully updated.'))
return redirect('/flights/today')
def get_requested_record(model, id):
"""Look up a record with the id (string) specified by a remote
client. Aborts the current request if the id is malformed or if
the record does not exist."""
try:
id = int(id)
except ValueError:
raise HTTPNotFound
record = DBSession.query(model).get(id)
if record is None:
raise HTTPNotFound
return record
def new_post(self, display_name, club, email_address, password, **kw):
if not club:
club = None
user = User(display_name=display_name, club_id=club,
email_address=email_address, password=password)
user.created_ip = request.remote_addr
user.generate_tracking_key()
DBSession.add(user)
pilots = DBSession.query(Group).filter(Group.group_name == 'pilots').first()
if pilots:
pilots.users.append(user)
redirect('/')
def igc_headers(self, **kwargs):
"""Hidden method that parses all missing IGC headers."""
igc_files = DBSession.query(IGCFile)
igc_files = igc_files.filter(or_(IGCFile.logger_manufacturer_id is None,
IGCFile.logger_id is None,
IGCFile.model is None,
IGCFile.registration is None,
IGCFile.competition_id is None,
IGCFile.date_utc is None))
for igc_file in igc_files:
igc_file.update_igc_headers()
DBSession.flush()
return redirect('.')
