118 lines
3.9 KiB
Python
118 lines
3.9 KiB
Python
from pycen import con_gn, con_sicen
|
|
import geopandas as gpd
|
|
from datetime import datetime as dt
|
|
import os
|
|
|
|
cols_sicen_out = {
|
|
'id_origine':'id_origine',
|
|
'':'organisme_gestionnaire',
|
|
'cd_nom':'cd_nom',
|
|
'nom_complet':'info_nomcomplet',
|
|
'nom_vern':'info_nomvern',
|
|
'date_debut_obs':'date_debut_obs',
|
|
'date_fin_obs':'date_fin_obs',
|
|
'date_obs':'date_obs',
|
|
'date_textuelle':'date_textuelle',
|
|
'effectif':'effectif',
|
|
'effectif_min':'effectif_min',
|
|
'effectif_max':'effectif_max',
|
|
'effectif_textuel':'effectif_textuel',
|
|
'remarque_obs':'remarque_obs',
|
|
'localisation':'remarque_loc',
|
|
'determination':'determination',
|
|
'type_effectif':'type_effectif',
|
|
'phenologie_age':'phenologie_age',
|
|
'observateur':'observateur',
|
|
'structure':'structure',
|
|
'etude':'nom_etude',
|
|
'protocole':'nom_protocole',
|
|
'':'id_mnhn',
|
|
'':'info_nomznieff',
|
|
'':'statut',
|
|
}
|
|
cols_geom_out = 'geometrie'
|
|
|
|
def get_zonage(type_code):
|
|
sql = '''
|
|
SELECT a.* FROM ref_geo.l_areas a
|
|
JOIN ref_geo.bib_areas_types b USING (id_type)
|
|
WHERE b.type_code = 'DEP' AND a.area_code = '38'
|
|
'''
|
|
return gpd.read_postgis(sql,con_gn)
|
|
|
|
|
|
def get_obs_gn(date_min = None):
|
|
from shapely.geometry import Point
|
|
sql = '''
|
|
SELECT * FROM gn_synthese.v_synthese_for_export
|
|
'''
|
|
if date_min:
|
|
sql += " WHERE date_debut > '{date_min}'".format(date_min=date_min)
|
|
res = gpd.pd.read_sql_query(sql,con_gn)
|
|
XY = tuple(zip(res.x_centroid_4326,res.y_centroid_4326))
|
|
res['geom'] = [Point(xy) for xy in XY]
|
|
|
|
return res.set_geometry('geom',crs=4326)\
|
|
.to_crs(2154)
|
|
|
|
|
|
def get_obs_sicen(date_min = None,intersects=False):
|
|
sql = '''
|
|
SELECT v.* FROM saisie.v_saisie_observation v
|
|
JOIN saisie.suivi_saisie_observation s USING (id_obs)
|
|
'''
|
|
if date_min or intersects:
|
|
sql += ' WHERE '
|
|
if date_min:
|
|
dtm = """
|
|
v.date_obs > '{date_min}'
|
|
-- OR s.date_operation > '{date_min}') AND s.operation = 'INSERT'
|
|
""".format(
|
|
date_min=date_min
|
|
)
|
|
sql += dtm
|
|
if intersects :
|
|
''
|
|
|
|
sql += ';'
|
|
return gpd.read_postgis(
|
|
sql,con_sicen,geom_col='geometrie',#parse_dates=['date_obs','date_debut_obs','date_fin_obs'],
|
|
)\
|
|
.rename_geometry('geom')
|
|
|
|
if __name__ == "__main__":
|
|
|
|
today = dt.today().date().strftime('%Y%m%d')
|
|
path = '/home/colas/Documents/tmp'
|
|
file_name = os.path.join(path,'export_znieff1_cen38_%s.gpkg'%today)
|
|
|
|
date_min = '2021-12-31' # Extraction des données observées ou intégrées après cette date.
|
|
date_max = '2023-01-01' # Extraction des données observées avant cette date.
|
|
|
|
zon = get_zonage('ZNIEFF1').unary_union
|
|
gn = get_obs_gn(date_min=date_min)
|
|
sicen = get_obs_sicen(date_min=date_min)
|
|
sicen['type_effectif'] = sicen['strate_flore'] + sicen['age_faune']
|
|
sicen['phenologie_age'] = sicen['phenologie_flore'] + sicen['sexe_faune']
|
|
|
|
gn.date_debut = gpd.pd.to_datetime(gn.date_debut)
|
|
sicen.date_obs = gpd.pd.to_datetime(sicen.date_obs)
|
|
|
|
obs_gn = gn[gn.date_debut < dt.strptime(date_max,'%Y-%m-%d')]
|
|
obs_sicen = sicen[sicen.date_obs < dt.strptime(date_max,'%Y-%m-%d')]
|
|
|
|
gn_date = obs_gn.columns[obs_gn.columns.str.contains('date_|time|heure|uuid')]
|
|
sicen_date = obs_sicen.columns[obs_sicen.columns.str.contains('date_|time|heure')]
|
|
|
|
obs_gn[gn_date] = obs_gn[gn_date].astype(str)
|
|
obs_sicen[sicen_date] = obs_sicen[sicen_date].astype(str)
|
|
|
|
obs_sicen.rename(columns=cols_sicen_out,inplace=True)
|
|
obs_sicen.rename_geometry(cols_geom_out,inplace=True)
|
|
keep_col = cols_sicen_out.values()
|
|
si_cols = obs_sicen.columns[obs_sicen.columns.isin([*keep_col,cols_geom_out])]
|
|
|
|
obs_sicen.loc[obs_sicen.intersects(zon),si_cols].to_file(file_name,driver='GPKG',layer='sicen_obs')
|
|
obs_gn[obs_gn.intersects(zon)].to_file(file_name,driver='GPKG',layer='geonature_obs')
|
|
|
|
|