Pular para conteúdo

Banco de Dados

Introdução

Em um mundo cada vez mais orientado por dados, a capacidade de armazenar, analisar e visualizar informações geoespaciais tornou-se fundamental. É nesse contexto que o PostgreSQL com a extensão PostGIS brilha como uma poderosa e versátil ferramenta. Enquanto o PostgreSQL é amplamente reconhecido por sua robustez e flexibilidade como Sistema de Gerenciamento de Banco de Dados Relacional (SGBDR), o PostGIS adiciona uma dimensão adicional: a habilidade de manipular e processar dados geoespaciais com eficiência.

Essas ferramentas oferecem uma série de vantagens, tornando-o a escolha preferencial para uma ampla gama de aplicações que envolvem dados geoespaciais. Por estes e outros motivos o Banco de Dados (BD) utilizado no projeto PGST é o PostgreSQL e como as aplicações incorporados no PGST lidam frequentemente com dados geoespaciais o PostGIS também será utilizado.

A seguir, serão apresentados os passos para introduzir as camadas de dados geoespaciais no banco de dados e como podemos manipulá-los utilizando funções da extensão do PostGIS.

Bases de Dados das Camadas

As bases de dados das camadas utilizadas no sistema PGST geralmente são baixadas da internet em arquivos zip. Ao descompactar o arquivo zip a pasta com as camadas terá arquivos com extensões dbf, fix, prj, shp, shx.

O formato SHP ou Shapefile é um formato popular de arquivo contendo dados geoespaciais em forma de vetor usado por Sistemas de Informações Geográficas (SIG). A figura a seguir ilustra os procedimentos necessários para transformar um arquivo SHP em arquivo SQL e, por fim, em uma tabela no banco de dados.

Fluxo do Banco de Dados

Transformando Shapefile em SQL do Postgres

Utilize o comando shp2pgsql a seguir no terminal para converter uma camada em arquivo shapefile (shp) para um arquivo sql (com os comandos SQL para criação da tabela no postgres). Esse comando ilustra o passo 1 da figura acima.

shp2pgsql nome-arquivo-shp nome-tabela-criada > nome-arquivo-sql.sql

shp2pgsql AREA_IMOVEL_1 area_imovel_mg > area_imovel_mg.sql

Observação

Os comandos que envolvem shp2pgsql precisam ser executados em um terminal na mesma pasta do arquivo shp.

Observação

Para utilizar o comando shp2pgsql, é necessário instalar o PostGIS previamente.

Acrescentando mais Camadas em um Único SQL

Algumas camadas estão organizadas por estado e no sistema PGST estamos projetando o mesmo para responder para o Brasil todo. Assim, é necessário fazer um append em um arquivo sql já criado. O comando shp2pgsql com argumento -a faz exatamente isso. Assim, utilize o comando abaixo:

shp2pgsql -a nome-arquivo-shp nome-tabela-criada >> ../ nome-arquivo-sql.sql

shp2pgsql -a AREA_IMOVEL_1 area_imovel >> ../area_imovel.sql

Mudando o Sistema de Codificação das Camadas

Em alguns casos um erro pode ser gerado ao executar os comandos acima. Esse erro gera uma mensagem semelhante a Unable to convert data value to UTF-8 (iconv reports "Argumento inválido"). Current encoding is "UTF-8". Try "LATIN1" (Western European), or one of the values described at http://www.postgresql.org/docs/current/static/multibyte.html. A solução para esse problema pode ser encontrada no Link da Solução. Para esse caso utilize o comando abaixo:

shp2pgsql -W "latin1" nome-arquivo-shp nome-tabela-criada > nome-arquivo-sql.sql

shp2pgsql -W "latin1" vw_brasil_adm_embargo_a areas_embargadas_ibama > areas_embargadas_ibama.sql

Mudando a Código da Projeção das Camadas

Comando para geração de arquivo SQL a partir de um arquivo shapefile com o código de projeção.

shp2pgsql -s codigo-projeção nome-arquivo-shp > nome-arquivo-sql.sql

