def create_combined_wh_nomination_view(input_nomination, WH_SHAPE, output_schema, conn):
# not table always be schema + table
sql_create = """
CREATE OR REPLACE VIEW %s AS
SELECT wdpaid, en_name, shape as geom
FROM %s
UNION
SELECT wdpaid, name, shape as geom
FROM %s
"""%(output_schema + '.' + COMBINED_WH_NOMINATION_VIEW, WH_SHAPE, input_nomination)
process_sql(sql_create, conn)
def correct_result_base10():
# get connection
conn = CONN_PARAM.getConn()
# create sql for country base stats and run
sql = create_sql_country_stats_no_year('ap_base_result_10', 'ap_country_lookup', 'ap_base_country_10')
process_sql(sql, conn)
# create sql for country base stats and run
sql = create_sql_country_stats_no_year('wa_base_result_10', 'wa_country_lookup', 'wa_base_country_10')
process_sql(sql, conn)
# close
conn.close()
def create_combined_wh_nomination_view(input_nomination, WH_SHAPE,
output_schema, conn):
# not table always be schema + table
sql_create = """
CREATE OR REPLACE VIEW %s AS
SELECT wdpaid, en_name, shape as geom
FROM %s
UNION
SELECT wdpaid, name, shape as geom
FROM %s
""" % (output_schema + '.' + COMBINED_WH_NOMINATION_VIEW, WH_SHAPE,
input_nomination)
process_sql(sql_create, conn)
def clean_view(schema_to_clean, conn_arg=get_ca_conn_arg(2015)):
"""
this function is used to clear ALL views in the given schema
"""
sql = """
SELECT table_name FROM information_schema.tables WHERE table_schema = '%s'
"""%(schema_to_clean, )
with conn_arg.getConn() as conn:
views = get_sql_result_as_list(sql, conn)
for view in views:
sql = """
DROP VIEW IF EXISTS %s CASCADE
"""%(schema_to_clean + '.' + view,)
process_sql(sql, conn)
def clean_view(schema_to_clean, conn_arg=get_ca_conn_arg(2015)):
"""
this function is used to clear ALL views in the given schema
"""
sql = """
SELECT table_name FROM information_schema.tables WHERE table_schema = '%s'
""" % (schema_to_clean, )
with conn_arg.getConn() as conn:
views = get_sql_result_as_list(sql, conn)
for view in views:
sql = """
DROP VIEW IF EXISTS %s CASCADE
""" % (schema_to_clean + '.' + view, )
process_sql(sql, conn)
def correct_result_base10():
# get connection
conn = CONN_PARAM.getConn()
# create sql for country base stats and run
sql = create_sql_country_stats_no_year('ap_base_result_10',
'ap_country_lookup',
'ap_base_country_10')
process_sql(sql, conn)
# create sql for country base stats and run
sql = create_sql_country_stats_no_year('wa_base_result_10',
'wa_country_lookup',
'wa_base_country_10')
process_sql(sql, conn)
# close
conn.close()
def post_intersection_mk2(conn_arg, intersect, output, nomi_id):
""" this function first filters the intersect table by its wdpaid """
conn = conn_arg.getConn()
whereclause = 'tid = ' + str(nomi_id)
header = "CREATE OR REPLACE VIEW %s AS " % (output.s_name)
body = """
SELECT * FROM %s
WHERE (bid in (SELECT bid FROM %s WHERE %s))
ORDER BY bid""" % (intersect.s_name, intersect.s_name, whereclause)
sql = header + body
process_sql(sql, conn)
conn.close()
def run_ca_tentative(conn_arg=get_ca_conn_arg()):
"""
create tentative list simple comparartive stats against bases
"""
for themekey in BASE_LOOKUP.keys():
themename, fieldname = BASE_LOOKUP[themekey]
intersect_table_name = get_intersectname(themekey)
with conn_arg.getConn() as conn:
sql= """
CREATE OR REPLACE VIEW %s AS
SELECT id as tid, base.%s as bid
FROM %s, %s as base
WHERE st_intersects(shape, geom)
"""%(TLS_SCHEMA + '.' + intersect_table_name, fieldname, TLS_SHAPE, BASEL_TABLE_SCHEMA + '.' + themename)
process_sql(sql, conn)
def post_intersection_mk2(conn_arg, intersect, output, nomi_id):
""" this function first filters the intersect table by its wdpaid """
conn = conn_arg.getConn()
whereclause = 'tid = ' + str(nomi_id)
header = "CREATE OR REPLACE VIEW %s AS "%(output.s_name)
body = """
SELECT * FROM %s
WHERE (bid in (SELECT bid FROM %s WHERE %s))
ORDER BY bid"""%(intersect.s_name,
intersect.s_name,
whereclause)
sql = header + body
process_sql(sql, conn)
conn.close()
def run_ca_tentative(conn_arg=get_ca_conn_arg()):
"""
create tentative list simple comparartive stats against bases
"""
for themekey in BASE_LOOKUP.keys():
themename, fieldname = BASE_LOOKUP[themekey]
intersect_table_name = get_intersectname(themekey)
with conn_arg.getConn() as conn:
sql = """
CREATE OR REPLACE VIEW %s AS
SELECT id as tid, base.%s as bid
FROM %s, %s as base
WHERE st_intersects(shape, geom)
""" % (TLS_SCHEMA + '.' + intersect_table_name, fieldname,
TLS_SHAPE, BASEL_TABLE_SCHEMA + '.' + themename)
process_sql(sql, conn)
def lac():
conn = CONN_PARAM.getConn()
# create sql for country loss stats and run
sql = create_sql_country_stats('result_loss', 'lac_teow_v2', 'lac_loss_country')
process_sql(sql, conn)
# create sql for loss year stats
sql = create_sql_loss_year_stats('result_loss', 'lac_loss_year')
process_sql(sql, conn)
# create sql for country base stats and run
sql = create_sql_country_stats_no_year('result_base10', 'lac_teow_v2', 'lac_base_country')
process_sql(sql, conn)
# close
conn.close()
def lac():
conn = CONN_PARAM.getConn()
# create sql for country loss stats and run
sql = create_sql_country_stats('result_loss', 'lac_teow_v2',
'lac_loss_country')
process_sql(sql, conn)
# create sql for loss year stats
sql = create_sql_loss_year_stats('result_loss', 'lac_loss_year')
process_sql(sql, conn)
# create sql for country base stats and run
sql = create_sql_country_stats_no_year('result_base10', 'lac_teow_v2',
'lac_base_country')
process_sql(sql, conn)
# close
conn.close()
def main():
# get connection
conn = CONN_PARAM.getConn()
# create sql for country loss stats and run
sql = create_sql_country_stats('ap_result', 'ap_country_lookup',
'ap_loss_country')
process_sql(sql, conn)
# create sql for loss year stats
sql = create_sql_loss_year_stats('ap_result', 'ap_loss_year')
process_sql(sql, conn)
# create sql for country base stats and run
sql = create_sql_country_stats_no_year('ap_base_result',
'ap_country_lookup',
'ap_base_country')
process_sql(sql, conn)
# create sql for country loss stats and run
sql = create_sql_country_stats('wa_result', 'wa_country_lookup',
'wa_loss_country')
process_sql(sql, conn)
# create sql for loss year stats
sql = create_sql_loss_year_stats('wa_result', 'wa_loss_year')
process_sql(sql, conn)
# create sql for country base stats and run
sql = create_sql_country_stats_no_year('wa_base_result',
'wa_country_lookup',
'wa_base_country')
process_sql(sql, conn)
# close
conn.close()
def main():
# get connection
conn = CONN_PARAM.getConn()
# create sql for country loss stats and run
sql = create_sql_country_stats('ap_result', 'ap_country_lookup', 'ap_loss_country')
process_sql(sql, conn)
# create sql for loss year stats
sql = create_sql_loss_year_stats('ap_result', 'ap_loss_year')
process_sql(sql, conn)
# create sql for country base stats and run
sql = create_sql_country_stats_no_year('ap_base_result', 'ap_country_lookup', 'ap_base_country')
process_sql(sql, conn)
# create sql for country loss stats and run
sql = create_sql_country_stats('wa_result', 'wa_country_lookup', 'wa_loss_country')
process_sql(sql, conn)
# create sql for loss year stats
sql = create_sql_loss_year_stats('wa_result', 'wa_loss_year')
process_sql(sql, conn)
# create sql for country base stats and run
sql = create_sql_country_stats_no_year('wa_base_result', 'wa_country_lookup', 'wa_base_country')
process_sql(sql, conn)
# close
conn.close()