Geospatial data com PostGIS: tipos e consultas

1. Introdução ao PostGIS e ao Modelo Geográfico

1.1 O que é PostGIS: extensão espacial para PostgreSQL

PostGIS é a extensão espacial mais popular para PostgreSQL, transformando o banco de dados relacional em um verdadeiro sistema de informação geográfica (SIG). Com PostGIS, é possível armazenar, consultar e manipular objetos geográficos como pontos, linhas e polígonos diretamente no banco de dados, utilizando funções especializadas que seguem os padrões do Open Geospatial Consortium (OGC).

1.2 Sistemas de referência espacial (SRID) e o padrão WGS84 (EPSG:4326)

Todo dado geográfico precisa de um sistema de referência espacial (SRID) para ser interpretado corretamente. O padrão mais comum é o WGS84 (EPSG:4326), que representa coordenadas em latitude e longitude. O PostGIS utiliza o catálogo spatial_ref_sys para gerenciar milhares de SRIDs diferentes.

1.3 Instalação e ativação da extensão PostGIS no banco

Para começar, é necessário criar a extensão no banco de dados:

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

Verifique a instalação com:

SELECT PostGIS_Version();

2. Tipos de Dados Geométricos e Geográficos

2.1 Tipos básicos: POINT, LINESTRING, POLYGON e MULTI* variantes

O PostGIS oferece tipos geométricos fundamentais e suas variantes multi:

  • POINT: coordenada única (x, y)
  • LINESTRING: sequência de pontos formando uma linha
  • POLYGON: área fechada com anéis exterior e interiores (buracos)
  • MULTIPOINT, MULTILINESTRING, MULTIPOLYGON: coleções homogêneas
  • GEOMETRYCOLLECTION: coleção heterogênea

2.2 Diferença entre geometry e geography: precisão vs. performance

A coluna geometry trabalha em um plano cartesiano, sendo mais rápida para cálculos, mas imprecisa para grandes distâncias. A coluna geography considera a curvatura da Terra (elipsoide WGS84), oferecendo precisão global, porém com custo computacional maior.

2.3 Construção de objetos com funções ST_GeomFromText, ST_MakePoint e ST_GeogFromText

-- Criando um ponto com ST_MakePoint (geometry)
SELECT ST_MakePoint(-46.6333, -23.5505) AS ponto_sp;

-- Criando um ponto com ST_GeomFromText (WKT)
SELECT ST_GeomFromText('POINT(-46.6333 -23.5505)', 4326) AS ponto_sp_wkt;

-- Criando um geography a partir de texto
SELECT ST_GeogFromText('SRID=4326;POINT(-46.6333 -23.5505)') AS ponto_sp_geog;

-- Criando um polígono
SELECT ST_GeomFromText('POLYGON((-46.7 -23.6, -46.5 -23.6, -46.5 -23.4, -46.7 -23.4, -46.7 -23.6))', 4326) AS area_sp;

3. Operações de Relacionamento Espacial

3.1 Testes booleanos: ST_Intersects, ST_Contains, ST_Within e ST_Touches

-- Verificar se um ponto está dentro de um polígono
SELECT ST_Intersects(
    ST_GeomFromText('POINT(-46.6333 -23.5505)', 4326),
    ST_GeomFromText('POLYGON((-46.7 -23.6, -46.5 -23.6, -46.5 -23.4, -46.7 -23.4, -46.7 -23.6))', 4326)
) AS ponto_na_area;

-- ST_Contains: a geometria A contém a geometria B?
SELECT ST_Contains(area_geo, ponto_geo) AS area_contem_ponto;

-- ST_Touches: as geometrias se tocam (mas não se sobrepõem)?
SELECT ST_Touches(poligono1, poligono2) AS tocam;

3.2 Distâncias e proximidade: ST_Distance, ST_DWithin e ST_ClosestPoint

-- Calcular distância entre dois pontos (em metros para geography)
SELECT ST_Distance(
    ST_GeogFromText('SRID=4326;POINT(-46.6333 -23.5505)'),
    ST_GeogFromText('SRID=4326;POINT(-43.1729 -22.9068)')
) AS distancia_rio_sp;

-- Encontrar pontos dentro de um raio de 10 km
SELECT nome, geom
FROM pontos_interesse
WHERE ST_DWithin(
    geom::geography,
    ST_GeogFromText('SRID=4326;POINT(-46.6333 -23.5505)'),
    10000
);

-- Encontrar o ponto mais próximo em outra geometria
SELECT ST_ClosestPoint(
    ST_GeomFromText('LINESTRING(-46.7 -23.5, -46.5 -23.5)', 4326),
    ST_GeomFromText('POINT(-46.6 -23.55)', 4326)
) AS ponto_mais_proximo;

3.3 Uso de índices espaciais (GIST) para acelerar consultas de junção espacial

-- Criar índice GIST em coluna geometry
CREATE INDEX idx_pontos_geom ON pontos_interesse USING GIST (geom);

-- Consulta de junção espacial com índice
SELECT p.nome, r.nome_regiao
FROM pontos_interesse p
JOIN regioes r ON ST_Within(p.geom, r.geom)
WHERE r.nome_regiao = 'Centro';

4. Funções de Medição e Transformação

4.1 Cálculo de área, perímetro e comprimento: ST_Area, ST_Perimeter, ST_Length

-- Área de um polígono (em metros quadrados para geography)
SELECT ST_Area(
    ST_GeogFromText('SRID=4326;POLYGON((-46.7 -23.6, -46.5 -23.6, -46.5 -23.4, -46.7 -23.4, -46.7 -23.6))')
) AS area_m2;

