CONSULTANDO DADOS DE OUTRO BANCO DE DADOS NO POSTGRESQL (DBLINK)

Utilizar dados de outro banco de dados pode, muitas vezes, parecer uma trabalhosa (e até mesmo arriscada) tarefa de exportação e importação. No entanto, com o uso do dblink, esses dados podem ser acessados diretamente.

Para habilitar o dblink utilize:

CREATE EXTENSION dblink;

Com a extensão habilitada, você pode buscar a informação que quiser em outro banco de dados passando os parâmetros de conexão, como no exemplo abaixo (inserir dados em uma tabela de clientes idosos buscando e filtrando clientes existentes em outro banco de dados, ambos no meu servidor local):

INSERT INTO cliente_idoso(codigo, nome)
SELECT d.codigo, d.nome FROM
  dblink('dbname=dados_clientes port=5432 user=postgres password=senha',
  'SELECT codigo, nome FROM cliente WHERE idade >= 60')
    AS d(codigo INT, nome VARCHAR(100));

MODO ESCURO DO QGIS

Desde as últimas versões o QGIS possui um modo escuro, especialmente interessante para quem utiliza o software por longos períodos. Para ativar o modo escuro vá em "Configurações" > "Opções" e, na aba "Geral" > "Tema UI" escolha "Night Mapping". Dê OK, feche o QGIS e o abra novamente para que a alteração tenha efeito.

Na mesma tela de seleção do modo escuro também há algumas opções visuais interessantes que podem ser alteradas como o estilo do QGIS e o tamanho dos ícones no software.

DESCOBRIR QUAL VERSÃO DO POSTGRESQL E DO POSTGIS ESTOU USANDO

Para saber qual é a versão do PostgreSQL e do PostGIS que você está usando, execute os comandos abaixo em uma ferramenta SQL conectada à base desejada (as imagens são do PgAdmin 4):

• PostgreSQL: SELECT version(); ou SHOW server_version;

 

• PostGIS: SELECT * FROM PostGis_Full_Version();

VISUALIZANDO MAPAS DO POSTGIS NO PGADMIN 4

Nas últimas versões do PgAdmin 4 é possível visualizar os dados de uma consulta que tenha objetos espaciais no mapa. Observe a consulta abaixo, dos municípios com mais de 200.000 habitantes no estado de São Paulo:

No título da coluna com o dado espacial aparece um ícone com formato de "olho" (View all geometries in this column). Clicando nele (ou na aba "Geometry Viewer") é possível visualizar os dados em um mapa (onde é possível, inclusive, escolher o mapa base que fica sob as geometrias).

CRIANDO MAPAS NO EXCEL

Você sabia que é possível criar mapas simples diretamente no Microsoft Excel? A partir de um conjunto de dados (tanto em formato de classificação - como o nome de uma região - quanto graduação - como a população de um local) que possam ser interpretados pelo Bing, basta selecionar os dados, ir na aba "Inserir" e escolher a opção "Mapas". As imagens abaixo foram criadas com dados de regiões por estados brasileiros e população por territórios australianos, dados facilmente encontrados na internet.

Para editar, por exemplo, as cores da série, basta clicar sobre o mapa que as opções aparecerão do lado direito.

ATUALIZAÇÃO COM RELAÇÃO ESPACIAL NO QGIS

Quando for necessário atualizar um dado baseado em uma relação espacial no QGIS (por exemplo, os trechos de uma rodovia que interceptam Áreas de Proteção Ambiental), podemos utilizar o Editor de Funções do mesmo. Para isso:

1) Selecione a camada (com um clique do botão esquerdo sobre seu nome) a partir da qual se quer selecionar os dados (de acordo com o exemplo, os trechos de uma rodovia);

2) Abra a "Calculadora de campos" e clique na aba "Editor de funções";

3) Clique em "Novo arquivo", dê um nome (por exemplo, "consulta_espacial") e clique em "OK";

