Grade logística dos municípios Brasil: mudanças entre as edições

De Documentação
m (format)
 
(11 revisões intermediárias por 2 usuários não estão sendo mostradas)
Linha 1: Linha 1:
<syntaxhighlight lang="sql">
Caso seja a primeira vez executando as instruções desse tutorial, siga as etapas 1 e 2. Caso contrário, apenas a etapa 2 será necessária.
create database <NOME_BASE_DADOS>;
</syntaxhighlight>Insira o comando abaixo para entrar no banco recém-criado<syntaxhighlight lang="psql">
\c <nome_base_de_dados>;
</syntaxhighlight>Habilite a base de dados para trabalhar com dados geográficos por meio do comando abaixo.<syntaxhighlight lang="psql">
create extension postgis;
</syntaxhighlight>Com a base de dados criada e habilitada para trabalhar com dados geográficos, agora precisamos prepará-la para lidar com rotulação hierárquica por meio de scripts SQL do repositório https://github.com/AddressForAll/WhitePaper01.


Vá para o diretório <code>/var/gits/_a4a/</code> e insira o comando abaixo para clonar o repositório WhitePaper01.
===Etapa 1 - Criação e configuração de base de dados===


<syntaxhighlight lang="sh">
No ambiente Ubuntu Linux, com o SGBD PostgreSQL versão 14 instalado, Postgis instalado na versão 3, o usuário <code>postgres</code> criado e o serviço configurado na porta 5432, siga os passos abaixo para criar uma base de dados. Primeiro, altere para o usuário postgres usando o comando:<syntaxhighlight lang="bash">
git clone https://github.com/AddressForAll/WhitePaper01
sudo -i -u postgres
</syntaxhighlight>Em seguida, vá para a interface de linha de comando (CLI) do postgreSQL inserindo o comando:<syntaxhighlight lang="bash">
psql
</syntaxhighlight>Na CLI do postgresSQL, insira o comando DDL abaixo para criar a base de dados <code>db_br_municipios</code>:<syntaxhighlight lang="sql">
create database db_br_municipios;
</syntaxhighlight>Com a base de dados criada, alterne para o usuário <code>root</code> do linux por meio do comando <code>\q</code>, posteriormente o comando <code>exit</code>, e insira o comando abaixo para baixar o script de preparação da base de dados:<syntaxhighlight lang="bash">
wget -P /tmp http://www.addressforall.org/_private/prepare1.sql
</syntaxhighlight>Logo em seguida, alterne para o usuário <code>postgres</code> (<code>sudo -i -u postgres</code>) e insira o seguinte comando para configurar a base de dados recém-criada:<syntaxhighlight lang="bash">
psql db_br_municipios < /tmp/prepare1.sql
</syntaxhighlight>
</syntaxhighlight>


Acesse o diretório <code>/var/gits/_a4a/WhitePaper01/sql</code> e execute sequencialmente os comandos abaixo.
===Etapa 2 - Carregamento de dados e visualização no QGIS===


<syntaxhighlight lang="sh">
psql $nome_base_de_dados < prepare0-binCodes.sql
psql $nome_base_de_dados < prepare1-binCodes.sql
psql $nome_base_de_dados < prepare1a-asserts.sql
psql $nome_base_de_dados < prepare1b-demo.sql | diff - ../data/prepare1b-demo.txt
psql $nome_base_de_dados < prepare2-baseConv.sql
psql $nome_base_de_dados < prepare2a-asserts.sql
psql $nome_base_de_dados < prepare2b-demo.sql | diff - ../data/prepare2b-demo.txt
</syntaxhighlight>


A base de dados está pronta para receber as grades do Brasil. Agora vá para o diretório <code>/tmp</code> e execute o comando abaixo para fazer download das grades do Brasil.
De volta para o usuário <code>root</code>,o próximo passo é baixar os dados das grades dos municípios para serem inseridos na base de dados recém-criada e configurada, por meio do comando:


<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
wget http://www.addressforall.org/_private/grid_br_dump2.sql.gz
wget -P /tmp http://www.addressforall.org/_private/grid_br2.sql.gz
</syntaxhighlight>Dê as permissões necessárias ao arquivo recém-baixado para que o mesmo possa ser descompactado. Após isso, mude o usuário do Ubuntu para o usuário postgres por meio do comando:<syntaxhighlight lang="bash">
</syntaxhighlight>
sudo -i -u postgres
 
