Usando a Extensão PostGIS no PostgreSQL

Introdução

Neste laboratório, você aprenderá a usar a extensão PostGIS para adicionar capacidades espaciais a um banco de dados PostgreSQL. O PostGIS transforma seu banco de dados em um banco de dados espacial, permitindo armazenar e consultar dados definidos em um espaço geométrico.

Você começará criando um novo banco de dados e habilitando a extensão PostGIS. Em seguida, criará uma tabela com uma coluna GEOMETRY para armazenar dados de localização. Finalmente, inserirá dados para várias cidades e realizará consultas espaciais para calcular distâncias e encontrar dados dentro de uma área específica.

Criar um Banco de Dados Espacial e Habilitar PostGIS

Nesta etapa, você criará um novo banco de dados e habilitará a extensão PostGIS dentro dele. É uma boa prática usar um banco de dados dedicado para dados espaciais para mantê-lo organizado.

Primeiro, abra um terminal e conecte-se ao servidor PostgreSQL usando o terminal interativo psql. Você se conectará como o superusuário padrão postgres.

sudo -u postgres psql

Você verá o prompt do psql, que se parece com postgres=#.

Em seguida, crie um novo banco de dados chamado spatial_db. Todos os comandos SQL no psql devem terminar com um ponto e vírgula (;).

CREATE DATABASE spatial_db;

Você deverá ver a seguinte mensagem de confirmação:

CREATE DATABASE

Agora, conecte-se ao seu banco de dados recém-criado. O comando \c é usado para se conectar a um banco de dados diferente.

\c spatial_db

Seu prompt mudará para indicar que você agora está conectado ao spatial_db:

You are now connected to database "spatial_db" as user "postgres".
spatial_db=#

Com o banco de dados pronto, habilite a extensão PostGIS. Este comando carrega todos os tipos, funções e tabelas espaciais do PostGIS em seu banco de dados.

CREATE EXTENSION postgis;

A saída confirmará que a extensão foi criada:

CREATE EXTENSION

Para verificar se o PostGIS está instalado corretamente, você pode verificar sua versão.

SELECT PostGIS_full_version();

Isso exibirá informações detalhadas da versão do PostGIS e suas bibliotecas relacionadas, confirmando uma configuração bem-sucedida. Você agora pode prosseguir para a próxima etapa. Não saia do shell psql.

Criar uma Tabela Espacial e Inserir Dados

Agora que você tem um banco de dados espacial, pode criar tabelas para armazenar dados geográficos. Nesta etapa, você criará uma tabela cities e inserirá as coordenadas de algumas cidades importantes.

Você ainda deve estar no shell psql, conectado ao banco de dados spatial_db.

Primeiro, crie uma tabela chamada cities. Esta tabela terá um id e name padrão, juntamente com uma coluna location do tipo GEOMETRY.

Entendendo GEOMETRY(Point, 4326)

• GEOMETRY: Este é o tipo de dado fundamental do PostGIS para armazenar feições espaciais.
• Point: Isso especifica que a coluna de geometria armazenará apenas pontos. Outros tipos incluem LINESTRING e POLYGON.
• 4326: Este é o Identificador do Sistema de Referência Espacial (SRID). O SRID 4326 corresponde ao WGS 84, o sistema de coordenadas padrão usado pelo GPS e Google Maps.

Execute o seguinte comando para criar a tabela:

CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    location GEOMETRY(Point, 4326)
);

Você verá a confirmação:

CREATE TABLE

Em seguida, insira dados na tabela cities. Você usará a função ST_GeomFromText para converter representações de texto em formato Well-Known Text (WKT) de pontos em objetos de geometria. O formato é POINT(longitude latitude).

INSERT INTO cities (name, location) VALUES
    ('New York', ST_GeomFromText('POINT(-74.0060 40.7128)', 4326)),
    ('London', ST_GeomFromText('POINT(-0.1278 51.5074)', 4326)),
    ('Tokyo', ST_GeomFromText('POINT(139.6917 35.6895)', 4326));

A saída indica que três linhas foram inseridas:

INSERT 0 3