4) Substitua o texto que aparece na parte do editor de funções (ao lado direito) por:

from qgis.core import *

from qgis.gui import *

@qgsfunction(args='auto', group='Personalizado')

def intersection_field_update(layername, column, feature, parent):

    layer = QgsMapLayerRegistry.instance().mapLayersByName(layername)[0]

    for feat in layer.getFeatures():

        if feature.geometry().intersects(feat.geometry()):

            return feat[column]

5) Clique em "Carregar";

Nesse momento, você criou uma função chamada intersection_field_update no grupo "Personalizado";

6) Volte para a aba "Expressão" e perceba que agora no conjunto de funções existe um grupo "Personalizado" com a função intersection_field_update. Use ela para atualizar ou criar um campo com dados de outra camada que possua relação de interceptar com a camada em uso - Será necessário passar dois parâmetros: a tabela e o campo (nessa ordem).

Considerando o exemplo, imaginemos um projeto com duas tabelas: trecho_rodovia e apa, e que na tabela apa exista um campo nome (com o nome da APA). Se utilizarmos a função para criar um campo virtual chamado apa na tabela trecho_rodovia, com o nome da APA que o trecho intercepta, teríamos o seguinte:

Obs.: O texto da expressão nesse caso é intersection_field_update('apa','nome') 

Ao clicar em OK será criado um campo virtual com o nome apa na tabela trecho_rodovia, onde estará o nome da APA que o trecho intercepta (ou nulo, se ele não intercepta nenhuma APA).

Após criar a função não é mais necessário fazer as etapas 3 a 5 para utilizá-la novamente, pois ela já estará criada. Essa função inclusive pode ser alterada para atingir outros objetivos (por exemplo, substituindo  o "intersects" por "within" é possível estabelecer uma relação de um elemento "estar contido" em outro - Como consta na postagem cujo link está nos agradecimentos).

