The below code is working well for smaller datasets, however I want to use this with a 30 GB table. Every time I run it using the larger table I run out of SSD space, the table gets to 127 GB in size!
Basically what I am try to achieve is calculate the minimum distance from each land parcel and the closest bus stop. I then want to save that distance in a separate column. Land parcels that do not have a bus stop within 2km, should have its distance set to -9999.
CREATE TABLE parcels_all_shapefile_bs AS SELECT a.ogc_fid, a.poly_id, a.title_no, a.insert, a.update, a.rec_status, a.wkb_geometry, MIN(COALESCE(ST_Distance(a.wkb_geometry, b.wkb_geometry), -9999)) AS bs FROM parcels_all_shapefile_cp a LEFT OUTER JOIN filter_busstops b ON ST_DWithin(a.wkb_geometry, b.wkb_geometry, 2000) GROUP BY a.ogc_fid, a.poly_id, a.title_no, a.insert, a.update, a.rec_status, a.wkb_geometry;
Can anyone see what I am doing wrong and how I could optimize the above code?