2 544
edições
m (→tentativa 4) |
m (→tentativa 3) |
||
(10 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 | ||
$$; | $$; | ||
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)= | where length(gid_vbit)=grid_generate_all_levels_logistic2.p_id_bitlen | ||
) t1 | ) t1 | ||
GROUP BY ord/ | 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:=''; | |||
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'; | ||
ret := ret || E'\n\nINSERT INTO grid_br2.tmp_cells \n'|| s --|| E';\n COMMIT; \n\n'; | |||
END LOOP; -- cover_frag | END LOOP; -- cover_frag | ||
RETURN | RETURN ret; | ||
END; | END; | ||
$f$; | $f$; | ||
select osmc.grid_generate_all_levels_logistic2( 2.5, 'BR', 37, | 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 === |
edições