TransWikia.com

Geometry from Record use in Function

Geographic Information Systems Asked on August 25, 2021

I have a function I am creating for some iterative task I need to do but I cannot get the geometry coming out of a record data type into the execute properly:

DO
$$
DECLARE
records record;
images record;
BEGIN                 

FOR records IN SELECT opening, shape FROM d.blocks_2013_2018
LOOP
    TRUNCATE d.xx_delete;

    INSERT INTO d.xx_delete (opening_number, image_name)
    SELECT records.opening, a.image_name
    FROM d.image_tiles_with_blocks a
    WHERE ST_Intersects(a.shape, records.shape);

    FOR images IN SELECT * FROM d.xx_delete
    LOOP
        EXECUTE 'INSERT INTO d.cc_analysis (opening_number, t_id, ht, d_huang, d_huang_adj, d_cortini, geom)
                 SELECT '''||images.opening_number||''', b.t_id, b.max, b.d_huang, b.d_huang_adj, b.d_cortini, b.geom
                 FROM p839.'||images.image_name||'_t b 
                 WHERE ST_Intersects(%, b.shape)', records.shape;   
        COMMIT;
    END LOOP;
END LOOP;
END;
$$
;

It is the record.shape at the end of the code that I cannot get into the code properly. Currently, I am getting an error stating that the “query … returned two columns”

One Answer

I was able to solve this by replacing the % with $1 and placing USING at the end in replacement of the comma:

EXECUTE 'INSERT INTO d.cc_analysis (opening_number, t_id, ht, d_huang, d_huang_adj, d_cortini, geom)
SELECT '''||images.opening_number||''', b.t_id, b.max, b.d_huang, b.d_huang_adj, b.d_cortini, b.geom
FROM p839.'||images.image_name||'_t b 
WHERE ST_Intersects($1, b.shape)' USING records.shape;   

Answered by D_C on August 25, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP