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
-- 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)
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
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
)
;
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
Testes de busca
shp2pgsql -s 4326 -W LATIN1 adm_num_porta_a | psql -U postgres lixo5