2 585
edições
Linha 88: | Linha 88: | ||
-- 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 | |||
<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') | |||
; | |||
</syntaxhighlight> | </syntaxhighlight> |
edições