shp2pgsql -s 4326 BR_Municipios_2022 > municipios.sql

Identificadores de Referência Espacial

SRID é a sigla para "Spatial Reference Identifier" (Identificador de Referência Espacial). É um número inteiro que identifica um sistema de referência espacial ou sistema de coordenadas no contexto de Sistemas de Informação Geográfica (GIS) e análise espacial. Em termos mais simples, o SRID é um código que descreve como os pontos geoespaciais são representados em um determinado plano, seja bidimensional ou tridimensional. O SRID padrão utilizado em nossos projetos é 4674 que representa o SIRGAS2000.

Dependendo da fonte de dados das camadas que utlizamos, o SRID pode vir diferente do qual utilizamos. Neste caso, para garantir que estamos utitlizando o correto, podemos especificá-lo ao converter a camada para um arquivo extensão .sql:

shp2pgsql -W "latin1" -s 4674 nome-arquivo-shp nome-tabela-criada  > nome-arquivo-sql.sql

shp2pgsql -W "latin1" -s 4674 vw_brasil_adm_embargo_a areas_embargadas_ibama > areas_embargadas_ibama.sql

shp2pgsql -W "latin1" -s 31983:4674 vw_brasil_adm_embargo_a areas_embargadas_ibama > areas_embargadas_ibama.sql

Observação

Caso não seja utilizado o SRID 4674 na camadas do Banco de Dados um erro ao fazer as consultas será gerado.

Tranferência dos Arquivos para Servidor

Utilize o programa Filezilla link para transferir os arquivos SQL para o servidor da aplicação. O Filezilla é um cliente FTP para transferência de arquivos.

Tabelas Cadastradas no Sistema

Uma vez que as bases de dados das camadas tenham sido convertidas para sql, a tabela correspondente a cada camada pode ser criadas no banco de dados Postgres do PGST.

O comando de terminal abaixo lê um arquivo com as instruções SQL e cria uma tabela com dados no BD do postgres do sistema PGST.

psql -h localhost -p porta -d bandodedados -U usuario -f nome-do-arquivo.sql

psql -h 192.168.1.166 -p 5433 -d pgst -U postgres -f area_imovel.sql

Observação

Utilize o comando acima, caso você estiver executando em sua máquina local.

Erro

Caso, após a utilização do comando acima apareça em uma mensagem semelhante a mensagem a seguir.

