Address weighted SA1 centroids

README.md

• Download the ASGS SA1 Shapefiles

• Download the latest GNAF from OpenAddresses (choose the Output file)

• psql -c 'CREATE EXTENSION postgis;'

• Load the address points ogr2ogr -f PostgreSQL -select 'ID' PG: au/countrywide.vrt -nln oa -lco UNLOGGED=YES

• Load the ASGS SA1 ogr2ogr -f PostgreSQL -t_srs 'EPSG:4326' PG: SA1_2016_AUST.shp -nln sa1 -lco UNLOGGED=YES -nlt PROMOTE_TO_MULTI

• Generate the address weighted centroid of each SA1. psql -f sa1_oa_weighted_centroids.sql

• Output to CSV ogr2ogr -f 'CSV' sa1_oa_weighted_centroid.csv PG: sa1_gnaf_weighted_centroids -lco GEOMETRY=AS_XY

sa1_oa_weighted_centroids.sql

-- Address weighted centorid of each SA1
-- This process does not ensure the resulting centroid is actually inside the SA1
-- If the SA1 has no address points inside, it will not get a centroid
CREATE TABLE sa1_oa_weighted_centroids AS
  SELECT
    sa1.sa1_main16,
    ST_Centroid( -- return the centroid of the MULTIPOINT geometry
      ST_Collect(oa.wkb_geometry) -- return a MULTIPOINT of all the address points group by SA1 ID
    )
  FROM
    sa1, oa
  WHERE
    ST_Contains(sa1.wkb_geometry, oa.wkb_geometry) -- address points contained within the SA1
  GROUP BY
    sa1.sa1_main16;