</syntaxhighlight>Agora vá para o diretório <code>/tmp</code>, e execute o comando abaixo:<syntaxhighlight lang="bash">
Ao fim do ''download'', dê permissão total ao arquivo <code>grid_br2.sql.gz</code> e alterne novamente para o usuário <code>postgres</code> (<code>sudo -i -u postgres</code>).
gunzip -c grid_br_dump2.sql.gz | <nome_base_de_dados>
</syntaxhighlight>Após a finalizar o carregamento das grades do brasil na base de dados, algumas configurações adicionais ainda são necessárias. Ainda no usuário postgres, mude para a interface de linha de comando (CLI) do postgres com o comando:<syntaxhighlight lang="bash">
psql
</syntaxhighlight>entre na base de dados onde acabamos de carregar as grade do brasil:<syntaxhighlight lang="bash">
\c <nome_base_de_dados>
</syntaxhighlight>Insira o script SQL abaixo:<syntaxhighlight lang="sql">
/**
* System's Public library (commom for many scripts)
* Module: PostGIS general complements. Fragment.
*/


CREATE extension IF NOT EXISTS postgis;
Execute o comando abaixo para inserir os dados das grades dos municípios na base de dados <code>db_br_municipios</code>:


INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) VALUES
<syntaxhighlight lang="bash">
  -- -- -- --
gunzip -c /tmp/grid_br2.sql.gz | psql db_br_municipios
(  -- IBGE Albers, SRID number convention in Project DigitalGuard-BR:
</syntaxhighlight>
  952019,
  'BR:IBGE',
  52019,
  '+proj=aea +lat_0=-12 +lon_0=-54 +lat_1=-2 +lat_2=-22 +x_0=5000000 +y_0=10000000 +ellps=WGS84 +units=m +no_defs',
  $$PROJCS[
  "Conica_Equivalente_de_Albers_Brasil",
  GEOGCS[
    "GCS_SIRGAS2000",
    DATUM["D_SIRGAS2000",SPHEROID["Geodetic_Reference_System_of_1980",6378137,298.2572221009113]],
    PRIMEM["Greenwich",0],
    UNIT["Degree",0.017453292519943295]
  ],
  PROJECTION["Albers"],
  PARAMETER["standard_parallel_1",-2],
  PARAMETER["standard_parallel_2",-22],
  PARAMETER["latitude_of_origin",-12],
  PARAMETER["central_meridian",-54],
  PARAMETER["false_easting",5000000],
  PARAMETER["false_northing",10000000],
  UNIT["Meter",1]
]$$
),
  -- -- -- --
( -- Grid of Colombia, IGAC MAGNA-SIRGAS / Origen-Nacional:
  9377, -- official EPSG number
  'CO:IGAC',
  9377,
  '+proj=tmerc +lat_0=4.0 +lon_0=-73.0 +k=0.9992 +x_0=5000000 +y_0=2000000 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs',
  $$PROJCS[
    "MAGNA-SIRGAS / Origen-Nacional",
    GEOGCS[
      "MAGNA-SIRGAS",
      DATUM[
        "Marco_Geocentrico_Nacional_de_Referencia",
        SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],
        TOWGS84[0,0,0,0,0,0,0],
        AUTHORITY["EPSG","6686"]
      ],
      PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
      UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],
      AUTHORITY["EPSG","4686"]
    ],
    PROJECTION["Transverse_Mercator"],
    PARAMETER["latitude_of_origin",4.0],
    PARAMETER["central_meridian",-73.0],
    PARAMETER["scale_factor",0.9992],
    PARAMETER["false_easting",5000000],
    PARAMETER["false_northing",2000000],
    UNIT["metre",1,AUTHORITY["EPSG","9001"]],
    AUTHORITY["EPSG","9377"]
  ]$$
),
  -- -- -- --
  --  "DGGS projections" from SRID 955001 to 955099.
  --
( -- rHEALPix default, PROJ v4.8+
  955001,  'DGGS:001:rHEALPix',  1,
  '+proj=rhealpix',
  NULL -- no srtext
),
( -- rHEALPix variant 2, PROJ v4.8+
  955002,  'DGGS:002:rHEALPix',  2,
  '+proj=rhealpix +ellps=WGS84 +south_square=0 +north_square=2',
  NULL -- no srtext
),


