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 08h54min de 28 de junho de 2024

4 005 bytes adicionados ,  28 junho
→‎Geração da grade
m (→‎Novo processo: revisando algoritmo para big text)
(5 revisões intermediárias pelo mesmo usuário não estão sendo mostradas)
Linha 3: Linha 3:


<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
CREATE FUNCTION osmc.decode_scientific_absolute_geoms_lixo1(
CREATE FUNCTION osmc.decode_scientific_absolute_geoms_lixo1(
p_code text, p_iso text,  
p_code text, p_iso text,  
Linha 66: Linha 65:
     ) v
     ) v
$f$;
$f$;
</syntaxhighlight>
=== Geração da grade ===


<syntaxhighlight lang="sql">
drop FUNCTION osmc.grid_generate_all_levels_logistic;
drop FUNCTION osmc.grid_generate_all_levels_logistic;
CREATE or replace FUNCTION osmc.grid_generate_all_levels_logistic(
CREATE or replace FUNCTION osmc.grid_generate_all_levels_logistic(
Linha 78: Linha 81:
DECLARE
DECLARE
   tpl text;
   tpl text;
  cover_frag text[];
   lev numeric;
   lev numeric;
   s  text  :='';
   s  text  :='';
Linha 90: Linha 94:
   ) t
   ) t
  $$;
  $$;
UNNEST ORD  
  FOR cover_frag IN (
GROUP BY ORD%1000  FAZ O FRAG
    select array_agg(x)
p_contry_cover quebrada em pedacos cover_frag de 1000 items
    from unnest(p_contry_cover) WITH ORDINALITY t(x,ord)
  FOR lev IN (select x from generate_series(p_lev0, p_lev_max, 2.5) t(x)) LOOP
    group by ord/1000
    IF lev>p_lev0 THEN s := s || E'\n UNION ALL \n'; END IF;
  ) LOOP
    s := s || format(tpl, lev::text, p_contry, cover_frag, p_contry_base::text);
    FOR lev IN (select x from generate_series(p_lev0, p_lev_max, 2.5) t(x)) LOOP
   END 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';
   s := s|| E'\n  ORDER BY 1';
   RETURN QUERY EXECUTE s;
   RETURN QUERY EXECUTE s;
END;
END;
$_$;
$_$;
</syntaxhighlight>
Ainda não funciona pois a nossa demanda requer subquery, e isso gera erro "cannot use subquery in CALL argument".
<syntaxhighlight lang="sql" style="font-size: 80%;">
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
  );
</syntaxhighlight>
<syntaxhighlight lang="sql" style="font-size: 80%;">
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.
---------------============================
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
) RETURNS text 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) 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)=p_id_bitlen  -- ! control here
    ) t1
    GROUP BY ord/1000
) t2    INTO oid_list;
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_b2.tmp_cells \n' || s || E';\n COMMIT;';
    RAISE NOTICE 'RODANDO %', s;
    s := s || E'\n\n';
  END LOOP; -- cover_frag
  RAISE NOTICE 'sai...';
  RETURN s;
END;
$f$;
select  osmc.grid_generate_all_levels_logistic2( 2.5, 'BR', 37, 18 );
</syntaxhighlight>
</syntaxhighlight>
Peter
2 391

edições

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