Tutorial sobre banco de dados geográficos

Esse tutorial foi baseado nas aulas do professor Eduilson Livio Neves da Costa Carneiro - IFPI

Nele iremos falar brevemente sobre o processo de instalação,criação e manipulação de dados com o pgAdmin III e Integração de banco de dados com sistemas de informação geográfica

Sumário

  1. Programa e instalação
  2. Criando e manipulando dados com o pgAdmin III
  3. Integração de banco de dados com sistemas de informação geográfica

1. Programas e instalação

Os programas usados neste tutorial são: QGIS, PostgreSQL, PgAdmin III, PostGIS e PostGIS Shapefile importer. Eles são relativamente simples para instalar, apenas do PostgresSQL será destacado alguns passos. O processo de instalação do QGIS é bem simples, podendo fazer a instalação padrão apenas pressionando o botão “Próximo”. Para este tutorial é possível utilizar o QGIS a partir da versão 2.11. Porém, aqui foi utilizado a versão 2.18.15. Você poderá baixar diretamente do site do QGIS, ou temporariamente no seguinte link: QGIS 2.18.15.

O PostgresSQL é de fato uma coleção de programas, incluindo o servidor e alguns programas. Destacando o PG Admin III que é usado para administrar o servidor e como cliente, para acessar e gerenciar os dados. A versão utilizada nesse tutorial é a 9.5, e pode ser baixada diretamente pelo site: https://www.postgresql.org/download/ Na tela inicial da instalação, basta clicar em “Next”.

Selecione o diretório destino, pode utilizar o diretório indicado por padrão:

O mesmo se aplica ao diretório aonde serão armazenados os dados:

Na tela a seguir entre com a senha que será usado pelo usuário postgres para acessar o servidor. Para uso pessoal, pode-se usar a senha postgres.

A porta para acessar o servidor pode ser a padrão:

Para as próximas telas, basta clicar em “Next”. Após a instalação, aparecerá a seguinte tela:

Mantenha selecionado a opção “Stack Builder”. Essa opção iniciará a instalação dos adicionais, incluindo o PostGIS. Na primeira tela, deve aparecer selecionado o servidor instalado, no caso PosgreSQL 9.5

A seguir irá aparecer uma lista para selecionar os componentes. Para este tutorial será preciso selecionar apenas o PostGIS como abaixo:

Para concluir a instalação basta selecionar “Next” nas próximas telas, então terá os programas PostgreSQL, PgAdmin III, PostGIS e PostGIS Shapefile importer instalados e prontos para iniciar o tutorial.


2. Criando e manipulando dados com o pgAdmin III

2.1 Criando o banco de dados

No pgAdmin III selecione o servidor através de um click duplo. O sistema pode te pedir a senha, nesse caso entre com a senha criada durante a instalação.

Selecione DATABASES em object browser, e em seguida selecione: Menu Edit – New Object – New Databases

Na janela seguinte, nomeie o banco de dados como: banco01:

Com o banco de dados banco01 selecionado: Menu Tools – Query Tool ou (Ctrl + E) Após abrir o ambiente digite

 create extension postgis;

e pressione (F5):

2.2 Criando uma tabela de pontos

Na Ferramenta de Consulta (Ctrl+E), digite o seguinte comando SQL e depois pressione F5:

CREATE TABLE pontos (
geom_pt      geometry(POINT,0),
nome           varchar
);

Caso tenha algumas dúvidas sobre os comandos SQLs, um bom site é o mantido pela W3cSchools: https://www.w3schools.com/sql/

2.3 Inserindo alguns pontos hipotéticos

Considere três pontos no plano cartesiano, como os da figura abaixo:

Na Ferramenta de Consulta (Ctrl+E), digite então os seguintes comandos SQL e depois pressione F5:

INSERT INTO pontos  VALUES ('POINT(0 0)', 'Origem');
INSERT INTO pontos  VALUES ('POINT(5 0)',  'Eixo X');
INSERT INTO pontos  VALUES ('POINT(0 5)',  'Eixo Y');

2.4 Consultando os dados inseridos

Na Ferramenta de Consulta (Ctrl+E), digite então os seguintes comandos SQL e depois pressione F5:

SELECT nome, geom_pt
FROM pontos;

O resultado da consulta anterior está no formato WKB (Well Known Binary). Para visualizar os dados em um formato textual, digite o seguinte comando:

SELECT nome, ST_AsText(geom_pt)
FROM pontos;

No comando anterior já foi utilizado uma função espacial, dado que ela tem como entrada um dado espacial. A função espacial ST_AsText() transforma uma geometria em uma expressão textual, chamada “Well-KnowText” – WKT. Nesse formato as seguintes geometrias simples são representados como se segue:

GeometriaWell-KnowText
PontoPOINT (1 1);
LinhaLINESTRING (11, 2 2, 3 4);
PoligonoPOLYGON ((0 0, 0 1, 1 1, 1 0, 0 0));

O resultado da consulta é mostrado na imagem a seguir:

Em uma consulta, podemos usar as diversas operações espaciais suportadas pelo PostGIS, por exemplo a função para calcular a distância entre duas geometrias. A função ST_Distance() é usada para calcular a menor distância cartesiana entre dois objetos espaciais. Considere então um quarto ponto (em vermelho) no plano cartesiano.

Podemos então calcular a distância de cada ponto da tabela para este quarto ponto usando o seguinte comando SQL:

SELECT nome, ST_AsText(geom_pt),
             ST_Distance(geom_pt, 'POINT(5 5)')
FROM pontos;

2.5 Exercícios

  1. Criar duas tabelas, a primeira para armazenar lotes e a segunda para armazenar quadras. Ambas terão apenas 3 campos:
    • codigo do tipo inteiro,
    • nome do tipo varchar(10) e
    • geom do tipo geometry(POLYGON, 0).
  2. Na tabela lotes insira quatro polígonos como na figura a seguir:

  1. Na tabela quadras, insiram dois polígonos como na figura abaixo:

  1. Escrever as consultas espaciais para responder individualmente cada questão:
    • Selecione os registros da tabela lotes.
    • Mostre os registros da tabela quadras.
    • Visualize os nomes dos registros da tabela lotes.
    • Quantos registros constam na tabela quadras;
    • Selecione os registros da tabela lotes com nome igual a L1.
    • Selecione os registros da tabela quadras com codigo igual a 1.
    • Selecione a geometria de quadras com codigo = 2.
  2. Usando as funções espaciais, escrevam códigos SQL para as seguintes questões:
    • Qual a área dos lotes ?
    • Qual o perímetro da quadra Q2?
    • Qual a distância entre o lote L1 e a quadra Q2?
    • Quais os lotes vizinhos ao lote L2 ?
    • Quantos lotes estão dentro da quadra Q1?
    • Quais os lotes estão dentro da quadra Q2?
    • Quantos lotes estão dentro da quadra Q2?
    • Quais os lotes vizinhos ao lote L3 ?

3. Integração de banco de dados com sistemas de informação geográfica

3.1 Acessando um banco de dados existente

Caso queira verificar as respostas da consulta SQL, podemos visualizar as tabelas como camadas vetoriais no sistema de informação geográfica aberto, QGIS. No menu camadas, selecione Adicionar Camada - PostGIS. Uma opção mais rápida é usando o atalho Ctrl+Shift+D. .

Na próxima janela, selecione novo, para criar uma nova conexão:

Ao clicar em Novo, deverá entrar com um nome para conexão, o nome da máquina (servidor), o nome do banco de dados que deseja conectar, nome do usuário e senha. Como o servidor de banco de dados está instalado na nossa máquina local, então o nome da máquina é localhost.

Depois, basta clicar em conectar e selecionar e adicionar as tabelas lotes e quadras. Apois isso, será necessário selecionar “Sistema de coordenadas definida pelo usuário” dado que nosso dado não possui um sistema de coordenada. Então, poderemos visualizar nosso dado hipotético através do QGIS:

3.2 Criando novo banco de dados a partir de um arquivo SQL

Nessa sequência, veremos como carregar dados de um script SQL para um novo banco de dados. No aplicativo PGAdmin III, crie um banco de dados com nome banco02. Na Ferramenta de Consulta (Ctrl+E):

Abra o QGIS, crie um novo projeto e selecione adicionar uma camada PostGIS, de modo similar ao passo anterior. Crie uma conexão para o banco02 recém criado:

Conecte e selecione todas as tabelas (camadas ou layers):

Modifique a posição das camadas para que todas fiquem visíveis:

  1. Viveiros
  2. Pontes
  3. Rios
  4. Estradas_duplicadas
  5. Estradas
  6. Edificacoes (pontos)
  7. Edificacoes
  8. Localidades
  9. Lagos
  10. Florestas
  11. Bordas_mapa

Como na figura abaixo:

3.3 Exercícios

6) Usando o banco de dados banco02, escreva as seguintes consultas em SQL:

  1. Qual a dimensão da geometria da tabela lagos?
  2. Qual o tipo da geometria da BR 700?
  3. Verificar se uma geometria esta vazia
  4. Verificar se uma geometria é simples
  5. Qual as bordas de uma geometria
  6. Qual o Retângulo Envolvente (Envelopamento) de uma geometria
  7. Qual a coordenada X de uma geometria ponto
  8. Qual a coordenada Y de uma geometria ponto

3.4 Importando dados geográficos

Nesta atividade iremos usar alguns dados geográficos usando o sistema de informação geográfica QGIS. Inicialmente, baixe os seguintes dadosshp.zip, e descompacte os arquivos em alguma pasta.

Então crie um novo banco de dados denominado Brasil no PG Admin III. Após, como realizado anteriormente, crie uma conexão no QGIS

Verifique se está instalado o plugin DBManager no menu Complementos:

Caso ele não esteja instalado, basta pesquisar e adicionar. Com o plugin instalado, acesse o menu Banco de Dados - Gerenciar banco de dados.

Clique na conexão criada anteriormente. Selecione o esquema public. Então no menu Base de Dados acesse SQL Window. Na janele entre com o código SQL:

create extension postgis;

Então, basta clicar no icone Importar layer. Na janela que irá surgir, selecione o layer municipios_br e preenche os demais campos como abaixo.

Na janela que segue, escolha o sistema de referência 4326.

Após a importação, adicione o layer importado a tela como na figura abaixo:

Então, no QGIS será visto o layer de municipios como abaixo:

Realize o mesmo processo com os seguintes dados: Observação: Caso prefira, pode utilizar o Shp2pgSQL ou GUI Shape file loader:

3.5 Exercícios

7. Utilizando o banco de dados brasil, faça as seguintes consultas espaciais:

  1. Quantas sedes de municípios estão dentro de um raio de 100km do aeroporto de São Luiz?
  2. O nome dos municípios em que suas sedes estão a pelo menos 100KM de distância de algum aeroporto.
  3. Criar uma nova tabela com todas as áreas indígenas que estão dentro dos limites do município de São Felix do Xingu.
  4. Qual o tamanho em quilômetros da rede ferroviária do Estado do Maranhão?

3.6

Por fim, é sempre imporante criar indices para as colunas de geometria, para que o gerenciador de banco de dados seja mais eficiente durante as operações espaciais.

CREATE INDEX sedes_gix ON sedes USING GIST (geom);
CREATE INDEX estados_gix ON estados USING GIST (geom);