0

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?

  • Can you share execution plan? Just write EXPLAIN at the beginning of your query. Did you add GIST indexes on geometry columns? – Losbaltica Jul 4 '18 at 14:49
1

There are many ways to sort this issue and is probably duplication of multiple others, with the sole difference being a nearest neighbour search.

First of all create GIST indexes on your geometry fields.

CREATE INDEX table_geom ON table USING GIST (geom);

After that check if index is in use adding EXPLAIN at the beginning of your query.

I not sure about your running out of space issue, but for me looks like when you joining the tables you increase number of rows that you reduce later on using group by. Even though left join shouldn't increase number of your rows. Instead you can try with this query where you order by distance on the fly and select closest one.

SELECT 
    parcel_bus.ogc_fid,
    parcel_bus.poly_id,
    parcel_bus.title_no,
    parcel_bus.insert,
    parcel_bus.update,
    parcel_bus.rec_status,
    parcel_bus.wkb_geometry,
    CASE WHEN bus.ogc_fid NOTNULL THEN st_distance(bus.wkb_geomtry, parcel_bus.wkb_geometry) ELSE -9999 END bs 
FROM
  (SELECT
    c.*,
    (SELECT b.ogc_fid
     FROM filter_busstops b
      WHERE st_dwithin(c.wkb_geometry, b.wkb_geometry, 2)
     ORDER BY c.wkb_geometry <-> b.wkb_geometry
     LIMIT 1
     OFFSET 0) bus_id
  FROM parcels_all_shapefile_cp c) parcel_bus
LEFT JOIN filter_busstops bus ON bus.ogc_fid = parcel_bus.ogc_fid;
  • Thanks, I'll try your solution. I have already created a spatial index, so that isn't the issue. – Trashmonk Jul 4 '18 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.