Script desenvolvido para o projeto SIMEX no Estado de Rondônia no período de Agosto de 2019 a Julho de 2020

SIMEX_RO_Ago19_Jul20.sql

---================================================================================================--
--			                         SIMEX 
--			                        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 ==--
--DROP MATERIALIZED VIEW ro.mv_autorizao_exploracao_dof_ibama_group CASCADE;
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';

--== Overlap Área Exploração e Autex, utilizando limite imóveis ==--
--DROP MATERIALIZED VIEW ro.mv_simex_exploracao_autorizacao_overlap CASCADE;
CREATE MATERIALIZED VIEW ro.mv_simex_exploracao_autorizacao_overlap AS
 SELECT row_number() OVER () AS id,
    a.id as id_explor, 
    a.area_ha AS explo_area_ha,
    e.id AS id_autex, e.nome_razao_social_detentor , e.area_ha AS autex_area_ha, num_serie_autex,
    CASE WHEN d.codigo_imo IS NOT NULL THEN d.codigo_imo 
    WHEN b.cod_imovel IS NOT NULL THEN b.cod_imovel
    ELSE c.imovel || ' ' || '-' || ' ' || c.gleba_sub || ' ' || '-' || ' ' || c.lote END AS cod_imovel,
    volume_original_autorizado, volume_remanescente,
    volume_original_autorizado - volume_remanescente AS volume_explorado,
    CASE WHEN ST_Intersects(a.geom, ST_Transform(b.geom, 4326)) THEN TRUE ELSE FALSE END AS explo_car,
    CASE WHEN ST_Intersects(a.geom, ST_Transform(c.geom, 4326)) THEN TRUE ELSE FALSE END AS explo_ati,
    CASE WHEN ST_Intersects(a.geom, ST_Transform(d.geom, 4326)) THEN TRUE ELSE FALSE END AS explo_sigef,
    CASE WHEN ST_Intersects(ST_Transform(b.geom, 4326), e.geom) THEN TRUE ELSE FALSE END AS autex_car,
    CASE WHEN ST_Intersects(ST_Transform(c.geom, 4326), e.geom) THEN TRUE ELSE FALSE END AS autex_ati,
    CASE WHEN ST_Intersects(e.geom, ST_Transform(d.geom, 4326)) THEN TRUE ELSE FALSE END AS autex_sigef,
    a.geom as geom_exp
   FROM ro.simex_exploracao_madeireira_2019_2020 a
   LEFT JOIN ro.car_areaimovel_original_20210122 b ON ST_Intersects(ST_Transform(b.geom, 4326), a.geom) 
   		AND NOT ST_Touches(ST_Transform(b.geom, 4326), a.geom)
   LEFT JOIN ro.ati_base_semas c ON ST_Intersects(ST_Transform(c.geom, 4326), a.geom) 
   		AND NOT ST_Touches(ST_Transform(c.geom, 4326), a.geom)
   LEFT JOIN ro.sigef_privado_incra d ON ST_Intersects(ST_Transform(d.geom, 4326), a.geom) 
   		AND NOT ST_Touches(ST_Transform(d.geom, 4326), a.geom)
   JOIN ro.mv_autorizao_exploracao_dof_ibama_group e ON ST_Intersects(ST_Transform(b.geom, 4326), e.geom) 
   OR ST_Intersects(ST_Transform(d.geom, 4326), e.geom) OR ST_Intersects(a.geom, e.geom);
CREATE UNIQUE INDEX ON ro.mv_simex_exploracao_autorizacao_overlap (id);
COMMENT ON MATERIALIZED VIEW ro.mv_simex_exploracao_autorizacao_overlap 
IS 'Overlap Área Exploração e Autex, utilizando limite imóveis';

--== Checagem de Área de Exploração com mais de um nome_razao_social OBS: Análise mais detalhada em SIG ==--
--DROP MATERIALIZED VIEW ro.mv_simex_exploracao_autorizacao_overlap_double CASCADE;
CREATE MATERIALIZED VIEW ro.mv_simex_exploracao_autorizacao_overlap_double AS
SELECT row_number() OVER () AS id, 
	ARRAY_AGG(DISTINCT id_explor ORDER BY id_explor) AS id_explor, 
	nome_razao_social_detentor,
	ARRAY_AGG(DISTINCT num_serie_autex ORDER BY num_serie_autex) AS num_serie_autex
