def get_geojson_cyclone(request, iso_alpha3=None): collection = None with GeoDashDatabaseConnection() as geodash_conn: # Admin 2 Districts collection = data_local_country_admin().get(cursor=geodash_conn.cursor, iso_alpha3=iso_alpha3, level=2) # Vam Data vam_by_admin1 = get_vam_by_admin1(request, iso_alpha3=iso_alpha3) # Population at Risk Data rows_popatrisk = geodash_conn.exec_query_multiple( get_template("sparc2/sql/_cyclone.sql").render({ 'admin2_popatrisk': 'cyclone.admin2_popatrisk', 'iso_alpha3': iso_alpha3 })) popatrisk_by_admin2 = {} for row in rows_popatrisk: newRow = json.loads(row[0]) if (type(row[0]) is not dict) else row[0] admin2_code = newRow.pop(u"admin2_code", None) newRow.pop(u"iso3", None) if admin2_code not in popatrisk_by_admin2: popatrisk_by_admin2[admin2_code] = [] popatrisk_by_admin2[admin2_code].append(newRow) context_by_admin2 = get_context_by_admin2(geodash_conn=geodash_conn, iso_alpha3=iso_alpha3) for feature in collection["features"]: admin1_code = str(feature["properties"]["admin1_code"]) admin2_code = str(feature["properties"]["admin2_code"]) if admin2_code in context_by_admin2: feature["properties"]["ldi"] = context_by_admin2[admin2_code][ "ldi"] feature["properties"]["delta_negative"] = context_by_admin2[ admin2_code]["delta_negative"] feature["properties"][ "erosion_propensity"] = context_by_admin2[admin2_code][ "erosion_propensity"] feature["properties"].update({ "FCS": 0, "FCS_border": 0, "FCS_acceptable": 0, "CSI_no": 0, "CSI_low": 0, "CSI_med": 0, "CSI_high": 0 }) feature["properties"]["addinfo"] = popatrisk_by_admin2[ admin2_code] if admin2_code in popatrisk_by_admin2 else [] if admin1_code in vam_by_admin1: feature["properties"].update(vam_by_admin1[admin1_code]) return collection
def get_geojson_flood(request, iso_alpha3=None): collection = None with GeoDashDatabaseConnection() as geodash_conn: # Admin 2 Districts collection = data_local_country_admin().get(cursor=geodash_conn.cursor, iso_alpha3=iso_alpha3, level=2) # Vam Data vam_by_admin1 = get_vam_by_admin1(request, iso_alpha3=iso_alpha3) # Population at Risk Data returnPeriods = [25, 50, 100, 200, 500, 1000] for rp in returnPeriods: rows = geodash_conn.exec_query_multiple( get_template("sparc2/sql/_flood_data_by_admin2_rp_month_asjson.sql").render({ 'admin2_popatrisk': 'flood.admin2_popatrisk', 'iso_alpha3': iso_alpha3, 'rp': rp})) values_by_admin2 = {} for row in rows: admin2_code, data = row data.pop(u"admin2_code") values_by_admin2[str(admin2_code)] = data context_by_admin2 = get_context_by_admin2(geodash_conn=geodash_conn, iso_alpha3=iso_alpha3) for feature in collection["features"]: admin1_code = str(feature["properties"]["admin1_code"]) admin2_code = str(feature["properties"]["admin2_code"]) values = values_by_admin2.get(admin2_code, None) if values: feature["properties"]["RP"+str(rp)] = values else: feature["properties"]["RP"+str(rp)] = {x: 0 for x in MONTHS_SHORT3} if admin2_code in context_by_admin2: feature["properties"]["ldi"] = context_by_admin2[admin2_code]["ldi"] feature["properties"]["delta_mean"] = context_by_admin2[admin2_code]["delta_mean"] feature["properties"]["delta_positive"] = context_by_admin2[admin2_code]["delta_positive"] feature["properties"]["delta_negative"] = context_by_admin2[admin2_code]["delta_negative"] feature["properties"]["delta_crop"] = context_by_admin2[admin2_code]["delta_crop"] feature["properties"]["delta_forest"] = context_by_admin2[admin2_code]["delta_forest"] feature["properties"]["erosion_propensity"] = context_by_admin2[admin2_code]["erosion_propensity"] if admin1_code in vam_by_admin1: feature["properties"].update(vam_by_admin1[admin1_code]) return collection
def get_geojson_context(request, iso_alpha3=None): collection = None with GeoDashDatabaseConnection() as geodash_conn: collection = data_local_country_admin().get(cursor=geodash_conn.cursor, iso_alpha3=iso_alpha3, level=2) rows_context = geodash_conn.exec_query_multiple( get_template("sparc2/sql/_context.sql").render({ 'admin2_context': 'context.admin2_context', 'iso_alpha3': iso_alpha3})) if "features" in collection: for feature in collection["features"]: for row_context in rows_context: json_context = json.loads(row_context[0]) if (type(row_context[0]) is not dict) else row_context[0] if int(json_context["admin2_code"]) == feature["properties"]["admin2_code"]: feature["properties"].update(json_context) return collection
def get_geojson_cyclone(request, iso_alpha3=None): collection = None with GeoDashDatabaseConnection() as geodash_conn: # Admin 2 Districts collection = data_local_country_admin().get(cursor=geodash_conn.cursor, iso_alpha3=iso_alpha3, level=2) # Vam Data vam_by_admin1 = get_vam_by_admin1(request, iso_alpha3=iso_alpha3) # Population at Risk Data rows_popatrisk = geodash_conn.exec_query_multiple( get_template("sparc2/sql/_cyclone.sql").render({ 'admin2_popatrisk': 'cyclone.admin2_popatrisk', 'iso_alpha3': iso_alpha3})) popatrisk_by_admin2 = {} for row in rows_popatrisk: newRow = json.loads(row[0]) if (type(row[0]) is not dict) else row[0] admin2_code = newRow.pop(u"admin2_code", None); newRow.pop(u"iso3", None) if admin2_code not in popatrisk_by_admin2: popatrisk_by_admin2[admin2_code] = [] popatrisk_by_admin2[admin2_code].append(newRow) context_by_admin2 = get_context_by_admin2(geodash_conn=geodash_conn, iso_alpha3=iso_alpha3) for feature in collection["features"]: admin1_code = str(feature["properties"]["admin1_code"]) admin2_code = str(feature["properties"]["admin2_code"]) if admin2_code in context_by_admin2: feature["properties"]["ldi"] = context_by_admin2[admin2_code]["ldi"] feature["properties"]["delta_negative"] = context_by_admin2[admin2_code]["delta_negative"] feature["properties"]["erosion_propensity"] = context_by_admin2[admin2_code]["erosion_propensity"] feature["properties"].update({ "FCS": 0, "FCS_border": 0, "FCS_acceptable": 0, "CSI_no": 0, "CSI_low": 0, "CSI_med": 0, "CSI_high": 0 }) feature["properties"]["addinfo"] = popatrisk_by_admin2[admin2_code] if admin2_code in popatrisk_by_admin2 else []; if admin1_code in vam_by_admin1: feature["properties"].update(vam_by_admin1[admin1_code]) return collection
def get_geojson_vam(request, iso_alpha3=None): collection = None with GeoDashDatabaseConnection() as geodash_conn: collection = data_local_country_admin().get(cursor=geodash_conn.cursor, iso_alpha3=iso_alpha3, level=1) for feature in collection["features"]: response = requests.get(url=URL_VAM["FCS"].format( admin0=feature["properties"]["admin0_code"], admin1=feature["properties"]["admin1_code"])) vam_data_fcs = response.json() response = requests.get(url=URL_VAM["CSI"].format( admin0=feature["properties"]["admin0_code"], admin1=feature["properties"]["admin1_code"])) vam_data_csi = response.json() vam = {} if vam_data_fcs: vam_data_fcs = vam_data_fcs[0] vam["fcs"] = { "year": vam_data_fcs["FCS_year"], "month": vam_data_fcs["FCS_month"], "source": vam_data_fcs["FCS_dataSource"], "poor": vam_data_fcs["FCS_poor"], "borderline": vam_data_fcs["FCS_borderline"], "acceptable": vam_data_fcs["FCS_acceptable"] } if vam_data_csi: vam_data_csi = vam_data_csi[0] vam["csi"] = { "year": vam_data_csi["CSI_rYear"], "month": vam_data_csi["CSI_rMonth"], "source": vam_data_csi["CSI_rDataSource"], "no": vam_data_csi["CSI_rNoCoping"], "low": vam_data_csi["CSI_rLowCoping"], "medium": vam_data_csi["CSI_rMediumCoping"], "high": vam_data_csi["CSI_rHighCoping"] } feature["properties"]["vam"] = vam return collection
def get_geojson_context(request, iso_alpha3=None): collection = None with GeoDashDatabaseConnection() as geodash_conn: collection = data_local_country_admin().get(cursor=geodash_conn.cursor, iso_alpha3=iso_alpha3, level=2) rows_context = geodash_conn.exec_query_multiple( get_template("sparc2/sql/_context.sql").render({ 'admin2_context': 'context.admin2_context', 'iso_alpha3': iso_alpha3 })) if "features" in collection: for feature in collection["features"]: for row_context in rows_context: json_context = json.loads(row_context[0]) if (type( row_context[0]) is not dict) else row_context[0] if int(json_context["admin2_code"] ) == feature["properties"]["admin2_code"]: feature["properties"].update(json_context) return collection
def get_geojson_landslide(request, iso_alpha3=None): collection = None prob_classes = [ { "code": 1, "label": "low" }, { "code": 2, "label": "medium" }, { "code": 3, "label": "high" }, { "code": 4, "label": "very_high" } ] with GeoDashDatabaseConnection() as geodash_conn: # Admin 2 Districts collection = data_local_country_admin().get(cursor=geodash_conn.cursor, iso_alpha3=iso_alpha3, level=2) # Vam Data vam_by_admin1 = get_vam_by_admin1(request, iso_alpha3=iso_alpha3) # Context Data context_by_admin2 = get_context_by_admin2(geodash_conn=geodash_conn, iso_alpha3=iso_alpha3) # Population at Risk Data popatrisk_by_admin2_probclass_month = {} for prob_class in prob_classes: rows_popatrisk = geodash_conn.exec_query_multiple( get_template("sparc2/sql/_landslide_data_by_admin2_for_probclass.sql").render({ 'admin2_popatrisk': 'landslide.admin2_popatrisk', 'prob_min': str(prob_class["code"]), 'prob_max': str(prob_class["code"]), 'iso_alpha3': iso_alpha3})) for r in rows_popatrisk: admin2_code, month, value = r if admin2_code not in popatrisk_by_admin2_probclass_month: popatrisk_by_admin2_probclass_month[admin2_code] = {} if prob_class["label"] not in popatrisk_by_admin2_probclass_month[admin2_code]: popatrisk_by_admin2_probclass_month[admin2_code][prob_class["label"]] = {} popatrisk_by_admin2_probclass_month[admin2_code][prob_class["label"]][month] = value for feature in collection["features"]: admin1_code = str(feature["properties"]["admin1_code"]) admin2_code = str(feature["properties"]["admin2_code"]) if admin2_code in context_by_admin2: feature["properties"]["ldi"] = context_by_admin2[admin2_code]["ldi"] feature["properties"]["delta_negative"] = context_by_admin2[admin2_code]["delta_negative"] feature["properties"]["erosion_propensity"] = context_by_admin2[admin2_code]["erosion_propensity"] feature["properties"].update({ "FCS": 0, "FCS_border": 0, "FCS_acceptable": 0, "CSI_no": 0, "CSI_low": 0, "CSI_med": 0, "CSI_high": 0 }) if admin1_code in vam_by_admin1: feature["properties"].update(vam_by_admin1[admin1_code]) feature["properties"]["addinfo"] = []; for prob_class in prob_classes: includeX = False x = { "prob_class": prob_class["label"], "prob_class_min": str(prob_class["code"]), "prob_class_max": str(prob_class["code"]) } for month in MONTHS_SHORT3: if admin2_code in popatrisk_by_admin2_probclass_month: if prob_class["label"] in popatrisk_by_admin2_probclass_month[admin2_code]: x[month] = popatrisk_by_admin2_probclass_month[admin2_code][prob_class["label"]].get(month, 0) includeX = True if includeX: feature["properties"]["addinfo"].append(x); return collection
import django django.setup() SLEEP_PERIOD = 0.5 results = {} with GeositeDatabaseConnection() as geosite_conn: q = "SELECT LSIB.iso_alpha3 as iso_alpha3 FROM lsibdjango_geographicthesaurusentry as LSIB LEFT JOIN gauldjango_gauladmin0 as G ON LSIB.gaul = G.admin0_code LEFT JOIN wfppresencedjango_wfpcountry as W ON LSIB.id = W.thesaurus_id WHERE LSIB.iso_alpha3 IS NOT NULL and W.id IS NOT NULL GROUP BY LSIB.iso_alpha3, G.admin0_code, G.admin0_name ORDER BY LSIB.iso_alpha3;" geosite_conn.cursor.execute(q) countries = geosite_conn.cursor.fetchall() for r in countries: iso_alpha3 = r[0] collection = data_local_country_admin().get(cursor=geosite_conn.cursor, iso_alpha3=iso_alpha3, level=1) if collection.get("features", None): for feature in collection.get("features", None): admin0_code = feature["properties"]["admin0_code"] admin0_name = feature["properties"]["admin0_name"] admin1 = feature["properties"]["admin1_code"] hasFCS = False try: response = requests.get(url=URL_VAM["FCS"].format( admin0=admin0_code, admin1=admin1)) vam_data_fcs = response.json() hasFCS = vam_data_fcs and len(vam_data_fcs) > 0 except: hasFCS = False
def get_geojson_landslide(request, iso_alpha3=None): collection = None prob_classes = [{ "code": 1, "label": "low" }, { "code": 2, "label": "medium" }, { "code": 3, "label": "high" }, { "code": 4, "label": "very_high" }] with GeoDashDatabaseConnection() as geodash_conn: # Admin 2 Districts collection = data_local_country_admin().get(cursor=geodash_conn.cursor, iso_alpha3=iso_alpha3, level=2) # Vam Data vam_by_admin1 = get_vam_by_admin1(request, iso_alpha3=iso_alpha3) # Context Data context_by_admin2 = get_context_by_admin2(geodash_conn=geodash_conn, iso_alpha3=iso_alpha3) # Population at Risk Data popatrisk_by_admin2_probclass_month = {} for prob_class in prob_classes: rows_popatrisk = geodash_conn.exec_query_multiple( get_template( "sparc2/sql/_landslide_data_by_admin2_for_probclass.sql"). render({ 'admin2_popatrisk': 'landslide.admin2_popatrisk', 'prob_min': str(prob_class["code"]), 'prob_max': str(prob_class["code"]), 'iso_alpha3': iso_alpha3 })) for r in rows_popatrisk: admin2_code, month, value = r if admin2_code not in popatrisk_by_admin2_probclass_month: popatrisk_by_admin2_probclass_month[admin2_code] = {} if prob_class[ "label"] not in popatrisk_by_admin2_probclass_month[ admin2_code]: popatrisk_by_admin2_probclass_month[admin2_code][ prob_class["label"]] = {} popatrisk_by_admin2_probclass_month[admin2_code][ prob_class["label"]][month] = value for feature in collection["features"]: admin1_code = str(feature["properties"]["admin1_code"]) admin2_code = str(feature["properties"]["admin2_code"]) if admin2_code in context_by_admin2: feature["properties"]["ldi"] = context_by_admin2[admin2_code][ "ldi"] feature["properties"]["delta_negative"] = context_by_admin2[ admin2_code]["delta_negative"] feature["properties"][ "erosion_propensity"] = context_by_admin2[admin2_code][ "erosion_propensity"] feature["properties"].update({ "FCS": 0, "FCS_border": 0, "FCS_acceptable": 0, "CSI_no": 0, "CSI_low": 0, "CSI_med": 0, "CSI_high": 0 }) if admin1_code in vam_by_admin1: feature["properties"].update(vam_by_admin1[admin1_code]) feature["properties"]["addinfo"] = [] for prob_class in prob_classes: includeX = False x = { "prob_class": prob_class["label"], "prob_class_min": str(prob_class["code"]), "prob_class_max": str(prob_class["code"]) } for month in MONTHS_SHORT3: if admin2_code in popatrisk_by_admin2_probclass_month: if prob_class[ "label"] in popatrisk_by_admin2_probclass_month[ admin2_code]: x[month] = popatrisk_by_admin2_probclass_month[ admin2_code][prob_class["label"]].get( month, 0) includeX = True if includeX: feature["properties"]["addinfo"].append(x) return collection
def get_geojson_flood(request, iso_alpha3=None): collection = None with GeoDashDatabaseConnection() as geodash_conn: # Admin 2 Districts collection = data_local_country_admin().get(cursor=geodash_conn.cursor, iso_alpha3=iso_alpha3, level=2) # Vam Data vam_by_admin1 = get_vam_by_admin1(request, iso_alpha3=iso_alpha3) # Population at Risk Data returnPeriods = [25, 50, 100, 200, 500, 1000] for rp in returnPeriods: rows = geodash_conn.exec_query_multiple( get_template( "sparc2/sql/_flood_data_by_admin2_rp_month_asjson.sql"). render({ 'admin2_popatrisk': 'flood.admin2_popatrisk', 'iso_alpha3': iso_alpha3, 'rp': rp })) values_by_admin2 = {} for row in rows: admin2_code, data = row data.pop(u"admin2_code") values_by_admin2[str(admin2_code)] = data context_by_admin2 = get_context_by_admin2( geodash_conn=geodash_conn, iso_alpha3=iso_alpha3) for feature in collection["features"]: admin1_code = str(feature["properties"]["admin1_code"]) admin2_code = str(feature["properties"]["admin2_code"]) values = values_by_admin2.get(admin2_code, None) if values: feature["properties"]["RP" + str(rp)] = values else: feature["properties"]["RP" + str(rp)] = { x: 0 for x in MONTHS_SHORT3 } if admin2_code in context_by_admin2: feature["properties"]["ldi"] = context_by_admin2[ admin2_code]["ldi"] feature["properties"]["delta_mean"] = context_by_admin2[ admin2_code]["delta_mean"] feature["properties"][ "delta_positive"] = context_by_admin2[admin2_code][ "delta_positive"] feature["properties"][ "delta_negative"] = context_by_admin2[admin2_code][ "delta_negative"] feature["properties"]["delta_crop"] = context_by_admin2[ admin2_code]["delta_crop"] feature["properties"]["delta_forest"] = context_by_admin2[ admin2_code]["delta_forest"] feature["properties"][ "erosion_propensity"] = context_by_admin2[admin2_code][ "erosion_propensity"] if admin1_code in vam_by_admin1: feature["properties"].update(vam_by_admin1[admin1_code]) return collection
import django django.setup() SLEEP_PERIOD = 0.5 results = {} with GeositeDatabaseConnection() as geosite_conn: q = "SELECT LSIB.iso_alpha3 as iso_alpha3 FROM lsibdjango_geographicthesaurusentry as LSIB LEFT JOIN gauldjango_gauladmin0 as G ON LSIB.gaul = G.admin0_code LEFT JOIN wfppresencedjango_wfpcountry as W ON LSIB.id = W.thesaurus_id WHERE LSIB.iso_alpha3 IS NOT NULL and W.id IS NOT NULL GROUP BY LSIB.iso_alpha3, G.admin0_code, G.admin0_name ORDER BY LSIB.iso_alpha3;" geosite_conn.cursor.execute(q) countries = geosite_conn.cursor.fetchall() for r in countries: iso_alpha3 = r[0] collection = data_local_country_admin().get(cursor=geosite_conn.cursor, iso_alpha3=iso_alpha3, level=1) if collection.get("features", None): for feature in collection.get("features", None): admin0_code = feature["properties"]["admin0_code"] admin0_name = feature["properties"]["admin0_name"] admin1 = feature["properties"]["admin1_code"] hasFCS = False try: response = requests.get(url=URL_VAM["FCS"].format(admin0=admin0_code, admin1=admin1)) vam_data_fcs = response.json() hasFCS = vam_data_fcs and len(vam_data_fcs) > 0 except: hasFCS = False hasCSI = False