Last active
May 3, 2021 16:35
-
-
Save tomasoak/77cd1386d2662dc793c0b3019cb76a42 to your computer and use it in GitHub Desktop.
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
---================================================================================================-- | |
-- 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