Usuário Discussão:Peter/Entrega2024-06grid

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;
  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
 $$;
UNNEST WITH ORDINALITY
GROUP BY ORD%1000   FAZ O FRAG 
p_contry_cover  quebrada em pedacos cover_frag de 1000 items
 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;
  s := s|| E'\n   ORDER BY 1';
  RETURN QUERY EXECUTE s;
END;
$_$;