|
|
| Linha 1: |
Linha 1: |
| <syntaxhighlight lang="sql">
| | O tutorial abaixo ... a etapa 1 precisa ser executada apenas 1 vez, para outros municípios, pule para a etapa 2... |
| 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 === |
|
| |
|
| <syntaxhighlight lang="sh">
| |
| git clone https://github.com/AddressForAll/WhitePaper01
| |
| </syntaxhighlight>
| |
|
| |
| Acesse o diretório <code>/var/gits/_a4a/WhitePaper01/sql</code> e execute sequencialmente os comandos abaixo.
| |
|
| |
| <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.
| |
|
| |
|
| <syntaxhighlight lang="bash">
| | No Ambiente Ubuntu Linux, com SGBD PostgreSQL instalado na versão 14 e com serviço configurado na porta 5432, para criarmos uma base de dados, altere para o usuário postgres por meio do comando:<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 postgres 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 postgres, insira o comando DDL abaixo para criar a base de dados db_br_municipios:<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 root do linux por meio do 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> |
| * Module: PostGIS general complements. Fragment.
| |
| */
| |
|
| |
|
| CREATE extension IF NOT EXISTS postgis;
| | === Etapa 2 === |
|
| |
|
| 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?+
| | 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;
| |
| </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">
| |
| 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===
| |
| | |
| | |
| 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.
| |
| <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>... | |
|
| |
|
| ====Consulta para verificação de cobertura lógistica otimizada====
| |
| <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
| |
|
| |
|
| -----------------------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| | Agora, execute o comando abaixo para inserir os dados das grades dos municípios na base de dados db_br_municipios:<syntaxhighlight lang="bash"> |
| 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}
| | gunzip -c /tmp/grid_br2.sql.gz | db_br_municipios |
| 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 ====
| | </syntaxhighlight>Após a finalizar o carregamento das grades dos municípios na base de dados, precisamos agora baixar o projeto do QGIS pré-configurado para visualizar as grades dos municípios nesta ferramenta. Vá para o diretório na sua máquina aonde o QGIS salva os projetos, e execute o comando abaixo:<syntaxhighlight lang="bash"> |
| <syntaxhighlight lang="sql">
| | wget http://www.addressforall.org/_private/br_municipios.qgz |
| 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 (
| |
| 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;
| |
| </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 (
| |
| 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">
| |
| 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> | | </syntaxhighlight> |
