def qa_display_localities(pg_cur, settings):
logger.info("\t- Step 8 of 8 : Start QA")
start_time = datetime.now()
pg_cur.execute(
geoscape.prep_sql(
"SELECT locality_pid, locality_name, coalesce(postcode, '') as postcode, state, "
"address_count, street_count "
"FROM admin_bdys.locality_bdys_display WHERE NOT ST_IsValid(geom);",
settings))
display_qa_results("Invalid Geometries", pg_cur)
pg_cur.execute(
geoscape.prep_sql(
"SELECT locality_pid, locality_name, coalesce(postcode, '') as postcode, state, "
"address_count, street_count "
"FROM admin_bdys.locality_bdys_display WHERE ST_IsEmpty(geom);",
settings))
display_qa_results("Empty Geometries", pg_cur)
pg_cur.execute(
geoscape.open_sql_file("08-qa-display-localities.sql", settings))
display_qa_results("Dropped Localities", pg_cur)
logger.info("\t- Step 8 of 8 : display localities qa'd : {0}".format(
datetime.now() - start_time))
def prep_admin_bdys(pg_cur):
# Step 3 of 4 : create admin bdy tables read to be used
start_time = datetime.now()
# create tables using multiprocessing - using flag in file to split file up into sets of statements
sql_list = geoscape.open_sql_file(
"02-02a-prep-admin-bdys-tables.sql").split("-- # --")
sql_list = sql_list + geoscape.open_sql_file(
"02-02b-prep-census-2011-bdys-tables.sql").split("-- # --")
sql_list = sql_list + geoscape.open_sql_file(
"02-02c-prep-census-2016-bdys-tables.sql").split("-- # --")
sql_list = sql_list + geoscape.open_sql_file(
"02-02d-prep-census-2021-bdys-tables.sql").split("-- # --")
# # Account for bdys that are not in states to load - not yet working
# for sql in sql_list:
# if settings.states_to_load == ["OT"] and ".commonwealth_electorates " in sql:
# sql_list.remove(sql)
#
# if settings.states_to_load == ["ACT"] and ".local_government_areas " in sql:
# sql_list.remove(sql)
#
# logger.info(settings.states_to_load
#
# if not ("NT" in settings.states_to_load or "SA" in settings.states_to_load
# or "VIC" in settings.states_to_load or "WA" in settings.states_to_load) \
# and ".local_government_wards " in sql:
# sql_list.remove(sql)
#
# if settings.states_to_load == ["OT"] and ".state_lower_house_electorates " in sql:
# sql_list.remove(sql)
#
# if not ("TAS" in settings.states_to_load or "VIC" in settings.states_to_load
# or "WA" in settings.states_to_load) and ".state_upper_house_electorates " in sql:
# sql_list.remove(sql)
geoscape.multiprocess_list("sql", sql_list, logger)
# Special case - remove custom outback bdy if South Australia not requested
if "SA" not in settings.states_to_load:
pg_cur.execute(
geoscape.prep_sql(
"DELETE FROM admin_bdys.locality_bdys WHERE locality_pid = 'SA999999'"
))
pg_cur.execute(
geoscape.prep_sql("VACUUM ANALYZE admin_bdys.locality_bdys"))
logger.info("\t- Step 2 of 3 : admin boundaries prepped : {0}".format(
datetime.now() - start_time))
def create_reference_tables(pg_cur):
# set postgres search path back to the default
pg_cur.execute("SET search_path = public, pg_catalog")
# Step 1 of 14 : create reference tables
start_time = datetime.now()
pg_cur.execute(geoscape.open_sql_file("03-01-reference-create-tables.sql"))
logger.info("\t- Step 1 of 14 : create reference tables : {0}".format(
datetime.now() - start_time))
# Step 2 of 14 : populate localities
start_time = datetime.now()
pg_cur.execute(
geoscape.open_sql_file("03-02-reference-populate-localities.sql"))
logger.info("\t- Step 2 of 14 : localities populated : {0}".format(
datetime.now() - start_time))
# Step 3 of 14 : populate locality aliases
start_time = datetime.now()
pg_cur.execute(
geoscape.open_sql_file(
"03-03-reference-populate-locality-aliases.sql"))
logger.info("\t- Step 3 of 14 : locality aliases populated : {0}".format(
datetime.now() - start_time))
# Step 4 of 14 : populate locality neighbours
start_time = datetime.now()
pg_cur.execute(
geoscape.open_sql_file(
"03-04-reference-populate-locality-neighbours.sql"))
logger.info(
"\t- Step 4 of 14 : locality neighbours populated : {0}".format(
datetime.now() - start_time))
# Step 5 of 14 : populate streets
start_time = datetime.now()
pg_cur.execute(
geoscape.open_sql_file("03-05-reference-populate-streets.sql"))
logger.info(
"\t- Step 5 of 14 : streets populated : {0}".format(datetime.now() -
start_time))
# Step 6 of 14 : populate street aliases
start_time = datetime.now()
pg_cur.execute(
geoscape.open_sql_file("03-06-reference-populate-street-aliases.sql"))
logger.info("\t- Step 6 of 14 : street aliases populated : {0}".format(
datetime.now() - start_time))
# Step 7 of 14 : populate addresses, using multiprocessing
start_time = datetime.now()
sql = geoscape.open_sql_file("03-07-reference-populate-addresses-1.sql")
sql_list = geoscape.split_sql_into_list(pg_cur, sql, settings.gnaf_schema,
"streets", "str", "gid", logger)
if sql_list is not None:
geoscape.multiprocess_list("sql", sql_list, logger)
pg_cur.execute(geoscape.prep_sql("ANALYZE gnaf.temp_addresses;"))
logger.info(
"\t- Step 7 of 14 : addresses populated : {0}".format(datetime.now() -
start_time))
# Step 8 of 14 : populate principal alias lookup
start_time = datetime.now()
pg_cur.execute(
geoscape.open_sql_file(
"03-08-reference-populate-address-alias-lookup.sql"))
logger.info(
"\t- Step 8 of 14 : principal alias lookup populated : {0}".format(
datetime.now() - start_time))
# Step 9 of 14 : populate primary secondary lookup
start_time = datetime.now()
pg_cur.execute(
geoscape.open_sql_file(
"03-09-reference-populate-address-secondary-lookup.sql"))
pg_cur.execute(
geoscape.prep_sql("VACUUM ANALYSE gnaf.address_secondary_lookup"))
logger.info(
"\t- Step 9 of 14 : primary secondary lookup populated : {0}".format(
datetime.now() - start_time))
# Step 10 of 14 : split the Melbourne locality into its 2 postcodes (3000, 3004)
start_time = datetime.now()
pg_cur.execute(
geoscape.open_sql_file("03-10-reference-split-melbourne.sql"))
logger.info(
"\t- Step 10 of 14 : Melbourne split : {0}".format(datetime.now() -
start_time))
# Step 11 of 14 : finalise localities assigned to streets and addresses
start_time = datetime.now()
pg_cur.execute(
geoscape.open_sql_file("03-11-reference-finalise-localities.sql"))
logger.info("\t- Step 11 of 14 : localities finalised : {0}".format(
datetime.now() - start_time))
# Step 12 of 14 : finalise addresses, using multiprocessing
start_time = datetime.now()
sql = geoscape.open_sql_file("03-12-reference-populate-addresses-2.sql")
sql_list = geoscape.split_sql_into_list(pg_cur, sql, settings.gnaf_schema,
"localities", "loc", "gid", logger)
if sql_list is not None:
geoscape.multiprocess_list("sql", sql_list, logger)
# turf the temp address table
pg_cur.execute(
geoscape.prep_sql("DROP TABLE IF EXISTS gnaf.temp_addresses"))
logger.info(
"\t- Step 12 of 14 : addresses finalised : {0}".format(datetime.now() -
start_time))
# Step 13 of 14 : create almost correct postcode boundaries by aggregating localities, using multiprocessing
start_time = datetime.now()
sql = geoscape.open_sql_file("03-13-reference-derived-postcode-bdys.sql")
sql_list = []
for state in settings.states_to_load:
state_sql = sql.replace("GROUP BY ",
"WHERE state = '{0}' GROUP BY ".format(state))
sql_list.append(state_sql)
geoscape.multiprocess_list("sql", sql_list, logger)
# create analysis table?
if settings.st_subdivide_supported:
pg_cur.execute(
geoscape.open_sql_file(
"03-13a-create-postcode-analysis-table.sql"))
logger.info("\t- Step 13 of 14 : postcode boundaries created : {0}".format(
datetime.now() - start_time))
# Step 14 of 14 : create indexes, primary and foreign keys, using multiprocessing
start_time = datetime.now()
raw_sql_list = geoscape.open_sql_file(
"03-14-reference-create-indexes.sql").split("\n")
sql_list = []
for sql in raw_sql_list:
if sql[0:2] != "--" and sql[0:2] != "":
sql_list.append(sql)
geoscape.multiprocess_list("sql", sql_list, logger)
logger.info(
"\t- Step 14 of 14 : create primary & foreign keys and indexes : {0}".
format(datetime.now() - start_time))
def main():
full_start_time = datetime.now()
# set command line arguments
args = set_arguments()
# get settings from arguments
settings = get_settings(args)
# connect to Postgres
try:
pg_conn = psycopg2.connect(settings['pg_connect_string'])
except psycopg2.Error:
logger.fatal(
"Unable to connect to database\nACTION: Check your Postgres parameters and/or database security"
)
return False
pg_conn.autocommit = True
pg_cur = pg_conn.cursor()
# log postgres/postgis versions being used
geoscape.check_postgis_version(pg_cur, settings, logger)
logger.info("")
# get SRID of locality boundaries
sql = geoscape.prep_sql(
f"select Find_SRID('{settings['admin_bdys_schema']}', 'locality_bdys', 'geom')",
settings)
pg_cur.execute(sql)
settings['srid'] = int(pg_cur.fetchone()[0])
if settings['srid'] == 4283:
logger.info(
f"Locality boundary coordinate system is EPSG:{settings['srid']} (GDA94)"
)
elif settings['srid'] == 7844:
logger.info(
f"Locality boundary coordinate system is EPSG:{settings['srid']} (GDA2020)"
)
else:
logger.fatal(
"Invalid coordinate system (SRID) - EXITING!\nValid values are 4283 (GDA94) and 7844 (GDA2020)"
)
exit()
# add Postgres functions to clean out non-polygon geometries from GeometryCollections
pg_cur.execute(
geoscape.open_sql_file("create-polygon-intersection-function.sql",
settings).format(settings['srid']))
pg_cur.execute(
geoscape.open_sql_file("create-multi-linestring-split-function.sql",
settings))
# let's build some clean localities!
logger.info("")
create_states_and_prep_localities(settings)
get_split_localities(pg_cur, settings)
verify_locality_polygons(pg_cur, settings)
get_locality_state_border_gaps(pg_cur, settings)
finalise_display_localities(pg_cur, settings)
create_display_postcodes(pg_cur, settings)
export_display_localities(pg_cur, settings)
qa_display_localities(pg_cur, settings)
pg_cur.close()
pg_conn.close()
logger.info("Total time : {0}".format(datetime.now() - full_start_time))
return True
def export_display_localities(pg_cur, settings):
start_time = datetime.now()
# create export path
pathlib.Path(settings['output_path']).mkdir(parents=True, exist_ok=True)
sql = geoscape.open_sql_file("07-export-display-localities.sql", settings)
if platform.system() == "Windows":
password_str = "SET"
else:
password_str = "export"
password_str += " PGPASSWORD={0}&&".format(settings['pg_password'])
cmd = password_str + "pgsql2shp -f \"{0}\" -u {1} -h {2} -p {3} {4} \"{5}\""\
.format(settings['shapefile_export_path'], settings['pg_user'], settings['pg_host'],
settings['pg_port'], settings['pg_db'], sql)
# logger.info(cmd
geoscape.run_command_line(cmd)
# zip shapefile
if settings['srid'] == 4283:
shp_zip_path = settings['shapefile_name'] + "-shapefile.zip"
else:
shp_zip_path = settings['shapefile_name'] + "-gda2020-shapefile.zip"
output_zipfile = os.path.join(settings['output_path'], shp_zip_path)
zf = zipfile.ZipFile(output_zipfile, mode="w")
for ext in settings['shapefile_extensions']:
file_name = settings['shapefile_name'] + ext
file_path = os.path.join(settings['output_path'], file_name)
zf.write(file_path, file_name, compress_type=zipfile.ZIP_DEFLATED)
zf.close()
time_elapsed = datetime.now() - start_time
logger.info(
"\t- Step 7 of 8 : display localities exported to SHP : {0}".format(
time_elapsed))
if time_elapsed.seconds < 2:
logger.warning(
"\t\t- This step took < 2 seconds - it may have failed silently. "
"Check your output directory!")
start_time = datetime.now()
# Export as GeoJSON FeatureCollection
sql = geoscape.prep_sql(
"SELECT gid, locality_pid, locality_name, COALESCE(postcode, '') AS postcode, state, "
"locality_class, address_count, street_count, ST_AsGeoJSON(geom, 5, 0) AS geom "
"FROM {0}.locality_bdys_display".format(settings['admin_bdys_schema']),
settings)
pg_cur.execute(sql)
# Create the GeoJSON output with an array of dictionaries containing the field names and values
# get column names from cursor
column_names = [desc[0] for desc in pg_cur.description]
json_dicts = []
row = pg_cur.fetchone()
if row is not None:
while row is not None:
rec = {}
props = {}
i = 0
rec["type"] = "Feature"
for column in column_names:
if column == "geometry" or column == "geom":
rec["geometry"] = row[i]
else:
props[column] = row[i]
i += 1
rec["properties"] = props
json_dicts.append(rec)
row = pg_cur.fetchone()
gj = json.dumps(json_dicts).replace("\\",
"").replace('"{',
'{').replace('}"', '}')
geojson = ''.join(['{"type":"FeatureCollection","features":', gj, '}'])
text_file = open(settings['geojson_export_path'], "w")
text_file.write(geojson)
text_file.close()
# compress GeoJSON
if settings['srid'] == 4283:
geojson_zip_path = settings['geojson_export_path'].replace(
".geojson", "-geojson.zip")
else:
geojson_zip_path = settings['geojson_export_path'].replace(
".geojson", "-gda2020-geojson.zip")
zipfile.ZipFile(geojson_zip_path, mode="w")\
.write(settings['geojson_export_path'], compress_type=zipfile.ZIP_DEFLATED)
logger.info(
"\t- Step 7 of 8 : display localities exported to GeoJSON : {0}".
format(datetime.now() - start_time))