Usuário:Peter/Entrega2024-06grid: mudanças entre as edições
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 | 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> | </syntaxhighlight> |
Edição das 23h42min de 24 de junho de 2024
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
-- 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.poc_caruaru DROP COLUMN intlevel CASCADE;
UPDATE grid_br2.poc_caruaru SET intLevel=(10*(length(gid_vbit)-8)/2.0)::int;
DELETE FROM grid_br2.poc_caruaru WHERE intLevel NOT IN (70,95,120);
DELETE FROM grid_br2.poc_caruaru WHERE NOT( ST_Intersects(geom,(select geom from grid_br2.municipio where isolabel_ext='BR-PE-Caruaru')) );
DROP VIEW if exists grid_br2.poc_caruaru_vw01logistic
;
CREATE VIEW grid_br2.poc_caruaru_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_caruaru
WHERE (10*(length(gid_vbit)-8)/2.0)::int =70 -- intlevel 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_caruaru r INNER JOIN t
ON r.gid_vbit between t.coverbits and (t.coverbits||b'11111111111111111111111111111111111')
;
-- WHERE r.intlevel in (70,95,120,145,170,195)
-- length(r.gid_vbit) in (22,27,32,37,42)