Usuário:Peter/Entrega2024-06grid: mudanças entre as edições
(Criou página com ' <pre> 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) ; </pre>') |
|||
(5 revisões intermediárias pelo mesmo usuário não estão sendo mostradas) | |||
Linha 1: | Linha 1: | ||
<syntaxhighlight lang="sql"> | |||
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 | |||
</syntaxhighlight> | |||
A Grade científica para ilustrar e analisar a escolha das coberturas municipais tem esse perfil: | |||
<pre> | <pre> | ||
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 | |||
FROM osmc. | --------+---------+--------+--------- | ||
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) | |||
</pre> | |||
== Views de cada municipio == | |||
<syntaxhighlight lang="sql"> | |||
-- 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) | |||
</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> | |||
<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> | </pre> | ||
== Testes de busca == | |||
shp2pgsql -s 4326 -W LATIN1 adm_num_porta_a | psql -U postgres lixo5 |
Edição das 14h12min de 25 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)
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