Documentação

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

Página do usuário Discussão

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

Edição das 14h12min de 25 de junho de 2024

2 361 bytes adicionados ,  25 junho
→‎Novo processo
(2 revisões intermediárias pelo mesmo usuário não estão sendo mostradas)
Linha 64: Linha 64:
-- alter table grid_br2.lixo_grid_br2caruaru alter column intlevel type int;
-- alter table grid_br2.lixo_grid_br2caruaru alter column intlevel type int;
-- alter table grid_br2.lixo_grid_br2caruaru rename to poc_caruaru;
-- alter table grid_br2.lixo_grid_br2caruaru rename to poc_caruaru;
ALTER TABLE grid_br2.poc_caruaru DROP COLUMN intlevel CASCADE;


UPDATE grid_br2.poc_caruaru SET intLevel=(10*(length(gid_vbit)-8)/2.0)::int;
UPDATE grid_br2.poc_caruaru SET intLevel=(10*(length(gid_vbit)-8)/2.0)::int;
Linha 76: Linha 77:
           natcod.vbit_to_strstd(substring((ROW_NUMBER() OVER(ORDER BY gid_vbit))::bit(32),32-4),'32nvu') as idx_b32nvu
           natcod.vbit_to_strstd(substring((ROW_NUMBER() OVER(ORDER BY gid_vbit))::bit(32),32-4),'32nvu') as idx_b32nvu
   FROM grid_br2.poc_caruaru
   FROM grid_br2.poc_caruaru
   WHERE intlevel=70  -- !!
   WHERE (10*(length(gid_vbit)-8)/2.0)::int =70  -- intlevel da cobertura do municipio
   ORDER BY gid_vbit
   ORDER BY gid_vbit
  )
  )
  SELECT natcod.vbit_to_hiddenbig(r.gid_vbit) as gid,
  SELECT natcod.vbit_to_hiddenbig(r.gid_vbit) as gid, (10*(length(gid_vbit)-8)/2.0)::int as intlevel,
       r.*, natcod.vbit_to_str(substring(gid_vbit,5),'16h') as cod_b16h,
       r.*, natcod.vbit_to_str(substring(gid_vbit,5),'16h') as cod_b16h,
       idx_b32nvu||COALESCE( natcod.vbit_to_strstd(substring(r.gid_vbit,22+1),'32nvu') , '' ) as cod_b32nvu
       idx_b32nvu||COALESCE( natcod.vbit_to_strstd(substring(r.gid_vbit,22+1),'32nvu') , '' ) as cod_b32nvu
  FROM grid_br2.poc_caruaru r INNER JOIN t
  FROM grid_br2.poc_caruaru r INNER JOIN t
   ON r.gid_vbit between t.coverbits and (t.coverbits||b'11111111111111111111111111111111111')
   ON r.gid_vbit between t.coverbits and (t.coverbits||b'11111111111111111111111111111111111')
;
  -- WHERE r.intlevel in (70,95,120,145,170,195)
  -- WHERE r.intlevel in (70,95,120,145,170,195)
  --      length(r.gid_vbit) in (22,27,32,37,42)
  --      length(r.gid_vbit) in (22,27,32,37,42)
</syntaxhighlight>
== Novo processo ==
Ver funções em [[Usuário Discussão:Peter/Entrega2024-06grid]].
# Gera recortando geometria até nivel 7.5 sob zero do municipio.
# acrescenta mais um se tiver disco
<syntaxhighlight lang="sql">
CREATE TABLE grid_br2.poc_NovaFrib AS
  SELECT *
  FROM osmc.grid_generate_all_levels_logistic(
    7.5,
    'BR',
    (select kx_cover_b16h::text from grid_br2.municipio where isolabel_ext='BR-RJ-NovaFriburgo'),
    18,
    0
  )
  WHERE ST_Intersects(geom,(select geom from grid_br2.municipio where isolabel_ext='BR-RJ-NovaFriburgo'))
;
---------
DROP VIEW if exists grid_br2.poc_NovaFrib_vw01logistic
;
CREATE VIEW grid_br2.poc_NovaFrib_vw01logistic AS
WITH t AS (
  SELECT  gid_vbit as coverbits,
          natcod.vbit_to_strstd(substring((ROW_NUMBER() OVER(ORDER BY gid_vbit))::bit(32),32-4),'32nvu') as idx_b32nvu
  FROM grid_br2.poc_NovaFrib
  WHERE length(gid_vbit)=22 --intlevel 70 da cobertura do municipio
  ORDER BY gid_vbit
)
SELECT natcod.vbit_to_hiddenbig(r.gid_vbit) as gid, (10*(length(gid_vbit)-8)/2.0)::int as intlevel,
      r.*, natcod.vbit_to_str(substring(gid_vbit,5),'16h') as cod_b16h,
      idx_b32nvu||COALESCE( natcod.vbit_to_strstd(substring(r.gid_vbit,22+1),'32nvu') , '' ) as cod_b32nvu
FROM grid_br2.poc_NovaFrib r INNER JOIN t
  ON r.gid_vbit between t.coverbits and (t.coverbits||b'11111111111111111111111111111111111')
;
----
CREATE TABLE grid_br2.poc_NovaFrib2 AS
  SELECT *
  FROM osmc.grid_generate_all_levels_logistic(
    2.5,
    'BR',
    (select array_agg(cod_b16h)::text from grid_br2.poc_caruaru_vw01logistic where length(gid_vbit)=37),
    18
  )
;
;
</syntaxhighlight>
</syntaxhighlight>
<pre>
select round(intlevel/10.0,1) as hlevel, length(gid_vbit) as gid_len,      count(*) n,
      round(sqrt(avg(st_area(geom)))/1000.0,2) as side_km
from grid_br2.poc_caruaru_vw01logistic
group by 1,2
order by 1,2;
hlevel | gid_len |  n    | side_km
--------+---------+--------+---------
    7.0 |      22 |    25 |    8.19
    9.5 |      27 |    511 |    1.45
  12.0 |      32 |  14489 |    0.26
  14.5 |      37 | 452818 |    0.05
</pre>
== Testes de busca ==
shp2pgsql -s 4326  -W LATIN1 adm_num_porta_a | psql -U postgres lixo5
Peter
2 391

edições

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