Geographic Information Systems Asked on November 26, 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”
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 November 26, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP