2 391
edições
(Criou página com ' == Novo processo == <syntaxhighlight lang="sql"> CREATE FUNCTION osmc.decode_scientific_absolute_geoms_lixo1( p_code text, p_iso text, p_base integer DEFAULT 16 ) RETURNS TABLE(cbits bit varying, geom public.geometry) LANGUAGE sql IMMUTABLE AS $f$ SELECT codebits, v.geom FROM ( SELECT DISTINCT code16h, -- trunca CASE WHEN p_base <> 18 AND length(code16h) > 12 AND up_iso IN ('BR') THEN substring(code16h,1,12)...') |
|||
(7 revisões intermediárias pelo mesmo usuário não estão sendo mostradas) | |||
Linha 3: | Linha 3: | ||
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
CREATE FUNCTION osmc.decode_scientific_absolute_geoms_lixo1( | CREATE FUNCTION osmc.decode_scientific_absolute_geoms_lixo1( | ||
p_code text, p_iso text, | p_code text, p_iso text, | ||
Linha 66: | Linha 65: | ||
) v | ) v | ||
$f$; | $f$; | ||
</syntaxhighlight> | |||
=== Geração da grade === | |||
<syntaxhighlight lang="sql"> | |||
drop FUNCTION osmc.grid_generate_all_levels_logistic; | |||
CREATE or replace FUNCTION osmc.grid_generate_all_levels_logistic( | CREATE or replace FUNCTION osmc.grid_generate_all_levels_logistic( | ||
p_lev_max numeric, p_contry text, | |||
p_contry_cover text, | p_contry_cover text[], | ||
p_contry_base integer | p_contry_base integer, | ||
p_lev0 numeric default 2.5 | |||
) RETURNS TABLE(gid_vbit bit varying, geom public.geometry) | ) RETURNS TABLE(gid_vbit bit varying, geom public.geometry) | ||
LANGUAGE plpgsql IMMUTABLE | LANGUAGE plpgsql IMMUTABLE | ||
Linha 76: | Linha 81: | ||
DECLARE | DECLARE | ||
tpl text; | tpl text; | ||
cover_frag text[]; | |||
lev numeric; | lev numeric; | ||
s text :=''; | s text :=''; | ||
gg text :='geom4326'; | gg text :='geom4326'; | ||
Linha 89: | Linha 94: | ||
) t | ) t | ||
$$; | $$; | ||
FOR lev IN (select x from generate_series( | FOR cover_frag IN ( | ||
select array_agg(x) | |||
from unnest(p_contry_cover) WITH ORDINALITY t(x,ord) | |||
END LOOP; | group by ord/1000 | ||
) LOOP | |||
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::text, p_contry_base::text); | |||
END LOOP; -- lev | |||
END LOOP; -- cover_frag | |||
s := s|| E'\n ORDER BY 1'; | s := s|| E'\n ORDER BY 1'; | ||
RETURN QUERY EXECUTE s; | RETURN QUERY EXECUTE s; | ||
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