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();
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 );
