2 391
edições
(→Geração da grade: osmc.grid_generate_all_levels_logistic rodar por fragmento de mil) |
|||
(3 revisões intermediárias pelo mesmo usuário não estão sendo mostradas) | |||
Linha 108: | Linha 108: | ||
END; | END; | ||
$_$; | $_$; | ||
</syntaxhighlight> | |||
Ainda não funciona pois a nossa demanda requer subquery, e isso gera erro "cannot use subquery in CALL argument". | |||
<syntaxhighlight lang="sql" style="font-size: 80%;"> | |||
CALL osmc.grid_generate_all_levels_logistic( | |||
2.5, | |||
'BR', | |||
(select array_agg(cod_b16h) from grid_br2.poc_caruaru_vw01logistic where length(gid_vbit)=37), | |||
18 | |||
); | |||
</syntaxhighlight> | |||
<syntaxhighlight lang="sql" style="font-size: 80%;"> | |||
DROP TABLE if exists grid_br2.tmp_cells CASCADE; | |||
CREATE TABLE grid_br2.tmp_cells (gid_vbit bit varying, geom public.geometry); | |||
DROP PROCEDURE if exists osmc.grid_generate_all_levels_logistic | |||
; | |||
CREATE or replace PROCEDURE osmc.grid_generate_all_levels_logistic( | |||
p_lev_max numeric, p_contry text, | |||
p_contry_base integer, | |||
p_id_bitlen integer, -- ex. 37 | |||
p_lev0 numeric default 2.5 | |||
) LANGUAGE plpgsql AS $f$ | |||
DECLARE | |||
tpl text; | |||
cover_frag text; | |||
lev numeric; | |||
s text :=''; | |||
gg text :='geom4326'; | |||
BEGIN | |||
tpl := $$ | |||
SELECT cbits as gid_vbit, geom | |||
FROM osmc.decode_scientific_absolute_geoms_lixo1( | |||
natcod.parents_to_children_baseh(%1$s::real, %3$L::text[], 16, true, true)::text, | |||
%2$L, | |||
%4$s::int | |||
) t | |||
$$; | |||
RAISE NOTICE 'entrei...'; | |||
FOREACH cover_frag IN ( | |||
SELECT array_agg(cod_b16h)::text | |||
FROM ( | |||
select cod_b16h, row_number() over() ord | |||
from grid_br2.poc_caruaru_vw01logistic | |||
where length(gid_vbit)=p_id_bitlen -- ! control here | |||
) t | |||
GROUP BY ord/1000 | |||
) LOOP | |||
s:=''; | |||
RAISE NOTICE 'Loop cover_frag... %s', cover_frag; | |||
FOR lev IN (select x from generate_series(p_lev0, p_lev_max, 2.5) t(x)) LOOP | |||
IF lev>p_lev0 THEN s := s || E'\n UNION ALL \n'; END IF; | |||
s := s || format(tpl, lev::text, p_contry, cover_frag, p_contry_base::text); | |||
END LOOP; -- lev | |||
s := s|| E'\n ORDER BY 1'; | |||
s := E'INSERT INTO grid_b2.tmp_cells \n' || s || E';\n COMMIT;'; | |||
RAISE NOTICE 'RODANDO %', s; | |||
PERFORM s; | |||
END LOOP; -- cover_frag | |||
RAISE NOTICE 'sai...'; | |||
END; | |||
$f$; | |||
CALL osmc.grid_generate_all_levels_logistic( 2.5, 'BR', 27, 18 ) | |||
; -- NADA DE MENSAGEM! | |||
-- nao roda o loop .. devolver a função e gerar texto e rodar na mão. | |||
---------------============================ | |||
DROP FUNCTION if exists osmc.grid_generate_all_levels_logistic2 | |||
; | |||
CREATE or replace FUNCTION osmc.grid_generate_all_levels_logistic2( | |||
p_lev_max numeric, p_contry text, | |||
p_contry_base integer, | |||
p_id_bitlen integer, -- ex. 37 | |||
p_lev0 numeric default 2.5 | |||
) RETURNS text LANGUAGE plpgsql AS $f$ | |||
DECLARE | |||
tpl text; | |||
cover_frag text; | |||
oid_list text[]; | |||
lev numeric; | |||
s text :=''; | |||
gg text :='geom4326'; | |||
BEGIN | |||
tpl := $$ | |||
SELECT cbits as gid_vbit, geom | |||
FROM osmc.decode_scientific_absolute_geoms_lixo1( | |||
natcod.parents_to_children_baseh(%1$s::real, %3$L::text[], 16, true, true)::text, | |||
%2$L, | |||
%4$s::int | |||
) t | |||
$$; | |||
RAISE NOTICE 'entrei...'; | |||
select array_agg(x) FROM ( | |||
SELECT array_agg(cod_b16h)::text as x | |||
FROM ( | |||
select cod_b16h, row_number() over() ord | |||
from grid_br2.poc_caruaru_vw01logistic | |||
where length(gid_vbit)=p_id_bitlen -- ! control here | |||
) t1 | |||
GROUP BY ord/1000 | |||
) t2 INTO oid_list; | |||
FOREACH cover_frag IN ARRAY oid_list LOOP | |||
s:=''; | |||
RAISE NOTICE 'Loop cover_frag... %s', cover_frag; | |||
FOR lev IN (select x from generate_series(p_lev0, p_lev_max, 2.5) t(x)) LOOP | |||
IF lev>p_lev0 THEN s := s || E'\n UNION ALL \n'; END IF; | |||
s := s || format(tpl, lev::text, p_contry, cover_frag, p_contry_base::text); | |||
END LOOP; -- lev | |||
s := s|| E'\n ORDER BY 1'; | |||
s := E'INSERT INTO grid_b2.tmp_cells \n' || s || E';\n COMMIT;'; | |||
RAISE NOTICE 'RODANDO %', s; | |||
s := s || E'\n\n'; | |||
END LOOP; -- cover_frag | |||
RAISE NOTICE 'sai...'; | |||
RETURN s; | |||
END; | |||
$f$; | |||
select osmc.grid_generate_all_levels_logistic2( 2.5, 'BR', 37, 18 ); | |||
</syntaxhighlight> | </syntaxhighlight> |
edições