Given a point table (table1) and a polygon table (table2) in PostgreSQL, I am trying to update a field from table1 wherever its value is NULL. This field should be updated with a certain field value (let's call it 'required_field') from the closest polygon in table2.
Both tables contain spatial index (gist) and are on projected coordinates. There are about 1.8 million points and 11000 polygons. 97.5% of points have got field_1 = NULL.
To obtain the value of required_field from closest polygon:
SELECT t2.required_field FROM table1 t1 CROSS JOIN LATERAL (SELECT required_field, geom FROM table2 ORDER BY t1.geom <-> geom LIMIT 1) AS t2
To update field_1 from table1 wherever its value is NULL:
UPDATE table1 SET field_1 = ... WHERE field_1 IS NULL
However, I don't know how I could combine these two queries to produce the result I need.
I am trying with the following query, but it seems to run forever (more than 4 hours and it keeps going)
UPDATE table1 SET field_1 = required_field FROM table1 AS t1 CROSS JOIN LATERAL (SELECT required_field, geom FROM table2 ORDER BY t1.geom <-> geom LIMIT 1) AS t2 WHERE field_1 IS NULL;
Is this query actually right? Any other way to do what I am after?
explain throws the following:
"Update on midpoints_wal_scot (cost=0.15..1318769591009.67 rows=15380552946848 width=1543)" " -> Nested Loop (cost=0.15..1318769591009.67 rows=15380552946848 width=1543)" " -> Nested Loop (cost=0.15..3429851.15 rows=3868432 width=2940)" " -> Seq Scan on midpoints_wal_scot t1 (cost=0.00..81053.14 rows=3868432 width=38)" " Filter: (sop_id IS NULL)" " -> Subquery Scan on t2 (cost=0.15..0.86 rows=1 width=2902)" " -> Limit (cost=0.15..0.85 rows=1 width=1447)" " -> Index Scan using sop_polyg_geom_idx on sop_polyg (cost=0.15..7672.73 rows=11029 width=1447)" " Order By: (geom <-> t1.geom)" " -> Materialize (cost=0.00..143643.71 rows=3975914 width=62)" " -> Seq Scan on midpoints_wal_scot (cost=0.00..81053.14 rows=3975914 width=62)"