def get_elevation(fixes): shortener = int(max(1, len(fixes) / 1000)) coordinates = [(fix[2]['longitude'], fix[2]['latitude']) for fix in fixes] points = MultiPoint(coordinates[::shortener]) locations = from_shape(points, srid=4326).ST_DumpPoints() locations_id = extract_array_item(locations.path, 1) subq = db.session.query(locations_id.label('location_id'), locations.geom.label('location')).subquery() elevation = Elevation.rast.ST_Value(subq.c.location) # Prepare main query q = db.session.query(literal_column('location_id'), elevation.label('elevation')) \ .filter(and_(subq.c.location.ST_Intersects(Elevation.rast), elevation != None)).all() fixes_copy = [list(fix) for fix in fixes] for i in xrange(1, len(q)): prev = q[i - 1].location_id - 1 current = q[i].location_id - 1 for j in range(prev * shortener, current * shortener): elev = q[i - 1].elevation + (q[i].elevation - q[i - 1].elevation) * (j - prev * shortener) fixes_copy[j][11] = elev return fixes_copy
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 _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 = db.session.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 = db.session.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