1

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)"
  • 1
    You've left a number of important details out of the question. What percentage of the large points table actually has a NULL value? What does EXPLAIN in front of the UPDATE report? Please edit the Question. – Vince Oct 17 '19 at 10:45
3

The update query is taking forever because you have introduce a cross join between the point table and a copy of it (UPDATE table1 SET ... FROM table1 AS t1). We can see that in the explain plan with the number of rows to be updated (rows=15 380 552 946 848)

When doing an update, each row is processed individually so you don' t need the lateral join. You could directly do

UPDATE table1
SET field_1= (SELECT required_field
     FROM table2 t2
     ORDER BY table1.geom <-> t2.geom
     LIMIT 1)
WHERE field_1 IS NULL;

To update multiple fields, you must enclose them in parentheses:

UPDATE table1
SET (field_1, field_2) = (SELECT required_field, another_field
     FROM table2 t2
     ...
| improve this answer | |
  • I'll try that query, thx. Just one thing, how can I add one more field to the SET? "SET field_1=(SELECT........), field_2 = ....." ? – Pitrako Junior Oct 17 '19 at 11:27
  • @PitrakoJunior see edits – JGH Oct 17 '19 at 11:32
  • Perfect. It runs in few minutes and result seems to be correct. – Pitrako Junior Oct 17 '19 at 11:50

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.