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]