( -- QSC default, PROJ v?+
Após a finalizar o carregamento das grades dos municípios na base de dados, precisamos visualizar na ferramenta recomendada, o QGIS. Para mais detalhes sobre a instalação da ferramenta ver [[sup:Usando o QGIS no A4A|Suporte QGIS]].
  955010, 'DGGS:010:QSC',  10,
  '+proj=qsc',
  NULL -- no srtext
),
--( -- S2 default, PROJ v8.2+
--  955020,  'DGGS:020:S2',  20,
--  '+proj=s2',
--  NULL -- no srtext
--),


( -- ISEA default, PROJ v?+ (without inverse!)
Para reaproveitar um projeto pré-configurado do QGIS,  para  esses dados de grades dos municípios, podemos baixar o existente. Alterne para o usuário <code>root</code> (<code>exit</code>). Vá para o diretório na sua máquina aonde o QGIS salva os projetos, e execute o comando abaixo:
  955030, 'DGGS:030:ISEA',  30,
  '+proj=isea',
  NULL -- no srtext
)


ON CONFLICT DO NOTHING;
<syntaxhighlight lang="bash">
</syntaxhighlight>O script SQL acima proporciona a base de dados a lidar com projeções compatíveis com o IBGE.
wget http://www.addressforall.org/_private/br_municipios.qgz
</syntaxhighlight>


Em seguida, precisamos criar as funções <code>vbit_interleave</code> e <code>ints_to_interleavedbits</code>. Ainda na CLI do postgres, adicione na sequência, os scripts SQL abaixo: <syntaxhighlight lang="sql">
Ao abrir o projeto <code>br_municipios</code> no QGIS, insira as credenciais de usuário e senha do seu postgreSQL. Em seguida, poderemos constatar a seguinte estrutura de camadas(layers) por município no projeto:
CREATE FUNCTION vbit_interleave(x varbit, y varbit) returns varbit as $f$
  SELECT  string_agg(( substring(x,i,1)||substring(y,i,1) )::text,'')::varbit
  FROM generate_series(1,bit_length(x)) t(i)
$f$ LANGUAGE SQL IMMUTABLE;
</syntaxhighlight> <syntaxhighlight lang="sql">
CREATE FUNCTION ints_to_interleavedbits(x int, y int, len int default 32) returns varbit as $f$
  SELECT vbit_interleave( substring(x::bit(32),33-len), substring(y::bit(32),33-len) )
$f$ LANGUAGE SQL IMMUTABLE;
</syntaxhighlight>Agora a base de dados está pronta e configurada. Iremos para a próxima fase que é a de geração de ''views'' para o QGIS. Mantenha-se na CLI do postgres.


=== Geração de ''views'' para os municípios de PE-Caruaru, RJ-NovaFriburgo e SP-SãoBentoSapucai===
[[Arquivo:Estrutura da view de cada municipio 1.png|centro|miniaturadaimagem]]


Recomendamos fortemente que se habilite a visualização de uma ''view'' de município por vez e, além disso, habilitar a visualização de uma camada/layer de município por vez. Por exemplo, na imagem acima, no respectivo município, apenas a layer ''cover'' está habilitada para visualização. A finalidade dessa política de visualização é para que não se esgote todos os recursos de ''hardware'' e subitamente o computador trave ou pare de funcionar.


Antes de iniciar o processo de geração de ''views'' para os municípios desejados, é uma boa prática verificar se a base dispõe de todos os níveis de grade que são necessários para os municípios em questão. Por exemplo, a consulta abaixo verifica o quantitativo de ocorrências para o nível de grade 3.5 e em seguida, verifica o quantitativo de ocorrências para o nível 7.5. Espera-se que quanto maior o nível, maior o número de ocorrências. Caso o resultado da consulta não siga esse padrão, muito provável que o nível mais alto naõ esteja disponível ainda para muitos municípios brasileiros, inclusive para os municípios desejados.
Cada ''view'' de município possui as seguintes propriedades de simbologia:
<syntaxhighlight lang="sql">
select count(*) FROM grid_br.all_levels where hlevel = 3.5; --  1984


select count(*) FROM grid_br.all_levels where hlevel = 7.5; -- 142
[[Arquivo:Propriedades simbologia views municipios.png|centro|600x600px]]
</syntaxhighlight>...


====Consulta para verificação de cobertura lógistica otimizada====
== Resultados ==
<syntaxhighlight lang="sql">
A melhor maneira de testar a grade instalada é visualizando no mapa, em particular através da ferramenta recomendada, o QGIS.
select t.isolabel_ext, count(*) n_cells, array_agg(p.code_b16h) cover from grid_br.vw_level07_0_poc p inner join grid_br.vw_poc t ON t.geom&&p.geom  group by 1 order by 1;
</syntaxhighlight>resultando em:<syntaxhighlight>
    isolabel_ext      | n_cells |                                                                                cover


-----------------------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Abaixo alguns exemplares de visualização de grades logísticas de municípios por dígito.
BR-PE-Caruaru        |      25 | {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}
BR-RJ-NovaFriburgo    |      27 | {ebbbV,b104H,b104M,b104V,b105H,b105M,b105R,b105V,b110H,b110M,b110R,ebaeM,ebaeR,ebaeV,ebafH,ebafM,ebafR,ebafV,ebbaH,ebbaM,ebbaR,ebbaV,ebbbH,ebbbM,ebbbR,ebadR,ebadV}
BR-SP-SaoBentoSapucai |      14 | {dff7M,dff7R,dff7V,dffcM,dffdH,dffdM,dffdR,dffdV,eaa2R,eaa8H,eaa8M,eaa8R,eaa8V,dff6V}
(3 rows)
</syntaxhighlight>Podemos constatar que o quantitativo de células está otimizado para os municípios de caruaru e nova friburgo. O município de Santo bento do sapucaí ainda é passível de otimização por meio de uma camada de nível 7.5.


====Criação de tabela de reindexação das células para base 32 com apenas um dígito ====
<gallery widths="400" heights="400" perrow="2">
<syntaxhighlight lang="sql">
Arquivo:NovaFriburgo cover digit1.png|Nova Friburgo, grade de cobertura, com células rotuladas pelo dígito indexador.
create table grid_br.caruaru_cover as select  natcod.vbit_to_strstd(substring((ROW_NUMBER() OVER())::bit(32),32-4),'32nvu') as idx_b32nvu, * from (
Arquivo:NovaFriburgo digit2.png|Nova Friburgo, segundo dígito.
select  p.* cover from grid_br.vw_level07_0_poc p inner join grid_br.vw_poc t ON t.geom&&p.geom where t.isolabel_ext like '%Caruaru' order by gid_vbit) TT;
Arquivo:NovaFriburgo digit3.png|Nova Friburgo, terceiro dígito.
</syntaxhighlight>...[[Arquivo:Eneas-caruaru-geocodigo-base32.png|centro|miniaturadaimagem|385x385px|Grade logística do município de caruaru]]...<syntaxhighlight lang="sql">
Arquivo:Caruaru cover digit1.png|Caruaru, dígito indexador.
create table grid_br.nova_friburgo_cover as select  natcod.vbit_to_strstd(substring((ROW_NUMBER() OVER())::bit(32),32-4),'32nvu') as idx_b32nvu, * from (
Arquivo:Caruaru digit2.png|Caruaru, segundo dígito.
select  p.* cover from grid_br.vw_level07_0_poc p inner join grid_br.vw_poc t ON t.geom&&p.geom  where t.isolabel_ext like '%NovaFriburgo' order by gid_vbit) TT;
Arquivo:Caruaru digit3.png|Caruaru, terceiro dígito.
</syntaxhighlight>...[[Arquivo:Eneas-novaFriburgo-geocodigo-base32.png|centro|miniaturadaimagem|564x564px|Grade logística do municípo de nova friburgo]]...<syntaxhighlight lang="sql">
</gallery>
create table grid_br.sao_bento_sapucai_cover as select  natcod.vbit_to_strstd(substring((ROW_NUMBER() OVER())::bit(32),32-4),'32nvu') as idx_b32nvu, * from (
select  p.* cover from grid_br.vw_level07_5_poc p inner join grid_br.vw_poc t ON t.geom&&p.geom  where t.isolabel_ext like '%SaoBentoSapucai' order by gid_vbit) TT;
</syntaxhighlight>

