Skip to content

Instantly share code, notes, and snippets.

@tomasoak

tomasoak/SIMEX_RO_Ranking.sql

Last active May 3, 2021 16:35
Show Gist options
  • Save tomasoak/77cd1386d2662dc793c0b3019cb76a42 to your computer and use it in GitHub Desktop.
Save tomasoak/77cd1386d2662dc793c0b3019cb76a42 to your computer and use it in GitHub Desktop.
Download ZIP
Script de rankeamento de Áreas de Exploração Madeireira desenvolvido para o projeto SIMEX no Estado de Rondônia no período de Agosto de 2019 a Julho de 2020
Raw
SIMEX_RO_Ranking.sql
---================================================================================================--
-- SIMEX - Ranking
-- Rondônia
-- Agosto 2019 - Julho 2020
--
-- Author: Tomás Carvalho
-- Data: Abr/2021
---================================================================================================--
--== Autex agrupada por Número de Série Data: > 2019-08-01 ==--
CREATE MATERIALIZED VIEW ro.mv_autorizao_exploracao_dof_ibama_group AS
SELECT ROW_NUMBER () OVER () AS id,
nome_razao_social_detentor, num_serie_autex,
SUM(area_ha) AS area_ha,
array_agg(nome_cientifico) AS nome_cientifico,
array_agg(nome_popular) AS nome_popular,
SUM(volume_original_autorizado) AS volume_original_autorizado,
SUM(volume_remanescente) AS volume_remanescente, ST_Union(geom) AS geom
FROM ro.autorizacao_exploracao_dof_ibama
WHERE data_validade_autex > '2019-08-01'
GROUP BY nome_razao_social_detentor, cpf_cnpj_detentor, ctf_detentor, nome_origem, sigla_uf, nm_mun, num_serie_autex;
CREATE UNIQUE INDEX ON ro.mv_autorizao_exploracao_dof_ibama_group (id);
COMMENT ON MATERIALIZED VIEW ro.mv_autorizao_exploracao_dof_ibama_group
IS 'Autex agrupada por Número de Série
Data: > 2019-08-01';
----==== Rank A ====-----
-- Autex dentro AE
-- AE dentro AMF
-- 99% área AMF dentro imóvel
WITH rank_a_prep AS (
SELECT a.id AS id_ea,
COUNT(CASE WHEN ST_Within(g.geom, a.geom) THEN -- Autex X AE
CASE WHEN ST_Within(a.geom, b.geom) OR ST_Within(a.geom, f.geom) THEN -- AE X AMF ou AE X UMF
CASE WHEN ST_Area(ST_Transform(ST_Intersection(b.geom, c.geom),97823)) >= (0.99 * ST_Area(ST_Transform(b.geom,97823))) -- 99% AMF x SIGEF
OR ST_Area(ST_Transform(ST_Intersection(b.geom, d.geom),97823)) >= (0.99 * ST_Area(ST_Transform(b.geom,97823))) -- 99% AMF x CAR
OR ST_Area(ST_Transform(ST_Intersection(b.geom, e.geom),97823)) >= (0.99 * ST_Area(ST_Transform(b.geom,97823))) -- 99% AMF x ATI
OR ST_Area(ST_Transform(ST_Intersection(f.geom, c.geom),97823)) >= (0.99 * ST_Area(ST_Transform(f.geom,97823))) -- 99% UMF x SIGEF
OR ST_Area(ST_Transform(ST_Intersection(f.geom, d.geom),97823)) >= (0.99 * ST_Area(ST_Transform(f.geom,97823))) -- 99% UMF x CAR
OR ST_Area(ST_Transform(ST_Intersection(f.geom, e.geom),97823)) >= (0.99 * ST_Area(ST_Transform(f.geom,97823))) -- 99% UMF x ATI
THEN 1 ELSE NULL END
END
END) AS rank_a_count
FROM ro.simex_exploracao_madeireira_2019_2020 a
LEFT JOIN ro.amf_base_semas b ON ST_Intersects(a.geom, b.geom)
LEFT JOIN ro.sigef_privado_incra c ON ST_Intersects(a.geom, c.geom)
LEFT JOIN ro.car_areaimovel_original_20210122 d ON ST_Intersects(a.geom, d.geom)
LEFT JOIN ro.ati_base_semas e ON ST_Intersects(a.geom, e.geom)
LEFT JOIN ro.limite_umf f ON ST_Intersects(a.geom, f.geom)
JOIN ro.mv_autorizao_exploracao_dof_ibama_group g ON ST_Intersects(g.geom, a.geom)
GROUP BY a.id
ORDER BY rank_a_count DESC
)
SELECT id_ea, rank_a_count
FROM rank_a_prep
WHERE rank_a_count <> 0
----==== Rank B ====-----
-- Autex dentro AMF
-- 90% área AMF dentro imóvel
-- 50% área AE dentro AMF
WITH rank_b_prep AS (
SELECT a.id AS id_ea,
COUNT(CASE WHEN ST_Within(g.geom, b.geom) OR ST_Within(g.geom, f.geom) THEN -- Autex X AMF
CASE WHEN ST_Area(ST_Transform(ST_Intersection(b.geom, c.geom),97823)) >= (0.90 * ST_Area(ST_Transform(b.geom,97823))) -- 90% AMF x SIGEF
OR ST_Area(ST_Transform(ST_Intersection(b.geom, d.geom),97823)) >= (0.90 * ST_Area(ST_Transform(b.geom,97823))) -- 90% AMF x CAR
OR ST_Area(ST_Transform(ST_Intersection(b.geom, e.geom),97823)) >= (0.90 * ST_Area(ST_Transform(b.geom,97823))) -- 90% AMF x ATI
OR ST_Area(ST_Transform(ST_Intersection(f.geom, c.geom),97823)) >= (0.90 * ST_Area(ST_Transform(f.geom,97823))) -- 90% UMF x SIGEF
OR ST_Area(ST_Transform(ST_Intersection(f.geom, d.geom),97823)) >= (0.90 * ST_Area(ST_Transform(f.geom,97823))) -- 90% UMF x CAR
OR ST_Area(ST_Transform(ST_Intersection(f.geom, e.geom),97823)) >= (0.90 * ST_Area(ST_Transform(f.geom,97823))) THEN -- 90% UMF x ATI
CASE WHEN ST_Area(ST_Transform(ST_Intersection(a.geom, b.geom),97823)) >= (0.50 * ST_Area(ST_Transform(a.geom,97823))) -- 50% AE x AMF
OR ST_Area(ST_Transform(ST_Intersection(a.geom, f.geom),97823)) >= (0.50 * ST_Area(ST_Transform(a.geom,97823))) -- 50% AE x UMF
THEN 1 ELSE NULL END
END
END) AS rank_b_count
FROM ro.simex_exploracao_madeireira_2019_2020 a
LEFT JOIN ro.amf_base_semas b ON ST_Intersects(a.geom, b.geom)
LEFT JOIN ro.sigef_privado_incra c ON ST_Intersects(a.geom, c.geom)
LEFT JOIN ro.car_areaimovel_original_20210122 d ON ST_Intersects(a.geom, d.geom)
LEFT JOIN ro.ati_base_semas e ON ST_Intersects(a.geom, e.geom)
LEFT JOIN ro.limite_umf f ON ST_Intersects(a.geom, f.geom)
JOIN ro.mv_autorizao_exploracao_dof_ibama_group g ON ST_Within(g.geom, b.geom) OR ST_Within(g.geom, f.geom)
GROUP BY a.id
ORDER BY rank_b_count DESC
)
SELECT id_ea, rank_b_count
FROM rank_b_prep
WHERE rank_b_count <> 0
----==== Rank C ====-----
-- Autex dentro AMF ou imóvel
-- 90% área AMF dentro imóvel
-- 5% área imóvel dentro AMF
WITH rank_c_prep AS (
SELECT a.id AS id_ea,
COUNT(CASE WHEN ST_Within(g.geom, b.geom) OR ST_Within(g.geom, c.geom) OR ST_Within(g.geom, d.geom) OR ST_Within(g.geom, e.geom)
OR ST_Within(g.geom, f.geom) THEN
CASE WHEN ST_Area(ST_Transform(ST_Intersection(b.geom, c.geom),97823)) >= (0.90 * ST_Area(ST_Transform(b.geom,97823))) -- 80% AMF x SIGEF
OR ST_Area(ST_Transform(ST_Intersection(b.geom, d.geom),97823)) >= (0.90 * ST_Area(ST_Transform(b.geom,97823))) -- 80% AMF x CAR
OR ST_Area(ST_Transform(ST_Intersection(b.geom, e.geom),97823)) >= (0.90 * ST_Area(ST_Transform(b.geom,97823))) -- 80% AMF x ATI
OR ST_Area(ST_Transform(ST_Intersection(f.geom, c.geom),97823)) >= (0.90 * ST_Area(ST_Transform(f.geom,97823))) -- 80% UMF x SIGEF
OR ST_Area(ST_Transform(ST_Intersection(f.geom, d.geom),97823)) >= (0.90 * ST_Area(ST_Transform(f.geom,97823))) -- 80% UMF x CAR
OR ST_Area(ST_Transform(ST_Intersection(f.geom, e.geom),97823)) >= (0.90 * ST_Area(ST_Transform(f.geom,97823))) -- 80% UMF x ATI
THEN
CASE WHEN ST_Area(ST_Transform(ST_Intersection(a.geom, b.geom),97823)) >= (0.05 * ST_Area(ST_Transform(a.geom,97823))) -- 5% AE x AMF
OR ST_Area(ST_Transform(ST_Intersection(a.geom, f.geom),97823)) >= (0.05 * ST_Area(ST_Transform(a.geom,97823))) -- 5% AE x UMF
THEN 1 ELSE NULL END
END
END) AS rank_c_count
FROM ro.simex_exploracao_madeireira_2019_2020 a
LEFT JOIN ro.amf_base_semas b ON ST_Intersects(a.geom, b.geom)
LEFT JOIN ro.sigef_privado_incra c ON ST_Intersects(a.geom, c.geom)
LEFT JOIN ro.car_areaimovel_original_20210122 d ON ST_Intersects(a.geom, d.geom)
LEFT JOIN ro.ati_base_semas e ON ST_Intersects(a.geom, e.geom)
LEFT JOIN ro.limite_umf f ON ST_Intersects(a.geom, f.geom)
JOIN ro.mv_autorizao_exploracao_dof_ibama_group g ON ST_Within(g.geom, b.geom) OR ST_Within(g.geom, c.geom)
OR ST_Within(g.geom, d.geom) OR ST_Within(g.geom, e.geom)
GROUP BY a.id
ORDER BY rank_c_count DESC
)
SELECT id_ea, rank_c_count
FROM rank_c_prep
WHERE rank_c_count <> 0
----==== Rank D ====-----
-- Autex dentro imóvel
-- AE dentro imóvel
SELECT a.id AS id_ea,
COUNT(CASE WHEN ST_Within(g.geom, c.geom) OR ST_Within(g.geom, d.geom) OR ST_Within(g.geom, e.geom) THEN
CASE WHEN ST_Within(a.geom, c.geom) OR ST_Within(a.geom, d.geom) OR ST_Within(a.geom, e.geom)
THEN 1 ELSE NULL END
END) AS rank_d_count
FROM ro.simex_exploracao_madeireira_2019_2020 a
LEFT JOIN ro.sigef_privado_incra c ON ST_Within(a.geom, c.geom)
LEFT JOIN ro.car_areaimovel_original_20210122 d ON ST_Within(a.geom, d.geom)
LEFT JOIN ro.ati_base_semas e ON ST_Within(a.geom, e.geom)
JOIN ro.mv_autorizao_exploracao_dof_ibama_group g ON ST_Within(g.geom, c.geom)
OR ST_Within(g.geom, d.geom) OR ST_Within(g.geom, e.geom)
GROUP BY a.id
----==== Rank E ====----
-- Autex dentro imóvel
-- 50% AE dentro imóvel
SELECT a.id AS id_ea,
COUNT(CASE WHEN ST_Within(g.geom, c.geom) OR ST_Within(g.geom, d.geom) OR ST_Within(g.geom, e.geom) THEN
CASE WHEN ST_Area(ST_Transform(ST_Intersection(a.geom, c.geom),97823)) >= (0.50 * ST_Area(ST_Transform(a.geom,97823))) -- 50% AE x SIGEF
OR ST_Area(ST_Transform(ST_Intersection(a.geom, d.geom),97823)) >= (0.50 * ST_Area(ST_Transform(a.geom,97823))) -- 50% AE x CAR
OR ST_Area(ST_Transform(ST_Intersection(a.geom, e.geom),97823)) >= (0.50 * ST_Area(ST_Transform(a.geom,97823))) -- 50% AE x ATI
THEN 1 ELSE NULL END
END) AS rank_e_count
FROM ro.simex_exploracao_madeireira_2019_2020 a
LEFT JOIN ro.sigef_privado_incra c ON ST_Intersects(a.geom, c.geom)
LEFT JOIN ro.car_areaimovel_original_20210122 d ON ST_Intersects(a.geom, d.geom)
LEFT JOIN ro.ati_base_semas e ON ST_Intersects(a.geom, e.geom)
JOIN ro.mv_autorizao_exploracao_dof_ibama_group g ON ST_Within(g.geom, c.geom)
OR ST_Within(g.geom, d.geom) OR ST_Within(g.geom, e.geom)
GROUP BY a.id
----==== Create Materialized View with SIMEX Ranking based on spatial relationships ====----
CREATE MATERIALIZED VIEW ro.mv_simex_ranking AS
WITH rank_a_prep AS (
SELECT a.id AS id_ea,
COUNT(CASE WHEN ST_Within(g.geom, a.geom) THEN -- Autex X AE
CASE WHEN ST_Within(a.geom, b.geom) OR ST_Within(a.geom, f.geom) THEN -- AE X AMF ou AE X UMF
CASE WHEN ST_Area(ST_Transform(ST_Intersection(b.geom, c.geom),97823)) >= (0.99 * ST_Area(ST_Transform(b.geom,97823))) -- 99% AMF x SIGEF
OR ST_Area(ST_Transform(ST_Intersection(b.geom, d.geom),97823)) >= (0.99 * ST_Area(ST_Transform(b.geom,97823))) -- 99% AMF x CAR
OR ST_Area(ST_Transform(ST_Intersection(b.geom, e.geom),97823)) >= (0.99 * ST_Area(ST_Transform(b.geom,97823))) -- 99% AMF x ATI
OR ST_Area(ST_Transform(ST_Intersection(f.geom, c.geom),97823)) >= (0.99 * ST_Area(ST_Transform(f.geom,97823))) -- 99% UMF x SIGEF
OR ST_Area(ST_Transform(ST_Intersection(f.geom, d.geom),97823)) >= (0.99 * ST_Area(ST_Transform(f.geom,97823))) -- 99% UMF x CAR
OR ST_Area(ST_Transform(ST_Intersection(f.geom, e.geom),97823)) >= (0.99 * ST_Area(ST_Transform(f.geom,97823))) -- 99% UMF x ATI
THEN 1 ELSE NULL END
END
END) AS rank_a_count
FROM ro.simex_exploracao_madeireira_2019_2020 a
LEFT JOIN ro.amf_base_semas b ON ST_Intersects(a.geom, b.geom)
LEFT JOIN ro.sigef_privado_incra c ON ST_Intersects(a.geom, c.geom)
LEFT JOIN ro.car_areaimovel_original_20210122 d ON ST_Intersects(a.geom, d.geom)
LEFT JOIN ro.ati_base_semas e ON ST_Intersects(a.geom, e.geom)
LEFT JOIN ro.limite_umf f ON ST_Intersects(a.geom, f.geom)
JOIN ro.mv_autorizao_exploracao_dof_ibama_group g ON ST_Intersects(g.geom, a.geom)
GROUP BY a.id
ORDER BY rank_a_count DESC
), rank_a AS (
SELECT id_ea, rank_a_count
FROM rank_a_prep
WHERE rank_a_count <> 0
), rank_b_prep AS (
SELECT a.id AS id_ea,
COUNT(CASE WHEN ST_Within(g.geom, b.geom) OR ST_Within(g.geom, f.geom) THEN -- Autex X AMF
CASE WHEN ST_Area(ST_Transform(ST_Intersection(b.geom, c.geom),97823)) >= (0.90 * ST_Area(ST_Transform(b.geom,97823))) -- 90% AMF x SIGEF
OR ST_Area(ST_Transform(ST_Intersection(b.geom, d.geom),97823)) >= (0.90 * ST_Area(ST_Transform(b.geom,97823))) -- 90% AMF x CAR
OR ST_Area(ST_Transform(ST_Intersection(b.geom, e.geom),97823)) >= (0.90 * ST_Area(ST_Transform(b.geom,97823))) -- 90% AMF x ATI
OR ST_Area(ST_Transform(ST_Intersection(f.geom, c.geom),97823)) >= (0.90 * ST_Area(ST_Transform(f.geom,97823))) -- 90% UMF x SIGEF
OR ST_Area(ST_Transform(ST_Intersection(f.geom, d.geom),97823)) >= (0.90 * ST_Area(ST_Transform(f.geom,97823))) -- 90% UMF x CAR
OR ST_Area(ST_Transform(ST_Intersection(f.geom, e.geom),97823)) >= (0.90 * ST_Area(ST_Transform(f.geom,97823))) THEN -- 90% UMF x ATI
CASE WHEN ST_Area(ST_Transform(ST_Intersection(a.geom, b.geom),97823)) >= (0.50 * ST_Area(ST_Transform(a.geom,97823))) -- 50% AE x AMF
OR ST_Area(ST_Transform(ST_Intersection(a.geom, f.geom),97823)) >= (0.50 * ST_Area(ST_Transform(a.geom,97823))) -- 50% AE x UMF
THEN 1 ELSE NULL END
END
END) AS rank_b_count
FROM ro.simex_exploracao_madeireira_2019_2020 a
LEFT JOIN ro.amf_base_semas b ON ST_Intersects(a.geom, b.geom)
LEFT JOIN ro.sigef_privado_incra c ON ST_Intersects(a.geom, c.geom)
LEFT JOIN ro.car_areaimovel_original_20210122 d ON ST_Intersects(a.geom, d.geom)
LEFT JOIN ro.ati_base_semas e ON ST_Intersects(a.geom, e.geom)
LEFT JOIN ro.limite_umf f ON ST_Intersects(a.geom, f.geom)
JOIN ro.mv_autorizao_exploracao_dof_ibama_group g ON ST_Within(g.geom, b.geom) OR ST_Within(g.geom, f.geom)
GROUP BY a.id
ORDER BY rank_b_count DESC
), rank_b AS (
SELECT id_ea, rank_b_count
FROM rank_b_prep
WHERE rank_b_count <> 0
), rank_c_prep AS (
SELECT a.id AS id_ea,
COUNT(CASE WHEN ST_Within(g.geom, b.geom) OR ST_Within(g.geom, c.geom) OR ST_Within(g.geom, d.geom) OR ST_Within(g.geom, e.geom)
OR ST_Within(g.geom, f.geom) THEN
CASE WHEN ST_Area(ST_Transform(ST_Intersection(b.geom, c.geom),97823)) >= (0.90 * ST_Area(ST_Transform(b.geom,97823))) -- 90% AMF x SIGEF
OR ST_Area(ST_Transform(ST_Intersection(b.geom, d.geom),97823)) >= (0.90 * ST_Area(ST_Transform(b.geom,97823))) -- 90% AMF x CAR
OR ST_Area(ST_Transform(ST_Intersection(b.geom, e.geom),97823)) >= (0.90 * ST_Area(ST_Transform(b.geom,97823))) -- 90% AMF x ATI
OR ST_Area(ST_Transform(ST_Intersection(f.geom, c.geom),97823)) >= (0.90 * ST_Area(ST_Transform(f.geom,97823))) -- 90% UMF x SIGEF
OR ST_Area(ST_Transform(ST_Intersection(f.geom, d.geom),97823)) >= (0.90 * ST_Area(ST_Transform(f.geom,97823))) -- 90% UMF x CAR
OR ST_Area(ST_Transform(ST_Intersection(f.geom, e.geom),97823)) >= (0.90 * ST_Area(ST_Transform(f.geom,97823))) -- 90% UMF x ATI
THEN
CASE WHEN ST_Area(ST_Transform(ST_Intersection(a.geom, b.geom),97823)) >= (0.05 * ST_Area(ST_Transform(a.geom,97823))) -- 5% AE x AMF
OR ST_Area(ST_Transform(ST_Intersection(a.geom, f.geom),97823)) >= (0.05 * ST_Area(ST_Transform(a.geom,97823))) -- 5% AE x UMF
THEN 1 ELSE NULL END
END
END) AS rank_c_count
FROM ro.simex_exploracao_madeireira_2019_2020 a
LEFT JOIN ro.amf_base_semas b ON ST_Intersects(a.geom, b.geom)
LEFT JOIN ro.sigef_privado_incra c ON ST_Intersects(a.geom, c.geom)
LEFT JOIN ro.car_areaimovel_original_20210122 d ON ST_Intersects(a.geom, d.geom)
LEFT JOIN ro.ati_base_semas e ON ST_Intersects(a.geom, e.geom)
LEFT JOIN ro.limite_umf f ON ST_Intersects(a.geom, f.geom)
JOIN ro.mv_autorizao_exploracao_dof_ibama_group g ON ST_Within(g.geom, b.geom) OR ST_Within(g.geom, c.geom)
OR ST_Within(g.geom, d.geom) OR ST_Within(g.geom, e.geom)
GROUP BY a.id
ORDER BY rank_c_count DESC
), rank_c AS (
SELECT id_ea, rank_c_count
FROM rank_c_prep
WHERE rank_c_count <> 0
), rank_d AS (
SELECT a.id AS id_ea,
COUNT(CASE WHEN ST_Within(g.geom, c.geom) OR ST_Within(g.geom, d.geom) OR ST_Within(g.geom, e.geom) THEN
CASE WHEN ST_Within(a.geom, c.geom) OR ST_Within(a.geom, d.geom) OR ST_Within(a.geom, e.geom)
THEN 1 ELSE NULL END
END) AS rank_d_count
FROM ro.simex_exploracao_madeireira_2019_2020 a
LEFT JOIN ro.sigef_privado_incra c ON ST_Within(a.geom, c.geom)
LEFT JOIN ro.car_areaimovel_original_20210122 d ON ST_Within(a.geom, d.geom)
LEFT JOIN ro.ati_base_semas e ON ST_Within(a.geom, e.geom)
JOIN ro.mv_autorizao_exploracao_dof_ibama_group g ON ST_Within(g.geom, c.geom)
OR ST_Within(g.geom, d.geom) OR ST_Within(g.geom, e.geom)
GROUP BY a.id
), rank_e AS (
SELECT a.id AS id_ea,
COUNT(CASE WHEN ST_Within(g.geom, c.geom) OR ST_Within(g.geom, d.geom) OR ST_Within(g.geom, e.geom) THEN
CASE WHEN ST_Area(ST_Transform(ST_Intersection(a.geom, c.geom),97823)) >= (0.50 * ST_Area(ST_Transform(a.geom,97823))) -- 50% AE x SIGEF
OR ST_Area(ST_Transform(ST_Intersection(a.geom, d.geom),97823)) >= (0.50 * ST_Area(ST_Transform(a.geom,97823))) -- 50% AE x CAR
OR ST_Area(ST_Transform(ST_Intersection(a.geom, e.geom),97823)) >= (0.50 * ST_Area(ST_Transform(a.geom,97823))) -- 50% AE x ATI
THEN 1 ELSE NULL END
END) AS rank_e_count
FROM ro.simex_exploracao_madeireira_2019_2020 a
LEFT JOIN ro.sigef_privado_incra c ON ST_Intersects(a.geom, c.geom)
LEFT JOIN ro.car_areaimovel_original_20210122 d ON ST_Intersects(a.geom, d.geom)
LEFT JOIN ro.ati_base_semas e ON ST_Intersects(a.geom, e.geom)
JOIN ro.mv_autorizao_exploracao_dof_ibama_group g ON ST_Within(g.geom, c.geom)
OR ST_Within(g.geom, d.geom) OR ST_Within(g.geom, e.geom)
GROUP BY a.id
),union_rank AS (
SELECT 'A'::TEXT AS rannk, id_ea
FROM rank_a
UNION
SELECT 'B'::TEXT AS rannk, id_ea
FROM rank_b
WHERE id_ea NOT IN (SELECT id_ea FROM rank_a)
UNION
SELECT 'C'::TEXT AS rannk, id_ea
FROM rank_c
WHERE id_ea NOT IN (SELECT id_ea FROM rank_a) AND id_ea NOT IN (SELECT id_ea FROM rank_b)
UNION
SELECT 'D'::TEXT AS rannk, id_ea
FROM rank_d
WHERE id_ea NOT IN (SELECT id_ea FROM rank_a) AND id_ea NOT IN (SELECT id_ea FROM rank_b) AND id_ea NOT IN (SELECT id_ea FROM rank_c)
UNION
SELECT 'E'::TEXT AS rannk, id_ea
FROM rank_e
WHERE id_ea NOT IN (SELECT id_ea FROM rank_a) AND id_ea NOT IN (SELECT id_ea FROM rank_b)
AND id_ea NOT IN (SELECT id_ea FROM rank_c) AND id_ea NOT IN (SELECT id_ea FROM rank_d)
ORDER BY rannk, id_ea
)
SELECT ROW_NUMBER() OVER() AS id, rannk, a.id_ea, area_ha
FROM union_rank a
JOIN ro.simex_exploracao_madeireira_2019_2020 b ON b.id = a.id_ea;
CREATE UNIQUE INDEX ON ro.mv_simex_ranking (id);
COMMENT ON MATERIALIZED VIEW ro.mv_simex_ranking IS
'Resumo Ranking por id de Área de Exploração Madeireira,
cruzamentos com Autex, Imóveis e AMF';
----==== Create Summary View per Rank Class ====----
CREATE VIEW ro.v_simex_ranking_summary AS
SELECT rannk, COUNT(id_ea) AS ea_count, ROUND(SUM(area_ha)::NUMERIC,2) AS ea_area_ha
FROM ro.mv_simex_ranking
GROUP BY rannk
ORDER BY rannk;
COMMENT ON VIEW ro.v_simex_ranking_summary IS 'Resumo Ranking';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment