Australian postcodes are not reliable nor good indicators of areas, but sometimes, in the absence of any other spatial identifiers, we tend to resort to using them. As we only had a list of centroids for each postcode, to better assess the data we need to create polygons. This example shows using the PostGIS st_voronoipolygons() function.
--B create a column for our polygons (although we normally use geom, I --B wanted to retain clarity around the fact that these were not 'real', --B but estimated) ALTER TABLE postcodes ADD voronoi geometry (MULTIPOLYGON, 4283); --B Now update the table using st_collect, st_voronoipolygons and st_dump UPDATE postcodes SET voronoi = dump.vor FROM ( SELECT st_multi( (ST_DUMP( ST_VoronoiPolygons( ST_Collect(centroid) ) )).geom ) AS vor FROM postcodes ) dump WHERE st_within(centroid, dump.vor); --B create an index for good measure CREATE INDEX postcode_voronoi_gist ON postcodes USING GIST (voronoi); VACUUM FULL ANALYZE postcodes; --B clip the voronoi polygons with the national border UPDATE postcodes SET vor_clip = st_multi(st_intersection(voronoi, n.geom)) FROM national n
Problems with this..? We have inadvertently created a problem that extends the basic postcode problem – postcodes are not all spatially distinct. By this, consider the postcode for Sydney… ‘2000’ right? In fact, there are 152 ‘postcodes’ called ‘Sydney’, and for which the supplied coordinates are 151.2073,-33.8678. This means, although we create a clean set of voronoi polygons, we then end up with overlapping polygons.
In the example image below – the Fremantle Office postcode 6160 is actually sharing with 8 other postcodes (all delivery centres).
About 10% of Australian postcodes share their spatial location with between 1 and 151 other postcodes. A better solution going forward would be to not create a polygon for each code, but associate codes with each polygon. Two common approaches would be to link postcodes to a table containing the polygons, or for each polygon provide an object or array holding the details of the related postcodes.