1

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.

  • 1
    I believe the open needs to be inside a begin. However, see this SO answer. Cursors are very rarely used in Postgres. You should be able to write the above as a simple update .... where st_contains(a.geom, b.geom); It will be vastly slower using a cursor. If you think you need a cursor over a straight update for some other reason (memory?), please update the question to explain why. – John Powell Apr 5 '16 at 8:49
  • Don't cross-post questions – Mike T Apr 6 '16 at 0:12
  • @MikeT - Thanks, I have removed it from overflow. Someone just mentioned here would be more appropriate. – whjd Apr 11 '16 at 7:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.