I have two tables:
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
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 ?