Agradecimentos a Alexandre Miguel Freitas da Silva e Ciro Borges de Oliveira, que incentivaram essa pesquisa, e a Detlev (https://gis.stackexchange.com/users/45346/detlev), cuja resposta em (https://gis.stackexchange.com/questions/178522/update-field-based-on-spatial-query-qgis?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa) possibilitou o embasamento para essa postagem.

ADICIONANDO TILES NO QGIS

A partir da versão 2.18, o QGIS ganhou a funcionalidade de adicionar camadas de tiles que, superficialmente falando, são imagens divididas em diversas partes e níveis de zoom e, portanto, carregam apenas a parte que o usuário está visualizando e com resolução de acordo com seu nível de zoom, o que as torna muito mais rápidas do que adicionar a imagem como um todo (ou um arquivo raster).

Para adicionar imagens de um servidor de tiles ao seu projeto QGIS, vá ao Navegador (se ele não estiver aberto, basta ir em Exibir - Painéis - Navegador) e encontre a opção Tile Server (XYZ). Clique com o botão direito do mouse sobre ela e selecione New Connection...

Digite ou cole a URL dos tiles e dê OK. Exemplos:

Bing Aerial:

http://ecn.t3.tiles.virtualearth.net/tiles/a{q}.jpeg?g=1

Google Hybrid:

https://mt1.google.com/vt/lyrs=y&x={x}&y={y}&z={z}

Google Satellite:

https://mt1.google.com/vt/lyrs=s&x={x}&y={y}&z={z}

OpenStreetMap:

http://tile.openstreetmap.org/{z}/{x}/{y}.png

Dê um nome a esse Tile Server e clique em OK.

Pronto, o Tile Server já está configurado e disponível (se não estiver visualizando ele, basta clicar na seta para baixo ao lado de Tile Server (XYZ)). Para adicioná-lo ao seu projeto clique sobre o nome do Tile Server desejado, segure e arraste ele para as camadas na posição desejada.

EXCLUINDO TODAS AS TABELAS DE UM SCHEMA - POSTGRESQL/POSTGIS

Para excluir todas as tabelas de um schema, você pode excluir e recriar ele:

DROP SCHEMA nome_do_esquema CASCADE;
CREATE SCHEMA nome_do_esquema;

Porém, se quiser excluir somente as tabelas, pode ser escrito um comando que gera a sintaxe completa para executar a instrução de exclusão de todas as tabelas de um determinado schema, como o comando abaixo:

SELECT 'DROP TABLE nome_do_esquema.' || tablename || ';' 
FROM pg_tables WHERE schemaname = 'nome_do_esquema';

Depois de gerar o comando, basta copiar, colar (se necessário, excluir as aspas) e executá-lo.
Se houver dependências e for necessário usar o CASCADE, use:

SELECT 'DROP TABLE nome_do_esquema.' || tablename || ' CASCADE;' 
FROM pg_tables WHERE schemaname = 'nome_do_esquema';

ATUALIZAÇÃO COM RELAÇÃO ESPACIAL NO POSTGIS

Muitas vezes é necessário fazer uma atualização de dados por relação espacial no PostGIS. Isso ocorre principalmente quando temos que transformar uma relação espacial em uma relação por chave estrangeira (que é mais rápida em retornar resultados) ou outras, quando há relação espacial entre determinadas geometrias. Quando essas geometrias se tocam, usamos a função ST_Intersects para fazer a atualização (para mais funções de relações espaciais clique aqui).

Por exemplo, uma situação onde é necessário atualizar o número de quadra em lotes:

UPDATE lotes SET id_quadra = id FROM quadras WHERE ST_Intersects(lotes.geom,quadras.geom);

Essa instrução pode ser melhorada, usando o centroide do lote:

UPDATE lotes SET id_quadra = id FROM quadras WHERE ST_Intersects(ST_Centroid(lotes.geom),quadras.geom);

Ou, em outro exemplo, para atualizar o nome do bairro nos lotes:

UPDATE lotes SET nome_bairro = nome FROM bairros WHERE 

ST_Intersects(ST_Centroid(lotes.geom),bairros.geom);

EDITANDO UM ARQUIVO DE PROJETO DO QGIS

Como o arquivo de projeto do QGIS é estruturado em formato XML, é possível alterar algumas de suas características editando o conteúdo de suas tags em um editor de texto simples (como o bloco de notas). Um exemplo de situação onde isso pode ser muito útil é a alteração de dados de uma conexão de bancos de dados - Na tag <datasource> temos o dbname (nome do banco de dados), o host (endereço IP) e a porta, entre outros. Basta substituir o valor antigo pelo desejado. 

Por exemplo, para alterar a conexão da "tabela", do "banco_antigo" em 192.168.0.100:5432 para o "banco_novo" em 192.168.0.101:5433, basta alterar a linha:

<datasource>dbname='banco_antigo' host=192.168.0.100 port=5432 sslmode=disable key='id' table="public"."tabela" sql=</datasource>

Por:

<datasource>dbname='banco_novo' host=192.168.0.101 port=5433 sslmode=disable key='id' table="public"."tabela" sql=</datasource>

A mesma estrutura vale para os arquivos de estilo de camadas. Um exemplo útil:

Como o QGIS não permite copiar / colar estilos entre tipos diferentes de elementos (ponto, linha ou polígono), essa edição também pode ser feita (no arquivo de estilo):

- Para copiar / colar nomes de campos, de um arquivo de estilo para outro, copiar / colar a tag <aliases>

- Para copiar / colar formatos de campos, de um arquivo de estilo para outro, copiar / colar a tag <edittypes>

DOCUMENTANDO UM BANCO DE DADOS POSTGRESQL/POSTGIS

Tendo as tabelas e campos de um banco de dados PostgreSQL comentados, fica fácil gerar a documentação desses campos e tabelas. Para isso pode ser usado:

• Para documentar as tabelas (o SQL a seguir traz o esquema, a tabela, o dono, o comentário, o número de registros e o tablespace):

SELECT nspname AS esquema, c.relname AS tabela,
pg_catalog.pg_get_userbyid(c.relowner) AS dono,
pg_catalog.obj_description(c.oid, 'pg_class') AS comentario,
reltuples::integer as registros,
(SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=c.reltablespace) AS tablespace
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
ORDER BY esquema, tabela;

• Para documentar os campos (o SQL a seguir traz a tabela, a coluna, o tipo e o comentário):

SELECT t.relname AS tabela,

a.attname AS coluna,

pg_catalog.format_type(a.atttypid, a.atttypmod) AS tipo,

(SELECT col_description(a.attrelid,a.attnum)) AS comentario

FROM pg_catalog.pg_attribute a

INNER JOIN pg_stat_user_tables t ON a.attrelid = t.relid

WHERE a.attnum > 0 AND NOT a.attisdropped

ORDER BY tabela, coluna;

Outros exemplos podem ser encontrados em https://pt.wikibooks.org/wiki/PostgreSQL_Pr%C3%A1tico/Metadados

COMENTÁRIOS NO POSTGRESQL

Comentar adequadamente um banco de dados é extremamente importante. Os comentários auxiliam principalmente na compreensão e documentação do conteúdo do banco de dados. No PostgreSQL usamos a seguinte sintaxe para comentários:

Comentário em uma tabela:

COMMENT ON TABLE nome_da_tabela IS 'comentário';

Para sobrepor um comentário basta executar a mesma instrução com o novo comentário. Se quiser  excluir um comentário pode usar:

COMMENT ON TABLE nome_da_tabela IS NULL;

Outros exemplos:
COMMENT ON COLUMN nome_da_tabela.nome_da_coluna IS 'Comentário';

COMMENT ON DATABASE nome_do_banco_de_dados IS 'Comentário';

COMMENT ON FUNCTION nome_da_função() IS 'Comentário';
COMMENT ON INDEX nome_do_índice IS 'Comentário';
COMMENT ON ROLE nome_do_papel IS 'Comentário';
COMMENT ON RULE nome_da_regra ON nome_da_tabela IS 'Comentário';
COMMENT ON SCHEMA nome_do_esquema IS 'Comentário';
COMMENT ON TRIGGER nome_da_trigger ON nome_da_tabela IS 'Comentário';
COMMENT ON TYPE nome_do_tipo IS 'Comentário';
COMMENT ON VIEW nome_da_visão IS 'Comentário';

ATRIBUIÇÃO DE ATIVIDADES A USUÁRIOS ESPECÍFICOS NO POSTGRESQL / POSTGIS

Uma atividade muito comum em Sistemas de Informação Geográfica é a atribuição de atividades específicas para usuários. Para definir níveis de permissões/operação para cada usuário dentro de uma mesma tabela no PostgreSQL / PostGIS devemos usar o conceito de POLICY (https://www.postgresql.org/docs/9.5/static/sql-createpolicy.html).

Vejamos o exemplo a seguir, onde é desejável que dois usuários de edição recebam atribuições específicas sobre a edição em uma tabela de "Lotes":

1) Criação dos usuários:

CREATE USER edicao1 LOGIN PASSWORD 'ed1';

CREATE USER edicao2 LOGIN PASSWORD 'ed2';

2) Criação do campo que vai receber o nome do usuário autorizado para edição:

ALTER TABLE lote ADD atribuicao VARCHAR(20);

3) Atribuição das quatro operações básicas aos usuários dentro da tabela de lotes:

GRANT SELECT,UPDATE,INSERT,DELETE ON lote TO edicao1;

GRANT SELECT,UPDATE,INSERT,DELETE ON lote TO edicao2;

4) Criação a regra de uso por usuário:

CREATE POLICY policy_atribuicao ON lote FOR ALL

TO PUBLIC USING (atribuicao = current_user);

ALTER TABLE lote ENABLE ROW LEVEL SECURITY;

Já podemos fazer a atribuição definindo o nome do usuário no campo atribuicao da tabela lote. Exemplo de atribuição:

UPDATE lote SET atribuicao = 'edicao1' WHERE id >= 1 AND id <= 100;

UPDATE lote SET atribuicao = 'edicao2' WHERE id >= 101 AND id <= 200;

Pronto. Nessa situação, quando um usuário (desenho1 ou desenho2) acessar o banco (inclusive por um software de SIG, como o QGIS, por exemplo), ele só terá acesso e possibilidade de edição nos registros que lhe estão atribuídos.

SELECIONAR E REINICIAR SEQUÊNCIAS (SEQUENCES) NO POSTGRESQL

Para selecionar as sequências do PostgreSQL basta usar o comando

SELECT * FROM information_schema.sequences;

Dentro dele podemos incluir algumas opções, de acordo com o resultado que queremos. Por exemplo:

• Seleciona somente as sequências do schema public:
• SELECT * FROM information_schema.sequences WHERE sequence_schema = 'public';

• Seleciona apenas os nomes das sequências:
• SELECT sequence_name FROM information_schema.sequences;

Para reiniciar uma sequência use o seguinte comando:

ALTER SEQUENCE nome_da_sequencia RESTART;

Finalmente, para atribuir os novos valores aos campos já existentes, use:

UPDATE nome_da_tabela SET coluna = nextval('nome_da_sequencia');

ESPECIALIZAÇÃO EM GEOPROCESSAMENTO

Curso de especialização em Geoprocessamento Aplicado na FAAG com pré-inscrições abertas!

OBTENDO IMAGENS GEORREFERENCIADAS DO GOOGLE OU BING

O AutoGR-Toolkit é uma ferramenta muito interessante para a obtenção de imagens já georreferenciadas do Google ou Bing. A seguir, preparei um breve tutorial de como obter essas imagens:

• Primeiro faça o download e instale o AutoGR-Toolkit, disponível em "http://www.ims.forth.gr/index_main.php?l=e&d=7&c=90";
• Após a instalação, entre no programa e selecione a opção GGrab;
• Vá em "Define your area of interest here [GBoundary]";
• Vá em "Google Catcher";
• Abra o Google Maps, encontre a área que você quer obter e copie o link (a própria URL);
• Volte ao "Catch Coordinates from Google Map" e cole a URL copiada;
• Defina o tamanho da área (em quilômetros) e clique em "Accept". Lembre-se que quanto maior a área, maior ficará o tamanho do arquivo;
• Verifique na miniatura se a área desejada está correta (se não estiver, você pode ajustar manualmente as coordenadas ou ir novamente para o GBoundary para adequá-la);
• Escolha a fonte (no caso do Brasil, pode-se optar pelo Google ou Bing);
• Escolha o "Zoom level" (quanto maior, melhor será a resolução e maior será o tamanho do arquivo);
• Defina o nome e o local de salvamento do arquivo;
• Escolha o sistema de coordenadas (EPSG);
• Clique em "Ready!" e espere o processo acabar.

Ao terminar o processo, você terá a imagem georreferenciada pronta para uso.

GEOWEEK

A GeoWeek será um evento online no qual, durante uma semana, serão abordadas as novidades e os assuntos mais interessantes do Universo das Geotecnologias e Meio Ambiente.

O blog e a página Geografando do facebook apoiam esse evento! Participe! Mais informações em http://www.geoweek.net/

HABILITANDO O TIPO GEOMETRY NO POSTGRESQL/POSTGIS