psql:arquivo.sql:24: ERROR:  function addgeometrycolumn(unknown, unknown, unknown, unknown, unknown, integer) does not exist
LINE 1: SELECT AddGeometryColumn('','nome_da_tabela','geom','0','M...

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

...
psql:arquivo.sql:538: ERROR:  relation "nome_da_tabela" does not exist.

Isso significa que você está tentanto rodar um comando do PostGis sem ter instalado essa extensão antes. Para corrigir esse erro, execute os passos descritos na seção Definindo Suporte para a Extensão do PostGIS no pgAdmin no link.

Para o caso de você estar logado no servidor via SSH utilize o comando abaixo, ou seja, não é necessário especificar o IP e a porta.

psql -d bandodedados -U usuario -f nome-do-arquivo.sql

psql -d pgst -U postgres -f area_imovel.sql

Observação

Os comandos que envolvem psql precisam ser executados em um terminal na mesma pasta do arquivo sql.

Comando de terminal para entrar no postgres modo linha de comando do servidor (sem estar em uma bando de dados específico):

psql -U usuario

psql -U postgres

Índices Cadastrados no Sistema

Os índices espaciais são cruciais para consultas eficientes em dados geoespaciais. Isso acelera consultas de interseção, proximidade e outros tipos de consultas espaciais.

Dessa maneira, caso o sistema tenha muitas tabelas e dados é necessário/importante criar índices para as tabelas de forma a otimizar o tempo de consulta.

Inicialmente, utilize o comando abaixo para entrar no postgres modo linha de comando:

psql -d bandodedados -U usuario

psql -d pgst -U postgres

Informação

Após isso o terminal terá um cursor semelhante a $ pgst=#.

Utilize o comando SQL abaixo para criar Índices.

CREATE INDEX tabela_bd_idx ON tabela_bd USING GIST (geom);

CREATE INDEX reserva_legal_idx ON reserva_legal USING GIST (geom);

Recomendação

Sugerimos que seja criado um índice para cada uma das tabelas do banco de dados do postgis de forma a otimizar as consultas.

Alterando o SRID de uma Tabela

O comando abaixo muda o SRID de uma tabela que já está cadastrada no Banco de Dados.

SELECT UpdateGeometrySRID(tabela, coluna, srid);

SELECT UpdateGeometrySRID('area_imovel', 'geom', 4674);

Atenção

Esse comando será necessário de executar quando o arquivo SQL gerado pelo comando shp2pgsql tiver uma linha semelhante a SELECT AddGeometryColumn('','ibama','geom','0','POINT',2);. Repare que o padrão da geometria (geom) é 0 o que está errado. Geralmente, trabalhamos com o padrão 4674. Caso esse arquivo SQL já tenha sido subido no banco aí devemos executar o comando acima (SELECT UpdateGeometrySRID) para atualização do SRID.

Informação

Este comando deve ser executado para cada tabela que estiver com o SRID incorreto.

Erro

Caso o sistema PGST esteja executando e em algum momento apareça a mensagem de erro abaixo, então uma das soluções possíveis é executar o comando SELECT UpdateGeometrySRID para atualizar o SRID.

sycopg2.errors.InternalError_: ST_Intersects: Operation on mixed SRID geometries (MultiPolygon, 0) != (MultiPolygon, 4674)

A mensagem de erro acima foi gerada ao tentar fazer a intersseção de duas camadas em diferentes sistemas de codificação (codificação do MultiPolygon em 0 e codificação do MultiPolygon em 4674). O comando de intersseção necessita que as camadas estejam no mesmo sistema de codificação.

Informação

Uma segunda forma de resolver isso é executando o comando shp2pgsql colocando o parâmetro -s. Por exemplo: shp2pgsql -s 4674 ibama_shp ibama > ibama.sql. Lembre-se: esse comando só funciona se arquivo shapefile estiver no padrão 4674 ou SIRGAS 2000. Caso o arquivo baixado esteja em outro formato esse comando não irá funcionar. Caso se escolha fazer dessa forma, então a camada com erro deverá ser removida e a camada corrigida deverá ser subida para o banco de dados.

Backup do Banco de Dados

Para realizar o backup do banco de dados no Docker, execute os seguintes comandos.

docker exec CONTAINER_ID pg_dump -U usuario nome_do_banco > nome_do_arquivo_sql.sql

docker exec 6d77e8796157 pg_dump -U postgres pgstmin > pgstmin.sql

Dica

Utilize o comando docker ps para descobrir o ID do container do postgis. É necessário que o container esteja em execução.

Restore do Banco de Dados

Para realizar a recuperação (restore) do banco de dados que está no Docker via linha de comando, execute os sequintes comandos.

cat nome_do_arquivo_sql.sql | docker exec -i CONTAINER_ID psql -U usuario -d nome_do_banco

cat pgstmin.sql | docker exec -i 6d77e8796157 psql -U postgres -d pgstmin

Resumos de Comandos do Postgres

Para entrar no Postgres modo linha de comando faça o seguinte:

psql -d bandodedados -U usuario

psql -d pgst -U postgres

Informação

Após isso o terminal terá um cursor semelhante a $ pgst=#.

Comando para listar todos os usuários criados:

\du

Comando para listar todas as bases de dados criadas:

\l

Comando para listar todas as tabelas criadas:

\dt

Comando para saber se uma tabela tem índice:

\d nome_da_tabela

\d area_imovel

Comando para apagar uma tabela criada:

DROP TABLE nome_da_tabela;

DROP TABLE area_imovel;

Comando SQL usado para apagar índices:

DROP INDEX nome_da_tabela_idx;

DROP INDEX area_imovel_idx;

Comando para sair do modo linha de comando do postgres:

\q

Operações Espaciais

O PostGIS é uma extensão poderosa para o PostgreSQL que permite realizar uma ampla variedade de operações espaciais em dados geoespaciais. Essas operações permitem analisar, manipular e extrair informações úteis de objetos geoespaciais, como pontos, linhas e polígonos. O PostGIS oferece uma gama diversificada de funções para atender às necessidades de análise espacial em uma ampla variedade de aplicações, desde sistemas de informação geográfica (GIS) até análise de localização.

Vamos explorar algumas das operações espaciais mais comuns oferecidas pelo PostGIS, juntamente com exemplos práticos:

Interseção Espacial

A operação de interseção permite determinar a sobreposição de dois objetos geoespaciais, retornando uma geometria que representa a área compartilhada.

Exemplo:

SELECT ST_Intersection(geoA, geoB) AS intersecao
FROM tabela_geoespacial1,
     tabela_geoespacial2
WHERE condicao;
Geo A Geo B area_intersecao intersecao
Geo A Geo B Área de Interseção Interseção

União Espacial

A operação de união combina dois ou mais objetos geoespaciais em um único objeto, formando uma geometria que engloba todos os elementos originais.

Exemplo:

SELECT ST_Union(geo) AS uniao
FROM tabela_geoespacial;

ou

SELECT ST_Union(geoA, geoB) as uniao
FROM tabela_geoespacial1,
     tabela_geoespacial2
WHERE condicao;
Geo A Geo B União

OBS: As imagens representam a segunda query do exemplo. Em relação à primeira, seriam um conjunto de duas ou mais geometrias de uma mesma tabela.

Buffer

A operação de buffer cria uma zona de influência ao redor de um objeto geoespacial, gerando uma nova geometria que representa uma área de distância uniforme em torno do objeto original.

Exemplo:

SELECT ST_Buffer(geo, 0.5) AS buffer
FROM tabela_geoespacial;
Geo Buffer

Diferença

A função ST_Difference permite calcular a diferença entre duas geometrias, resultando em uma nova geometria que representa a parte da primeira geometria que não se sobrepõe com a segunda geometria.

Exemplo:

SELECT ST_Difference(geoA, geoB) AS diferenca
FROM tabela_geoespacia1,
     tabela_geoespacial2
WHERE condicao;
Geo A Geo B Área de União Diferença

Centróide

A função ST_Centroid calcula o centróide de uma geometria, que é o ponto médio do objeto. Isso é útil para determinar o ponto central de um polígono, por exemplo.

Exemplo:

SELECT nome, ST_Centroid(geo) AS centroide
FROM tabela_poligonos;
Geo Centróide

Área

A função ST_Area calcula a área de uma geometria, seja ela um polígono, linha ou ponto, em unidades do sistema de coordenadas definido.

Exemplo:

SELECT nome, ST_Area(geo) AS area
FROM tabela_poligonos;

Distância

A função ST_Distance calcula a distância entre dois pontos, linhas ou polígonos em unidades do sistema de coordenadas definido.

Exemplo:

SELECT nome1, nome2, ST_Distance(geoA, geoB) AS distancia
FROM tabela_geoespacial1,
     tabela_geoespacial2;

Igualdade

A função ST_Equals verifica se duas geometrias são iguais, ou seja, têm a mesma forma e as mesmas coordenadas.

Exemplo:

SELECT nome
FROM tabela_geoespacial1,
     tabela_geoespacial2
WHERE ST_Equals(geoA, geoB);

Interseção

A função ST_Intersects verifica se duas geometrias se intersectam, ou seja, se elas têm alguma área em comum. Retorna TRUE se houver interseção e FALSE caso contrário.

Exemplo:

SELECT nome
FROM tabela_geoespacial1,
     tabela_geoespacial2
WHERE ST_Intersects(geom1, geom2);