1

I have two tables: Table_1 and Table_2.

Table_1 has 926019 rows. Table_2 has 3089 rows.

I want to update a field of Table_1 with the distance to the closest item from Table_2. Here is my query:

UPDATE Table_1
SET closest_distance = list.distance
FROM(
    SELECT T1.id AS name, MIN(T1.geom <-> T2.geom) AS distance
    FROM Table_1 AS T1
    CROSS JOIN Table_2 AS T2
    GROUP BY T1.id
    ORDER BY T1.id)
    AS list
WHERE Table_1.id = list.name;

When executing the query, after a long moment, I receive this error message:

Error: could not write the block 24453337 from temporary file : No space left on device

I think the solution is to execute the query by sets of rows (10k rows by 10k rows for example). I just do not know if this is possible on PostGIS, I have searched about it on PostgreSQL and found nothing relevant.

My question is: How can I complete my query ?

  • you need to create some more disc space – Ian Turton Aug 2 '19 at 9:59
  • how can I do this ? I already have 186Go available on my disc. – Basile Aug 2 '19 at 10:09
  • This is more a PostgreSQL issue than PostGIS, and therefore more appropriate to Database Administrators. Just because you have space on a device doesn't mean you have space where the file is being created. A more complete description of your available devices and where PostgreSQL has been installed and configured will be necessary. Note that a cross join of 3k by 1m rows is 3g rows -- populated in the temp tablespace. – Vince Aug 2 '19 at 10:55
  • @JGH's answer is spot on; you are doing an unnecessary full cross join between your tables, with multiple costly operations, for each row in the updated table - and all that has to be kept in memory/extended disk memory until the transaction has completed! It's just not the way to handle UPDATE logic. (Btw. I read another answer of yours with this very query structure...you might want to revise that). – ThingumaBob Aug 2 '19 at 14:16
2

Beside adding more disk space, you can try to modify the update query.

  • No need to do the costly order by in the subquery
  • No need to compute the distance between every entry in both tables at once. You can do it per row
  • You can make use of a spatial index by moving the distance computation in the order by clause and keeping only the 1st result.
UPDATE Table_1
SET closest_distance = 
 (
    SELECT Table_1.geom <-> T2.geom AS distance
    FROM Table_2 AS T2
    ORDER BY Table_1.geom <-> T2.geom
    LIMIT 1 
 );
2

When handling sorts, joins and other operations that require more space than you have memory for Postgresql will store temporary files in $PGDATA/base/pgsql_tmp. So you will need to make sure there is sufficient space on whichever disk that is.

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.