def test_delete_shape(self): # Can we remove a shape that's fully ingested? city_meta = postgres_session.query(ShapeMetadata).get( shape_fixtures['city'].table_name) self.assertIsNotNone(city_meta) city_meta.remove_table() postgres_session.commit() city_meta = postgres_session.query(ShapeMetadata).get( shape_fixtures['city'].table_name) self.assertIsNone(city_meta) # Can we remove a shape that's only in the metadata? dummy_meta = postgres_session.query(ShapeMetadata).get(self.dummy_name) self.assertIsNotNone(dummy_meta) dummy_meta.remove_table() postgres_session.commit() dummy_meta = postgres_session.query(ShapeMetadata).get(self.dummy_name) self.assertIsNone(dummy_meta) # Add them back to return to original test state ShapeTests.ingest_fixture(shape_fixtures['city']) ShapeMetadata.add(human_name='Dummy Name', source_url=None, update_freq='yearly', approved_status=False) postgres_session.commit()
def frequency_update(frequency) -> bool: """Queue an update task for all the tables whose corresponding meta info is part of this frequency group. """ logger.info('Begin. (frequency: "{}")'.format(frequency)) logger.debug('Query for all point dataset meta records.') point_metas = postgres_session.query(MetaTable) \ .filter(MetaTable.update_freq == frequency) \ .filter(MetaTable.date_added != None) \ .all() logger.debug('Queue an update task for each point dataset.') for point in point_metas: update_dataset.delay(point.dataset_name) logger.debug('Query for all shape dataset meta records.') shape_metas = postgres_session.query(ShapeMetadata) \ .filter(ShapeMetadata.update_freq == frequency) \ .filter(ShapeMetadata.is_ingested == True) \ .all() logger.debug('Queue an update task for each shape dataset.') for shape_meta in shape_metas: update_shape.delay(shape_meta.dataset_name) logger.info('End.') return True
def edit_shape(dataset_name): form = EditShapeForm() meta = postgres_session.query(ShapeMetadata).get(dataset_name) if form.validate_on_submit(): upd = { 'human_name': form.human_name.data, 'description': form.description.data, 'attribution': form.attribution.data, 'update_freq': form.update_freq.data, } postgres_session.query(ShapeMetadata) \ .filter(ShapeMetadata.dataset_name == meta.dataset_name) \ .update(upd) postgres_session.commit() if not meta.approved_status: approve_shape(dataset_name) flash('%s updated successfully!' % meta.human_name, 'success') return redirect(url_for('views.view_datasets')) else: pass num_rows = meta.num_shapes if meta.num_shapes else 0 context = {'form': form, 'meta': meta, 'num_rows': num_rows} return render_template('admin/edit-shape.html', **context)
def edit_shape(dataset_name): form = EditShapeForm() meta = postgres_session.query(ShapeMetadata).get(dataset_name) if form.validate_on_submit(): upd = { 'human_name': form.human_name.data, 'description': form.description.data, 'attribution': form.attribution.data, 'update_freq': form.update_freq.data, } postgres_session.query(ShapeMetadata) \ .filter(ShapeMetadata.dataset_name == meta.dataset_name) \ .update(upd) postgres_session.commit() if not meta.approved_status: approve_shape(dataset_name) flash('%s updated successfully!' % meta.human_name, 'success') return redirect(url_for('views.view_datasets')) else: pass num_rows = meta.num_shapes if meta.num_shapes else 0 context = { 'form': form, 'meta': meta, 'num_rows': num_rows } return render_template('admin/edit-shape.html', **context)
def test_delete_shape(self): # Can we remove a shape that's fully ingested? city_meta = postgres_session.query(ShapeMetadata).get(shape_fixtures['city'].table_name) self.assertIsNotNone(city_meta) city_meta.remove_table() postgres_session.commit() city_meta = postgres_session.query(ShapeMetadata).get(shape_fixtures['city'].table_name) self.assertIsNone(city_meta) # Can we remove a shape that's only in the metadata? dummy_meta = postgres_session.query(ShapeMetadata).get(self.dummy_name) self.assertIsNotNone(dummy_meta) dummy_meta.remove_table() postgres_session.commit() dummy_meta = postgres_session.query(ShapeMetadata).get(self.dummy_name) self.assertIsNone(dummy_meta) # Add them back to return to original test state ShapeTests.ingest_fixture(shape_fixtures['city']) ShapeMetadata.add(human_name='Dummy Name', source_url=None, update_freq='yearly', approved_status=False) postgres_session.commit()
def update_meta(metatable, table): """ After ingest/update, update the metatable registry to reflect table information. :param metatable: MetaTable instance to update. :param table: Table instance to update from. :returns: None """ metatable.update_date_added() metatable.obs_from, metatable.obs_to = postgres_session.query( func.min(table.c.point_date), func.max(table.c.point_date) ).first() metatable.bbox = postgres_session.query( func.ST_SetSRID( func.ST_Envelope(func.ST_Union(table.c.geom)), 4326 ) ).first()[0] metatable.column_names = { c.name: str(c.type) for c in metatable.column_info() if c.name not in {'geom', 'point_date', 'hash'} } postgres_session.add(metatable) postgres_session.commit()
def frequency_update(frequency) -> bool: """Queue an update task for all the tables whose corresponding meta info is part of this frequency group. """ logger.info('Begin. (frequency: "{}")'.format(frequency)) logger.debug('Query for all point dataset meta records.') point_metas = postgres_session.query(MetaTable) \ .filter(MetaTable.update_freq == frequency) \ .filter(MetaTable.date_added != None) \ .all() logger.debug('Queue an update task for each point dataset.') for point in point_metas: update_dataset.delay(point.dataset_name) logger.debug('Query for all shape dataset meta records.') shape_metas = postgres_session.query(ShapeMetadata) \ .filter(ShapeMetadata.update_freq == frequency) \ .filter(ShapeMetadata.is_ingested == True) \ .all() logger.debug('Queue an update task for each shape dataset.') for shape_meta in shape_metas: update_shape.delay(shape_meta.dataset_name) logger.info('End.') return True
def edit_dataset(source_url_hash): form = EditDatasetForm() meta = postgres_session.query(MetaTable).get(source_url_hash) fieldnames = meta.column_names num_rows = 0 if meta.approved_status: try: table_name = meta.dataset_name table = Table(table_name, postgres_base.metadata, autoload=True, autoload_with=engine) # Would prefer to just get the names from the metadata # without needing to reflect. fieldnames = list(table.columns.keys()) pk_name = [p.name for p in table.primary_key][0] pk = table.c[pk_name] num_rows = postgres_session.query(pk).count() except NoSuchTableError: # dataset has been approved, but perhaps still processing. pass if form.validate_on_submit(): upd = { 'human_name': form.human_name.data, 'description': form.description.data, 'attribution': form.attribution.data, 'update_freq': form.update_freq.data, 'latitude': form.latitude.data, 'longitude': form.longitude.data, 'location': form.location.data, 'observed_date': form.observed_date.data, } postgres_session.query(MetaTable) \ .filter(MetaTable.source_url_hash == meta.source_url_hash) \ .update(upd) postgres_session.commit() if not meta.approved_status: approve_dataset(source_url_hash) flash('%s updated successfully!' % meta.human_name, 'success') return redirect(url_for('views.view_datasets')) else: pass context = { 'form': form, 'meta': meta, 'fieldnames': fieldnames, 'num_rows': num_rows, } return render_template('admin/edit-dataset.html', **context)
def edit_dataset(source_url_hash): form = EditDatasetForm() meta = postgres_session.query(MetaTable).get(source_url_hash) fieldnames = meta.column_names num_rows = 0 if meta.approved_status: try: table_name = meta.dataset_name table = Table(table_name, postgres_base.metadata, autoload=True, autoload_with=engine) # Would prefer to just get the names from the metadata # without needing to reflect. fieldnames = list(table.columns.keys()) pk_name = [p.name for p in table.primary_key][0] pk = table.c[pk_name] num_rows = postgres_session.query(pk).count() except NoSuchTableError: # dataset has been approved, but perhaps still processing. pass if form.validate_on_submit(): upd = { 'human_name': form.human_name.data, 'description': form.description.data, 'attribution': form.attribution.data, 'update_freq': form.update_freq.data, 'latitude': form.latitude.data, 'longitude': form.longitude.data, 'location': form.location.data, 'observed_date': form.observed_date.data, } postgres_session.query(MetaTable) \ .filter(MetaTable.source_url_hash == meta.source_url_hash) \ .update(upd) postgres_session.commit() if not meta.approved_status: approve_dataset(source_url_hash) flash('%s updated successfully!' % meta.human_name, 'success') return redirect(url_for('views.view_datasets')) else: pass context = { 'form': form, 'meta': meta, 'fieldnames': fieldnames, 'num_rows': num_rows, } return render_template('admin/edit-dataset.html', **context)
def decorated(*args, **kwargs): if flask_session.get('user_id'): api_key = flask_session['user_id'] elif request.form.get('api_key'): api_key = request.form['api_key'] elif request.args.get('api_key'): api_key = request.args['api_key'] else: try: api_key = json.loads(request.data).get('api_key') except ValueError: api_key = None if api_key: db_session.query(User).get(api_key) flask_session['user_id'] = api_key return f(*args, **kwargs)
def properties_of(feature): query = postgres_session.query(FeatureMeta.observed_properties).filter( FeatureMeta.name == feature) return [ feature + '.' + prop['name'] for prop in query.first().observed_properties ]
def weather_stations(): raw_query_params = request.args.copy() stations_table = Table('weather_stations', postgres_base.metadata, autoload=True, autoload_with=engine, extend_existing=True) valid_query, query_clauses, resp, status_code = make_query( stations_table, raw_query_params) if valid_query: resp['meta']['status'] = 'ok' base_query = postgres_session.query(stations_table) for clause in query_clauses: print(('weather_stations(): filtering on clause', clause)) base_query = base_query.filter(clause) values = [r for r in base_query.all()] fieldnames = [f for f in list(stations_table.columns.keys())] for value in values: d = {f: getattr(value, f) for f in fieldnames} loc = bytes(value.location.data) d['location'] = shapely.wkb.loads(loc).__geo_interface__ resp['objects'].append(d) resp['meta']['query'] = raw_query_params resp = make_response(json.dumps(resp, default=date_json_handler), status_code) resp.headers['Content-Type'] = 'application/json' return resp
def admin_add_dataset(): user = postgres_session.query(User).get(flask_session['user_id']) context = {'is_admin': True, 'contributor_name': user.name, 'contributor_organization': 'Plenario Admin', 'contributor_email': user.email} return add(context)
def on_model_change(self, form, model, is_created): network = form.sensor_network.data validate_node(network) network_obj = postgres_session.query(NetworkMeta).filter( NetworkMeta.name == network).first() network_obj.nodes.append(model) postgres_session.commit()
def get_meta(name: str): """Return meta record given a point table name or a shape table name. """ logger.info('Begin. (name: "{}")'.format(name)) query = postgres_session.query(MetaTable).filter(MetaTable.dataset_name == name) result = query.first() if result is None: result = postgres_session.query(ShapeMetadata).filter( ShapeMetadata.dataset_name == name ).first() if result is None: raise ValueError("dataset '%s' not found in metadata records" % name) logger.info('End.') return result
def get_meta(name: str): """Return meta record given a point table name or a shape table name. """ logger.info('Begin. (name: "{}")'.format(name)) query = postgres_session.query(MetaTable).filter( MetaTable.dataset_name == name) result = query.first() if result is None: result = postgres_session.query(ShapeMetadata).filter( ShapeMetadata.dataset_name == name).first() if result is None: raise ValueError("dataset '%s' not found in metadata records" % name) logger.info('End.') return result
def index(network_name=None): features = [] for node in postgres_session.query(NodeMeta).all(): if network_name is None or node.sensor_network.lower() == network_name.lower(): for sensor in node.sensors: for prop in sensor.observed_properties.values(): features.append(prop.split('.')[0].lower()) return list(set(features))
def update_dataset_view(source_url_hash): meta = postgres_session.query(MetaTable).get(source_url_hash) ticket = worker.update_dataset.delay(meta.dataset_name).id meta.result_ids = [ticket] postgres_session.add(meta) postgres_session.commit() return redirect(url_for('views.view_datasets'))
def validate(self): rv = Form.validate(self) if not rv: return False existing_name = db_session.query(User) \ .filter(User.name == self.name.data).first() if existing_name: self.name.errors.append('Name is already registered') return False existing_email = db_session.query(User) \ .filter(User.email == self.email.data).first() if existing_email: self.email.errors.append('Email address is already registered') return False return True
def admin_add_dataset(): user = postgres_session.query(User).get(flask_session['user_id']) context = { 'is_admin': True, 'contributor_name': user.name, 'contributor_organization': 'Plenario Admin', 'contributor_email': user.email } return add(context)
def index(network_name=None): features = [] for node in postgres_session.query(NodeMeta).all(): if network_name is None or node.sensor_network.lower( ) == network_name.lower(): for sensor in node.sensors: for prop in sensor.observed_properties.values(): features.append(prop.split('.')[0].lower()) return list(set(features))
def update_dataset_view(source_url_hash): meta = postgres_session.query(MetaTable).get(source_url_hash) ticket = worker.update_dataset.delay(meta.dataset_name).id meta.result_ids = [ticket] postgres_session.add(meta) postgres_session.commit() return redirect(url_for('views.view_datasets'))
def fetch_pending_tables(model): """Used in views.py, fetch all records corresponding to tables pending administrator approval. These tables exist in the master tables, but their corresponding records have not been ingested. :param model: (class) ORM Class corresponding to a meta table :returns: (list) contains all records for which is_approved is false""" query = postgres_session.query(model).filter(model.approved_status != True) return query.all()
def approve_dataset(source_url_hash): meta = postgres_session.query(MetaTable).get(source_url_hash) ticket = worker.add_dataset.delay(meta.dataset_name).id meta.approved_status = True meta.result_ids = [ticket] postgres_session.commit() send_approval_email(meta.human_name, meta.contributor_name, meta.contributor_email)
def fetch_pending_tables(model): """Used in views.py, fetch all records corresponding to tables pending administrator approval. These tables exist in the master tables, but their corresponding records have not been ingested. :param model: (class) ORM Class corresponding to a meta table :returns: (list) contains all records for which is_approved is false""" query = postgres_session.query(model).filter(model.approved_status != True) return query.all()
def approve_shape(dataset_name): meta = postgres_session.query(ShapeMetadata).get(dataset_name) ticket = worker.add_shape.delay(dataset_name).id meta.approved_status = True meta.celery_task_id = ticket postgres_session.commit() send_approval_email(meta.human_name, meta.contributor_name, meta.contributor_email)
def index(cls): try: q = postgres_session.query(cls.dataset_name) q = q.filter(cls.approved_status == True) names = [result.dataset_name for result in q.all()] except ProgrammingError: # Handles a case that causes init_db to crash. # Validator calls index when initializing, prevents this call # from raising an error when the database is empty. names = [] return names
def approve_shape(dataset_name): meta = postgres_session.query(ShapeMetadata).get(dataset_name) ticket = worker.add_shape.delay(dataset_name).id meta.approved_status = True meta.celery_task_id = ticket postgres_session.commit() send_approval_email( meta.human_name, meta.contributor_name, meta.contributor_email )
def approve_dataset(source_url_hash): meta = postgres_session.query(MetaTable).get(source_url_hash) ticket = worker.add_dataset.delay(meta.dataset_name).id meta.approved_status = True meta.result_ids = [ticket] postgres_session.commit() send_approval_email( meta.human_name, meta.contributor_name, meta.contributor_email )
def test_update(self): # Try to ingest slightly changed shape fixture = shape_fixtures['changed_neighborhoods'] # Add the fixture to the registry first shape_meta = postgres_session.query(ShapeMetadata).get('chicago_neighborhoods') # Do a ShapeETL update ShapeETL(meta=shape_meta, source_path=fixture.path).update() t = shape_meta.shape_table sel = t.select().where(t.c['sec_neigh'] == 'ENGLEWOOD') res = engine.execute(sel).fetchall() altered_value = res[0]['pri_neigh'] # I changed Englewood to Englerwood :P self.assertEqual(altered_value, 'Englerwood')
def test_update(self): # Try to ingest slightly changed shape fixture = shape_fixtures['changed_neighborhoods'] # Add the fixture to the registry first shape_meta = postgres_session.query(ShapeMetadata).get( 'chicago_neighborhoods') # Do a ShapeETL update ShapeETL(meta=shape_meta, source_path=fixture.path).update() t = shape_meta.shape_table sel = t.select().where(t.c['sec_neigh'] == 'ENGLEWOOD') res = engine.execute(sel).fetchall() altered_value = res[0]['pri_neigh'] # I changed Englewood to Englerwood :P self.assertEqual(altered_value, 'Englerwood')
def reset_password(): form = ResetPasswordForm() errors = [] if form.validate_on_submit(): user = db_session.query(User).get(flask_session['user_id']) check = user.check_password(user.name, form.old_password.data) if check: user.password = form.new_password.data db_session.add(user) db_session.commit() flash('Password reset successful!', 'success') else: errors.append('Password is not correct') return render_template('admin/reset-password.html', form=form, errors=errors)
def test_new_table_has_correct_column_names_in_meta(self): drop_if_exists(self.unloaded_meta.dataset_name) etl = PlenarioETL(self.unloaded_meta, source_path=self.radio_path) new_table = etl.add() columns = postgres_session.query(MetaTable.column_names) columns = columns.filter(MetaTable.dataset_name == self.unloaded_meta.dataset_name) columns = columns.first()[0] self.assertEqual(len(columns), 4) postgres_session.close() new_table.drop(postgres_engine, checkfirst=True)
def validate(self): rv = Form.validate(self) if not rv: return False user = db_session.query(User) \ .filter(User.email == self.email.data).first() if user is None: self.email.errors.append('Email address is not registered') return False if not user.check_password(user.name, self.password.data): self.password.errors.append('Password is not valid') return False self.user = user return True
def add_user(): form = AddUserForm() if form.validate_on_submit(): user_info = { 'name': form.name.data, 'email': form.email.data, 'password': form.password.data } user = User(**user_info) db_session.add(user) db_session.commit() context = { 'form': form, 'name': form.name.data, 'email': form.email.data, 'users': db_session.query(User).all() } return render_template('admin/add-user.html', **context)
def attach_metadata(cls, rows): """Given a list of dicts that include a dataset_name, add metadata about the datasets to each dict. :param rows: List of dict-likes with a dataset_name attribute """ dataset_names = [row['dataset_name'] for row in rows] # All the metadata attributes that we can pull in unaltered as_is_attr_names = [ 'dataset_name', 'human_name', 'date_added', 'obs_from', 'obs_to', 'last_update', 'attribution', 'description', 'update_freq', 'view_url', 'source_url', 'contributor_name', 'contributor_email', 'contributor_organization' ] as_is_attrs = [getattr(cls, name) for name in as_is_attr_names] # Bounding box is the exception. We need to process it a bit. bbox = func.ST_AsGeoJSON(cls.bbox) # Put our as-is and processed attributes together attr_names = as_is_attr_names + ['bbox'] attrs = as_is_attrs + [bbox] # Make the DB call result = postgres_session.query(*attrs). \ filter(cls.dataset_name.in_(dataset_names)) meta_list = [dict(list(zip(attr_names, row))) for row in result] # We need to coerce datetimes to strings date_attrs = ['date_added', 'obs_from', 'obs_to'] for row in meta_list: row['bbox'] = json.loads(row['bbox']) for attr in date_attrs: row[attr] = str(row[attr]) # Align the original list and metadata list... meta_list = sorted(meta_list, key=itemgetter('dataset_name')) to_coalesce = sorted(rows, key=itemgetter('dataset_name')) # and coalesce them. for original, meta in zip(to_coalesce, meta_list): original.update(meta) return to_coalesce
def validate_sensor_properties(observed_properties): if not observed_properties: raise ValidationError('No observed properties were provided!') features = defaultdict(list) for feature in postgres_session.query(FeatureMeta).all(): for property_dict in feature.observed_properties: features[feature.name].append(property_dict['name']) for feature_property in list(observed_properties.values()): try: feat, prop = feature_property.split('.') except ValueError: raise ValidationError('Feature specified without property: {}' .format(feature_property)) if feat not in features: raise ValidationError('Bad FOI name: {!r}'.format(feat)) if prop not in features[feat]: raise ValidationError('Bad property name: {!r}'.format(prop))
def narrow_candidates(cls, dataset_names, start, end, geom=None): """ :param dataset_names: Names of point datasets to be considered :return names: Names of point datasets whose bounding box and date range interesects with the given bounds. """ # Filter out datsets that don't intersect the time boundary q = postgres_session.query(cls.dataset_name) \ .filter(cls.dataset_name.in_(dataset_names), cls.date_added != None, cls.obs_from < end, cls.obs_to > start) # or the geometry boundary if geom: intersecting = cls.bbox.ST_Intersects( func.ST_GeomFromGeoJSON(geom)) q = q.filter(intersecting) return [row.dataset_name for row in q.all()]
def validate_sensor_properties(observed_properties): if not observed_properties: raise ValidationError('No observed properties were provided!') features = defaultdict(list) for feature in postgres_session.query(FeatureMeta).all(): for property_dict in feature.observed_properties: features[feature.name].append(property_dict['name']) for feature_property in list(observed_properties.values()): try: feat, prop = feature_property.split('.') except ValueError: raise ValidationError( 'Feature specified without property: {}'.format( feature_property)) if feat not in features: raise ValidationError('Bad FOI name: {!r}'.format(feat)) if prop not in features[feat]: raise ValidationError('Bad property name: {!r}'.format(prop))
def make_grid(self, resolution, geom=None, conditions=None, obs_dates={}): """ :param resolution: length of side of grid square in meters :type resolution: int :param geom: string representation of geojson fragment :type geom: str :param conditions: conditions on columns to filter on :type conditions: list of SQLAlchemy binary operations (e.g. col > value) :return: grid: result proxy with all result rows size_x, size_y: the horizontal and vertical size of the grid squares in degrees """ if conditions is None: conditions = [] # We need to convert resolution (given in meters) to degrees # - which is the unit of measure for EPSG 4326 - # - in order to generate our grid. center = self.get_bbox_center() # center[1] is longitude size_x, size_y = get_size_in_degrees(resolution, center[1]) t = self.point_table q = postgres_session.query( func.count(t.c.hash), func.ST_SnapToGrid(t.c.geom, 0, 0, size_x, size_y).label('squares')).filter( *conditions).group_by('squares') if geom: q = q.filter(t.c.geom.ST_Within(func.ST_GeomFromGeoJSON(geom))) if obs_dates: q = q.filter(t.c.point_date >= obs_dates['lower']) q = q.filter(t.c.point_date <= obs_dates['upper']) return postgres_session.execute(q), size_x, size_y
def weather_stations(): raw_query_params = request.args.copy() stations_table = Table( 'weather_stations', postgres_base.metadata, autoload=True, autoload_with=engine, extend_existing=True ) valid_query, query_clauses, resp, status_code = make_query(stations_table, raw_query_params) if valid_query: resp['meta']['status'] = 'ok' base_query = postgres_session.query(stations_table) for clause in query_clauses: print(('weather_stations(): filtering on clause', clause)) base_query = base_query.filter(clause) values = [r for r in base_query.all()] fieldnames = [f for f in list(stations_table.columns.keys())] for value in values: d = {f: getattr(value, f) for f in fieldnames} loc = bytes(value.location.data) d['location'] = shapely.wkb.loads(loc).__geo_interface__ resp['objects'].append(d) resp['meta']['query'] = raw_query_params resp = make_response( json.dumps(resp, default=date_json_handler), status_code ) resp.headers['Content-Type'] = 'application/json' return resp
def on_model_change(self, form, model, is_created): network = form.sensor_network.data validate_node(network) network_obj = postgres_session.query(NetworkMeta).filter(NetworkMeta.name == network).first() network_obj.nodes.append(model) postgres_session.commit()
def delete_dataset_view(source_url_hash): meta = postgres_session.query(MetaTable).get(source_url_hash) worker.delete_dataset.delay(meta.dataset_name) return redirect(url_for('views.view_datasets'))
def weather(table): raw_query_params = request.args.copy() weather_table = Table( 'dat_weather_observations_{}'.format(table), postgres_base.metadata, autoload=True, autoload_with=engine, extend_existing=True ) stations_table = Table( 'weather_stations', postgres_base.metadata, autoload=True, autoload_with=engine, extend_existing=True ) valid_query, query_clauses, resp, status_code = make_query(weather_table, raw_query_params) if valid_query: resp['meta']['status'] = 'ok' base_query = postgres_session.query(weather_table, stations_table) base_query = base_query.join( stations_table, weather_table.c.wban_code == stations_table.c.wban_code ) for clause in query_clauses: base_query = base_query.filter(clause) try: base_query = base_query.order_by( getattr(weather_table.c, 'date').desc() ) except AttributeError: base_query = base_query.order_by( getattr(weather_table.c, 'datetime').desc() ) base_query = base_query.limit(RESPONSE_LIMIT) if raw_query_params.get('offset'): offset = raw_query_params['offset'] base_query = base_query.offset(int(offset)) values = [r for r in base_query.all()] weather_fields = list(weather_table.columns.keys()) station_fields = list(stations_table.columns.keys()) weather_data = {} station_data = {} for value in values: wd = {f: getattr(value, f) for f in weather_fields} sd = {f: getattr(value, f) for f in station_fields} if weather_data.get(value.wban_code): weather_data[value.wban_code].append(wd) else: weather_data[value.wban_code] = [wd] loc = bytes(value.location.data) sd['location'] = shapely.wkb.loads(loc).__geo_interface__ station_data[value.wban_code] = sd for station_id in list(weather_data.keys()): d = { 'station_info': station_data[station_id], 'observations': weather_data[station_id], } resp['objects'].append(d) resp['meta']['total'] = sum([len(r['observations']) for r in resp['objects']]) resp['meta']['query'] = raw_query_params resp = make_response( json.dumps(resp, default=date_json_handler), status_code ) resp.headers['Content-Type'] = 'application/json' return resp
def detail_query(args, aggregate=False): meta_params = ('dataset', 'shapeset', 'data_type', 'geom', 'obs_date__ge', 'obs_date__le') meta_vals = (args.data.get(k) for k in meta_params) dataset, shapeset, data_type, geom, obs_date__ge, obs_date__le = meta_vals # If there aren't tree filters provided, a little formatting is needed # to make the general filters into an 'and' tree. if not has_tree_filters(args.data): # Creates an AND condition tree and adds it to args. args.data[dataset.name + '__filter'] = request_args_to_condition_tree( request_args=args.data, ignore=['shapeset'] ) # Sort out the filter conditions from the rest of the user arguments. filters = {k: v for k, v in list(args.data.items()) if 'filter' in k} # Get upset if they specify more than a dataset and shapeset filter. if len(filters) > 2: return api_response.bad_request('Too many table filters provided.') # Query the point dataset. q = postgres_session.query(dataset) # If the user specified a geom, filter results to those within its shape. if geom: q = q.filter(dataset.c.geom.ST_Within( sqlalchemy.func.ST_GeomFromGeoJSON(geom) )) # Retrieve the filters and build conditions from them if they exist. point_ctree = filters.get(dataset.name + '__filter') # If the user specified point dataset filters, parse and apply them. if point_ctree: point_conditions = parse_tree(dataset, point_ctree) q = q.filter(point_conditions) # To allow both obs_date meta params and filter trees. q = q.filter(dataset.c.point_date >= obs_date__ge) if obs_date__ge else q q = q.filter(dataset.c.point_date <= obs_date__le) if obs_date__le else q # If a user specified a shape dataset, it was either through the /shapes # enpoint, which uses the aggregate result, or through the /detail endpoint # which uses the joined result. if shapeset is not None: if aggregate: q = q.from_self(shapeset).filter(dataset.c.geom.ST_Intersects(shapeset.c.geom)).group_by(shapeset) else: shape_columns = ['{}.{} as {}'.format(shapeset.name, col.name, col.name) for col in shapeset.c] q = q.join(shapeset, dataset.c.geom.ST_Within(shapeset.c.geom)) q = q.add_columns(*shape_columns) # If there's a filter specified for the shape dataset, apply those conditions. shape_ctree = filters.get(shapeset.name + '__filter') if shape_ctree: shape_conditions = parse_tree(shapeset, shape_ctree) q = q.filter(shape_conditions) return q
def csv_already_submitted(url): digest = md5(bytes(url, encoding="utf-8")).hexdigest() return postgres_session.query(MetaTable).get(digest) is not None
def properties_of(feature): query = postgres_session.query(FeatureMeta.observed_properties).filter( FeatureMeta.name == feature) return [feature + '.' + prop['name'] for prop in query.first().observed_properties]
def validate_node(network): if network not in [ net.name for net in postgres_session.query(NetworkMeta).all() ]: raise ValidationError('Invalid network name!')
def _meta(args): """Generate meta information about table(s) with records from MetaTable. :param args: dictionary of request arguments (?foo=bar) :returns: response dictionary """ meta_params = ('dataset', 'geom', 'obs_date__ge', 'obs_date__le') meta_vals = (args.data.get(k) for k in meta_params) dataset, geom, start_date, end_date = meta_vals # Columns to select as-is cols_to_return = ['human_name', 'dataset_name', 'source_url', 'view_url', 'date_added', 'last_update', 'update_freq', 'attribution', 'description', 'obs_from', 'obs_to', 'column_names', 'observed_date', 'latitude', 'longitude', 'location'] col_objects = [getattr(MetaTable, col) for col in cols_to_return] # Columns that need pre-processing col_objects.append(sqlalchemy.func.ST_AsGeoJSON(MetaTable.bbox)) cols_to_return.append('bbox') # Only return datasets that have been successfully ingested q = postgres_session.query(*col_objects).filter(MetaTable.date_added.isnot(None)) # Filter over datasets if user provides full date range or geom should_filter = geom or (start_date and end_date) if dataset is not None: # If the user specified a name, don't try any filtering. # Just spit back that dataset's metadata. q = q.filter(MetaTable.dataset_name == dataset.name) # Otherwise, just send back all the (filtered) datasets elif should_filter: if geom: intersects = sqlalchemy.func.ST_Intersects( sqlalchemy.func.ST_GeomFromGeoJSON(geom), MetaTable.bbox ) q = q.filter(intersects) if start_date and end_date: q = q.filter( sqlalchemy.and_( MetaTable.obs_from < end_date, MetaTable.obs_to > start_date ) ) metadata_records = [dict(list(zip(cols_to_return, row))) for row in q.all()] for record in metadata_records: try: if record.get('bbox') is not None: # serialize bounding box geometry to string record['bbox'] = json.loads(record['bbox']) # format columns in the expected way record['columns'] = [{'field_name': k, 'field_type': v} for k, v in list(record['column_names'].items())] except Exception as e: args.warnings.append(e.message) # clear column_names off the json, users don't need to see it del record['column_names'] return metadata_records
def detail_query(args, aggregate=False): meta_params = ('dataset', 'shapeset', 'data_type', 'geom', 'obs_date__ge', 'obs_date__le') meta_vals = (args.data.get(k) for k in meta_params) dataset, shapeset, data_type, geom, obs_date__ge, obs_date__le = meta_vals # If there aren't tree filters provided, a little formatting is needed # to make the general filters into an 'and' tree. if not has_tree_filters(args.data): # Creates an AND condition tree and adds it to args. args.data[dataset.name + '__filter'] = request_args_to_condition_tree( request_args=args.data, ignore=['shapeset']) # Sort out the filter conditions from the rest of the user arguments. filters = {k: v for k, v in list(args.data.items()) if 'filter' in k} # Get upset if they specify more than a dataset and shapeset filter. if len(filters) > 2: return api_response.bad_request('Too many table filters provided.') # Query the point dataset. q = postgres_session.query(dataset) # If the user specified a geom, filter results to those within its shape. if geom: q = q.filter( dataset.c.geom.ST_Within(sqlalchemy.func.ST_GeomFromGeoJSON(geom))) # Retrieve the filters and build conditions from them if they exist. point_ctree = filters.get(dataset.name + '__filter') # If the user specified point dataset filters, parse and apply them. if point_ctree: point_conditions = parse_tree(dataset, point_ctree) q = q.filter(point_conditions) # To allow both obs_date meta params and filter trees. q = q.filter( dataset.c.point_date >= obs_date__ge) if obs_date__ge else q q = q.filter( dataset.c.point_date <= obs_date__le) if obs_date__le else q # If a user specified a shape dataset, it was either through the /shapes # enpoint, which uses the aggregate result, or through the /detail endpoint # which uses the joined result. if shapeset is not None: if aggregate: q = q.from_self(shapeset).filter( dataset.c.geom.ST_Intersects( shapeset.c.geom)).group_by(shapeset) else: shape_columns = [ '{}.{} as {}'.format(shapeset.name, col.name, col.name) for col in shapeset.c ] q = q.join(shapeset, dataset.c.geom.ST_Within(shapeset.c.geom)) q = q.add_columns(*shape_columns) # If there's a filter specified for the shape dataset, apply those conditions. shape_ctree = filters.get(shapeset.name + '__filter') if shape_ctree: shape_conditions = parse_tree(shapeset, shape_ctree) q = q.filter(shape_conditions) return q
def validate_node(network): if network not in [net.name for net in postgres_session.query(NetworkMeta).all()]: raise ValidationError('Invalid network name!')
def _meta(args): """Generate meta information about table(s) with records from MetaTable. :param args: dictionary of request arguments (?foo=bar) :returns: response dictionary """ meta_params = ('dataset', 'geom', 'obs_date__ge', 'obs_date__le') meta_vals = (args.data.get(k) for k in meta_params) dataset, geom, start_date, end_date = meta_vals # Columns to select as-is cols_to_return = [ 'human_name', 'dataset_name', 'source_url', 'view_url', 'date_added', 'last_update', 'update_freq', 'attribution', 'description', 'obs_from', 'obs_to', 'column_names', 'observed_date', 'latitude', 'longitude', 'location' ] col_objects = [getattr(MetaTable, col) for col in cols_to_return] # Columns that need pre-processing col_objects.append(sqlalchemy.func.ST_AsGeoJSON(MetaTable.bbox)) cols_to_return.append('bbox') # Only return datasets that have been successfully ingested q = postgres_session.query(*col_objects).filter( MetaTable.date_added.isnot(None)) # Filter over datasets if user provides full date range or geom should_filter = geom or (start_date and end_date) if dataset is not None: # If the user specified a name, don't try any filtering. # Just spit back that dataset's metadata. q = q.filter(MetaTable.dataset_name == dataset.name) # Otherwise, just send back all the (filtered) datasets elif should_filter: if geom: intersects = sqlalchemy.func.ST_Intersects( sqlalchemy.func.ST_GeomFromGeoJSON(geom), MetaTable.bbox) q = q.filter(intersects) if start_date and end_date: q = q.filter( sqlalchemy.and_(MetaTable.obs_from < end_date, MetaTable.obs_to > start_date)) metadata_records = [ dict(list(zip(cols_to_return, row))) for row in q.all() ] for record in metadata_records: try: if record.get('bbox') is not None: # serialize bounding box geometry to string record['bbox'] = json.loads(record['bbox']) # format columns in the expected way record['columns'] = [{ 'field_name': k, 'field_type': v } for k, v in list(record['column_names'].items())] except Exception as e: args.warnings.append(e.message) # clear column_names off the json, users don't need to see it del record['column_names'] return metadata_records
def update_shape_view(dataset_name): meta = postgres_session.query(ShapeMetadata).get(dataset_name) ticket = worker.update_shape.delay(dataset_name).id meta.celery_task_id = ticket postgres_session.commit() return redirect(url_for('views.view_datasets'))
def get_by_username(cls, name): return postgres_session.query(cls).filter(cls.name == name).first()
def index(): networks = postgres_session.query(NetworkMeta) return [network.name.lower() for network in networks]