Usuário Discussão:Peter/Entrega2024-06grid: mudanças entre as edições
(→Geração da grade: procedure) |
|||
Linha 114: | Linha 114: | ||
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
CREATE TABLE | DROP TABLE if exists grid_br2.tmp_cells CASCADE; | ||
CREATE TABLE grid_br2.tmp_cells (gid_vbit bit varying, geom public.geometry); | |||
drop PROCEDURE osmc.grid_generate_all_levels_logistic; | drop PROCEDURE if exists osmc.grid_generate_all_levels_logistic; | ||
CREATE or replace PROCEDURE osmc.grid_generate_all_levels_logistic( | CREATE or replace PROCEDURE osmc.grid_generate_all_levels_logistic( | ||
p_lev_max numeric, p_contry text, | p_lev_max numeric, p_contry text, | ||
Linha 122: | Linha 123: | ||
p_contry_base integer, | p_contry_base integer, | ||
p_lev0 numeric default 2.5 | p_lev0 numeric default 2.5 | ||
) | ) LANGUAGE plpgsql AS $f$ | ||
DECLARE | DECLARE | ||
tpl text; | tpl text; | ||
Linha 144: | Linha 144: | ||
group by ord/1000 | group by ord/1000 | ||
) LOOP | ) LOOP | ||
s:=''; | |||
FOR lev IN (select x from generate_series(p_lev0, p_lev_max, 2.5) t(x)) 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; | 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); | s := s || format(tpl, lev::text, p_contry, cover_frag::text, p_contry_base::text); | ||
END LOOP; -- lev | END LOOP; -- lev | ||
s := s|| E'\n ORDER BY 1'; | |||
s := E'INSERT INTO grid_b2.tmp_cells \n' || s || E';\n COMMIT;'; | |||
PERFORM s; | |||
END LOOP; -- cover_frag | END LOOP; -- cover_frag | ||
END; | END; | ||
$ | $f$; | ||
</syntaxhighlight> | </syntaxhighlight> |
Edição das 07h39min de 28 de junho de 2024
Novo processo
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)
WHEN p_base <> 18 AND length(code16h) > 11 AND up_iso IN ('EC','CO','UY') THEN substring(code16h,1,11)
WHEN p_base <> 18 AND length(code16h) > 10 AND up_iso IN ('CM') THEN substring(code16h,1,10)
WHEN p_base = 18 AND length(code) > 11 AND up_iso IN ('BR') THEN substring(code,1,11)
WHEN p_base = 18 AND length(code) > 10 AND up_iso IN ('UY') THEN substring(code,1,10)
ELSE (CASE WHEN p_base=18 THEN code ELSE code16h END)
END AS code_tru,
-- flag
CASE
WHEN p_base <> 18 AND length(code16h) > 12 AND up_iso IN ('BR') THEN TRUE
WHEN p_base <> 18 AND length(code16h) > 11 AND up_iso IN ('EC','CO','UY') THEN TRUE
WHEN p_base <> 18 AND length(code16h) > 10 AND up_iso IN ('CM') THEN TRUE
WHEN p_base = 18 AND length(code) > 11 AND up_iso IN ('BR') THEN TRUE
WHEN p_base = 18 AND length(code) > 10 AND up_iso IN ('UY') THEN TRUE
ELSE NULL
END AS truncated_code,
-- vbit code16h
CASE
WHEN length(code16h) > 12 AND up_iso IN ('BR') THEN natcod.baseh_to_vbit(substring(code16h,1,12),16)
WHEN length(code16h) > 11 AND up_iso IN ('EC','CO','UY') THEN natcod.baseh_to_vbit(substring(code16h,1,11),16)
WHEN length(code16h) > 10 AND up_iso IN ('CM') THEN natcod.baseh_to_vbit(substring(code16h,1,10),16)
ELSE natcod.baseh_to_vbit(code16h,16)
END AS codebits,
code,up_iso
FROM
(
SELECT code, upper(p_iso) AS up_iso,
CASE
WHEN p_base = 18 THEN osmc.decode_16h1c(code,upper(p_iso))
ELSE code
END AS code16h
FROM regexp_split_to_table(lower(trim(p_code,'{}')),',') code
) u
) c,
LATERAL
(
SELECT ggeohash.draw_cell_bybox(ggeohash.decode_box2(osmc.vbit_withoutL0(codebits,c.up_iso),bbox, CASE WHEN c.up_iso='EC' THEN TRUE ELSE FALSE END),false,ST_SRID(geom)) AS geom
FROM osmc.coverage
WHERE is_country IS TRUE AND isolabel_ext = c.up_iso -- cobertura nacional apenas
AND
CASE
WHEN up_iso IN ('CO','CM') THEN ( ( osmc.extract_L0bits(cbits,'CO') # codebits::bit(4) ) = 0::bit(4) ) -- 1 dígito base16h
ELSE ( ( osmc.extract_L0bits(cbits,up_iso) # codebits::bit(8) ) = 0::bit(8) ) -- 2 dígitos base16h
END
) v
$f$;
Geração da grade
drop 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_base integer,
p_lev0 numeric default 2.5
) RETURNS TABLE(gid_vbit bit varying, geom public.geometry)
LANGUAGE plpgsql IMMUTABLE
AS $_$
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
$$;
FOR cover_frag IN (
select array_agg(x)
from unnest(p_contry_cover) WITH ORDINALITY t(x,ord)
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';
RETURN QUERY EXECUTE s;
END;
$_$;
Infelizmente, por não realizar commit, essa estratégia não funciona, nem adianta tentar corrigir... Gravando tudo numa tabela temporária.
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_cover text[],
p_contry_base integer,
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
$$;
FOR cover_frag IN (
select array_agg(x)
from unnest(p_contry_cover) WITH ORDINALITY t(x,ord)
group by ord/1000
) LOOP
s:='';
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
s := s|| E'\n ORDER BY 1';
s := E'INSERT INTO grid_b2.tmp_cells \n' || s || E';\n COMMIT;';
PERFORM s;
END LOOP; -- cover_frag
END;
$f$;