389 lines
15 KiB
Python
389 lines
15 KiB
Python
#!/usr/bin/env python
|
||
# -*- coding: UTF-8 -*-
|
||
|
||
from os import getlogin,path,chdir
|
||
import sys
|
||
import pandas as pd
|
||
import geopandas as gpd
|
||
from sqlalchemy.engine import URL
|
||
from sqlalchemy import create_engine
|
||
from pycen import con_bdcen as con, con_fon, wfs
|
||
|
||
# NOT WORK
|
||
# SCRIPT_DIR = path.dirname(path.abspath(__file__))
|
||
# sys.path.append(path.dirname(SCRIPT_DIR))
|
||
# from . import get_siteCen_fede
|
||
|
||
|
||
|
||
annee = 2023
|
||
term_parcelle_conv = ['CONVENTION',"CONVENTION D'USAGE",'ACCORD VERBAL']
|
||
path_agri = '/media/colas/SRV/FICHIERS/OUTILS/CARTOGRAPHIE/ESPACE DE TRAVAIL/TRAVAUX/Couches de référence/'
|
||
agri_partenaire_surf = gpd.read_file(path_agri+'AGRIS_partenaires_surface exploitées.shp')
|
||
agri_partenaire = gpd.read_file(path_agri+'AGRIS_partenaires.shp')
|
||
id_rnx = ['RNGL','RNIP']
|
||
|
||
|
||
MAJ_COLNAME = {
|
||
'doc_gestion_annee_ini':'doc_gestion_date_ini',
|
||
'doc_gestion_annne_maj':'doc_gestion_date_maj',
|
||
'source_geom_site_annee':'source_geom_site_date',
|
||
'0':'surf_carto_habitat_m2',
|
||
'F':'geol_site_inpn',
|
||
}
|
||
|
||
|
||
def drop_specialchar(obj):
|
||
return obj.replace({
|
||
r'[’]':"'",
|
||
r'[àáâãäå]':'a',
|
||
r'[èéêë]':'e',
|
||
r'[ìíîï]':'i',
|
||
r'[òóôõö]':'o',
|
||
r'[ùúûü]':'u',
|
||
# r'[ ]':"",
|
||
r'[–]':"-"
|
||
},regex=True)
|
||
|
||
def get_site():
|
||
sqlsit = '''SELECT * FROM %s.%s'''%('sites','c_sites_zonages')
|
||
return gpd.read_postgis(sql=sqlsit,geom_col='geom',con=con)
|
||
|
||
|
||
|
||
def get_siteCen_fede(an=None):
|
||
sqlsit = '''SELECT * FROM %s.%s'''%('sites','c_sites_zonages')
|
||
if an :
|
||
sqlsit += ''' WHERE date_fin is NULL OR date_fin >= '%i-01-01' '''%an
|
||
|
||
sqlsit += ' order by code_site'
|
||
dfsite = gpd.read_postgis(sql=sqlsit,geom_col='geom',con=con)
|
||
|
||
# Isolation de tous les sites CEN (gestion/assist/mili)
|
||
dup = dfsite.code_site.duplicated(keep=False)
|
||
typzon = dfsite.type_zonage.isin(['ZI','DO','DH'])
|
||
return dfsite[
|
||
~dup | (dup & typzon)
|
||
]
|
||
|
||
|
||
def get_parcelleCen_fede(an=None):
|
||
sqlsit = '''SELECT * FROM %s.%s'''%('foncier','c_anim_foncier_v2')
|
||
if an :
|
||
sqlsit += '''
|
||
WHERE maitrise IN (
|
||
'ACCORD VERBAL','ACQUI PAR LE CEN EN BND','ACQUI PAR LE CEN ISERE',
|
||
'ACQUIS PAR LE CEN ISERE','ACQUIS&CONVENTION','Acquis par le CEN ou CREN',
|
||
'BAIL EMPHYTEOTIQUE','CONVENTION','CONVENTION D''USAGE','FRUP')
|
||
AND (commentair not ilike '%%{an}%%' OR commentair IS NULL)'''.format(an=an)
|
||
|
||
return gpd.read_postgis(sql=sqlsit,geom_col='geom',con=con)
|
||
|
||
|
||
def update_date_site2022(df):
|
||
df['date_crea_site'].replace({
|
||
'01/012005': '01/01/2005',
|
||
'01/01/98': '01/01/1998',
|
||
'01/01/03': '01/01/2003',
|
||
'09.05.2001': '09/05/2001',
|
||
'03/1986': '01/03/1986',
|
||
'01/01/94': '01/01/1994',
|
||
}, inplace=True)
|
||
df['date_crea_site'] = pd.to_datetime(
|
||
df['date_crea_site'],
|
||
# format='%Y/%m/%d',
|
||
yearfirst=True
|
||
)
|
||
return df
|
||
|
||
|
||
def update_date(serie):
|
||
DICT = {
|
||
'024/04/2019':'24/04/2019'
|
||
}
|
||
return pd.to_datetime(
|
||
serie.replace(DICT),
|
||
# format='%Y/%m/%d',
|
||
yearfirst=True
|
||
)
|
||
|
||
|
||
if __name__ == "__main__":
|
||
|
||
|
||
path_source = '/media/{login}/SRV/FICHIERS/OUTILS/BASES DE DONNEES/BILAN_FEDE_CEN/{an}/'.format(login=getlogin(),an=annee)
|
||
path_an_precedent = '/media/{login}/SRV/FICHIERS/OUTILS/BASES DE DONNEES/BILAN_FEDE_CEN/{an}/ENVOIE/'.format(login=getlogin(),an=annee-1)
|
||
tdb_sites_csv = 'Sites_CEN_38_%i.csv'
|
||
receuil_path = 'Recueil_data/'
|
||
receuil_datasite = 'TBD - receuil data SITES.xlsx'
|
||
shp_cnpe = 'primtresitecnpe/perimetre_site_CNPE_2019.shp'
|
||
|
||
# path_source = '/media/{login}/SRV/FICHIERS/OUTILS/BASES DE DONNEES/BILAN_FEDE_CEN/{an}/Docs_SIG_joints{an}/'.format(login=getlogin(),an=annee)
|
||
dico_data = 'Docs_SIG_joints{an}/Dico_DATA_sites_CEN_v{an}_v1.xlsx'.format(an=annee)
|
||
lv_typmilie = pd.read_excel(path_source+dico_data,sheet_name='listes_valeurs',header=1, usecols='M:N',nrows=16)
|
||
lv_typmilie.columns = ['code_type_milieu','libelle_type_milieu']
|
||
|
||
dfsite = get_siteCen_fede(annee-1)\
|
||
.sort_values('code_site')\
|
||
.reset_index()
|
||
dfsite.loc[(dfsite.code_site=='N2IP') & (dfsite.type_zonage=='DO'),'code_site'] = 'N2IP_DO'
|
||
dfsite.loc[(dfsite.code_site=='N2IP') & (dfsite.type_zonage=='DH'),'code_site'] = 'N2IP_DH'
|
||
dfparc = get_parcelleCen_fede(annee)
|
||
|
||
sitefcen = dfsite[dfsite.intersects(dfparc.unary_union)]
|
||
|
||
parconv = dfparc.maitrise.isin(term_parcelle_conv)
|
||
site_rnx = dfsite[dfsite.code_site.isin(id_rnx)].copy()
|
||
|
||
n2000_zps = wfs.get_wfs(
|
||
url = "https://ws.carmencarto.fr/WMS/119/fxx_inpn?",
|
||
layer = "Zones_de_protection_speciale",
|
||
bbox = dfsite.unary_union )
|
||
# n2000_zps = wfs.get_wfs(url="https://ws.carmencarto.fr/WMS/119/fxx_inpn?",layer="Zones_de_protection_speciale")
|
||
n2000_zsc = wfs.get_wfs(
|
||
url="https://ws.carmencarto.fr/WMS/119/fxx_inpn?",
|
||
layer="Sites_d_importance_communautaire_JOUE__ZSC_SIC_",
|
||
bbox=dfsite.unary_union )
|
||
|
||
|
||
site2022 = pd.read_csv(path_an_precedent+tdb_sites_csv % (annee-1))\
|
||
.rename(columns=MAJ_COLNAME)\
|
||
.replace({'id_site_cen':{'COMB':'COCO'}})
|
||
|
||
site2022 = update_date_site2022(site2022)
|
||
site2023 = site2022[site2022.id_site_cen.isin(dfsite.code_site)]\
|
||
.reset_index(drop=True)
|
||
recueil_site = pd.read_excel(path_source+receuil_path+receuil_datasite,header=0,skiprows=[1])\
|
||
.replace({'Null':None})
|
||
recueil_site['doc_gestion_date_ini'] = update_date(recueil_site['doc_gestion_date_ini'])
|
||
recueil_site['doc_gestion_date_maj'] = update_date(recueil_site['doc_gestion_date_maj'])
|
||
recueil_site['doc_gestion_date_fin'] = update_date(recueil_site['doc_gestion_date_fin'])
|
||
|
||
|
||
test_add = ~recueil_site.id_site.isin(site2023.id_site_cen)
|
||
add_site = recueil_site[test_add].rename(columns={
|
||
'id_site':'id_site_cen'
|
||
})
|
||
site2023 = pd.concat([site2023,add_site])
|
||
|
||
# site_exploit_agri = dfsite.intersects(agri_partenaire.unary_union)
|
||
site_exploit_agri_surf = dfsite.intersects(agri_partenaire_surf.unary_union)
|
||
|
||
|
||
# Test d'identification des nouveaux sites
|
||
sitenew = ~dfsite.code_site.isin(site2022.id_site_cen)
|
||
if sitenew.any():
|
||
print('WARNING : de nouveaux sites sont apparus cette année :\n\t%s'%str(tuple(dfsite[sitenew].code_site)))
|
||
intersect_acqu = dfsite[sitenew].intersects(dfparc[~parconv].unary_union)
|
||
intersect_conv = dfsite[sitenew].intersects(dfparc[parconv].unary_union)
|
||
if intersect_acqu.any():
|
||
print(' Le(s) nouveau(x) site(s) intersect(ent) des parcelles acquises : \n\t%s'%str(tuple(dfsite[sitenew&intersect_acqu].code_site)))
|
||
|
||
else:
|
||
print(' Le(s) nouveau(x) site(s) ne contien(nen)t pas de parcelles acquises ...')
|
||
if intersect_conv.any():
|
||
print(' Le(s) nouveau(x) site(s) intersect(ent) des parcelles conventionnées : \n\t%s'%str(tuple(dfsite[sitenew&intersect_conv].code_site)))
|
||
else:
|
||
print(' Le(s) nouveau(x) site(s) ne contien(nen)t pas de parcelles conventionnées ...')
|
||
dfsite = dfsite[~sitenew].copy()\
|
||
.reset_index()
|
||
else :
|
||
print("Pas de nouveaux sites pour l'année %i."%annee)
|
||
|
||
site2023.sort_values('id_site_cen',inplace=True)
|
||
recueil_site.sort_values('id_site',inplace=True)
|
||
dfsite.sort_values('code_site',inplace=True)
|
||
|
||
|
||
# nom_site
|
||
site2023['nom_site'] = [
|
||
dfsite[dfsite.code_site==x].nom_site.values[0]
|
||
if x in [*dfsite.code_site] else None
|
||
for x in site2023.id_site_cen
|
||
]
|
||
site2023['nom_site'] = drop_specialchar(site2023['nom_site'].str.lower())
|
||
site2023['nom_site'] = site2023['nom_site'].str.upper()
|
||
# site_rnx_surface_m2
|
||
site2023.site_rnx_surface_m2 = dfsite\
|
||
.intersection(site_rnx.unary_union)\
|
||
.area.astype(int)
|
||
# ens (A FAIRE)
|
||
site2023['ens'] = site2023['ens'].fillna(1).astype(int)
|
||
# site_cdl
|
||
site2023.site_cdl = 0
|
||
# n2000_directive
|
||
site2023 = site2023.merge(dfsite[['code_site','geom']],how='left',left_on='id_site_cen',right_on='code_site')\
|
||
.drop(columns=['code_site'])
|
||
cnpe = gpd.read_file(path_source+receuil_path+shp_cnpe)
|
||
# site2023.loc[site2023.id_site_cen=='CNPE','geom'] = cnpe.iloc[0].geometry
|
||
site2023 = gpd.GeoDataFrame(site2023,geometry='geom',crs=2154)
|
||
|
||
site2023['is_zps'] = site2023.intersects(n2000_zps.unary_union)
|
||
site2023['is_zsc'] = site2023.intersects(n2000_zsc.unary_union)
|
||
site2023['n2000'] = site2023[['is_zps','is_zsc']].sum(axis=1)
|
||
site2023.loc[site2023.n2000==2,'n2000_directive'] = 'ZPS_ZSC'
|
||
site2023.loc[site2023.n2000==0,'n2000_directive'] = '0'
|
||
site2023.loc[site2023.is_zps & (site2023.n2000!=2),'n2000_directive'] = 'ZPS'
|
||
site2023.loc[site2023.is_zsc & (site2023.n2000!=2),'n2000_directive'] = 'ZSC'
|
||
site2023.drop(columns=['is_zps','is_zsc','n2000'],inplace=True)
|
||
# n2000_surface_m2
|
||
site2023['n2000_surface_m2'] = site2023\
|
||
.intersection(pd.concat([n2000_zps,n2000_zsc]).unary_union)\
|
||
.area.astype(int)
|
||
# terrain_militaire
|
||
site2023['terrain_militaire'] = (dfsite.type_site == 'MILI').astype(int)
|
||
# site_marin
|
||
site2023['site_marin'] = 0
|
||
# nb_contrat_agri
|
||
site2023['nb_contrat_agri'] = site2023['nb_contrat_agri'].fillna(0)
|
||
# nb_agri
|
||
site2023['nb_agri'] = site2023['nb_agri']\
|
||
.replace({'?':0})\
|
||
.fillna(0)\
|
||
.astype(int)
|
||
# surf_contra_m2
|
||
site2023['surf_contra_m2'] = site2023['surf_contra_m2'].fillna(0)
|
||
|
||
# code_milieu_princ
|
||
dict_milieu = dict(zip(lv_typmilie.libelle_type_milieu,lv_typmilie.code_type_milieu))
|
||
dfsite['milieux'].replace({'Gîtes à chiroptères et milieux souterrains':'Gîtes à chiroptères'}, inplace=True)
|
||
dfsite['code_milieu'] = dfsite.milieux.str.lower().replace(dict_milieu)
|
||
site2023['code_milieu_princ'] = [
|
||
dfsite[dfsite.code_site==x].code_milieu.values[0] if x in [*dfsite.code_site] else None for x in site2023.id_site_cen
|
||
]
|
||
|
||
# nature_site_inpn
|
||
site2023['nature_site_inpn'] = 'N'
|
||
# geol_site_inpn
|
||
site2023['geol_site_inpn'] = 'N'
|
||
# code_geol
|
||
site2023['code_geol'] = 21
|
||
# carto_habitats
|
||
site2023['carto_habitats'] = site2023['carto_habitats'].fillna(0)
|
||
# typo_carto_habitat
|
||
site2023['typo_carto_habitat'] = site2023['typo_carto_habitat'].fillna(0)
|
||
# surf_carto_habitat_m2
|
||
site2023['surf_carto_habitat_m2'] = site2023['surf_carto_habitat_m2'].fillna(0)
|
||
# date_crea_site
|
||
datemiss = site2023['date_crea_site'].isnull()
|
||
for s in site2023[datemiss].id_site_cen:
|
||
if s not in dfsite.code_site : pass
|
||
|
||
site2023.loc[site2023.id_site_cen==s,'date_crea_site'] = dfsite[dfsite.code_site==s].date_ajout
|
||
site2023['date_crea_site'] = pd.to_datetime(site2023['date_crea_site'],yearfirst=True, format='%Y/%m/%d')
|
||
# date_maj_site
|
||
site2023['date_maj_site'] = dfsite.date_maj.copy()
|
||
# nature_perimetre
|
||
site2023['nature_perimetre'] = site2023['nature_perimetre'].fillna(0)
|
||
# source_geom_site_nature
|
||
site2023['source_geom_site_nature'] = site2023['source_geom_site_nature'].fillna(0)
|
||
# source_geom_site_date
|
||
site2023['source_geom_site_date']
|
||
# echelle_num_site
|
||
site2023['echelle_num_site'] = site2023['echelle_num_site'].fillna(0)
|
||
# precision_num_site
|
||
site2023['precision_num_site'] = site2023['precision_num_site'].fillna('NE')
|
||
# gestionnaire_site
|
||
gestmiss = site2023['gestionnaire_site'].isna()
|
||
site2023.loc[gestmiss,'gestionnaire_site'] = 'CEN Isère'
|
||
# operateur
|
||
opmiss = site2023['operateur'].isna()
|
||
site2023.loc[opmiss,'operateur'] = 'FCEN'
|
||
# surf_libre_evolution_m2
|
||
site2023['surf_libre_evolution_m2'] = site2023['surf_libre_evolution_m2'].fillna(0)
|
||
# doc_gestion_presence
|
||
site2023['doc_gestion_presence'] = site2023['doc_gestion_presence'].fillna(0)
|
||
# doc_gestion_nom
|
||
site2023['doc_gestion_nom']
|
||
# doc_gestion_evaluation
|
||
site2023['doc_gestion_evaluation']
|
||
# doc_gestion_date_ini
|
||
# site2023['doc_gestion_date_ini'] = pd.to_datetime(site2023['doc_gestion_date_ini'],yearfirst=True)
|
||
site2023['doc_gestion_date_ini'] = update_date(site2023['doc_gestion_date_ini'])
|
||
# doc_gestion_date_maj
|
||
# site2023['doc_gestion_date_maj'] = pd.to_datetime(site2023['doc_gestion_date_maj'],yearfirst=True)
|
||
site2023['doc_gestion_date_maj'] = update_date(site2023['doc_gestion_date_maj'])
|
||
# doc_gestion_date_fin
|
||
# site2023['doc_gestion_date_fin'] = pd.to_datetime(site2023['doc_gestion_date_fin'],yearfirst=True)
|
||
site2023['doc_gestion_date_fin'] = update_date(site2023['doc_gestion_date_fin'])
|
||
# surf_doc_gestion_m2
|
||
site2023['surf_doc_gestion_m2'] = site2023['surf_doc_gestion_m2'].fillna(0)
|
||
# url_fiche_inpn
|
||
site2023['url_fiche_inpn']
|
||
# url_fiche_cen
|
||
site2023['url_fiche_cen']
|
||
# doc_justif_admin
|
||
site2023['doc_justif_admin']
|
||
# ouverture_public
|
||
site2023['ouverture_public']
|
||
# description_site
|
||
site2023['description_site']
|
||
# url_site_photo
|
||
site2023['url_site_photo']
|
||
# sensibilite
|
||
site2023['sensibilite']
|
||
# remq_sensibilite
|
||
site2023['remq_sensibilite']
|
||
|
||
site2023.sort_values('id_site_cen',inplace=True)
|
||
recueil_site.sort_values('id_site',inplace=True)
|
||
|
||
for c in recueil_site.columns:
|
||
if c not in site2023.columns: pass
|
||
|
||
notna = recueil_site[c].notna()
|
||
lstsite = recueil_site[notna].id_site.tolist()
|
||
s_2023 = site2023.id_site_cen.isin(lstsite)
|
||
r_2023 = recueil_site.id_site.isin(lstsite)
|
||
site2023.loc[s_2023,c] = recueil_site[r_2023][c]
|
||
|
||
# test_add = ~recueil_site.id_site.isin(site2023.id_site_cen)
|
||
# add_site = recueil_site[test_add].rename(columns={
|
||
# 'id_site':'id_site_cen'
|
||
# })
|
||
# SITE2023 = pd.concat([site2023,recueil_site[test_add]])
|
||
|
||
for c in site2023.columns:
|
||
if site2023[c].dtype==float:
|
||
try:
|
||
site2023[c] = site2023[c].astype(int)
|
||
except:
|
||
pass
|
||
|
||
# Mise au format des dates
|
||
date_cols = site2023.columns[site2023.columns.str.contains('date')]
|
||
for c in date_cols:
|
||
site2023[c] = pd.to_datetime(site2023[c],yearfirst=True).dt.strftime('%Y/%m/%d')
|
||
# site2023[c] = site2023[c].dt.strftime('%Y/%m/%d')
|
||
|
||
|
||
|
||
shp2023 = site2023[['id_site_cen','id_site_fcen','date_crea_site','date_maj_site','geom']].copy()
|
||
shp2023['date_maj_s'] = [
|
||
shp2023.date_maj_site[i] if pd.notna(shp2023.date_maj_site[i]) else shp2023.date_crea_site[i] for i in shp2023.index
|
||
]
|
||
shp2023.drop(columns=['date_crea_site','date_maj_site'],inplace=True)
|
||
shp2023.rename(columns={'id_site_fcen':'id_fcen'},inplace=True)
|
||
|
||
# Ecriture des fichiers finaux
|
||
site2023.drop(columns=['geom','id_site','gestionnaire']).to_csv(path_source+receuil_path+tdb_sites_csv%annee,index=False)
|
||
shp2023.to_file(
|
||
(path_source+receuil_path+tdb_sites_csv[:-3]+'shp')%annee,
|
||
index=False)
|
||
# from pycen import update_to_sql,
|
||
# site = get_site()
|
||
# darp = site.code_site.isin(['DARN','PEYR'])
|
||
# zi = site.type_zonage=='ZI'
|
||
# zo = site.type_zonage=='ZO'
|
||
# DARP = gpd.GeoDataFrame({
|
||
# 'code_site':['DARP','DARP'],
|
||
# 'type_zonage': ['ZI','ZO'],
|
||
# },
|
||
# geometry=[
|
||
# site[darp&zi].unary_union,site[darp&zo].unary_union
|
||
# ],crs=2154)\
|
||
# .rename_geometry('geom')
|
||
|
||
|
||
# DARP['surface_ha'] = round(DARP.area / 10000,2)
|
||
# update_to_sql(cnpe,con,'c_sites_zonages','sites','code_site') |