Tratamento dos Dados do GeoFazendas Web

consultas.sql

WITH municipio AS (
    SELECT geom
    FROM maps_geometriamunicipio
    WHERE cod_ibge_m = '5007935'
)

SELECT
    t.*
FROM maps_texturasolo t
JOIN maps_grade10000 g
  ON g.id = t.grade_id
JOIN municipio m
  ON ST_Intersects(g.geom, m.geom)
ORDER BY t.grade_id, t.val;

ALTER TABLE maps_texturasolo
ALTER COLUMN geom SET STATISTICS 1000;

ANALYZE maps_texturasolo;


CLUSTER maps_usoocupacaosolo USING idx_maps_usoocupacaosolo_geom;

dump.md

dump:

pg_dump -h localhost -p 5432 -U postgres -d sigitr \
  -t maps_texturasolo \
  -Fc \
  -f maps_texturasolo.dump

restore

pv maps_texturasolo.dump | \
/usr/lib/postgresql/17/bin/pg_restore -t maps_texturasolo -Fc -f - | \
sed '/SET transaction_timeout = 0;/d' | \
PGPASSWORD=<pass> psql \
  --host=<host> \
  --port=5432 \
  --username=<user> \
  --dbname=<db_name>

load_raster.sql

-- ============================================================
-- PIPELINE: Raster -> Vetor otimizado por grade espacial
-- Tabela destino: textura_solo_grade
-- Autor: Marcello Benigno
-- ============================================================

-- ============================================================
-- CONFIGURAÇÕES DE SESSÃO
-- ============================================================
SET work_mem = '1GB';
SET maintenance_work_mem = '2GB';
SET max_parallel_workers_per_gather = 2;
SET enable_hashagg = on;

-- ============================================================
-- 1. CRIAÇÃO DA TABELA
-- ============================================================
DROP TABLE IF EXISTS textura_solo_grade;

CREATE TABLE textura_solo_grade (
    id       serial PRIMARY KEY,
    grade_id integer NOT NULL,
    classe   integer NOT NULL,
    geom     geometry(MultiPolygon, 4326)
);

-- ============================================================
-- 2. PROCEDURE COM BATCH + COMMIT POR CÉLULA DA GRADE
-- ============================================================
CREATE OR REPLACE PROCEDURE processar_textura_solo_grade(
    p_batch_size integer DEFAULT 50
)
LANGUAGE plpgsql AS $$
DECLARE
    v_grade_id  integer;
    v_count     integer := 0;
    v_inserted  integer := 0;
    v_total     integer;
BEGIN
    SELECT count(*) INTO v_total FROM maps_grade10000;
    RAISE NOTICE 'Total de células na grade: %', v_total;

    FOR v_grade_id IN
        SELECT id FROM maps_grade10000 ORDER BY id
    LOOP

        INSERT INTO textura_solo_grade (grade_id, classe, geom)

        WITH
        tiles_da_celula AS (
            SELECT
                r.rid,
                r.rast,
                g.geom AS grade_geom
            FROM rst_textura_solo r
            JOIN maps_grade10000 g
                ON g.id = v_grade_id
               AND ST_Intersects(r.rast::geometry, g.geom)
        ),

        poligonos AS (
            SELECT
                (dp).val::integer AS classe,
                (dp).geom         AS geom
            FROM tiles_da_celula t
            CROSS JOIN LATERAL
                ST_DumpAsPolygons(
                    ST_Clip(t.rast, t.grade_geom, 0, true)
                ) AS dp
            WHERE (dp).val <> 0
        ),

        dissolve AS (
            SELECT
                classe,
                ST_Multi(
                    ST_Union(geom)
                ) AS geom
            FROM poligonos
            GROUP BY classe
        )

        SELECT
            v_grade_id,
            classe,
            ST_Multi(ST_MakeValid(geom))
        FROM dissolve
        WHERE geom IS NOT NULL
          AND NOT ST_IsEmpty(geom);

        v_count := v_count + 1;

        IF mod(v_count, p_batch_size) = 0 THEN
            COMMIT;
            RAISE NOTICE '[%/%] células processadas',
                v_count, v_total;
        END IF;

    END LOOP;

    COMMIT;

    RAISE NOTICE 'Concluído! % células processadas.', v_count;

