def query_osm(changeset_ids: list, changeset_results):
"""Get data from changesetId."""
id_string = ",".join(map(str, changeset_ids))
url = OSM_API_LINK + f"changesets?changesets={id_string}"
response = retry_get(url)
if response.status_code != 200:
err = f"osm request failed: {response.status_code}"
logger.warning(f"{err}")
logger.warning(response.json())
raise CustomError(err)
tree = ElementTree.fromstring(response.content)
for changeset in tree.iter("changeset"):
id = changeset.attrib["id"]
username = remove_troublesome_chars(changeset.attrib["user"])
userid = changeset.attrib["uid"]
comment = created_by = None
for tag in changeset.iter("tag"):
if tag.attrib["k"] == "comment":
comment = tag.attrib["v"]
if tag.attrib["k"] == "created_by":
created_by = tag.attrib["v"]
changeset_results[int(id)] = {
"username": remove_troublesome_chars(username),
"userid": userid,
"comment": remove_troublesome_chars(comment),
"editor": remove_troublesome_chars(created_by),
}
return changeset_results
def update_groups_table(project_id: str):
"""Remove duplicates in 'project_types_specifics' attribute in groups table."""
logger.info(f"Start process for project: '{project_id}'")
p_con = auth.postgresDB()
query = """
UPDATE groups
SET project_type_specifics = project_type_specifics::jsonb
#- '{projectId}'
#- '{id}'
#- '{requiredCount}'
#- '{finishedCount}'
#- '{neededCount}'
#- '{reportCount}'
#- '{distributedCount}'
WHERE project_id = %(project_id)s
"""
try:
p_con.query(query, {"project_id": project_id})
logger.info(f"Updated tasks table for project '{project_id}'.")
except Exception as e:
sentry.capture_exception(e)
sentry.capture_message(
f"Could NOT update tasks table for project '{project_id}'.")
logger.exception(e)
logger.warning(
f"Could NOT update tasks table for project '{project_id}'.")
def remove_timestamps(uid):
"""Remove timestamps from user contributions in Firebase."""
fb_db = firebaseDB()
try:
ref = fb_db.reference(f"v2/users/{uid}/contributions")
user_contributions = ref.get()
if not user_contributions:
logger.info(f"user {uid} has no contributions in firebase.")
else:
for project_id in user_contributions.keys():
for key in user_contributions[project_id].keys():
if key == "taskContributionCount":
continue
user_contributions[project_id][key] = True
ref.update(user_contributions)
logger.info(f"updated user contributions for user {uid}")
except ValueError:
logger.warning(
f"could not remove timestamps for user {uid} in firebase.")
def query_osmcha(changeset_ids: list, changeset_results):
"""Get data from changesetId."""
id_string = ",".join(map(str, changeset_ids))
url = OSMCHA_API_LINK + f"changesets/?ids={id_string}"
response = retry_get(url, to_osmcha=True)
if response.status_code != 200:
err = f"osmcha request failed: {response.status_code}"
logger.warning(f"{err}")
logger.warning(response.json())
raise CustomError(err)
response = response.json()
for feature in response["features"]:
changeset_results[int(feature["id"])] = {
"username":
remove_troublesome_chars(feature["properties"]["user"]),
"userid": feature["properties"]["uid"],
"comment":
remove_troublesome_chars(feature["properties"]["comment"]),
"editor":
remove_troublesome_chars(feature["properties"]["editor"]),
}
return changeset_results
def ohsome(request: dict, area: str, properties=None) -> dict:
"""Request data from Ohsome API."""
url = OHSOME_API_LINK + request["endpoint"]
data = {"bpolys": area, "filter": request["filter"]}
if properties:
data["properties"] = properties
logger.info("Target: " + url)
logger.info("Filter: " + request["filter"])
response = requests.post(url, data=data)
if response.status_code != 200:
err = f"ohsome request failed: {response.status_code}"
logger.warning(
f"{err} - check for errors in filter or geometries - {request['filter']}"
)
logger.warning(response.json())
raise CustomError(err)
else:
logger.info("Query succesfull.")
response = response.json()
if properties:
response = remove_noise_and_add_user_info(response)
return response
def results_to_file(results, projectId):
"""
Writes results to an in-memory file like object
formatted as a csv using the buffer module (StringIO).
This can be then used by the COPY statement of Postgres
for a more efficient import of many results into the Postgres
instance.
Parameters
----------
results: dict
The results as retrived from the Firebase Realtime Database instance.
Returns
-------
results_file: io.StingIO
The results in an StringIO buffer.
"""
# If csv file is a file object, it should be opened with newline=''
results_file = io.StringIO("")
w = csv.writer(results_file, delimiter="\t", quotechar="'")
logger.info(f"Got %s groups for project {projectId} to transfer" %
len(results.items()))
for groupId, users in results.items():
for userId, results in users.items():
# check if all attributes are set,
# if not don't transfer the results for this group
try:
start_time = results["startTime"]
end_time = results["endTime"]
results = results["results"]
except KeyError as e:
sentry.capture_exception(e)
sentry.capture_message(
f"at least one missing attribute for: "
f"{projectId}/{groupId}/{userId}, will skip this one")
logger.exception(e)
logger.warning(
f"at least one missing attribute for: "
f"{projectId}/{groupId}/{userId}, will skip this one")
continue
start_time = dateutil.parser.parse(start_time)
end_time = dateutil.parser.parse(end_time)
timestamp = end_time
if type(results) is dict:
for taskId, result in results.items():
w.writerow([
projectId,
groupId,
userId,
taskId,
timestamp,
start_time,
end_time,
result,
])
elif type(results) is list:
# TODO: optimize for performance
# (make sure data from firebase is always a dict)
# if key is a integer firebase will return a list
# if first key (list index) is 5
# list indicies 0-4 will have value None
for taskId, result in enumerate(results):
if result is None:
continue
else:
w.writerow([
projectId,
groupId,
userId,
taskId,
timestamp,
start_time,
end_time,
result,
])
else:
raise TypeError
results_file.seek(0)
return results_file
def validate_geometries(self):
raw_input_file = (
f"{DATA_PATH}/"
f"input_geometries/raw_input_{self.projectId}.geojson")
valid_input_file = (
f"{DATA_PATH}/"
f"input_geometries/valid_input_{self.projectId}.geojson")
if not os.path.isdir("{}/input_geometries".format(DATA_PATH)):
os.mkdir("{}/input_geometries".format(DATA_PATH))
# download file from given url
url = self.inputGeometries
urllib.request.urlretrieve(url, raw_input_file)
logger.info(f"{self.projectId}"
f" - __init__ - "
f"downloaded input geometries from url and saved as file: "
f"{raw_input_file}")
self.inputGeometries = raw_input_file
# open the raw input file and get layer
driver = ogr.GetDriverByName("GeoJSON")
datasource = driver.Open(raw_input_file, 0)
try:
layer = datasource.GetLayer()
LayerDefn = layer.GetLayerDefn()
except AttributeError:
raise CustomError("Value error in input geometries file")
# create layer for valid_input_file to store all valid geometries
outDriver = ogr.GetDriverByName("GeoJSON")
# Remove output geojson if it already exists
if os.path.exists(valid_input_file):
outDriver.DeleteDataSource(valid_input_file)
outDataSource = outDriver.CreateDataSource(valid_input_file)
outLayer = outDataSource.CreateLayer("geometries",
geom_type=ogr.wkbMultiPolygon)
for i in range(0, LayerDefn.GetFieldCount()):
fieldDefn = LayerDefn.GetFieldDefn(i)
outLayer.CreateField(fieldDefn)
outLayerDefn = outLayer.GetLayerDefn()
# check if raw_input_file layer is empty
if layer.GetFeatureCount() < 1:
err = "empty file. No geometries provided"
# TODO: How to user logger and exceptions?
logger.warning(f"{self.projectId} - check_input_geometry - {err}")
raise Exception(err)
# get geometry as wkt
# get the bounding box/ extent of the layer
extent = layer.GetExtent()
# Create a Polygon from the extent tuple
ring = ogr.Geometry(ogr.wkbLinearRing)
ring.AddPoint(extent[0], extent[2])
ring.AddPoint(extent[1], extent[2])
ring.AddPoint(extent[1], extent[3])
ring.AddPoint(extent[0], extent[3])
ring.AddPoint(extent[0], extent[2])
poly = ogr.Geometry(ogr.wkbPolygon)
poly.AddGeometry(ring)
wkt_geometry = poly.ExportToWkt()
# check if the input geometry is a valid polygon
for feature in layer:
feat_geom = feature.GetGeometryRef()
geom_name = feat_geom.GetGeometryName()
fid = feature.GetFID
if not feat_geom.IsValid():
layer.DeleteFeature(fid)
logger.warning(f"{self.projectId}"
f" - check_input_geometries - "
f"deleted invalid feature {fid}")
# we accept only POLYGON or MULTIPOLYGON geometries
elif geom_name != "POLYGON" and geom_name != "MULTIPOLYGON":
layer.DeleteFeature(fid)
logger.warning(f"{self.projectId}"
f" - check_input_geometries - "
f"deleted non polygon feature {fid}")
else:
# Create output Feature
outFeature = ogr.Feature(outLayerDefn)
# Add field values from input Layer
for i in range(0, outLayerDefn.GetFieldCount()):
outFeature.SetField(
outLayerDefn.GetFieldDefn(i).GetNameRef(),
feature.GetField(i))
outFeature.SetGeometry(feat_geom)
outLayer.CreateFeature(outFeature)
outFeature = None
# check if layer is empty
if layer.GetFeatureCount() < 1:
err = "no geometries left after checking validity and geometry type."
logger.warning(f"{self.projectId} - check_input_geometry - {err}")
raise Exception(err)
del datasource
del outDataSource
del layer
self.validInputGeometries = valid_input_file
logger.info(f"{self.projectId}"
f" - check_input_geometry - "
f"filtered correct input geometries and created file: "
f"{valid_input_file}")
return wkt_geometry
def validate_geometries(self):
raw_input_file = (f"{DATA_PATH}/input_geometries/"
f"raw_input_{self.projectId}.geojson")
# check if a 'data' folder exists and create one if not
if not os.path.isdir("{}/input_geometries".format(DATA_PATH)):
os.mkdir("{}/input_geometries".format(DATA_PATH))
# write string to geom file
with open(raw_input_file, "w") as geom_file:
json.dump(self.geometry, geom_file)
driver = ogr.GetDriverByName("GeoJSON")
datasource = driver.Open(raw_input_file, 0)
try:
layer = datasource.GetLayer()
except AttributeError:
logger.warning(f"{self.projectId}"
f" - validate geometry - "
f"Could not get layer for datasource")
raise CustomError("could not get layer for datasource")
# check if layer is empty
if layer.GetFeatureCount() < 1:
logger.warning(f"{self.projectId}"
f" - validate geometry - "
f"Empty file. "
f"No geometry is provided.")
raise CustomError("Empty file. ")
# check if more than 1 geometry is provided
elif layer.GetFeatureCount() > MAX_INPUT_GEOMETRIES:
logger.warning(
f"{self.projectId}"
f" - validate geometry - "
f"Input file contains more than {MAX_INPUT_GEOMETRIES} geometries. "
f"Make sure to provide less than {MAX_INPUT_GEOMETRIES} geometries."
)
raise CustomError(
f"Input file contains more than {MAX_INPUT_GEOMETRIES} geometries. "
)
project_area = 0
geometry_collection = ogr.Geometry(ogr.wkbMultiPolygon)
# check if the input geometry is a valid polygon
for feature in layer:
feat_geom = feature.GetGeometryRef()
geom_name = feat_geom.GetGeometryName()
# add geometry to geometry collection
if geom_name == "MULTIPOLYGON":
for singlepart_polygon in feat_geom:
geometry_collection.AddGeometry(singlepart_polygon)
if geom_name == "POLYGON":
geometry_collection.AddGeometry(feat_geom)
if not feat_geom.IsValid():
logger.warning(f"{self.projectId}"
f" - validate geometry - "
f"Geometry is not valid: {geom_name}. "
f"Tested with IsValid() ogr method. "
f"Probably self-intersections.")
raise CustomError(f"Geometry is not valid: {geom_name}. ")
# we accept only POLYGON or MULTIPOLYGON geometries
if geom_name != "POLYGON" and geom_name != "MULTIPOLYGON":
logger.warning(f"{self.projectId}"
f" - validate geometry - "
f"Invalid geometry type: {geom_name}. "
f'Please provide "POLYGON" or "MULTIPOLYGON"')
raise CustomError(f"Invalid geometry type: {geom_name}. ")
# check size of project make sure its smaller than 5,000 sqkm
# for doing this we transform the geometry
# into Mollweide projection (EPSG Code 54009)
source = feat_geom.GetSpatialReference()
target = osr.SpatialReference()
target.ImportFromProj4(
"+proj=moll +lon_0=0 +x_0=0 +y_0=0 +datum=WGS84 +units=m +no_defs"
)
transform = osr.CoordinateTransformation(source, target)
feat_geom.Transform(transform)
project_area = +feat_geom.GetArea() / 1000000
# calculate max area based on zoom level
# for zoom level 18 this will be 5000 square kilometers
# max zoom level is 22
if self.zoomLevel > 22:
raise CustomError(
f"zoom level is to large (max: 22): {self.zoomLevel}.")
max_area = (23 - int(self.zoomLevel)) * (23 -
int(self.zoomLevel)) * 200
if project_area > max_area:
logger.warning(
f"{self.projectId}"
f" - validate geometry - "
f"Project is to large: {project_area} sqkm. "
f"Please split your projects into smaller sub-projects and resubmit"
)
raise CustomError(
f"Project is to large: {project_area} sqkm. "
f"Max area for zoom level {self.zoomLevel} = {max_area} sqkm")
del datasource
del layer
self.validInputGeometries = raw_input_file
logger.info(f"{self.projectId}"
f" - validate geometry - "
f"input geometry is correct.")
dissolved_geometry = geometry_collection.UnionCascaded()
wkt_geometry_collection = dissolved_geometry.ExportToWkt()
return wkt_geometry_collection
def remove_noise_and_add_user_info(json: dict) -> dict:
"""Delete unwanted information from properties."""
logger.info("starting filtering and adding extra info")
batch_size = 100
# remove noise
changeset_results = {}
missing_rows = {
"@changesetId": 0,
"@lastEdit": 0,
"@osmId": 0,
"@version": 0,
}
for feature in json["features"]:
new_properties = {}
for attribute in missing_rows.keys():
try:
new_properties[attribute.replace(
"@", "")] = feature["properties"][attribute]
except KeyError:
missing_rows[attribute] += 1
changeset_results[new_properties["changesetId"]] = None
feature["properties"] = new_properties
# add info
len_osm = len(changeset_results.keys())
batches = int(len(changeset_results.keys()) / batch_size) + 1
logger.info(
f"""{len_osm} changesets will be queried in roughly {batches} batches from osmCHA""" # noqa E501
)
chunk_list = chunks(list(changeset_results.keys()), batch_size)
for i, subset in enumerate(chunk_list):
changeset_results = query_osmcha(subset, changeset_results)
progress = round(100 * ((i + 1) / len(chunk_list)), 1)
logger.info(f"finished query {i+1}/{len(chunk_list)}, {progress}")
missing_ids = [i for i, v in changeset_results.items() if v is None]
chunk_list = chunks(missing_ids, batch_size)
batches = int(len(missing_ids) / batch_size) + 1
logger.info(
f"""{len(missing_ids)} changesets where missing from osmCHA and are now queried via osmAPI in {batches} batches""" # noqa E501
)
for i, subset in enumerate(chunk_list):
changeset_results = query_osm(subset, changeset_results)
progress = round(100 * ((i + 1) / len(chunk_list)), 1)
logger.info(f"finished query {i+1}/{len(chunk_list)}, {progress}")
for feature in json["features"]:
changeset = changeset_results[int(
feature["properties"]["changesetId"])]
for attribute_name in ["username", "comment", "editor", "userid"]:
if attribute_name == "userid":
feature["properties"][attribute_name] = int(
changeset[attribute_name])
else:
feature["properties"][attribute_name] = changeset[
attribute_name]
logger.info("finished filtering and adding extra info")
if any(x > 0 for x in missing_rows.values()):
logger.warning(f"features missing values:\n{missing_rows}")
return json
def transfer_results_for_project(project_id, results, filter_mode: bool = False):
"""Transfer the results for a specific project.
Save results into an in-memory file.
Copy the results to postgres.
Delete results in firebase.
We are NOT using a Firebase transaction functions here anymore.
This has caused problems, in situations where a lot of mappers are
uploading results to Firebase at the same time. Basically, this is
due to the behaviour of Firebase Transaction function:
"If another client writes to this location
before the new value is successfully saved,
the update function is called again with the new current value,
and the write will be retried."
(source: https://firebase.google.com/docs/reference/admin/python/firebase_admin.db#firebase_admin.db.Reference.transaction) # noqa
Using Firebase transaction on the group level
has turned out to be too slow when using "normal" queries,
e.g. without using threading. Threading should be avoided here
as well to not run into unforeseen errors.
For more details see issue #478.
"""
if results is None:
logger.info(f"{project_id}: No results in Firebase")
else:
# First we check for new users in Firebase.
# The user_id is used as a key in the postgres database for the results
# and thus users need to be inserted before results get inserted.
results_user_id_list = get_user_ids_from_results(results)
update_data.update_user_data(results_user_id_list)
try:
# Results are dumped into an in-memory file.
# This allows us to use the COPY statement to insert many
# results at relatively high speed.
results_file = results_to_file(results, project_id)
truncate_temp_results()
save_results_to_postgres(results_file, project_id, filter_mode=filter_mode)
except psycopg2.errors.ForeignKeyViolation as e:
sentry.capture_exception(e)
sentry.capture_message(
"could not transfer results to postgres due to ForeignKeyViolation: "
f"{project_id}; filter_mode={filter_mode}"
)
logger.exception(e)
logger.warning(
"could not transfer results to postgres due to ForeignKeyViolation: "
f"{project_id}; filter_mode={filter_mode}"
)
# There is an exception where additional invalid tasks are in a group.
# If that happens we arrive here and add the flag filtermode=true
# to this function, which could solve the issue in save_results_to_postgres.
# If it does not solve the issue we arrive again but
# since filtermode is already true, we will not try to transfer results again.
if not filter_mode:
transfer_results_for_project(project_id, results, filter_mode=True)
except Exception as e:
sentry.capture_exception(e)
sentry.capture_message(f"could not transfer results to postgres: {project_id}")
logger.exception(e)
logger.warning(f"could not transfer results to postgres: {project_id}")
else:
# It is important here that we first insert results into postgres
# and then delete these results from Firebase.
# In case something goes wrong during the insert, results in Firebase
# will not get deleted.
delete_results_from_firebase(project_id, results)
logger.info(f"{project_id}: Transferred results to postgres")