def search_indicators():
"""
A very basic, naive text search for indicators with the following search criteria
- start: start year
- end: end year
- geolocation: location text
- match: matching string
The search returns a listing of distinct indicator names/variables that match the criteria
"""
args = request.get_json()
start = args.get("start")
end = args.get("end")
geolocation = args.get("geolocation")
match = args.get("match")
sql = "SELECT DISTINCT `Variable` from indicator WHERE 1 = 1"
if match is not None:
sql = (
sql +
f" AND (`Variable` LIKE '{match}%' OR `Variable` LIKE '% {match}%')"
) # trying to match prefix
if start is not None:
sql = sql + f" AND `Year` > {start}"
if end is not None:
sql = sql + f" AND `Year` < {end}"
print("Running SQL: ", sql)
records = list(engine.execute(sql))
result = []
for r in records:
result.append(r["Variable"])
return jsonify(result)
def search_concept_indicators():
"""
Given a list of concepts, this endpoint returns their respective matching
indicators. The search parameters are:
- concepts: a list of concepts
- start: start year
- end: end year
- geolocation: geolocation string
"""
args = request.get_json()
concepts = args.get("concepts")
start = args.get("start")
end = args.get("end")
geolocation = args.get("geolocation")
result = {}
for concept in args.get("concepts"):
sql = "SELECT `Concept`, `Source`, `Indicator`, `Score` FROM concept_to_indicator_mapping "
sql = sql + f" WHERE `Concept` = '{concept}'"
mappings = engine.execute(sql)
concept_result = []
for mapping in mappings:
indicator = mapping["Indicator"]
source = mapping["Source"]
score = mapping["Score"]
detail = get_indicator_detail(indicator, start, end, geolocation)
concept_result.append({
"name": indicator,
"score": score,
"source": source,
"value": detail,
})
result[concept] = concept_result
return jsonify(result)
def listAllModels():
""" Return UUIDs for all the models in the database. """
query = ("SELECT name FROM sqlite_master WHERE type='table' "
"AND name='icmmetadata'")
if list(engine.execute(query)) == []:
return jsonify([])
else:
return jsonify([metadata.id for metadata in ICMMetadata.query.all()])
def listAllICMs():
""" List all ICMs"""
if (list(
engine.execute("SELECT name FROM sqlite_master WHERE type='table' "
"AND name='icmmetadata'")) == []):
return jsonify([])
else:
ids = [metadata.id for metadata in ICMMetadata.query.all()]
ids.reverse()
return jsonify(ids)
def get_indicator_detail(indicator, start, end, geolocation):
"""
Helper method to return raw indicator data, applying the following filters
- indicator: indicator string
- start: start yaer
- end: end year
- geolocation: geolocation string
"""
indicator = indicator.replace("'", "''")
sql = "SELECT * from indicator WHERE 1 = 1"
if start is not None:
sql = sql + f" AND `Year` > {start}"
if end is not None:
sql = sql + f" AND `Year` < {end}"
sql = sql + f" AND `Variable` = '{indicator}'"
records = list(engine.execute(sql))
result = {}
for r in records:
unit, value, year, month, source = (
r["Unit"],
r["Value"],
r["Year"],
r["Month"],
r["Source"],
)
value = float(re.findall(r"-?\d+\.?\d*", value)[0])
if unit is None:
unit = PLACEHOLDER_UNIT
_dict = {
"year": year,
"month": month,
"value": float(value),
"source": source,
}
if unit not in result:
result[unit] = []
result[unit].append(_dict)
return result
def getIndicators():
"""
Given a list of concepts, this endpoint returns their respective matching
indicators. The search parameters are:
- start/end: To specify search criteria in years (YYYY)
- geolocation: To match indicators with matching geolocation
- func: To apply a transform function onto the raw indicator values
- concepts: List of concepts
- outputResolution: month/year
The search will return a listing of matching indicators, sorted by
similarity score. For each concept a maximum of 10 indicator matches will
be returned. If there are no matches for a given concept an empty array is
returned.
"""
args = request.get_json()
func_dict = {
"mean": mean,
"median": median,
"max": max,
"min": min,
"raw": lambda x: x,
}
output_dict = {}
for concept in args.get("concepts"):
output_dict[concept] = []
query = ("select `Concept`, `Source`, `Indicator`, `Score` "
"from concept_to_indicator_mapping "
f"where `Concept` like '{concept}'")
for indicator_mapping in engine.execute(query):
variable_name = indicator_mapping["Indicator"].replace("'", "''")
query_parts = [
f"select * from indicator",
f"where `Variable` like '{variable_name}'",
]
outputResolution = args.get("outputResolution")
start = args.get("start")
end = args.get("end")
func = args.get("func", "raw")
if outputResolution is not None:
query_parts.append(f"and `{outputResolution}` is not null")
if start is not None:
query_parts.append(f"and `Year` > {start}")
if end is not None:
query_parts.append(f"and `Year` < {end}")
records = list(engine.execute(" ".join(query_parts)))
value_dict = {}
source = "Unknown"
if func == "raw":
for r in records:
unit, value, year, month, source = (
r["Unit"],
r["Value"],
r["Year"],
r["Month"],
r["Source"],
)
value = float(re.findall(r"-?\d+\.?\d*", value)[0])
# Sort of a hack - some of the variables in the tables we
# process don't have units specified, so we put a
# placeholder string to get it to work with CauseMos.
if unit is None:
unit = PLACEHOLDER_UNIT
_dict = {
"year": year,
"month": month,
"value": float(value),
"source": source,
}
if unit not in value_dict:
value_dict[unit] = [_dict]
else:
value_dict[unit].append(_dict)
value = value_dict
else:
for r in records:
unit, value, source = r["Unit"], r["Value"], r["Source"]
if unit is None:
unit = PLACEHOLDER_UNIT
value = float(re.findall(r"-?\d+\.?\d*", value)[0])
# HACK! if the variables have the same names but different
# sources, this will only give the most recent source
if unit not in value_dict:
value_dict[unit] = [value]
else:
value_dict[unit].append(value)
value = {
unit: func_dict[func](lmap(float, values))
for unit, values in value_dict.items()
}
output_dict[concept].append({
"name": indicator_mapping["Indicator"],
"score": indicator_mapping["Score"],
"value": value,
"source": source,
})
return jsonify(output_dict)