END;
$$;

-- ============================================================
-- 3. EXECUÇÃO
-- ============================================================
CALL processar_textura_solo_grade(p_batch_size := 50);

-- ============================================================
-- 4. ÍNDICES
-- ============================================================
CREATE INDEX textura_solo_grade_geom_idx
ON textura_solo_grade
USING GIST (geom);

CREATE INDEX textura_solo_grade_grade_idx
ON textura_solo_grade (grade_id);

CREATE INDEX textura_solo_grade_classe_idx
ON textura_solo_grade (classe);

CREATE INDEX textura_solo_grade_grade_classe_idx
ON textura_solo_grade (grade_id, classe);

CREATE INDEX textura_solo_grade_grade_classe_geom_idx
ON textura_solo_grade (grade_id, classe) INCLUDE (geom);

ANALYZE textura_solo_grade;

-- ============================================================
-- 5. VALIDAÇÃO DO RESULTADO
-- ============================================================
SELECT
    count(*)                           AS total_registros,
    count(DISTINCT grade_id)           AS celulas_com_dados,
    count(DISTINCT classe)             AS classes_distintas,
    sum(ST_Area(geom::geography)/1e6)  AS area_total_km2,
    sum(CASE WHEN NOT ST_IsValid(geom) THEN 1 ELSE 0 END) AS geoms_invalidas
FROM textura_solo_grade;

script_versao2.sql

