|
|
(13 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.
| |
|
| |
|
| | ===Etapa 1 - Criação e configuração de base de dados=== |
|
| |
|
| <nowiki>Vá para o diretório {{/var/gits/_a4a/ e insira o comando abaixo para clonar o repositório WhitePaper01.</nowiki><syntaxhighlight lang="bash">
| | 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
| |
| </syntaxhighlight>Acesse o diretório <nowiki><code>/var/gits/_a4a/WhitePaper01/sql</code></nowiki> e execute sequencialmente os comandos abaixo.<syntaxhighlight lang="bash">
| |
| psql <nome_base_de_dados> < prepare0-binCodes.sql
| |
| </syntaxhighlight><syntaxhighlight lang="bash">
| |
| psql <nome_base_de_dados> < prepare1-binCodes.sql
| |
| </syntaxhighlight><syntaxhighlight lang="bash">
| |
| psql <nome_base_de_dados> < prepare1a-asserts.sql
| |
| </syntaxhighlight><syntaxhighlight lang="bash">
| |
| psql <nome_base_de_dados> < prepare1b-demo.sql | diff - ../data/prepare1b-demo.txt
| |
| </syntaxhighlight><syntaxhighlight lang="bash">
| |
| psql <nome_base_de_dados> < prepare2-baseConv.sql
| |
| </syntaxhighlight><syntaxhighlight lang="bash">
| |
| psql <nome_base_de_dados> < prepare2a-asserts.sql
| |
| </syntaxhighlight><syntaxhighlight lang="bash">
| |
| 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 /tmp e execute o comando abaixo para fazer download das grades do Brasil.<syntaxhighlight lang="bash">
| |
| wget http://www.addressforall.org/_private/grid_br_dump2.sql.gz
| |
| </syntaxhighlight>Dê as permissoõ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">
| |
| sudo -i -u postgres | | sudo -i -u postgres |
| </syntaxhighlight>Agora vá para o diretório /tmp, e execute o comando abaixo:<syntaxhighlight lang="bash"> | | </syntaxhighlight>Em seguida, vá para a interface de linha de comando (CLI) do postgreSQL inserindo o comando:<syntaxhighlight lang="bash"> |
| 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 | | psql |
| </syntaxhighlight>entre na base de dados onde acabamos de carregar as grade do brasil:<syntaxhighlight lang="bash"> | | </syntaxhighlight>Na CLI do postgresSQL, insira o comando DDL abaixo para criar a base de dados <code>db_br_municipios</code>:<syntaxhighlight lang="sql"> |
| \c <nome_base_de_dados> | | create database db_br_municipios; |
| </syntaxhighlight>Insira o script SQL abaixo:<syntaxhighlight lang="sql"> | | </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 |
| * System's Public library (commom for many scripts)
| | </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"> |
| * Module: PostGIS general complements. Fragment.
| | psql db_br_municipios < /tmp/prepare1.sql |
| */
| | </syntaxhighlight> |
| | |
| | ===Etapa 2 - Carregamento de dados e visualização no QGIS=== |
| | |
| | |
| | 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"> |
| | wget -P /tmp http://www.addressforall.org/_private/grid_br2.sql.gz |
| | </syntaxhighlight> |
|
| |
|
| CREATE extension IF NOT EXISTS postgis;
| | 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>). |
|
| |
|
| INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) VALUES
| | Execute o comando abaixo para inserir os dados das grades dos municípios na base de dados <code>db_br_municipios</code>: |
| -- -- -- --
| |
| ( -- IBGE Albers, SRID number convention in Project DigitalGuard-BR:
| |
| 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?+
| | <syntaxhighlight lang="bash"> |
| 955010, 'DGGS:010:QSC', 10,
| | gunzip -c /tmp/grid_br2.sql.gz | psql db_br_municipios |
| '+proj=qsc',
| | </syntaxhighlight> |
| NULL -- no srtext
| |
| ),
| |
| --( -- S2 default, PROJ v8.2+
| |
| -- 955020, 'DGGS:020:S2', 20,
| |
| -- '+proj=s2',
| |
| -- NULL -- no srtext
| |
| --),
| |
|
| |
|
| ( -- ISEA default, PROJ v?+ (without inverse!)
| | 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]]. |
| 955030, 'DGGS:030:ISEA', 30,
| |
| '+proj=isea',
| |
| NULL -- no srtext
| |
| )
| |
|
| |
|
| ON CONFLICT DO NOTHING;
| | 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: |
| </syntaxhighlight>O script SQL acima proporciona a base de dados a lidar com projeções compatíveis com o IBGE. | |
|
| |
|
| Em seguida, precisamos criar as funções vbit_interleave e ints_to_interleavedbits. Ainda na CLI do postgres, adicione na sequência, os scripts SQL abaixo: <syntaxhighlight lang="sql">
| | <syntaxhighlight lang="bash"> |
| CREATE FUNCTION vbit_interleave(x varbit, y varbit) returns varbit as $f$
| | wget http://www.addressforall.org/_private/br_municipios.qgz |
| SELECT string_agg(( substring(x,i,1)||substring(y,i,1) )::text,'')::varbit
| | </syntaxhighlight> |
| 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 ===
| | 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: |
|
| |
|
| | [[Arquivo:Estrutura da view de cada municipio 1.png|centro|miniaturadaimagem]] |
|
| |
|
| 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.
| | 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. |
| <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
| | Cada ''view'' de município possui as seguintes propriedades de simbologia: |
| </syntaxhighlight>...
| |
|
| |
|
| ==== Consulta para verificação de cobertura lógistica otimizada ====
| | [[Arquivo:Propriedades simbologia views municipios.png|centro|600x600px]] |
| <syntaxhighlight lang="sql">
| |
| 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
| |
|
| |
|
| -----------------------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| | == Resultados == |
| 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}
| | A melhor maneira de testar a grade instalada é visualizando no mapa, em particular através da ferramenta recomendada, o QGIS. |
| 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 ====
| | Abaixo alguns exemplares de visualização de grades logísticas de municípios por dígito. |
| <syntaxhighlight lang="sql">
| | |
| 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 (
| | <gallery widths="400" heights="400" perrow="2"> |
| 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 cover digit1.png|Nova Friburgo, grade de cobertura, com células rotuladas pelo dígito indexador. |
| </syntaxhighlight>...[[Arquivo:Eneas-caruaru-geocodigo-base32.png|centro|miniaturadaimagem|385x385px|Grade logística do município de caruaru]]...<syntaxhighlight lang="sql">
| | Arquivo:NovaFriburgo digit2.png|Nova Friburgo, segundo dígito. |
| 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:NovaFriburgo digit3.png|Nova Friburgo, terceiro 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 cover digit1.png|Caruaru, dígito indexador. |
| </syntaxhighlight>...[[Arquivo:Eneas-novaFriburgo-geocodigo-base32.png|centro|miniaturadaimagem|564x564px|Grade logística do municípo de nova friburgo]]...<syntaxhighlight lang="sql">
| | Arquivo:Caruaru digit2.png|Caruaru, segundo dígito. |
| 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 (
| | Arquivo:Caruaru digit3.png|Caruaru, terceiro dígito. |
| 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;
| | </gallery> |
| </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.