def fix_boundary_ids(settings):
# Step 2 of 3 : add bdy type prefix to bdy id to enabled joins with stat data (Census 2016 data issue only)
start_time = datetime.now()
alter_sql_list = list()
update_sql_list = list()
vacuum_sql_list = list()
for boundary_dict in settings['bdy_table_dicts']:
boundary_name = boundary_dict["boundary"]
input_pg_table = "{0}_{1}_aust".format(boundary_name, settings["census_year"])
if boundary_name in ["ced", "iare", "iloc", "ireg", "lga", "poa", "sed", "ssc"]:
id_field = boundary_dict["id_field"]
sql = "ALTER TABLE {0}.{1} ALTER COLUMN {2} TYPE text"\
.format(settings['boundary_schema'], input_pg_table, id_field)
alter_sql_list.append(sql)
sql = "UPDATE {0}.{1} SET {2} = upper('{3}') || {2}"\
.format(settings['boundary_schema'], input_pg_table, id_field, boundary_name)
update_sql_list.append(sql)
vacuum_sql_list.append("VACUUM ANALYZE {0}.{1}".format(settings['boundary_schema'], input_pg_table))
utils.multiprocess_list("sql", alter_sql_list, settings, logger)
utils.multiprocess_list("sql", update_sql_list, settings, logger)
utils.multiprocess_list("sql", vacuum_sql_list, settings, logger)
logger.info("\t- Step 2 of 3 : boundary ids prefixed : {0}".format(datetime.now() - start_time))
def create_display_boundaries(pg_cur, settings):
# Step 3 of 3 : create web optimised versions of the census boundaries
start_time = datetime.now()
# create schema
if settings['web_schema'] != "public":
pg_cur.execute(
"CREATE SCHEMA IF NOT EXISTS {0} AUTHORIZATION {1}".format(
settings['web_schema'], settings['pg_user']))
# prepare boundaries for all tiled map zoom levels
create_sql_list = list()
insert_sql_list = list()
vacuum_sql_list = list()
for boundary_dict in settings['bdy_table_dicts']:
boundary_name = boundary_dict["boundary"]
if boundary_name != "mb":
id_field = boundary_dict["id_field"]
name_field = boundary_dict["name_field"]
area_field = boundary_dict["area_field"]
input_pg_table = "{0}_{1}_aust".format(boundary_name,
settings["census_year"])
pg_table = "{0}".format(boundary_name)
# build create table statement
create_table_list = list()
create_table_list.append("DROP TABLE IF EXISTS {0}.{1} CASCADE;")
create_table_list.append("CREATE TABLE {0}.{1} (")
# build column list
column_list = list()
column_list.append("id text NOT NULL PRIMARY KEY")
column_list.append("name text NOT NULL")
column_list.append("area double precision NOT NULL")
column_list.append("population double precision NOT NULL")
column_list.append("geom geometry(MultiPolygon, 4283) NULL")
for zoom_level in range(4, 18):
display_zoom = str(zoom_level).zfill(2)
column_list.append(
"geojson_{0} jsonb NOT NULL".format(display_zoom))
# add columns to create table statement and finish it
create_table_list.append(",".join(column_list))
create_table_list.append(") WITH (OIDS=FALSE);")
create_table_list.append("ALTER TABLE {0}.{1} OWNER TO {2};")
create_table_list.append(
"CREATE INDEX {1}_geom_idx ON {0}.{1} USING gist (geom);")
create_table_list.append(
"ALTER TABLE {0}.{1} CLUSTER ON {1}_geom_idx")
sql = "".join(create_table_list).format(settings['web_schema'],
pg_table,
settings['pg_user'])
create_sql_list.append(sql)
# get population field and table
if boundary_name[:1] == "i":
pop_stat = settings['indigenous_population_stat']
pop_table = settings['indigenous_population_table']
else:
pop_stat = settings['population_stat']
pop_table = settings['population_table']
# print(boundary_name)
# print(pop_stat + " - " + pop_table)
# build insert statement
insert_into_list = list()
insert_into_list.append("INSERT INTO {0}.{1}".format(
settings['web_schema'], pg_table))
insert_into_list.append(
"SELECT bdy.{0} AS id, {1} AS name, SUM(bdy.{2}) AS area, tab.{3} AS population,"
.format(id_field, name_field, area_field, pop_stat))
# thin geometry to make querying faster
tolerance = utils.get_tolerance(10)
insert_into_list.append(
"ST_Transform(ST_Multi(ST_Union(ST_SimplifyVW("
"ST_Transform(geom, 3577), {0}))), 4283),".format(tolerance, ))
# create statements for geojson optimised for each zoom level
geojson_list = list()
for zoom_level in range(4, 18):
# thin geometries to a default tolerance per zoom level
tolerance = utils.get_tolerance(zoom_level)
# trim coords to only the significant ones
decimal_places = utils.get_decimal_places(zoom_level)
geojson_list.append(
"ST_AsGeoJSON(ST_Transform(ST_Multi(ST_Union(ST_SimplifyVW(ST_Transform("
"bdy.geom, 3577), {0}))), 4283), {1})::jsonb".format(
tolerance, decimal_places))
insert_into_list.append(",".join(geojson_list))
insert_into_list.append("FROM {0}.{1} AS bdy".format(
settings['boundary_schema'], input_pg_table))
insert_into_list.append("INNER JOIN {0}.{1}_{2} AS tab".format(
settings['data_schema'], boundary_name, pop_table))
insert_into_list.append("ON bdy.{0} = tab.{1}".format(
id_field, settings["region_id_field"]))
insert_into_list.append("WHERE bdy.geom IS NOT NULL")
insert_into_list.append("GROUP BY {0}, {1}, {2}".format(
id_field, name_field, pop_stat))
sql = " ".join(insert_into_list)
insert_sql_list.append(sql)
vacuum_sql_list.append("VACUUM ANALYZE {0}.{1}".format(
settings['web_schema'], pg_table))
# print("\n".join(insert_sql_list))
utils.multiprocess_list("sql", create_sql_list, settings, logger)
utils.multiprocess_list("sql", insert_sql_list, settings, logger)
utils.multiprocess_list("sql", vacuum_sql_list, settings, logger)
logger.info(
"\t- Step 3 of 3 : web optimised boundaries created : {0}".format(
datetime.now() - start_time))