I have two very large tables on which I want to perform a spatial query.
UPDATE table_a SET att_1 = b.att_1, att_2 = b.att_2 FROM table_a AS a JOIN table_b AS b ON ST_Contains(a.geom,b.geom);
I am rather new to cursors, but here is my current attempt:
DECLARE curs1 CURSOR FOR SELECT att_1, att_2 FROM table_a; OPEN curs1 FETCH NEXT FROM curs1 WHILE @@FETCH_STATUS = 0 BEGIN UPDATE table_a SET att_1 = b.att_1, att_2 = b.att_2 FROM table_a AS a JOIN table_b AS b ON ST_Contains(a.geom,b.geom) WHERE CURRENT OF curs1 FETCH NEXT FROM curs1 END CLOSE curs1 DEALLOCATE curs1 ;
My current error is:
ERROR: syntax error at or near "OPEN" LINE 5: OPEN curs1
You may have surmised that all I want to do is take two attributes from my second table and add them to my first table where they meet spatially (table a is polygon data and table b is point data).
I am currently using Postgres 9.4 with PostGIS 1.2.
The two tables are massive, 25 million and 5 million respectively. This is why I need to go down the cursor route, the straight update query simply fails with such large datasets.