Gerenciamento de Conexões no PostgreSQL

 Utilizando os comandos abaixo é possível monitorar as conexões ativas no banco de dados e a partir da análise terminar o processo diretamente no banco de dados.

select * from pg_stat_activity;

select client_addr, count(1) from pg_stat_activity group by 1;

SELECT pg_terminate_backend(pg_stat_activity.pid) 

FROM pg_stat_activity 

where query ilike '%roll%' and client_addr <> '<IP_address>';

SELECT pg_terminate_backend(pg_stat_activity.pid) 

FROM pg_stat_activity 

where client_addr = '<IP_address>';

Criação de um ambiente virutal para pygis

Depois de instalado o miniconda em qualquer sistema operacional rodar os seguintes comandos 

 conda create -n gee python=3.10 

 conda activate gee 

 conda install mamba -c conda-forge 

 mamba install pygis -c conda-forge

Como representar a área de um píxel a partir do centroid

Neste post encontrei um script sql que foi utilizado para gerar o que chamei de pixel de fogo, que na verdade representa uma área nominal teórica da imagem do satélite no qual um determinado foco foi extraído. Em um caso mais fiel a geometria da imagem em relação ao plano Terrestre ajustes devem ser realizados para levar em consideração o IFOV (Istantaneous Field of View - Campo de. Visada Instantâneo), porém para uma grande maioria das aplicações este exemplo atende.

CREATE TABLE public.pixel_foco (
	fid serial NOT NULL,
	foco_id varchar(80) NOT NULL,
	data_hora_gmt timestamp NOT NULL,
	satelite varchar(100) NOT NULL,
	geometria geometry(POLYGON, 4326) NOT NULL,
	CONSTRAINT pixel_foco_pkey PRIMARY KEY (foco_id)
);
CREATE INDEX sidx_pixel_foco_geometria ON public.pixel_foco USING gist (geometria);


CREATE OR REPLACE FUNCTION public.create_pixel_foco()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF NEW.satelite in ('NOAA-20', 'NPP-375') THEN
  INSERT INTO pixel_foco(foco_id,data_hora_gmt, satelite, geometria) VALUES(NEW.foco_id, NEW.data_hora_gmt, new.satelite, ST_buffer(new.geometria, 0.00375, 'endcap=square'));
  ELSIF NEW.satelite in ('AQUA_M-M', 'AQUA_M-T', 'TERRA_M-M', 'TERRA_M-T') THEN
  INSERT INTO pixel_foco(foco_id,data_hora_gmt, satelite, geometria) VALUES(NEW.foco_id, NEW.data_hora_gmt, new.satelite, ST_buffer(new.geometria, 0.005, 'endcap=square'));
  end if;

  RETURN NEW;
END;
$function$;


CREATE TRIGGER create_pixel_foco_trigger BEFORE INSERT ON public.focos FOR EACH ROW EXECUTE PROCEDURE public.create_pixel_foco();

  
        

Linhas duplicadas no banco de dados Postgres ... como resolver?

Quando uma tabela do banco de dados Postgresql possui registros duplicados é possível identificar utilizando uma "Window Function" que vai gerar um novo valor sequencial baseado nos critérios que forem definidos pelo usuário.
Para deixar mais claro vou exemplificar com uma tabela bem simples:

select orb_pto, ano, lim_ndvi, lim_nbrfrom tabela_limiares tl order by orb_pto, ano;

orb_pto	ano	lim_ndvi	lim_nbr
216_065	2018	0.2		0.5
216_065	2018	0.2		0.5
216_065	2019	0.2		0.5
216_065	2019	0.2		0.5
216_066	2018	0.2		0.5
216_066	2018	0.2		0.5

É possível notar que existem linhas duplicadas porém difícil de identificar.

---

Utilizando um comando para criar um número sequencial baseado em uma condição é possível melhorar esta visualização