FROM ro.mv_simex_exploracao_autorizacao_overlap
GROUP BY nome_razao_social_detentor
HAVING array_length(ARRAY_AGG(DISTINCT id_explor),1) > 1
ORDER BY id_explor, nome_razao_social_detentor;
CREATE UNIQUE INDEX ON ro.mv_simex_exploracao_autorizacao_overlap_double (id);
COMMENT ON MATERIALIZED VIEW ro.mv_simex_exploracao_autorizacao_overlap_double 
IS 'Checagem de Área de Exploração com mais de um nome_razao_social,
OBS: Análise mais detalhada em SIG';

--== Agrupando Autex e Área de Exploração com apenas um nome_razao_social ==--
--DROP MATERIALIZED VIEW ro.mv_simex_exploracao_autorizacao_overlap_unique CASCADE;
CREATE MATERIALIZED VIEW ro.mv_simex_exploracao_autorizacao_overlap_unique AS 
SELECT DISTINCT ON (id_explor, nome_razao_social_detentor) ROW_NUMBER() OVER() AS id, id_explor, nome_razao_social_detentor, 
ARRAY_AGG(num_serie_autex ORDER BY num_serie_autex) AS num_serie_autex,
ROUND(SUM(DISTINCT autex_area_ha)::NUMERIC, 2) AS autex_area_ha,
ROUND(SUM(DISTINCT explo_area_ha)::NUMERIC, 2) AS explo_area_ha,
ROUND(volume_original_autorizado,2) AS volume_original_autorizado, 
ROUND(volume_remanescente,2) AS volume_remanescente, 
ROUND(volume_explorado,2) AS volume_explorado
FROM ro.mv_simex_exploracao_autorizacao_overlap
WHERE nome_razao_social_detentor NOT IN (SELECT nome_razao_social_detentor FROM ro.mv_simex_exploracao_autorizacao_overlap_double)
GROUP BY id_explor, nome_razao_social_detentor, volume_original_autorizado, volume_remanescente, volume_explorado
ORDER BY nome_razao_social_detentor
CREATE UNIQUE INDEX ON ro.mv_simex_exploracao_autorizacao_overlap_unique (id);
COMMENT ON MATERIALIZED VIEW ro.mv_simex_exploracao_autorizacao_overlap_unique 
IS 'Agrupando Autex e Área de Exploração com apenas um nome_razao_social';

--== Agrupando Autex e Área de Exploração pela Soma de Área de Autex ==--
--DROP MATERIALIZED VIEW ro.mv_simex_exploracao_autorizacao_overlap_group CASCADE;   
CREATE MATERIALIZED VIEW ro.mv_simex_exploracao_autorizacao_overlap_group AS
SELECT row_number() OVER () AS id, 
	id_explor, 
	nome_razao_social_detentor ,
	array_agg(DISTINCT num_serie_autex ORDER BY num_serie_autex) AS num_serie_autex,
	SUM(autex_area_ha) AS autex_area_ha,
	explo_area_ha,
	ROUND((SUM(autex_area_ha) - SUM(DISTINCT explo_area_ha))::NUMERIC,2) AS autex_menos_explorado,
	ROUND(SUM(volume_original_autorizado),2) AS volume_original_autorizado, 
	ROUND(SUM(volume_remanescente),2) AS volume_remanescente, 
	ROUND(SUM(volume_explorado),2) AS volume_explorado 
FROM ro.mv_simex_exploracao_autorizacao_overlap
GROUP BY nome_razao_social_detentor, explo_area_ha, id_explor
ORDER BY id_explor;
CREATE UNIQUE INDEX ON ro.mv_simex_exploracao_autorizacao_overlap_group (id);
COMMENT ON MATERIALIZED VIEW ro.mv_simex_exploracao_autorizacao_overlap_group 
IS 'Agrupando Autex e Área de Exploração pela Soma de Área de Autex';

