Descarga-masiva-de-indices

Ver en Git

---

Son dos consultas a la BD y luego se procesa con pandas para llevar a csv.

PLANET

with fields as (
	select l.id as id_lote, l.nombre as lote, e2.nombre as campo
	from login_usuarios_escritorios lue 
	join permisos p on p.id_usuario =lue.id_usuario and p.fecha_fin is null and p.id_rol =1
	join lotes l on l.id =p.id_lote and l.fecha_fin is null
	join estancias e2 on l.id_estancia =e2.id 
    where l.id in ()
)
	select 	distinct on (campo, lote, fecha_captura::date)
			fields.*,
			fecha_captura::date as "fecha", 
            replace( sl.ndvi_mean::text, '.', ',') as ndvi,
            replace( sl.gndvi_mean::text, '.', ',') as gndvi,
			3 as "Resolución(m)"
	from capas
		join fields using (id_lote)
		join planet_layers sl on capas.id=layer_id 
	where not cloudy 
		and ndvi_mean is not null and gndvi_mean is not null
		and fecha_captura between '2014-12-09'::date and '2022-02-28'::date
		and sl.status_id=3
order by campo, lote, fecha_captura::date desc, (metadata->'properties'->>'view_angle')::float asc

import pandas as pd

df=pd.read_csv('~/Desktop/planet.csv')

df.drop(['id_lote', 'Resolución(m)'],axis=1)\
  .set_index(['campo', 'lote'])\
  .pivot(columns='fecha')['ndvi']\
  .to_csv('~/Desktop/ndvi planet.csv')

df.drop(['id_lote', 'Resolución(m)'],axis=1)\
  .set_index(['campo', 'lote'])\
  .pivot(columns='fecha')['gndvi']\
  .to_csv('~/Desktop/gndvi planet.csv')

SENTINEL

with fields as (
	select l.id as id_lote, l.nombre as lote, e2.nombre as campo
	from login_usuarios_escritorios lue 
	join permisos p on p.id_usuario =lue.id_usuario and p.fecha_fin is null and p.id_rol =1
	join lotes l on l.id =p.id_lote and l.fecha_fin is null
	join estancias e2 on l.id_estancia =e2.id 
    where l.id in ()
)
	select 	distinct on (campo, lote, fecha_captura::date)
			fields.*,
			fecha_captura::date as "fecha", 
            replace( sl.ndvi_mean::text, '.', ',') as ndvi,
            replace( sl.gndvi_mean::text, '.', ',') as gndvi,
			10 as "Resolución(m)"
	from capas
		join fields using (id_lote)
		join sentinel_layers sl on capas.id=layer_id 
	where not cloudy 
		and ndvi_mean is not null and gndvi_mean is not null
		and fecha_captura between '2014-12-09'::date and '2022-02-28'::date
		and sl.status_id=3
order by campo, lote, fecha_captura::date desc

import pandas as pd

df=pd.read_csv('~/Desktop/sentinel.csv')

df.drop(['id_lote', 'Resolución(m)'],axis=1)\
  .set_index(['campo', 'lote'])\
  .pivot(columns='fecha')['ndvi']\
  .to_csv('~/Desktop/ndvi sentinel.csv')

df.drop(['id_lote', 'Resolución(m)'],axis=1)\
  .set_index(['campo', 'lote'])\
  .pivot(columns='fecha')['gndvi']\
  .to_csv('~/Desktop/gndvi sentinel.csv')