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)