Last active
April 19, 2021 19:56
-
-
Save tomasoak/251297da776a5d6018ce5f9f350fad04 to your computer and use it in GitHub Desktop.
Script 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 | |
-- 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'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment