|
|
(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> | |
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:
Em seguida, vá para a interface de linha de comando (CLI) do postgreSQL inserindo o comando:
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:
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:
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, segundo dígito.
Nova Friburgo, terceiro dígito.
Caruaru, dígito indexador.
Caruaru, terceiro dígito.