Usuário:Peter/Entrega2024-06grid: mudanças entre as edições

Linha 62:

DROP VIEW if exists ~~lixo_grid_br2caruaru_codes~~

-- alter table grid_br2.lixo_grid_br2caruaru alter column intlevel type int;

-- alter table grid_br2.lixo_grid_br2caruaru rename to poc_caruaru;

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 ~~lixo_grid_br2caruaru_codes~~ AS

CREATE VIEW grid_br2.poc_caruaru_vw01logistic AS

WITH t AS (

SELECT gid_vbit as coverbits~~, length(gid_vbit) as coverlen~~,

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 ~~lixo_grid_br2caruaru where~~ intlevel=0 ORDER BY gid_vbit

FROM grid_br2.poc_caruaru

WHERE intlevel=70 -- !!

ORDER BY gid_vbit

)

SELECT r.*, natcod.vbit_to_str(substring(gid_vbit,5),'16h') as cod_b16h,

SELECT natcod.vbit_to_hiddenbig(r.gid_vbit) as gid,

idx_b32nvu||COALESCE(natcod.vbit_to_strstd(substring(r.gid_vbit,22+1),'32nvu'),'') as cod_b32nvu

r.*, natcod.vbit_to_str(substring(gid_vbit,5),'16h') as cod_b16h,

FROM ~~lixo_grid_br2caruaru~~ r INNER JOIN t

idx_b32nvu||COALESCE( natcod.vbit_to_strstd(substring(r.gid_vbit,22+1),'32nvu') , '' ) as cod_b32nvu

ON r.gid_vbit between t.coverbits and (t.coverbits||b'~~1111111111111111~~')

FROM grid_br2.poc_caruaru r INNER JOIN t

WHERE r.intlevel in (0,25,50,75,~~100~~)

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>