--== Junção final das Autex que tiveram intersecção com as Área de Exploração ==--
--DROP MATERIALIZED VIEW ro.mv_simex_exploracao_autorizacao_overlap_final CASCADE;
CREATE MATERIALIZED VIEW ro.mv_simex_exploracao_autorizacao_overlap_final AS
WITH explo_area_double AS (
	SELECT id_explor, 
		SUM(DISTINCT CASE WHEN id_explor = 1 AND nome_razao_social_detentor = 'MADEFLONA INDUSTRIAL MADEIREIRA LTDA - FILIAL 1' THEN explo_area_ha
		WHEN id_explor IN (2,3,4,5) AND nome_razao_social_detentor = 'AMATA S.A.' THEN explo_area_ha
		WHEN id_explor = 7 AND nome_razao_social_detentor IN ('MADEFLONA INDUSTRIAL MADEIREIRA LTDA - FILIAL 2', 'MADEFLONA INDUSTRIAL MADEIREIRA LTDA') 
				THEN explo_area_ha
		WHEN id_explor = 9 AND nome_razao_social_detentor IN ('JAM', 'PLR') THEN explo_area_ha
		WHEN id_explor = 14 AND nome_razao_social_detentor = 'JFDC' THEN explo_area_ha
		WHEN id_explor = 59 AND nome_razao_social_detentor = 'PORTAL COMERCIO BENEFCIAMENTO DE MADEIRAS LTDA-EPP' THEN explo_area_ha 
		WHEN id_explor = 58 AND nome_razao_social_detentor = 'MADREX EIRELI - ME' THEN explo_area_ha 
		WHEN id_explor = 193 AND nome_razao_social_detentor = 'EDCS' THEN explo_area_ha
		END) AS explo_area_ha,
		CASE WHEN id_explor = 1 THEN 'MADEFLONA INDUSTRIAL MADEIREIRA LTDA - FILIAL 1' 
			WHEN id_explor IN (2,3,4,5) THEN 'AMATA S.A.'
			WHEN id_explor = 7 THEN 'MADEFLONA INDUSTRIAL MADEIREIRA LTDA - FILIAL 2, MADEFLONA INDUSTRIAL MADEIREIRA LTDA'
			WHEN id_explor = 9 THEN 'JAM, PLR'
			WHEN id_explor = 14 THEN 'JFDC'
			WHEN id_explor = 59 THEN 'PORTAL COMERCIO BENEFCIAMENTO DE MADEIRAS LTDA-EPP'
			WHEN id_explor = 58 THEN 'MADREX EIRELI - ME'
			WHEN id_explor = 193 THEN 'EDCS'
			ELSE nome_razao_social_detentor END AS nome_razao_social_detentor,
	num_serie_autex,
	autex_area_ha 
	FROM ro.mv_simex_exploracao_autorizacao_overlap_group
	GROUP BY id_explor, nome_razao_social_detentor, num_serie_autex, autex_area_ha
	ORDER BY id_explor
), explo_area_union AS (
	SELECT id_explor, nome_razao_social_detentor, num_serie_autex, explo_area_ha, autex_area_ha 
	FROM explo_area_double
	WHERE explo_area_ha IS NOT NULL
	UNION
	SELECT id_explor, nome_razao_social_detentor, num_serie_autex,
	SUM(explo_area_ha) AS explo_area_ha, autex_area_ha
	FROM ro.mv_simex_exploracao_autorizacao_overlap_group
	WHERE id_explor NOT IN (1,2,3,4,5,6,7,9,59,58,193,191) AND nome_razao_social_detentor NOT IN ('JAM', 'PLR')
	GROUP BY id_explor, nome_razao_social_detentor, num_serie_autex, autex_area_ha
	ORDER BY num_serie_autex 
), overlap_group_array AS (
	SELECT ARRAY_AGG(id_explor ORDER BY id_explor) AS id_explor, nome_razao_social_detentor, UNNEST(num_serie_autex) AS num_serie_autex,
	ROUND(SUM(DISTINCT autex_area_ha)::NUMERIC, 2) AS autex_area_ha,
	ROUND(SUM(DISTINCT explo_area_ha)::NUMERIC, 2) AS explo_area_ha
	FROM explo_area_union
	GROUP BY nome_razao_social_detentor, num_serie_autex
	ORDER BY id_explor
), adjust_autex_area AS (
	SELECT UNNEST(a.id_explor) AS id_explor, a.nome_razao_social_detentor, a.num_serie_autex, 
	SUM(b.area_ha) AS  autex_area_ha, SUM(b.volume_original_autorizado) AS volume_original_autorizado,
	SUM(b.volume_remanescente) AS volume_remanescente,explo_area_ha
	FROM overlap_group_array a
	JOIN ro.mv_autorizao_exploracao_dof_ibama_group b ON b.num_serie_autex = a.num_serie_autex
	GROUP BY id_explor, a.nome_razao_social_detentor, a.num_serie_autex, explo_area_ha
), pre_final AS (
	SELECT id_explor, nome_razao_social_detentor, ARRAY_AGG(DISTINCT num_serie_autex) AS num_serie_autex, 
	ROUND(SUM(autex_area_ha),2) AS autex_area_ha, explo_area_ha, ROUND(SUM(volume_original_autorizado),2) AS volume_original_autorizado,
	ROUND(SUM(volume_remanescente),2) AS volume_remanescente, ROUND(SUM(volume_original_autorizado - volume_remanescente),2) AS volume_explorado
	FROM adjust_autex_area
	WHERE id_explor NOT IN (132,134,135,136,137,138,139)
	GROUP BY id_explor, nome_razao_social_detentor, explo_area_ha
)
SELECT ROW_NUMBER() OVER() AS id, ARRAY_AGG(id_explor ORDER BY id_explor) AS id_explor, nome_razao_social_detentor, num_serie_autex, 
SUM(DISTINCT autex_area_ha) AS autex_area_ha, explo_area_ha, volume_original_autorizado, volume_remanescente, volume_explorado
FROM pre_final 
GROUP BY nome_razao_social_detentor, num_serie_autex, explo_area_ha, volume_original_autorizado, volume_remanescente, volume_explorado
ORDER BY id_explor;
CREATE UNIQUE INDEX ON ro.mv_simex_exploracao_autorizacao_overlap_final (id);
COMMENT ON MATERIALIZED VIEW ro.mv_simex_exploracao_autorizacao_overlap_final 
IS 'Junção final das Autex que tiveram intersecção com as Área de Exploração';

