I have a table called sites of polygons, and a table called postcodes of points. For each row in sites I need to find the nearest point in postcodes, then update the sites column postcode from column pc in postcodes. Can anyone please help me write this in PostGIS?
This gets all the correct values, I just don't know how to do an update that can read these straight into the postcodes column:
SELECT DISTINCT ON(g1.key_column) g1.key_column As key_column, g2.pc As postcode FROM sites As g1, postcodes As g2 WHERE g1.key_column <> g2.key_column AND ST_DWithin(g1.geom, g2.geom, 1000) ORDER BY g1.key_column, ST_Distance(g1.geom, g2.geom)