generate a SQLite import script for the AUS G-NAF dataset

gnaf-sqlite.sh

#!/bin/bash
# ref: https://github.com/data61/gnaf/blob/master/gnaf-db/src/main/script/createGnafDb.sh

# input dir
DATA_DIR="/data/gnaf/G-NAF/G-NAF AUGUST 2021"
EXTRAS_DIR="/data/gnaf/G-NAF/Extras"

# fast import pragmas
echo '.output /dev/null'
echo 'PRAGMA main.foreign_keys=OFF;'
echo 'PRAGMA main.page_size=4096;'
echo 'PRAGMA main.cache_size=-2000;'
echo 'PRAGMA main.synchronous=OFF;'
echo 'PRAGMA main.journal_mode=OFF;'
echo 'PRAGMA main.temp_store=MEMORY;'
echo '.output stdout'

# csv mode
echo '.mode csv'
echo '.separator "|"'

# import schema and view files from G-NAF
cat "${EXTRAS_DIR}/GNAF_TableCreation_Scripts/create_tables_ansi.sql"
cat "${EXTRAS_DIR}/GNAF_View_Scripts/address_view.sql" | sed 's/ OR REPLACE//g'
echo ';'

OIFS="$IFS"
IFS=$'\n'

# import authority codes
for FILEPATH in `find "${DATA_DIR}/Authority Code" -type f -name "*.psv"`; do
  BASENAME=$(basename $FILEPATH)
  TABLE_NAME="${BASENAME/Authority_Code_/}"
  TABLE_NAME="${TABLE_NAME/_psv.psv/}"
  TABLE_NAME="${TABLE_NAME/.psv/}"
  echo ".import '${FILEPATH}' '${TABLE_NAME}'"
done

# import standard
for FILEPATH in `find "${DATA_DIR}/Standard" -type f -name "*.psv"`; do
  BASENAME=$(basename $FILEPATH)
  TABLE_NAME="${BASENAME#*_}"
  TABLE_NAME="${TABLE_NAME/_psv.psv/}"
  TABLE_NAME="${TABLE_NAME/.psv/}"

  # only import to uppercase tables
  # this avoids files like 'nt_locality_pid_linkage.psv which dont exist in the schema
  if [[ $TABLE_NAME != $(echo $TABLE_NAME | tr '[:lower:]' '[:upper:']) ]]; then
    continue
  fi

  echo ".import '| tail -n +2 \"${FILEPATH}\"' '${TABLE_NAME}'"
done

IFS="$OIFS"

index-generator.sql

SELECT
  printf(
    'CREATE INDEX IF NOT EXISTS %s ON %s (%s);',
    printf('%s-%s', LOWER(t.name), LOWER(c.name)),
    t.name,
    c.name
  )
FROM sqlite_master t
LEFT OUTER JOIN pragma_table_info((t.name)) c
WHERE t.type = 'table'
AND (
  c.name LIKE '%\_pid' ESCAPE '\' OR
  c.name LIKE '%\_code' ESCAPE '\' OR
  c.name == 'code'
);

query-geojson.sql

SELECT json_object(
  'type', 'FeatureCollection',
  'features', json_group_array(
    json_object(
      'type', 'Feature',
      'geometry', json_object(
        'type', 'Point',
        'coordinates', json_array(
          LONGITUDE, LATITUDE
        )
      ),
      'properties', json_object(
        'ADDRESS_DETAIL_PID', ADDRESS_DETAIL_PID,
        'STREET_LOCALITY_PID', STREET_LOCALITY_PID,
        'LOCALITY_PID', LOCALITY_PID,
        'BUILDING_NAME', BUILDING_NAME,
        'LOT_NUMBER_PREFIX', LOT_NUMBER_PREFIX,
        'LOT_NUMBER', LOT_NUMBER,
        'LOT_NUMBER_SUFFIX', LOT_NUMBER_SUFFIX,
        'FLAT_TYPE', FLAT_TYPE,
        'FLAT_NUMBER_PREFIX', FLAT_NUMBER_PREFIX,
        'FLAT_NUMBER', FLAT_NUMBER,
        'FLAT_NUMBER_SUFFIX', FLAT_NUMBER_SUFFIX,
        'LEVEL_TYPE', LEVEL_TYPE,
        'LEVEL_NUMBER_PREFIX', LEVEL_NUMBER_PREFIX,
        'LEVEL_NUMBER', LEVEL_NUMBER,
        'LEVEL_NUMBER_SUFFIX', LEVEL_NUMBER_SUFFIX,
        'NUMBER_FIRST_PREFIX', NUMBER_FIRST_PREFIX,
        'NUMBER_FIRST', NUMBER_FIRST,
        'NUMBER_FIRST_SUFFIX', NUMBER_FIRST_SUFFIX,
        'NUMBER_LAST_PREFIX', NUMBER_LAST_PREFIX,
        'NUMBER_LAST', NUMBER_LAST,
        'NUMBER_LAST_SUFFIX', NUMBER_LAST_SUFFIX,
        'STREET_NAME', STREET_NAME,
        'STREET_CLASS_CODE', STREET_CLASS_CODE,
        'STREET_CLASS_TYPE', STREET_CLASS_TYPE,
        'STREET_TYPE_CODE', STREET_TYPE_CODE,
        'STREET_SUFFIX_CODE', STREET_SUFFIX_CODE,
        'STREET_SUFFIX_TYPE', STREET_SUFFIX_TYPE,
        'LOCALITY_NAME', LOCALITY_NAME,
        'STATE_ABBREVIATION', STATE_ABBREVIATION,
        'POSTCODE', POSTCODE,
        'LATITUDE', LATITUDE,
        'LONGITUDE', LONGITUDE,
        'GEOCODE_TYPE', GEOCODE_TYPE,
        'CONFIDENCE', CONFIDENCE,
        'ALIAS_PRINCIPAL', ALIAS_PRINCIPAL,
        'PRIMARY_SECONDARY', PRIMARY_SECONDARY,
        'LEGAL_PARCEL_ID', LEGAL_PARCEL_ID,
        'DATE_CREATED', DATE_CREATED
      )
    )
  )
)
FROM ADDRESS_VIEW
WHERE STREET_LOCALITY_PID = 'VIC1930629';

run.sh

#!/bin/bash

rm -f gnaf.db;
./gnaf-sqlite.sh | sqlite3 gnaf.db