Exemplo n.º 1
0
def prep_admin_bdys(pg_cur, settings):
    # Step 2 of 3 : create admin bdy tables read to be used
    start_time = datetime.now()

    if settings['admin_bdys_schema'] != "public":
        pg_cur.execute(
            "CREATE SCHEMA IF NOT EXISTS {0} AUTHORIZATION {1}".format(
                settings['admin_bdys_schema'], settings['pg_user']))

    # create tables using multiprocessing - using flag in file to split file up into sets of statements
    sql_list = psma.open_sql_file("02-02a-prep-admin-bdys-tables.sql",
                                  settings).split("-- # --")
    sql_list = sql_list + psma.open_sql_file(
        "02-02b-prep-census-2011-bdys-tables.sql", settings).split("-- # --")
    sql_list = sql_list + psma.open_sql_file(
        "02-02c-prep-census-2016-bdys-tables.sql", settings).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)

    psma.multiprocess_list("sql", sql_list, settings, logger)

    # Special case - remove custom outback bdy if South Australia not requested
    if 'SA' not in settings['states_to_load']:
        pg_cur.execute(
            psma.prep_sql(
                "DELETE FROM admin_bdys.locality_bdys WHERE locality_pid = 'SA999999'",
                settings))
        pg_cur.execute(
            psma.prep_sql("VACUUM ANALYZE admin_bdys.locality_bdys", settings))

    logger.info("\t- Step 2 of 3 : admin boundaries prepped : {0}".format(
        datetime.now() - start_time))
Exemplo n.º 2
0
def qa_display_localities(pg_cur, settings):
    logger.info("\t- Step 8 of 7 : Start QA")
    start_time = datetime.now()

    pg_cur.execute(psma.prep_sql("SELECT locality_pid, Locality_name, 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(psma.prep_sql("SELECT locality_pid, Locality_name, 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(psma.open_sql_file("08-qa-display-localities.sql", settings))
    display_qa_results("Dropped Localities", pg_cur)

    logger.info("\t- Step 8 of 7 : display localities qa'd : {0}".format(datetime.now() - start_time))
Exemplo n.º 3
0
def qa_display_localities(pg_cur, settings):
    logger.info("\t- Step 7 of 7 : Start QA")
    start_time = datetime.now()

    pg_cur.execute(
        psma.prep_sql(
            "SELECT locality_pid, Locality_name, 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(
        psma.prep_sql(
            "SELECT locality_pid, Locality_name, 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(psma.open_sql_file("07-qa-display-localities.sql",
                                      settings))
    display_qa_results("Dropped Localities", pg_cur)

    logger.info("\t- Step 7 of 7 : display localities qa'd : {0}".format(
        datetime.now() - start_time))
Exemplo n.º 4
0
def create_reference_tables(pg_cur, settings):
    # 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(
        psma.open_sql_file("03-01-reference-create-tables.sql", settings))
    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(
        psma.open_sql_file("03-02-reference-populate-localities.sql",
                           settings))
    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(
        psma.open_sql_file("03-03-reference-populate-locality-aliases.sql",
                           settings))
    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(
        psma.open_sql_file("03-04-reference-populate-locality-neighbours.sql",
                           settings))
    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(
        psma.open_sql_file("03-05-reference-populate-streets.sql", settings))
    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(
        psma.open_sql_file("03-06-reference-populate-street-aliases.sql",
                           settings))
    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 = psma.open_sql_file("03-07-reference-populate-addresses-1.sql",
                             settings)
    sql_list = psma.split_sql_into_list(pg_cur, sql, settings['gnaf_schema'],
                                        "streets", "str", "gid", settings,
                                        logger)
    if sql_list is not None:
        psma.multiprocess_list('sql', sql_list, settings, logger)
    pg_cur.execute(psma.prep_sql("ANALYZE gnaf.temp_addresses;", settings))
    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(
        psma.open_sql_file("03-08-reference-populate-address-alias-lookup.sql",
                           settings))
    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(
        psma.open_sql_file(
            "03-09-reference-populate-address-secondary-lookup.sql", settings))
    pg_cur.execute(
        psma.prep_sql("VACUUM ANALYSE gnaf.address_secondary_lookup",
                      settings))
    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(
        psma.open_sql_file("03-10-reference-split-melbourne.sql", settings))
    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(
        psma.open_sql_file("03-11-reference-finalise-localities.sql",
                           settings))
    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 = psma.open_sql_file("03-12-reference-populate-addresses-2.sql",
                             settings)
    sql_list = psma.split_sql_into_list(pg_cur, sql, settings['gnaf_schema'],
                                        "localities", "loc", "gid", settings,
                                        logger)
    if sql_list is not None:
        psma.multiprocess_list('sql', sql_list, settings, logger)

    # turf the temp address table
    pg_cur.execute(
        psma.prep_sql("DROP TABLE IF EXISTS gnaf.temp_addresses", settings))
    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 = psma.open_sql_file("03-13-reference-derived-postcode-bdys.sql",
                             settings)
    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)
    psma.multiprocess_list("sql", sql_list, settings, logger)

    # create analysis table?
    if settings['st_subdivide_supported']:
        pg_cur.execute(
            psma.open_sql_file("03-13a-create-postcode-analysis-table.sql",
                               settings))

    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 = psma.open_sql_file("03-14-reference-create-indexes.sql",
                                      settings).split("\n")
    sql_list = []
    for sql in raw_sql_list:
        if sql[0:2] != "--" and sql[0:2] != "":
            sql_list.append(sql)
    psma.multiprocess_list("sql", sql_list, settings, logger)
    logger.info(
        "\t- Step 14 of 14 : create primary & foreign keys and indexes : {0}".
        format(datetime.now() - start_time))
Exemplo n.º 5
0
def export_display_localities(pg_cur, settings):
    start_time = datetime.now()

    sql = psma.open_sql_file("06-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
    psma.run_command_line(cmd)

    logger.info(
        "\t- Step 6 of 7 : display localities exported to SHP : {0}".format(
            datetime.now() - start_time))
    logger.warning(
        "\t\t- If this step took < 1 second - it may have failed silently. "
        "Check your output directory!")

    start_time = datetime.now()

    # Export as GeoJSON FeatureCollection
    sql = psma.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()

    logger.info(
        "\t- Step 6 of 7 : display localities exported to GeoJSON : {0}".
        format(datetime.now() - start_time))
Exemplo n.º 6
0
def export_display_localities(pg_cur, settings):
    start_time = datetime.now()

    sql = psma.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
    psma.run_command_line(cmd)

    time_elapsed = datetime.now() - start_time

    logger.info("\t- Step 7 of 7 : 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 = psma.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()

    logger.info("\t- Step 7 of 7 : display localities exported to GeoJSON : {0}".format(datetime.now() - start_time))