89 lines
2.5 KiB
Python
89 lines
2.5 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
|
|
|
|
# HOMER
|
|
user = 'cen_admin'
|
|
pwd = '#CEN38@venir'
|
|
adr = '91.134.194.221'
|
|
port = '5432'
|
|
bd_si = 'sicen2'
|
|
|
|
url = URL.create('postgresql+psycopg2',
|
|
username=user,
|
|
password=pwd,
|
|
host=adr,
|
|
database=bd_si,
|
|
)
|
|
con = create_engine(url)
|
|
|
|
# BART
|
|
bd_38 = 'bd-cen-38'
|
|
user = 'cen_admin'
|
|
pwd = '#CEN38@venir'
|
|
adr = '192.168.0.189'
|
|
port = '5432'
|
|
|
|
url = URL.create('postgresql+psycopg2',
|
|
username=user,
|
|
password=pwd,
|
|
host=adr,
|
|
database=bd_38,
|
|
)
|
|
con38 = create_engine(url)
|
|
|
|
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()
|
|
|
|
|