61 lines
2.0 KiB
Python
61 lines
2.0 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding:UTF-8 -*-
|
|
|
|
|
|
from sqlalchemy import create_engine #, text
|
|
from sqlalchemy.engine import URL
|
|
import geopandas as gpd
|
|
import pandas as pd
|
|
from pycen import con_sicen as con, con_bdcen as con38
|
|
|
|
|
|
sql = 'SELECT * FROM sites.c_sites_zonages'
|
|
site = gpd.read_postgis(sql,con38,geom_col='geom')
|
|
site.sort_values(['code_site','surface_ha'], inplace=True)
|
|
site.reset_index(inplace=True,drop=True)
|
|
site.drop_duplicates(subset=['code_site'],keep='last', inplace=True)
|
|
|
|
|
|
cod = 'ARSE'
|
|
for cod in site.code_site:
|
|
print(cod)
|
|
sql = """
|
|
SELECT * FROM saisie.vm_synthese_observations
|
|
WHERE ST_Intersects(geom,'SRID=2154;%s')
|
|
""" % site[site.code_site==cod].geom.values[0]
|
|
obs = gpd.read_postgis(sql,con,geom_col='geom')
|
|
|
|
if obs.empty:
|
|
continue
|
|
|
|
sql = """
|
|
SELECT operation op, date_operation date_op, utilisateur user_op, id_obs FROM saisie.suivi_saisie_observation
|
|
WHERE operation = 'INSERT' AND id_obs IN (%s)
|
|
""" % ",".join(obs.id_obs.astype(str))
|
|
ins_bos = pd.read_sql_query(
|
|
sql,con
|
|
)
|
|
if ins_bos.empty: pass
|
|
else:
|
|
ins_bos['heure_op'] = ins_bos.date_op.dt.time
|
|
ins_bos.date_op = ins_bos.date_op.dt.date
|
|
ins_bos.date_op = ins_bos.date_op.astype(str)
|
|
ins_bos.heure_op = ins_bos.heure_op.astype(str)
|
|
|
|
obs = pd.merge(ins_bos,obs, on=['id_obs'], how='right')
|
|
obs.structures = [' & '.join(i) for i in obs.structures]
|
|
obs.observateurs = [' & '.join(i) for i in obs.observateurs]
|
|
obs.date_obs = obs.date_obs.astype(str)
|
|
obs.date_debut_obs = obs.date_debut_obs.astype(str)
|
|
obs.date_fin_obs = obs.date_fin_obs.astype(str)
|
|
obs.heure_obs = obs.heure_obs.astype(str)
|
|
obs.set_index('id_obs', inplace=True)
|
|
|
|
output = '/home/colas/Documents/tmp/obsbysite/%s.xlsx' % cod
|
|
with pd.ExcelWriter(output) as writer:
|
|
obs[obs.structures=='CEN Isère'].to_excel(writer,sheet_name='CEN Isère', index=True)
|
|
obs[obs.structures!='CEN Isère'].to_excel(writer,sheet_name='Partenaire', index=True)
|
|
writer.save()
|
|
|
|
|