Capítulo 8: Atividade Prática Supervisionada

1. Contexto do Projeto

O banco de dados espacial PostgreSQL/PostGIS é a ferramenta central desta atividade. Você trabalhará a partir de dados oficiais "brutos" (raw), realizando todo o ciclo de inteligência geográfica: extração, tratamento, análise e cruzamento de informações.

O trabalho é individual e deve ser realizado estritamente dentro do seu Schema pessoal.

2. Organização dos Dados

No servidor, você encontrará um schema público (somente leitura) contendo as seguintes tabelas de origem:

• public.pontos_coleta_raw — Pontos geográficos de coleta
• public.balneabilidade_2024_raw — Planilha de medições (sem geometria)
• public.densidade_demografica_raw — Polígonos censitários

Importante: Você não deve alterar nada em public.*. Sempre copie os dados para o seu schema bal_seunome usando CREATE TABLE AS antes de higienizar ou editar. Suas tabelas de trabalho devem seguir nomes claros como pontos_coleta, densidade_demografica e relatorio_balneabilidade.

Etapa 0: Preparação do Ambiente

Antes de manipular dados, precisamos garantir que sua "mesa de trabalho" (Schema) esteja pronta. No QGIS DB Manager, execute um bloco por vez para evitar travamentos.

Requisitos:

1. Crie seu schema pessoal no banco seguindo o padrão: bal_seunome.
2. Verifique suas permissões de escrita criando e apagando uma tabela de teste.

Roteiro de Comandos SQL

Passo 1: Criar o seu espaço de trabalho (Schema)

-- Substitua 'seunome' pelo seu usuário real. Ex: bal_joao
CREATE SCHEMA bal_seunome;

Passo 2: Testar permissão de escrita (Criar tabela temporária)

-- Tente criar uma tabela simples para ver se funciona
CREATE TABLE bal_seunome.teste_permissao (
    id INTEGER,
    nome VARCHAR(50)
);

Passo 3: Limpeza (Apagar tabela de teste)

-- Se o passo anterior funcionou, remova a tabela de teste
DROP TABLE bal_seunome.teste_permissao;

Etapa 1: Criação de Tabelas de Trabalho

Teoria: Independência dos Dados

Vamos utilizar a técnica Snapshot (Cópia Estática). Ao contrário de uma View, que é uma janela dinâmica para o dado original, o comando CREATE TABLE AS cria uma cópia física independente.

Prática: A Lógica SQL

A estrutura lógica abaixo é um modelo que deve ser adaptado à estrutura real das tabelas de origem (nomes de colunas, filtros, etc.). Confira sempre os nomes exatos no DB Manager antes de rodar.

CREATE TABLE bal_seunome.pontos_balneabilidade AS
SELECT
    coluna_tx_BAIRRO_2020        AS id_ponto,  -- Nome original não padronizado; renomeado para 'id_ponto' em snake_case, seguindo o padrão do banco
    "BAIRRO_TEXTO_RUIM_ORIGINAL" AS bairro,    -- Nome original pouco legível; renomeado para 'bairro' em snake_case para facilitar leitura e consultas
    geom                                      -- Coluna de geometria mantida; 'geom' é o nome padrão adotado para a geometria
FROM
    public.tabela_gigante_origem
WHERE
    municipio ILIKE 'Florianópolis';          -- Filtro textual com ILIKE para considerar variações de maiúsculas/minúsculas;
                                              -- ATENÇÃO: se a coluna foi criada como "MUNICIPIO" (com aspas e em MAIÚSCULAS),
                                              -- será necessário escrever "MUNICIPIO" em vez de municipio neste WHERE.

Dica Prática: Antes de filtrar, execute SELECT DISTINCT municipio FROM public.tabela_gigante_origem; para identificar todas as variações de escrita (maiúsculas, sem acento, espaços extras) e decidir se usará apenas ILIKE ou funções de limpeza.

Engenharia de Prompt (Uso de IA)

Use os modelos abaixo para gerar suas queries na IA. Não esqueça de mencionar para a IA criar no schema bal_seunome.

O "Pulo do Gato": PK e Índices

Tabelas criadas assim nascem "mortas" para edição no QGIS (sem Chave Primária, o QGIS não permite salvar edições) e lentas (sem Índice Espacial, o desempenho de pan/zoom e joins degrada rapidamente).