Caso você esteja tentando executar uma instrução SQL para criação de tabela com elementos espaciais no PostgreSQL/PostGIS e tenha obtido o erro de que o tipo "geometria" - ou qualquer outro do PostGIS - não exista (ERROR: type "geometry" does not exist), isso se refere ao fato de elementos do PostGIS "não terem sido carregados" (o que pode ocorrer por diversos motivos).

A solução do problema é simples:

• Entre no PGAdmin;
• Conecte-se ao banco desejado;
• Abra a janela de SQL e digite o comando "CREATE EXTENSION postgis;" e execute (F5).

Pronto, o seu banco de dados já está aceitando os tipos de dados do PostGIS.

ALTERANDO OU RECUPERANDO A SENHA DO USUÁRIO POSTGRES (POSTGRESQL/POSTGIS) NO WINDOWS

Em algumas situações (restore por SQL, dependendo da maneira como ele foi gerado, por exemplo) a senha do usuário postgres do PostgreSQL pode ser alterada. Se ocorreu essa situação, ou se por algum outro motivo você esqueceu ou perdeu a senha, pode seguir os passos abaixo para recuperá-la:

1. Abra o arquivo pg_hba.conf (o arquivo que contém, entre outras, as configurações de acesso do PostgreSQL e pode ser aberto no bloco de notas ou no próprio PGAdmin. Ele se encontra na pasta data);
2. No final do arquivo, substitua a palavra md5 por trust (em METHOD, nas linhas que não estão comentadas. Isso permitirá o acesso sem o uso de senha);
3. No Windows, vá em "Serviços", encontre o postgresql, selecione e clique em Reiniciar o serviço.
4. No prompt de comando do Windows, vá até a pasta bin, dentro da instalação do PostgreSQL (por exemplo: C:\Program Files\PostgreSQL\9.4\bin) e execute o comando: psql -h localhost -U postgres -W -d postgres (esse último pode ser o nome de qualquer banco de dados existente) e tecle enter. Se pedir a senha tecle enter.
5. Conectado no psql execute o comando ALTER USER postgres ENCRYPTED PASSWORD 'senha'; (substituindo 'senha' pela senha que você deseja).
6. Saia do prompt, retorne aos serviços e reinicie novamente o serviço do postgresql. Lembre-se de alterar novamente o arquivo pg_hba.conf para que o usuário postgres não possa ser acessado sem senha.

Observação: em substituição às etapas 3, 4 e 5, você também pode se logar pelo pgAdmin e executar a instrução ALTER USER postgres ENCRYPTED PASSWORD 'senha'; (substituindo 'senha' pela senha que você deseja) na janela de SQL e executá-la.

Pronto, você já pode acessar o PostgreSQL pelo usuário postgres usando a nova senha.

INTERSECÇÃO COM TOLERÂNCIA NO POSTGIS

A função ST_Intersects do PostGIS retorna verdadeiro se duas geometrias compartilham qualquer porção do espaço (é a "função contrária" à ST_Disjoint). Essa função recebe as duas geometrias envolvidas:

                ST_Intersects (a.geom, b.geom)

A função ST_Intersects tem elevada precisão (tolerância muito baixa). Mesmo quando a vetorização é feita com o “SNAP” (atração) habilitado, isso eventualmente pode ser um problema quando precisa ser usado em porções intermediárias de linhas ou polígonos (em pontos, vértices ou nós dificilmente ocorre), ou em algum outro caso específico. Uma solução para essa questão, que permite verificar intersecções com tolerância maior, é o uso da função ST_DWithin, que recebe por parâmetro, além das geometrias envolvidas, o valor tolerância (ou seja, funciona como se fosse um ST_Intersects com “buffer” definido pela tolerância).

                ST_DWithin (a.geom, b.geom, tolerância)

Obs.: A tolerância depende do sistema de coordenadas utilizado. Utilizei tolerância 0.00001 em sistema de coordenadas UTM e isso se mostrou bem adequado para trabalhar com elementos em cidades de médio porte.