Usuário:Peter/Entrega2024-06grid

create table lixo_grid_br as  SELECT gid_vbit, 10*hlevel as intlevel,  geom
  FROM osmc.grid_generate_all_levels(5, 'BR', '{0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,fT,fY,fP,fN}', 18, 0.005, 0.00000005)
  WHERE ST_intersects(geom, (select st_transform(geom,952019) from grid_br.isoadmin_l0)) 
; -- sem interseção 31725 linhas, com 17430. Em 5 minutos

create table lixo_grid_br as  SELECT gid_vbit, 10*hlevel as intlevel,  geom
  FROM osmc.grid_generate_all_levels(8.5, 'BR', '{0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,fT,fY,fP,fN}', 18, 0.005, 0.00000005)
  WHERE ST_intersects(geom, (select st_transform(geom,952019) from grid_br.isoadmin_l0)) 
; -- em ? minutos... muito longo. Cancelado. 

create table lixo_grid_br2 as  SELECT gid_vbit, 10*hlevel as intlevel,  geom
  FROM osmc.grid_generate_all_levels(8.5, 'BR', '{d}', 18, 0.005, 0.00000005)
 -- tempo ~10min. 262143 linhas

create table lixo_grid_br2sp as  SELECT gid_vbit, 10*hlevel as intlevel,  geom
  FROM osmc.grid_generate_all_levels(8.5, 'BR', '{a1,a4,a5,ea}', 18, 0.005, 0.00000005)
 -- ~30 min, 1048572 linhas

create table lixo_grid_br2caruaru AS SELECT gid_vbit, 10*hlevel as intlevel,  geom
  FROM osmc.grid_generate_all_levels(12.5, 'BR', '{82a4V,82a5R,82a5V,82a6H,82a6M,82a6R,82a6V,82a7H,82a7M,82a7R,82a4R,82a7V,82a9M,82a9V,82abM,82acH,82acM,82acR,82acV,82adH,82adR,82aeH,82aeM,82afH,82afM}', 18, 0.005, 0.00000005);    -- começou no L7 vai até 20-7=13

A Grade científica para ilustrar e analisar a escolha das coberturas municipais tem esse perfil:

select intlevel/10.0 as hlevel, length(gid_vbit) as gid_len,
       count(*) n,
       round(sqrt(avg(st_area(geom)))/1000.0,2) as side_km
from eneas_grid_br
group by 1,2
order by 1,2;

 hlevel | gid_len |   n    | side_km 
--------+---------+--------+---------
      0 |       8 |     15 | 1048.58
      0 |      11 |      3 |  370.73
    0.5 |       9 |     27 |  741.46
      1 |      10 |     49 |  524.29
    1.5 |      11 |     92 |  370.73
      2 |      12 |    167 |  262.14
    2.5 |      13 |    315 |  185.36
      3 |      14 |    592 |  131.07
    3.5 |      15 |   1140 |   92.68
      4 |      16 |   2209 |   65.54
    4.5 |      17 |   4335 |   46.34
      5 |      18 |   8486 |   32.77
    5.5 |      19 |   2048 |   23.17
      6 |      20 |   4096 |   16.38
    6.5 |      21 |   8192 |   11.59
      7 |      22 |  16384 |    8.19
    7.5 |      23 |  32768 |    5.79
      8 |      24 |  65536 |    4.10
    8.5 |      25 | 131072 |    2.90
      9 |      26 |  65536 |    2.05
    9.5 |      27 | 131072 |    1.45
     10 |      28 | 262144 |    1.02
   10.5 |      29 | 524288 |    0.72
(23 rows)

Views de cada municipio

DROP VIEW if exists lixo_grid_br2caruaru_codes
;
CREATE VIEW lixo_grid_br2caruaru_codes AS
 WITH t AS (
  SELECT  gid_vbit as coverbits, length(gid_vbit) as coverlen,
          natcod.vbit_to_strstd(substring((ROW_NUMBER() OVER(ORDER BY gid_vbit))::bit(32),32-4),'32nvu') as idx_b32nvu
  FROM lixo_grid_br2caruaru where intlevel=0 ORDER BY gid_vbit
 )
 SELECT 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 lixo_grid_br2caruaru r INNER JOIN t
   ON r.gid_vbit between t.coverbits and (t.coverbits||b'1111111111111111')
 WHERE r.intlevel in (0,25,50,75,100)
;