--== Tabela Agregada de Áreas de Exploração com e sem intersecção espacial com Autex ==--
--DROP MATERIALIZED VIEW ro.mv_simex_exploracao_autorizacao_total CASCADE;
CREATE MATERIALIZED VIEW ro.mv_simex_exploracao_autorizacao_total AS
WITH union_all AS (
	SELECT ARRAY_AGG(id) AS id_explor, NULL::varchar AS nome_razao_social_detentor, NULL::_numeric AS num_serie_autex,
	NULL::NUMERIC AS autex_area_ha, ROUND(area_ha::NUMERIC,2) AS explo_area_ha, 
	NULL::NUMERIC AS volume_original_autorizado, 
	NULL::NUMERIC AS volume_remanescente, 
	NULL::NUMERIC AS volume_explorado
	FROM ro.simex_exploracao_madeireira_2019_2020
	WHERE id NOT IN (SELECT UNNEST(id_explor) FROM ro.mv_simex_exploracao_autorizacao_overlap_final)
	GROUP BY area_ha
	UNION 
	SELECT id_explor, nome_razao_social_detentor, num_serie_autex, autex_area_ha, explo_area_ha,
	volume_original_autorizado, volume_remanescente, volume_explorado
	FROM ro.mv_simex_exploracao_autorizacao_overlap_final
	ORDER BY id_explor)
SELECT ROW_NUMBER() OVER() AS id, id_explor, nome_razao_social_detentor, num_serie_autex, autex_area_ha, explo_area_ha, 
volume_original_autorizado, volume_remanescente, volume_explorado
FROM union_all;
CREATE UNIQUE INDEX ON ro.mv_simex_exploracao_autorizacao_total (id);
COMMENT ON MATERIALIZED VIEW ro.mv_simex_exploracao_autorizacao_total 
IS 'Junção final das Área de Exploração(AE) com Autex com as AE sem intersecação com Autex';

--== Pré Resumo: Agregação de Exploração ==--
--DROP VIEW ro.v_simex_pre_summary;
CREATE VIEW ro.v_simex_pre_summary AS
WITH foo AS (
	SELECT id_explor, 
	ARRAY_AGG(nome_razao_social_detentor) AS nome_razao_social_detentor,
	SUM(autex_area_ha) AS autex_area_ha, 
	SUM(DISTINCT explo_area_ha) AS explo_area_ha,
	SUM(volume_original_autorizado) AS volume_original_autorizado,
	SUM(volume_remanescente) AS volume_remanescente, 
	SUM(volume_explorado) AS volume_explorado
	FROM ro.mv_simex_exploracao_autorizacao_total
	GROUP BY id_explor
)
SELECT ROW_NUMBER() OVER() AS id, id_explor, nome_razao_social_detentor, autex_area_ha, explo_area_ha,
CASE WHEN autex_area_ha - explo_area_ha >=0 THEN TRUE ELSE FALSE END AS legal_explo,
volume_original_autorizado, volume_remanescente, volume_explorado,
CASE WHEN volume_explorado >=0 THEN TRUE ELSE FALSE END AS volume_suspeito
FROM foo
ORDER BY id_explor, id;
COMMENT ON VIEW ro.v_simex_pre_summary IS 'Pré-resumo final SIMEX,
Agregação por Área de Exploração, atribuição legalidade e volume suspeito';