Execute os passos abaixo separadamente para cada tabela criada:

Passo 1Adicionar Chave Primária (PK)

ALTER TABLE bal_seunome.sua_tabela ADD COLUMN id SERIAL PRIMARY KEY;

Passo 2Adicionar Índice Espacial

CREATE INDEX idx_nome_geom ON bal_seunome.sua_tabela USING GIST (geom);

* Repita esses dois passos para a tabela de demografia, pontos e histórico (na de histórico, pule o índice espacial pois ela não tem mapa).

Etapa 2: Higienização de Dados

Dados importados de planilhas frequentemente chegam como TEXTO. Antes de alterar a tabela, inspecione os valores com um SELECT DISTINCT e teste suas expressões de limpeza (ex: SELECT REPLACE(temperatura, ',', '.')::NUMERIC FROM ... LIMIT 20).

2.1 Conversão de Tipos (Casting)

Exemplo 1: Convertendo Data (De Texto 'DD/MM/YYYY' para DATE)

ALTER TABLE bal_seunome.relatorio_balneabilidade 
ALTER COLUMN data_coleta TYPE DATE 
USING TO_DATE(data_coleta, 'DD/MM/YYYY');

Exemplo 2: Limpando Números (Trocando vírgula por ponto)

-- Necessário para cálculos de estatística
ALTER TABLE bal_seunome.relatorio_balneabilidade 
ALTER COLUMN temperatura TYPE NUMERIC 
USING REPLACE(temperatura, ',', '.')::NUMERIC;

Exemplo 3: Removendo símbolos (Ex: '< 10' vira 10)

-- Limpeza da coluna de bactérias
UPDATE bal_seunome.relatorio_balneabilidade 
SET ecoli = REGEXP_REPLACE(ecoli, '[^0-9]', '', 'g')::INTEGER;

2.2 Padronização de SRID (Sistema de Coordenadas)

Para realizar cruzamentos espaciais, as tabelas de mapa devem "falar a mesma língua" métrica (UTM 22S - SIRGAS 2000).

Passo 1: Verificar o SRID atual

SELECT ST_SRID(geom) FROM bal_seunome.densidade_demografica LIMIT 1;

Passo 2: Transformar para UTM 22S (Metros)

ALTER TABLE bal_seunome.densidade_demografica 
ALTER COLUMN geom TYPE geometry(MultiPolygon, 31982) 
USING ST_Transform(geom, 31982);

Etapa 3: Join de Atributos (Table to Table)

Objetivo: Criar uma VIEW que una os dados de qualidade da água (Tabela) com a geometria (Ponto).

CREATE VIEW bal_seunome.vw_balneabilidade_mapa AS
SELECT
    h.data_coleta,
    h.condicao,
    h.ecoli,
    p.geom       -- Trazendo a geometria da tabela de pontos
FROM
    bal_seunome.relatorio_balneabilidade h
JOIN
    bal_seunome.pontos_coleta p
ON
    -- Higienização "on-the-fly" para garantir o vínculo
    TRIM(UPPER(h.nome_ponto)) = TRIM(UPPER(p.nome_local));

Etapa 4: Join Espacial (Spatial Join)

Objetivo: Descobrir em qual Setor Censitário cada ponto de coleta está localizado.

Requisito Crítico: SRID

Use ST_Transform para garantir que o ponto (geralmente em graus/4326) esteja em metros (31982) igual à demografia.

CREATE VIEW bal_seunome.vw_pontos_com_demografia AS
SELECT
    p.nome_local,
    d.bairro,
    d.populacao_total,
    d.densidade_ha
FROM
    bal_seunome.pontos_coleta p
JOIN
    bal_seunome.densidade_demografica d
ON
    -- A mágica espacial: Verdadeiro se o Ponto P estiver DENTRO do Polígono D
    ST_Within( ST_Transform(p.geom, 31982), d.geom );

Etapa 5: Consultas Temáticas

Agora que temos Views poderosas integrando Balneabilidade, Localização e Demografia, escolha um foco para extrair inteligência dos dados.

Troubleshooting: Por que meu SQL falhou?

Checklist de sobrevivência para erros comuns no PostGIS.