def data_gsm_unchecked(request): '''Get the unchecked GSM data. ''' platform = int(request.matchdict['id']) if request.GET['format'] == 'geojson': # Query query = select([ DataGsm.id.label('id'), DataGsm.lat, DataGsm.lon, DataGsm.date ]).where(DataGsm.platform_ == platform).where(DataGsm.checked == False ).order_by(desc(DataGsm.date)).limit(1000) # Create list of features from query result features = [ { 'type':'Feature', 'properties':{'date':str(date)}, 'geometry':{'type':'Point', 'coordinates':[float(lon),float(lat)]}, 'id':id_ } for id_, lat, lon, date in DBSession.execute(query).fetchall()] transaction.commit() result = {'type':'FeatureCollection', 'features':features} return result elif request.GET['format'] == 'json': # Query query = select([ DataGsm.id.label('id'), DataGsm.lat.label('lat'), DataGsm.lon.label('lon'), DataGsm.ele.label('ele'), DataGsm.date.label('date')] ).where(DataGsm.platform_ == platform ).where(DataGsm.checked == False ).order_by(desc(DataGsm.date)).limit(1000) data = DBSession.execute(query).fetchall() # Load data from the DB then # compute the distance between 2 consecutive points. df = pd.DataFrame.from_records(data, columns=data[0].keys(), coerce_float=True) X1 = df.iloc[:-1][['lat', 'lon']].values X2 = df.iloc[1:][['lat', 'lon']].values df['dist'] = np.append(haversine(X1, X2), 0).round(3) # Compute the speed df['speed'] = (df['dist']/((df['date']-df['date'].shift(-1)).fillna(1)/np.timedelta64(1, 'h'))).round(3) # Values to import : the first per hour ids = df.set_index('date').resample('1H', how='first').dropna().id.values df['import'] = df.id.isin(ids) df['date'] = df['date'].apply(str) # Fill NaN df.fillna(value={'ele':-999}, inplace=True) return df.to_dict('records')
def argos_unchecked(request): """Returns list of unchecked locations for a given ptt.""" # ptt is a mandatory parameter. try: ptt = int(request.GET['ptt']) except: raise HTTPBadRequest() # Get all unchecked data for this ptt and this individual # Type 0 = Argos data, type 1 = GPS data argos_data = select([ Argos.pk.label('pk'), Argos.date, Argos.lat, Argos.lon, Argos.lc, literal_column('0').label('type') ]).where(Argos.checked == False).where(Argos.ptt == ptt) gps_data = select([ Gps.pk.label('pk'), Gps.date, Gps.lat, Gps.lon, literal_column('NULL').label('lc'), literal_column('1').label('type') ]).where(Gps.checked == False).where(Gps.ptt == ptt) unchecked = union(argos_data, gps_data).alias('unchecked') # ind_id is a facultative parameter try: ind_id = int(request.GET['ind_id']) all_data = select([ unchecked.c.pk, unchecked.c.date, unchecked.c.lat, unchecked.c.lon, unchecked.c.lc, unchecked.c.type ]).select_from(unchecked .join(SatTrx, SatTrx.ptt == ptt) .join(ProtocolIndividualEquipment, and_( SatTrx.id == ProtocolIndividualEquipment.sat_id, unchecked.c.date >= ProtocolIndividualEquipment.begin_date, or_( unchecked.c.date < ProtocolIndividualEquipment.end_date, ProtocolIndividualEquipment.end_date == None ) ) ) ).where(ProtocolIndividualEquipment.ind_id == ind_id) except KeyError or TypeError: all_data = select([ unchecked.c.pk, unchecked.c.date, unchecked.c.lat, unchecked.c.lon, unchecked.c.lc, unchecked.c.type ]) ind_id = None # Initialize json object result = {'ptt':{}, 'locations':[], 'indiv':{}} # Load data from the DB then # compute the distance between 2 consecutive points. data = DBSession.execute(all_data.order_by(desc(all_data.c.date))).fetchall() df = pd.DataFrame.from_records(data, columns=data[0].keys(), coerce_float=True) X1 = df.ix[:,['lat', 'lon']].values[:-1,:] X2 = df.ix[1:,['lat', 'lon']].values dist = pd.Series(np.append(haversine(X1, X2).round(3), 0), name='dist') df = pd.concat([df['pk'], df['date'].apply(lambda x: str(x)), df['lat'], df['lon'], df['lc'], df['type'], dist], axis=1) result['locations'] = df.to_dict('records') # Get informations for this ptt ptt_infos = select([SatTrx.ptt, SatTrx.manufacturer, SatTrx.model]).where(SatTrx.ptt == ptt) result['ptt']['ptt'], result['ptt']['manufacturer'], result['ptt']['model'] = DBSession.execute(ptt_infos).fetchone() # Get informations for the individual if ind_id is not None: query = select([ Individual.id.label('id'), Individual.age.label('age'), Individual.sex.label('sex'), Individual.specie.label('specie'), Individual.monitoring_status.label('monitoring_status'), Individual.origin.label('origin'), Individual.survey_type.label('survey_type') ]).where(Individual.id == ind_id) result['indiv'] = dict(DBSession.execute(query).fetchone()) # Last known location c = V_Individuals_LatLonDate.c query = select([c.lat, c.lon, c.date]).where(c.ind_id == ind_id).order_by(desc(c.date)).limit(1) lat, lon, date = DBSession.execute(query).fetchone() result['indiv']['last_loc'] = {'date':str(date), 'lat':float(lat), 'lon':float(lon)} return result