select orb_pto, ano, lim_ndvi, lim_nbr, row_number() over(
		partition by orb_pto, ano  order by 1,2 ) as num_lin  
	from tabela_limiares tl;

orb_pto	ano	lim_ndvi	lim_nbr	num_lin
216_065	2018	0.2		0.5		1
216_065	2018	0.2		0.5		2
216_065	2019	0.2		0.5		1
216_065	2019	0.2		0.5		2
216_066	2018	0.2		0.5		1
216_066	2018	0.2		0.5		2

Note que sempre que o num_lin for igual a 2 representa que aquela tupla considerando orb_pto e ano estão duplicadas. Porém antes de apagar esta linha quero me certificar que as quatro condições são únicas, e por isso vou fazer a consulta um pouco mais refinada:

with duplicados as (
select orb_pto, ano, lim_ndvi, lim_nbr, row_number() over(
		partition by orb_pto, ano, lim_ndvi, lim_nbr  order by 1,2 ) as num_lin  
	from tabela_limiares tl
) select * from duplicados where duplicados.num_lin =2 ;

orb_pto	ano	lim_ndvi	lim_nbr	num_lin
216_065	2018	0.2		0.5		2
216_065	2019	0.2		0.5		2
216_066	2018	0.2		0.5		2
216_066	2019	0.2		0.5		2
216_067	2018	0.2		0.5		2
217_063	2019	0.2		0.5		2

Pronto agora eu posso apagar ou inserir em outra tabela conforme meu interesse

with duplicados as (
	select *, row_number() over(
		partition by orb_pto, ano order by 1,2 ) as num_lin  
	from tabela_limiares tl 
)insert into aq30m_limiar (orbita_ponto, lim_ndvi, lim_nbr, ano) 
(select orb_pto,  lim_ndvi, lim_nbr, ano from duplicados where duplicados.num_lin =1 );

CUIDADO COM O DELETE!!!!

A inspiração para este post veio do vídeo https://www.scalingpostgres.com/episodes/225-psql-gexec-delete-duplicates-postgres-podcast-puny-powerful/

Como utilizar o comando PARALLEL

 Um inicio é ler a documentação em https://www.gnu.org/software/parallel/parallel_tutorial.html 

na falta de algum exemplo melhor tenho este ...

#!/bin/bash
for ano in {2001..2019}
do
echo gdal_translate -of GTiff -a_srs EPSG:4326 -co TILED=YES Merge_IGBP_C6_"$ano".nc Vegetation_"$ano".tif >>lista.txt
done
nohup parallel -j 5 < lista.txt > lista.log &
        

Sucesso!!

Consultas encadeadas utilizando With

Neste post estou apresentando o esquema comentado de uma consulta encadeada que agiliza as análises de grandes quantidade de dados.

with alias_1 as (
	select colunas, 
		(row_number() over(partition by coluna order by coluna)) as id -- Exemplo de "Window Function"
		from tabela
	), -- a virgula indica que pode ter outras tabelas disponíveis
	alias_2 as (
		select coluna 
			from tabela
	) -- sem a virgura indica que será feira uma consulta final
select a1.coluna, a2.coluna
	from alias_1 a1, alias_2 a2
	where a1.coluna = a2.coluna;

dica para formatacao do codigo http://hilite.me/

Como criar um retângulo (polígono) a partir de de dois pontos.

Existem situações que é necessário criar um Bounding Box ou Retângulo Envolvente para executar uma filtragem no Banco de Dados. Neste caso vamos utilizar os dois pontos encontrados no comando ogrinfo que foi explicado em https://geoajuda.blogspot.com/2021/01/como-saber-qual-o-bounding-box-envelope.html

st_setsrid( st_makebox2d( st_makepoint(-58.8984,-9.8412), st_makepoint(-46.0608,2.5911)), 4326)

Aqui está o link [https://postgis.net/docs/ST_MakeBox2D.html] para detalhamento do comando no manual