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_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