-- ============================================================
  -- PIPELINE: Raster -> Vetor otimizado por grade espacial
  -- Tabela destino: textura_solo_grade
  -- ============================================================

  SET work_mem                     = '512MB';
  SET maintenance_work_mem         = '2GB';
  SET max_parallel_workers_per_gather = 4;
  SET enable_hashagg               = on;
  SET jit                          = off;   -- JIT prejudica loops curtos

  -- ============================================================
  -- 1. TABELA DESTINO
  -- ============================================================

  DROP TABLE IF EXISTS textura_solo_grade;

  CREATE UNLOGGED TABLE textura_solo_grade (   -- UNLOGGED: ~2x mais rápido na carga
      id       serial PRIMARY KEY,
      grade_id integer NOT NULL,
      classe   smallint NOT NULL,              -- smallint: 8BUI nunca passa de 255
      geom     geometry(MultiPolygon, 4326)
  );

  -- ============================================================
  -- 2. PROCEDURE PRINCIPAL
  -- ============================================================

  CREATE OR REPLACE PROCEDURE processar_textura_solo_grade(
      p_batch_size integer DEFAULT 50
  )
  LANGUAGE plpgsql AS $$
  DECLARE
      v_grade_id integer;
      v_count    integer := 0;
      v_total    integer;
  BEGIN
      SELECT count(*) INTO v_total FROM maps_grade10000;
      RAISE NOTICE 'Total de células na grade: %', v_total;

      FOR v_grade_id IN
          SELECT id FROM maps_grade10000 ORDER BY id
      LOOP

          INSERT INTO textura_solo_grade (grade_id, classe, geom)

          WITH

          -- 1. TILES QUE INTERSECTAM A CÉLULA
          --    && usa o índice GiST do raster (operador bbox, O(log n))
          --    ST_Intersects refina com geometria real
          tiles AS (
              SELECT
                  r.rast,
                  g.geom AS grade_geom
              FROM rst_uso_ocupacao_solo r
              JOIN maps_grade10000 g
                ON g.id = v_grade_id
               AND r.rast && g.geom
               AND ST_Intersects(r.rast::geometry, g.geom)
          ),

          -- 2. CLIP + VETORIZAÇÃO em uma passagem só
          --    Evita materializar o raster clippado desnecessariamente
          poligonos AS (
              SELECT
                  (dp).val::smallint AS classe,
                  (dp).geom          AS geom
              FROM tiles t
              CROSS JOIN LATERAL
                  ST_DumpAsPolygons(
                      ST_Clip(t.rast, t.grade_geom, 0, true)
                  ) dp
              WHERE (dp).val <> 0
                AND (dp).geom IS NOT NULL
                AND NOT ST_IsEmpty((dp).geom)
          ),

          -- 3. DISSOLVE POR CLASSE
          --    ST_UnaryUnion: opera em coleção, não em agregação linha a linha
          --    ~3x mais rápido que ST_Union para muitos polígonos pequenos
          dissolvido AS (
              SELECT
                  classe,
                  ST_UnaryUnion(
                      ST_Collect(geom)       -- coleta primeiro, une de uma vez
                  ) AS geom
              FROM poligonos
              GROUP BY classe
          ),

          -- 4. VALIDAÇÃO + SUBDIVISÃO
          --    ST_MakeValid: evita falhas em geometrias degeneradas pós-clip
          --    ST_Subdivide(256): chunks menores → queries com ST_Intersects mais rápidas
          --    ST_Multi: garante MultiPolygon mesmo após subdivisão
          final AS (
              SELECT
                  classe,
                  ST_Multi(
                      ST_Subdivide(
                          ST_MakeValid(geom),
                          256               -- ← ajuste conforme resolução do raster
                      )
                  ) AS geom
              FROM dissolvido
              WHERE geom IS NOT NULL
                AND NOT ST_IsEmpty(geom)
          )

          SELECT v_grade_id, classe, geom
          FROM final
          WHERE geom IS NOT NULL
            AND NOT ST_IsEmpty(geom);

          v_count := v_count + 1;

          IF mod(v_count, p_batch_size) = 0 THEN
              COMMIT;
              RAISE NOTICE '[%/%] células processadas', v_count, v_total;
          END IF;

      END LOOP;

      COMMIT;
      RAISE NOTICE 'Concluído: % células processadas.', v_count;
  END;
  $$;

  -- ============================================================
  -- 3. EXECUÇÃO
  -- ============================================================

  CALL processar_textura_solo_grade(50);

  -- ============================================================
  -- 4. CONVERTER PARA LOGGED APÓS CARGA
  -- ============================================================

  ALTER TABLE textura_solo_grade SET LOGGED;

  -- ============================================================
  -- 5. ÍNDICES (após carga: muito mais rápido que durante)
  -- ============================================================

  -- Índice espacial principal
  CREATE INDEX textura_solo_grade_geom_idx
      ON textura_solo_grade USING GIST (geom);

  -- Filtros por grade e classe
  CREATE INDEX textura_solo_grade_grade_idx
      ON textura_solo_grade (grade_id);

  CREATE INDEX textura_solo_grade_grade_classe_idx
      ON textura_solo_grade (grade_id, classe);

  -- Covering index: evita heap fetch em queries grade+classe+geom
  CREATE INDEX textura_solo_grade_covering_idx
      ON textura_solo_grade (grade_id, classe)
      INCLUDE (geom);

  ANALYZE textura_solo_grade;

  -- ============================================================
  -- 6. VALIDAÇÃO
  -- ============================================================

  SELECT
      count(*)                                              AS total_registros,
      count(DISTINCT grade_id)                              AS celulas_com_dados,
      count(DISTINCT classe)                                AS classes_distintas,
      round(sum(ST_Area(geom::geography) / 1e6)::numeric, 2) AS area_total_km2,
      sum(CASE WHEN NOT ST_IsValid(geom) THEN 1 ELSE 0 END) AS geoms_invalidas
  FROM textura_solo_grade;

  ---

tratamento.md

1. Limpeza do arquiv raster

gdal_sieve.py \
-st 4 \
-8 \
classes_textuais.tif \
classes_textuais_clean_tmp.tif


gdal_translate \
classes_textuais_clean_tmp.tif \
classes_textuais_clean.tif \
-co COMPRESS=DEFLATE \
-co ZLEVEL=9 \
-co PREDICTOR=2 \
-co TILED=YES

rm classes_textuais_clean_tmp.tif

2. Importação do arquivo raster para o banco:

raster2pgsql \
-s 4326 \
-I \
-C \
-M \
-t 512x512 \
classes_textuais_clean.tif \
public.rst_textura_solo \
| psql -d sigitr