-- Perímetro do mesmo polígono
SELECT ST_Perimeter(
    ST_GeogFromText('SRID=4326;POLYGON((-46.7 -23.6, -46.5 -23.6, -46.5 -23.4, -46.7 -23.4, -46.7 -23.6))')
) AS perimetro_m;

-- Comprimento de uma linha
SELECT ST_Length(
    ST_GeogFromText('SRID=4326;LINESTRING(-46.7 -23.5, -46.5 -23.5)')
) AS comprimento_m;

4.2 Transformação entre SRIDs com ST_Transform

-- Converter de WGS84 (4326) para UTM zona 23S (32723)
SELECT ST_Transform(
    ST_GeomFromText('POINT(-46.6333 -23.5505)', 4326),
    32723
) AS ponto_utm;

4.3 Operações de buffer, simplificação e união: ST_Buffer, ST_Simplify, ST_Union

-- Criar buffer de 100 metros ao redor de um ponto
SELECT ST_Buffer(
    ST_GeomFromText('POINT(-46.6333 -23.5505)', 4326),
    0.001
) AS buffer_ponto;

-- Simplificar geometria (reduzir número de vértices)
SELECT ST_Simplify(geom, 0.01) AS geom_simplificada
FROM regioes;

-- Unir múltiplas geometrias
SELECT ST_Union(geom) AS area_total
FROM lotes
WHERE bairro = 'Pinheiros';

5. Consultas Práticas com Dados Geográficos

5.1 Encontrar pontos dentro de um polígono (ex: lojas em uma região)

-- Criar tabela de exemplo
CREATE TABLE lojas (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100),
    geom GEOMETRY(POINT, 4326)
);

CREATE TABLE regioes (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100),
    geom GEOMETRY(POLYGON, 4326)
);

-- Consultar lojas dentro de uma região
SELECT l.nome, l.geom
FROM lojas l
JOIN regioes r ON ST_Within(l.geom, r.geom)
WHERE r.nome = 'Zona Sul';

5.2 Calcular distância entre dois pontos e ordenar por proximidade

-- Encontrar as 5 lojas mais próximas de um ponto de referência
SELECT nome, 
       ST_Distance(geom::geography, ST_GeogFromText('SRID=4326;POINT(-46.6333 -23.5505)')) AS distancia
FROM lojas
ORDER BY distancia
LIMIT 5;

5.3 Agregação espacial: ST_Collect, ST_ConvexHull e agrupamento por região

-- Coletar todos os pontos de uma região em uma única geometria
SELECT ST_Collect(geom) AS pontos_coletados
FROM lojas
WHERE bairro = 'Centro';

-- Calcular o envoltório convexo (menor polígono convexo que contém todos os pontos)
SELECT ST_ConvexHull(ST_Collect(geom)) AS envoltorio
FROM lojas
WHERE bairro = 'Centro';

-- Agrupar lojas por bairro e criar geometrias agregadas
SELECT bairro, ST_Collect(geom) AS geometria_agregada
FROM lojas
GROUP BY bairro;

6. Otimização e Indexação Espacial

6.1 Criando e mantendo índices GIST em colunas geometry/geography

-- Índice GIST para coluna geometry
CREATE INDEX idx_lojas_geom ON lojas USING GIST (geom);

-- Índice GIST para coluna geography (requer cast explícito)
CREATE INDEX idx_lojas_geog ON lojas USING GIST ((geom::geography));

-- Manutenção do índice
REINDEX INDEX idx_lojas_geom;

6.2 Estratégias de clusterização com CLUSTER e ST_ClusterDBSCAN

-- Clusterizar tabela fisicamente pelo índice espacial
CLUSTER lojas USING idx_lojas_geom;

-- Identificar clusters com DBSCAN (distância máxima de 100 metros)
SELECT id, nome, geom,
       ST_ClusterDBSCAN(geom, 0.001, 2) OVER () AS cluster_id
FROM lojas;

6.3 Uso de EXPLAIN ANALYZE para depuração de consultas espaciais

EXPLAIN ANALYZE
SELECT l.nome
FROM lojas l
JOIN regioes r ON ST_Within(l.geom, r.geom)
WHERE r.nome = 'Zona Sul';

7. Casos de Uso Avançados e Integração

7.1 Trabalhando com dados de GPS e séries temporais geoespaciais

-- Tabela de rastreamento GPS
CREATE TABLE rastreamento_gps (
    veiculo_id INTEGER,
    timestamp TIMESTAMP,
    geom GEOMETRY(POINT, 4326)
);

-- Encontrar trajetória de um veículo em um período
SELECT ST_MakeLine(geom ORDER BY timestamp) AS trajetoria
FROM rastreamento_gps
WHERE veiculo_id = 42
  AND timestamp BETWEEN '2024-01-01' AND '2024-01-02';

7.2 Consultas com LATERAL JOIN para cálculos ponto-a-polígono

-- Para cada ponto, encontrar o polígono mais próximo
SELECT p.nome AS ponto,
       r.nome AS regiao_proxima,
       ST_Distance(p.geom, r.geom) AS distancia
FROM pontos_interesse p
CROSS JOIN LATERAL (
    SELECT nome, geom
    FROM regioes
    ORDER BY p.geom <-> geom
    LIMIT 1
) r;

7.3 Exportação de resultados para GeoJSON com ST_AsGeoJSON

-- Exportar todas as lojas como GeoJSON
SELECT json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(
        json_build_object(
            'type', 'Feature',
            'geometry', ST_AsGeoJSON(geom)::json,
            'properties', json_build_object('nome', nome, 'bairro', bairro)
        )
    )
) AS geojson
FROM lojas;

Referências