|
|
(12 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="bash">
| | |
| git clone https://github.com/AddressForAll/WhitePaper01
| | 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"> |
| </syntaxhighlight>Acesse o diretório <code>/var/gits/_a4a/WhitePaper01/sql</code> 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 <code>/tmp</code> 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 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">
| |
| sudo -i -u postgres | | sudo -i -u postgres |
| </syntaxhighlight>Agora vá para o diretório <code>/tmp</code>, 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> |
|
| |
|
| CREATE extension IF NOT EXISTS postgis;
| | ===Etapa 2 - Carregamento de dados e visualização no QGIS=== |
|
| |
|
| INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) VALUES
| |
| -- -- -- --
| |
| ( -- 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?+
| | 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: |
| 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!)
| | <syntaxhighlight lang="bash"> |
| 955030, 'DGGS:030:ISEA', 30,
| | wget -P /tmp http://www.addressforall.org/_private/grid_br2.sql.gz |
| '+proj=isea',
| | </syntaxhighlight> |
| NULL -- no srtext
| |
| )
| |
|
| |
|
| ON CONFLICT DO NOTHING;
| | 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>). |
| </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 <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">
| | Execute o comando abaixo para inserir os dados das grades dos municípios na base de dados <code>db_br_municipios</code>: |
| 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=== | | <syntaxhighlight lang="bash"> |
| | gunzip -c /tmp/grid_br2.sql.gz | psql db_br_municipios |
| | </syntaxhighlight> |
|
| |
|
| | 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]]. |
|
| |
|
| 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.
| | 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 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
| | <syntaxhighlight lang="bash"> |
| </syntaxhighlight>... | | wget http://www.addressforall.org/_private/br_municipios.qgz |
| | </syntaxhighlight> |
| | |
| | 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]] |
|
| |
|
| ====Consulta para verificação de cobertura lógistica otimizada====
| | 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 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
| |
|
| |
|
| -----------------------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| | Cada ''view'' de município possui as seguintes propriedades de simbologia: |
| 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 ==== | | [[Arquivo:Propriedades simbologia views municipios.png|centro|600x600px]] |
| <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 (
| | == Resultados == |
| 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;
| | A melhor maneira de testar a grade instalada é visualizando no mapa, em particular através da ferramenta recomendada, o QGIS. |
| </syntaxhighlight>...[[Arquivo:Eneas-caruaru-geocodigo-base32.png|centro|miniaturadaimagem|385x385px|Grade logística do município de caruaru]]...<syntaxhighlight lang="sql">
| | |
| 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 (
| | Abaixo alguns exemplares de visualização de grades logísticas de municípios por 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;
| | |
| </syntaxhighlight>...[[Arquivo:Eneas-novaFriburgo-geocodigo-base32.png|centro|miniaturadaimagem|564x564px|Grade logística do municípo de nova friburgo]]...<syntaxhighlight lang="sql">
| | <gallery widths="400" heights="400" perrow="2"> |
| 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:NovaFriburgo cover digit1.png|Nova Friburgo, grade de cobertura, com células rotuladas pelo dígito indexador. |
| 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;
| | Arquivo:NovaFriburgo digit2.png|Nova Friburgo, segundo dígito. |
| </syntaxhighlight> | | Arquivo:NovaFriburgo digit3.png|Nova Friburgo, terceiro dígito. |
| | Arquivo:Caruaru cover digit1.png|Caruaru, dígito indexador. |
| | Arquivo:Caruaru digit2.png|Caruaru, segundo dígito. |
| | Arquivo:Caruaru digit3.png|Caruaru, terceiro dígito. |
| | </gallery> |