Para verificar se os dados foram inseridos corretamente, você pode consultar a tabela. Use a função ST_AsText para converter o objeto de geometria de volta para um formato de texto legível por humanos.

SELECT id, name, ST_AsText(location) FROM cities;

A saída deve exibir os dados que você acabou de inserir:

 id |   name   |          st_astext
----+----------+-----------------------------
  1 | New York | POINT(-74.006 40.7128)
  2 | London   | POINT(-0.1278 51.5074)
  3 | Tokyo    | POINT(139.6917 35.6895)
(3 rows)

Você criou com sucesso uma tabela espacial e a preencheu com dados.

Realizar Consultas e Análises Espaciais

O verdadeiro poder do PostGIS reside na sua capacidade de realizar consultas espaciais. Nesta etapa, você usará funções do PostGIS para calcular a distância entre dois pontos e para encontrar quais pontos se encontram dentro de uma determinada área.

Você ainda deve estar no shell psql, conectado ao banco de dados spatial_db.

Primeiro, vamos calcular a distância entre Nova York e Londres. Para cálculos de distância precisos na superfície da Terra, é melhor converter o tipo geometry para o tipo geography. O tipo geography leva em consideração a curvatura da Terra, e ST_Distance retornará o resultado em metros.

SELECT ST_Distance(
    (SELECT location::geography FROM cities WHERE name = 'New York'),
    (SELECT location::geography FROM cities WHERE name = 'London')
);

A consulta retornará a distância do círculo máximo em metros. O resultado será um número grande, aproximadamente 5,57 milhões de metros.

   st_distance
-----------------
 5570299.7888681
(1 row)

Em seguida, vamos realizar uma análise para encontrar quais de nossas cidades estão a menos de 200 quilômetros de Londres. Para fazer isso, você primeiro criará um buffer circular em torno da localização de Londres e, em seguida, verificará quais localizações de cidades se intersectam com essa área de buffer.

• ST_Buffer: Cria uma geometria que representa todos os pontos cuja distância da geometria de origem é menor ou igual a uma distância especificada. A distância é em metros quando usada com o tipo geography.
• ST_Intersects: Retorna true se duas geometrias "se intersectam espacialmente" - se elas têm algum espaço em comum.

WITH london_buffer AS (
  SELECT ST_Buffer(location::geography, 200000) AS geom FROM cities WHERE name = 'London'
)
SELECT c.name
FROM cities c, london_buffer lb
WHERE ST_Intersects(c.location::geography, lb.geom);

Esta consulta usa uma Common Table Expression (CTE) para criar um buffer de 200.000 metros (200 km) em torno de Londres. Em seguida, encontra todas as cidades que se intersectam com este buffer. Como esperado, apenas Londres está dentro desta área.

  name
--------
 London
(1 row)

Você agora realizou uma análise espacial básica. Você pode sair do shell psql digitando \q.

\q

Limpar o Banco de Dados

É uma boa prática limpar seu ambiente após concluir seu trabalho. Nesta etapa, você removerá o banco de dados spatial_db. Remover um banco de dados apaga o próprio banco de dados, juntamente com todos os objetos que ele contém, como tabelas e extensões.

Você deve estar de volta ao prompt do terminal regular (labex@vm:~$).

Para remover um banco de dados, você deve estar conectado a um banco de dados diferente. Conecte-se ao banco de dados padrão postgres.

sudo -u postgres psql

Agora, execute o comando DROP DATABASE.

DROP DATABASE spatial_db;

Você verá uma mensagem de confirmação:

DROP DATABASE

Você pode verificar se o banco de dados foi removido listando todos os bancos de dados disponíveis com o comando \l.

\l

Você verá que spatial_db não está mais na lista.

Finalmente, saia do shell psql.

\q

Resumo

Neste laboratório, você aprendeu a usar a extensão PostGIS para adicionar suporte a dados espaciais ao PostgreSQL. Você criou com sucesso um banco de dados dedicado, habilitou a extensão PostGIS, definiu uma tabela com uma coluna de geometria, inseriu dados de localização e realizou consultas espaciais para calcular distâncias e encontrar interseções. Essas habilidades fornecem uma base sólida para a construção de aplicações cientes de localização com PostgreSQL.