Documentação

Usuário Discussão:Peter/Entrega2024-06grid: mudanças entre as edições

Página do usuário Discussão

Usuário Discussão:Peter/Entrega2024-06grid (ver código-fonte)

Edição das 09h38min de 1 de julho de 2024

2 833 bytes adicionados ,  1 julho
m
→‎tentativa 3
Sem resumo de edição
 
(11 revisões intermediárias pelo mesmo usuário não estão sendo mostradas)
Linha 185: Linha 185:
;
;
CREATE or replace FUNCTION osmc.grid_generate_all_levels_logistic2(
CREATE or replace FUNCTION osmc.grid_generate_all_levels_logistic2(
     p_lev_max numeric, p_contry text,
     p_lev_max numeric,
    p_contry text,
p_contry_base integer,
p_contry_base integer,
     p_id_bitlen integer,  -- ex. 37
     p_id_bitlen integer,  -- ex. 37
     p_lev0 numeric default 2.5
     p_lev0 numeric default 2.5,
    p_blocksize int default 100
) RETURNS text LANGUAGE plpgsql  AS $f$
) RETURNS text LANGUAGE plpgsql  AS $f$
DECLARE
DECLARE
Linha 196: Linha 198:
   lev numeric;
   lev numeric;
   s  text  :='';
   s  text  :='';
  ret text :='';
   gg  text  :='geom4326';
   gg  text  :='geom4326';
BEGIN
BEGIN
Linha 206: Linha 209:
   ) t
   ) t
  $$;
  $$;
  RAISE NOTICE 'entrei...';
  SELECT array_agg(x::text) INTO oid_list
select array_agg(x::text) INTO oid_list
  FROM (
  FROM (
   SELECT array_agg(cod_b16h)::text as x  
   SELECT array_agg(cod_b16h)::text as x  
Linha 213: Linha 215:
         select cod_b16h, row_number() over() ord
         select cod_b16h, row_number() over() ord
         from grid_br2.poc_caruaru_vw01logistic
         from grid_br2.poc_caruaru_vw01logistic
         where length(gid_vbit)=27
         where length(gid_vbit)=grid_generate_all_levels_logistic2.p_id_bitlen
       ) t1
       ) t1
       GROUP BY ord/100
       GROUP BY ord/grid_generate_all_levels_logistic2.p_blocksize
  ) t2;
  ) t2;
  FOREACH cover_frag IN ARRAY oid_list LOOP
  FOREACH cover_frag IN ARRAY oid_list LOOP
    s:='';
    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
     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;
Linha 225: Linha 226:
     END LOOP; -- lev
     END LOOP; -- lev
     s := s|| E'\n  ORDER BY 1';
     s := s|| E'\n  ORDER BY 1';
     s := E'INSERT INTO grid_b2.tmp_cells \n' || s || E';\n COMMIT;';
     ret := ret || E'\n\nINSERT INTO grid_br2.tmp_cells \n'|| s --|| E';\n COMMIT; \n\n';
    RAISE NOTICE 'RODANDO %', s;
    s := s || E'\n\n';
   END LOOP; -- cover_frag
   END LOOP; -- cover_frag
  RAISE NOTICE 'sai...';
 
   RETURN s;
   RETURN ret;
END;
END;
$f$;
$f$;


select  osmc.grid_generate_all_levels_logistic2( 2.5, 'BR', 37, 18 );
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;
</syntaxhighlight>
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:
<syntaxhighlight lang="sql" style="font-size: 80%;">
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);
</syntaxhighlight>
</syntaxhighlight>
Mais um dígito, o sexto:
<pre>
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
</pre>


=== tentativa 4 ===
=== tentativa 4 ===
Linha 286: Linha 343:
     END LOOP; -- lev
     END LOOP; -- lev
     s := s|| E'\n  ORDER BY 1';
     s := s|| E'\n  ORDER BY 1';
     s := E'INSERT INTO grid_b2.tmp_cells \n' || s || E';\n COMMIT;';
     s := E'INSERT INTO grid_br2.tmp_cells \n' || s || E';\n COMMIT;';
     RAISE NOTICE '.. RODANDO';
     RAISE NOTICE '.. RODANDO';
     PERFORM s;
     PERFORM s;
Linha 292: Linha 349:
   END LOOP; -- cover_frag
   END LOOP; -- cover_frag
   RAISE NOTICE 'sai...';
   RAISE NOTICE 'sai...';
  RETURN s;
END;
END;
$f$;
$f$;


select  osmc.grid_generate_all_levels_logistic2( 2.5, 'BR', 37, 18 );
CALL osmc.grid_generate_all_levels_logistic2( 2.5, 'BR', 27, 18 );
 
</syntaxhighlight>
</syntaxhighlight>
Peter
2 402

edições

Disponível em “https://wiki.addressforall.org/doc/Especial:MobileDiff/4168...4182”