--== Resumo SIMEX Ago/2019 - Jul/2020 ==--
--DROP VIEW ro.v_simex_summary;
CREATE VIEW ro.v_simex_summary AS
WITH explo_total AS (
	SELECT SUM(explo_area_ha) AS explo_area_ha
	FROM ro.v_simex_pre_summary
)
SELECT 
a.legal_explo AS exploracao_legal,
SUM(DISTINCT a.explo_area_ha) AS explo_area_total,
SUM(CASE WHEN a.legal_explo IS TRUE THEN ROUND((a.explo_area_ha / b.explo_area_ha) * 100,2)
WHEN a.legal_explo IS FALSE THEN ROUND((a.explo_area_ha / b.explo_area_ha) * 100,2) END) AS explo_area_pct,
SUM(CASE WHEN a.autex_area_ha IS NULL THEN 0 ELSE a.autex_area_ha END) AS autex_area_total
FROM ro.v_simex_pre_summary a, explo_total b
GROUP BY a.legal_explo;
COMMENT ON VIEW ro.v_simex_summary IS 
'Exploração Madeireira Autorizada x Não Autorizada em Rondônia
Data: Agosto/2019 - Julho/2020';
						 
--== Resumo Autex  ==--
-- DROP VIEW ro.v_autorizacao_exploracao_dof_ibama_group_summary;
CREATE VIEW ro.v_autorizacao_exploracao_dof_ibama_group_summary AS
SELECT 'Autex Total'::TEXT AS categ,
COUNT(DISTINCT nome_razao_social_detentor) cnt_razoes, 
COUNT(DISTINCT num_serie_autex) cnt_num_serie_autex,
ROUND(SUM(area_ha),2) area_autex_ha,
percentile_cont(0.25) WITHIN GROUP (ORDER BY area_ha) area_pri_quartil,
ROUND(AVG(area_ha)::NUMERIC,2) area_avg,
percentile_cont(0.75) WITHIN GROUP (ORDER BY area_ha) area_ter_quartil,
ROUND(stddev(area_ha)::NUMERIC,2) area_std_dev
FROM ro.mv_autorizao_exploracao_dof_ibama_group 
UNION 
SELECT 'Autex Overlap Área Exploradas'::TEXT AS categ,
COUNT(DISTINCT nome_razao_social_detentor) cnt_razoes, 
COUNT(DISTINCT num_serie_autex) cnt_num_serie_autex,
ROUND(SUM(DISTINCT autex_area_ha),2) area_autex_ha,
percentile_cont(0.25) WITHIN GROUP (ORDER BY autex_area_ha) area_pri_quartil,
ROUND(AVG(autex_area_ha)::NUMERIC,2) area_avg,
percentile_cont(0.75) WITHIN GROUP (ORDER BY autex_area_ha) area_ter_quartil,
ROUND(stddev(autex_area_ha)::NUMERIC,2) area_std_dev
FROM ro.mv_simex_exploracao_autorizacao_overlap;
COMMENT ON VIEW ro.v_autorizacao_exploracao_dof_ibama_group_summary IS 
'Resumo Infos Agregadas de Autex Válidas';

--== Resumo Áreas de Exploração  ==--
--DROP VIEW ro.v_simex_exploracao_madeireira_2019_2020_summary;
CREATE VIEW ro.v_simex_exploracao_madeireira_2019_2020_summary AS
SELECT COUNT(id) cnt_exploracao,
ROUND(SUM(area_ha)::NUMERIC,2) area_ha,
percentile_cont(0.25) WITHIN GROUP (ORDER BY area_ha) area_pri_quartil,
ROUND(AVG(area_ha)::NUMERIC,2) area_avg,
percentile_cont(0.75) WITHIN GROUP (ORDER BY area_ha) area_ter_quartil,
ROUND(stddev(area_ha)::NUMERIC,2) area_std_dev
FROM ro.simex_exploracao_madeireira_2019_2020; 
COMMENT ON VIEW ro.v_simex_exploracao_madeireira_2019_2020_summary IS 
'Resumo Exploração Madeireira
Data: Agosto/2019 - Julho/2020';