Edição atual tal como às 23h34min de 26 de junho de 2024

Caso seja a primeira vez executando as instruções desse tutorial, siga as etapas 1 e 2. Caso contrário, apenas a etapa 2 será necessária.

Etapa 1 - Criação e configuração de base de dados

No ambiente Ubuntu Linux, com o SGBD PostgreSQL versão 14 instalado, Postgis instalado na versão 3, o usuário postgres criado e o serviço configurado na porta 5432, siga os passos abaixo para criar uma base de dados. Primeiro, altere para o usuário postgres usando o comando:

sudo -i -u postgres

Em seguida, vá para a interface de linha de comando (CLI) do postgreSQL inserindo o comando:

psql

Na CLI do postgresSQL, insira o comando DDL abaixo para criar a base de dados db_br_municipios:

create database db_br_municipios;

Com a base de dados criada, alterne para o usuário root do linux por meio do comando \q, posteriormente o comando exit, e insira o comando abaixo para baixar o script de preparação da base de dados:

wget -P /tmp http://www.addressforall.org/_private/prepare1.sql

Logo em seguida, alterne para o usuário postgres (sudo -i -u postgres) e insira o seguinte comando para configurar a base de dados recém-criada:

psql db_br_municipios < /tmp/prepare1.sql

Etapa 2 - Carregamento de dados e visualização no QGIS

De volta para o usuário root,o próximo passo é baixar os dados das grades dos municípios para serem inseridos na base de dados recém-criada e configurada, por meio do comando: 

wget -P /tmp http://www.addressforall.org/_private/grid_br2.sql.gz

Ao fim do download, dê permissão total ao arquivo grid_br2.sql.gz e alterne novamente para o usuário postgres (sudo -i -u postgres).

Execute o comando abaixo para inserir os dados das grades dos municípios na base de dados db_br_municipios: 

gunzip -c /tmp/grid_br2.sql.gz | psql db_br_municipios

Após a finalizar o carregamento das grades dos municípios na base de dados, precisamos visualizar na ferramenta recomendada, o QGIS. Para mais detalhes sobre a instalação da ferramenta ver Suporte QGIS.

Para reaproveitar um projeto pré-configurado do QGIS, para esses dados de grades dos municípios, podemos baixar o existente. Alterne para o usuário root (exit). Vá para o diretório na sua máquina aonde o QGIS salva os projetos, e execute o comando abaixo: 

wget http://www.addressforall.org/_private/br_municipios.qgz

Ao abrir o projeto br_municipios no QGIS, insira as credenciais de usuário e senha do seu postgreSQL. Em seguida, poderemos constatar a seguinte estrutura de camadas(layers) por município no projeto:

Estrutura da view de cada municipio 1.png

Recomendamos fortemente que se habilite a visualização de uma view de município por vez e, além disso, habilitar a visualização de uma camada/layer de município por vez. Por exemplo, na imagem acima, no respectivo município, apenas a layer cover está habilitada para visualização. A finalidade dessa política de visualização é para que não se esgote todos os recursos de hardware e subitamente o computador trave ou pare de funcionar.

Cada view de município possui as seguintes propriedades de simbologia:

Propriedades simbologia views municipios.png

Resultados

A melhor maneira de testar a grade instalada é visualizando no mapa, em particular através da ferramenta recomendada, o QGIS.

Abaixo alguns exemplares de visualização de grades logísticas de municípios por dígito.

  • Nova Friburgo, grade de cobertura, com células rotuladas pelo dígito indexador.

    Nova Friburgo, grade de cobertura, com células rotuladas pelo dígito indexador.

  • Nova Friburgo, segundo dígito.

    Nova Friburgo, segundo dígito.

  • Nova Friburgo, terceiro dígito.

    Nova Friburgo, terceiro dígito.

  • Caruaru, dígito indexador.

    Caruaru, dígito indexador.

  • Caruaru, segundo dígito.

    Caruaru, segundo dígito.

  • Caruaru, terceiro dígito.

    Caruaru, terceiro dígito.

Disponível em “https://wiki.addressforall.org/w/index.php?title=Grade_logística_dos_municípios_Brasil&oldid=4133”