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;
  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;
$_$;

Ainda não funciona pois a nossa demanda requer subquery, e isso gera erro "cannot use subquery in CALL argument".

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
   );

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.

tentativa 3

Tentando simplesmente devolver a string. Parece que agora funciona:

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,
    p_blocksize int default 100
) RETURNS text LANGUAGE plpgsql  AS $f$
DECLARE
  tpl text;
  cover_frag text;
  oid_list text[];
  lev numeric;
  s   text  :='';
  ret 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
 $$;
 SELECT array_agg(x::text) INTO oid_list
 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)=grid_generate_all_levels_logistic2.p_id_bitlen
      ) t1
      GROUP BY ord/grid_generate_all_levels_logistic2.p_blocksize
 ) t2;
 FOREACH cover_frag IN ARRAY oid_list 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, p_contry_base::text);
    END LOOP; -- lev
    s := s|| E'\n   ORDER BY 1';
    ret := ret || E'\n\nINSERT INTO grid_br2.tmp_cells \n'|| s --|| E';\n COMMIT; \n\n';
  END LOOP; -- cover_frag

  RETURN ret;
END;
$f$;

SELECT volat_file_write(  -- com 37 e 500 dá 5Mb
  '/tmp/inserts_logistic2.sql'
  , osmc.grid_generate_all_levels_logistic2( 2.5, 'BR', 18, 27, 2.5, 300 )
);
-- deu certo!
chmod 777 
psql < sql
---
select count(*) from grid_br2.tmp_cells; -- 16352

select count(*) 
from grid_br2.tmp_cells 
where ST_Intersects(geom,(select geom from grid_br2.municipio where isolabel_ext='BR-PE-Caruaru'))
; --14489
select count(*) from grid_br2.tmp_cells t inner join grid_br2.poc_caruaru c on c.gid_vbit=t.gid_vbit; -- 14489
---- -------------------------------------------
SELECT volat_file_write(
  '/tmp/inserts_logistic2.sql'
  , osmc.grid_generate_all_levels_logistic2( 2.5, 'BR', 18, 37, 2.5, 400 )
);  -- 4828425 byts = 4.7M. Em 17 segundos faz 20*12800. Faz 14 mil celulas por segundo. celulas de 8 metros de lado são 64m2.
-- depois de rodar
delete from grid_br2.tmp_cells where not(ST_Intersects(geom,(select geom from grid_br2.municipio where isolabel_ext='BR-PE-Caruaru')) );
-- DELETE 70068
insert into  grid_br2.poc_caruaru SELECT * FROM grid_br2.tmp_cells ORDER BY gid_vbit;
-- INSERT 0 14420108
delete from grid_br2.tmp_cells;

Como Caruaru tem da ordem de 920 km2, são 14406456 (14 milhões) de células, portanto 1 mil segundos ou 16 minutos.

Indexar não ajudou, outra saida é materializar:

DROP VIEW grid_br2.poc_caruaru_vw01logistic
;
CREATE MATERIALIZED VIEW grid_br2.poc_caruaru_vw01logistic AS
 WITH t AS (
         SELECT poc_caruaru.gid_vbit AS coverbits,
            natcod.vbit_to_strstd("substring"(row_number() OVER (ORDER BY poc_caruaru.gid_vbit)::bit(32), 32 - 4)::bit varying, '32nvu'::text) AS idx_b32nvu
           FROM grid_br2.poc_caruaru
          WHERE ((10 * (length(poc_caruaru.gid_vbit::"bit") - 8))::numeric / 2.0)::integer = 70
          ORDER BY poc_caruaru.gid_vbit
        )
 SELECT natcod.vbit_to_hiddenbig(r.gid_vbit) AS gid,
    ((10 * (length(r.gid_vbit::"bit") - 8))::numeric / 2.0)::integer AS intlevel,
    r.gid_vbit,
    r.geom,
    natcod.vbit_to_str("substring"(r.gid_vbit::"bit", 5)::bit varying, '16h'::text) AS cod_b16h,
    t.idx_b32nvu || COALESCE(natcod.vbit_to_strstd("substring"(r.gid_vbit::"bit", 22 + 1)::bit varying, '32nvu'::text), ''::text) AS cod_b32nvu
   FROM grid_br2.poc_caruaru r
     JOIN t ON r.gid_vbit >= t.coverbits AND r.gid_vbit <= (t.coverbits || '11111111111111111111111111111111111'::"bit"::bit varying)
;
-- Para facilitar a construção do layer no QGIS: 
CREATE UNIQUE INDEX grid_br2_poc_caruaru_mw01logistic_idx1 ON grid_br2.poc_caruaru_vw01logistic (gid);
CREATE INDEX grid_br2_poc_caruaru_mw01logistic_idx2 ON grid_br2.poc_caruaru_vw01logistic (intlevel);

Mais um dígito, o sexto:

SELECT volat_file_write(
  '/tmp/inserts_logistic2.sql'
  , osmc.grid_generate_all_levels_logistic2( 2.5, 'BR', 18, 42, 2.5, 500 )
);  -- 166264053 bytes , duas horas

tentativa 4

Finalmente reescrevendo como procedure:

DROP FUNCTION if exists osmc.grid_generate_all_levels_logistic2
;
DROP PROCEDURE if exists osmc.grid_generate_all_levels_logistic2
;
CREATE PROCEDURE 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
) 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::text) INTO oid_list
 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)=27
      ) t1
      GROUP BY ord/100
 ) t2;
 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_br2.tmp_cells \n' || s || E';\n COMMIT;';
    RAISE NOTICE '.. RODANDO';
    PERFORM s;
    s := '';
  END LOOP; -- cover_frag
  RAISE NOTICE 'sai...';
END;
$f$;

CALL osmc.grid_generate_all_levels_logistic2( 2.5, 'BR', 27, 18 );