173 lines
6.1 KiB
Python
173 lines
6.1 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: UTF-8 -*-
|
|
#Nom : : recup_ps.py
|
|
#Description :
|
|
#Copyright : 2021, CEN38
|
|
#Auteur : Colas Geier
|
|
#Version : 1.0
|
|
|
|
|
|
|
|
# from _typeshed import IdentityFunction
|
|
import pandas as pd
|
|
import geopandas as gpd
|
|
from sqlalchemy.engine import URL
|
|
from sqlalchemy import create_engine
|
|
import pycen
|
|
# Parametres bdd IN
|
|
user = 'cen_admin'
|
|
pwd = '#CEN38@venir'
|
|
adr = '192.168.0.189'
|
|
base = 'bd-cen-38'
|
|
url = URL.create('postgresql+psycopg2',
|
|
username=user,
|
|
password=pwd,
|
|
host=adr,
|
|
database=base,
|
|
)
|
|
|
|
dic = {
|
|
'Ssp':'IDFINAL',
|
|
'cod_cb':'code_cb',
|
|
'ccorineb':'code_cb',
|
|
'id_orig':'idorig',
|
|
'pourcentag':'part',
|
|
'carte_cb':'type',
|
|
'lib_cb':'libelle',
|
|
'id':'idorig',
|
|
'hectare':'hectares',
|
|
'nbre_hc':'hectares',
|
|
'observat':'obs',
|
|
'surf_m2':'surface',
|
|
'statut_car':'statutcart',
|
|
}
|
|
# table = '"PS_CHAMBARAN_CEN38_2013"'
|
|
con = create_engine(url)
|
|
path = '/home/colas/Documents/9_PROJETS/2_PS/CBNA/'
|
|
fV = path + 'PNR_Vercors_SHP/PNRV_data_04_2012.dbf'
|
|
fgV = path + 'PNR_Vercors_SHP/PNRV_poly_10_2008_L93_region.shp'
|
|
nV = 'cr_VERCORS_habitats_CBNA_1999-2007'
|
|
fC = path + 'PNR_Chartreuse_SHP/Chartreuse_2012_data.dbf'
|
|
fgC = path + 'PNR_Chartreuse_SHP/Chartreuse_2012_poly_region.shp'
|
|
nC = 'cr_CHARTREUSE_habitats_CBNA_2000-2012'
|
|
fT = path + 'Trieves_Vercors/TRIEVES_DATA.dbf'
|
|
fgT = path + 'Trieves_Vercors/TRIEVES_VERCORS.shp'
|
|
nT = 'cr_TRIEVES+VERCORS_habitats_CBNA'
|
|
|
|
fE = path + 'PNE/DELPHINE_DATA.dbf'
|
|
fgE = path + 'PNE/DELPHINE_POLY_L93.shp'
|
|
nE = 'cr_ECRIN_habitats_CBNA'
|
|
|
|
|
|
|
|
fileG = fgV
|
|
file = fV
|
|
name = nV
|
|
|
|
gdf = gpd.read_file(fileG)
|
|
gdf = pycen.tools.dropZ(gdf,geom_col='geometry')
|
|
dbf = gpd.read_file(file)
|
|
dbf.rename(columns=dic,inplace=True)
|
|
gdf.rename(columns=dic,inplace=True)
|
|
if gdf.columns[0] != 'IDFINAL':
|
|
gdf.rename(columns={gdf.columns[0]:'IDFINAL'},inplace=True)
|
|
# if dbf.columns[0] != 'IDFINAL':
|
|
# dbf.rename(columns={dbf.columns[0]:'IDFINAL'},inplace=True)
|
|
dbf.sort_values('IDFINAL', inplace=True)
|
|
gdf.sort_values('IDFINAL', inplace=True)
|
|
dbf.drop(columns=['geometry'], inplace=True)
|
|
same_cols = gdf.columns[gdf.columns.isin(dbf.columns)]
|
|
gdf.rename_geometry('geom',inplace=True)
|
|
dbf.drop_duplicates(inplace=True)
|
|
dbf.reset_index(drop=True,inplace=True)
|
|
dbf.columns = dbf.columns.str.lower()
|
|
gdf.columns = gdf.columns.str.lower()
|
|
dbf.rename(columns=dic,inplace=True)
|
|
gdf.rename(columns=dic,inplace=True)
|
|
dbf.drop(columns=[
|
|
'clemax','cle_code','codevegfin','code_final','lettre','hectares','lib_delphi','lien_habit','lien_typol','evolutif_m','codedelphi',
|
|
'cb_carte','cb_2','ch_eur25','ch_stat','cb_choix','choix','codedelphine',], inplace=True, errors='ignore')
|
|
gdf.drop(columns=[
|
|
'surface','hectares','support','echelle','perimetr',
|
|
'part','lettre','phyto','code_cb','cd_eur15','statut','type1','type2','cod_mil',], inplace=True, errors='ignore')
|
|
dbf.loc[dbf.statut=='Habitat non désigné','statut'] = None
|
|
dbf.loc[dbf.statut=='A préciser','statut'] = None
|
|
dbf.loc[dbf.code_cb=='A préciser','code_cb'] = None
|
|
dbf.loc[dbf.phyto=='A préciser','phyto'] = None
|
|
dbf.loc[dbf.phyto=='Néant','phyto'] = None
|
|
#### PNE ####
|
|
# dbf.loc[dbf.eur25=='Néant','eur25'] = None
|
|
# dbf.loc[dbf.l_eur25=='Néant','l_eur25'] = None
|
|
# dbf.loc[dbf.code_cb=='Néant','code_cb'] = None
|
|
# dbf.part = dbf.part.astype(int).astype(str)
|
|
# dbf.sort_values(['idfinal','part'], ascending=[True,False], inplace=True)
|
|
|
|
# dbf.dropna(axis=0, subset=['code_cb'], inplace=True)
|
|
|
|
dbf.sort_values(['idfinal','part'],ascending=[True,False],inplace=True)
|
|
tt = [list(range(1,dbf[dbf.idfinal==i].shape[0]+1)) for i in dbf.idfinal.unique() ]
|
|
dbf['num'] = [item for sublist in tt for item in sublist]
|
|
|
|
# dbf.rename(columns={'code_eur27':'code_eur27.'}, inplace=True)
|
|
dbf.rename(columns={'code_eur15':'code_eur15.'}, inplace=True)
|
|
# dbf.rename(columns={'eur25':'code_eur25.'}, inplace=True)
|
|
# dbf.rename(columns={'l_eur25':'l_eur25.'}, inplace=True)
|
|
|
|
gdf.set_index(['idfinal','idorig','secteur'], inplace=True)
|
|
dbf.set_index(['idfinal','idorig','secteur'], inplace=True)
|
|
# gdf.set_index(['idfinal','idorig'], inplace=True)
|
|
# dbf.set_index(['idfinal','idorig'], inplace=True)
|
|
dbf.set_index('num',append=True, inplace=True)
|
|
dbf2 = dbf.unstack().copy()
|
|
dbf2.dropna(axis=1,how='all', inplace=True)
|
|
lst_col = dbf2.columns.get_level_values(0).unique()
|
|
stt = ['statut%s'%i for i in range(1,dbf2.iloc[:,dbf2.columns.get_level_values(0)=='statut'].shape[1]+1)]
|
|
stc = ['statutcart%s'%i for i in range(1,dbf2.iloc[:,dbf2.columns.get_level_values(0)=='statutcart'].shape[1]+1)]
|
|
ste = ['site%s'%i for i in range(1,dbf2.iloc[:,dbf2.columns.get_level_values(0)=='site'].shape[1]+1)]
|
|
dbf2.columns = [x+str(j) for x,j in dbf2.columns]
|
|
for s in stt:
|
|
dbf2.loc[dbf2.statut1.isna(),'statut1'] = dbf2.loc[dbf2.statut1.isna(),s]
|
|
for s in stc:
|
|
dbf2.loc[dbf2.statutcart1.isna(),'statutcart1'] = dbf2.loc[dbf2.statutcart1.isna(),s]
|
|
dbf2.drop(columns=[
|
|
*stt[1:],*ste[1:],*stc[1:]
|
|
],inplace=True)
|
|
dbf2.rename(columns={
|
|
'statut1':'statut',
|
|
'statutcart1':'statutcart',
|
|
'site1':'site'
|
|
}, inplace=True)
|
|
dbf2.loc[dbf2.statut.isna(),'statut'] = 'N.D'
|
|
dbf2.part1 = dbf2.part1.astype(int)
|
|
dbf2.columns = dbf2.columns.str.replace('code_cb','code_hab',regex=True)
|
|
dbf2.columns = dbf2.columns.str.replace('part','n_hab',regex=True)
|
|
df = pd.merge(gdf,dbf2,how='inner',right_index=True,left_index=True)
|
|
df.geom = df.buffer(0)
|
|
df = pycen.tools.Polygons_to_MultiPolygon(df)
|
|
df.to_postgis(
|
|
name=name,
|
|
con=con,
|
|
schema='habitat',
|
|
if_exists='replace',
|
|
index=True,
|
|
geom_col='geom',
|
|
)
|
|
|
|
|
|
|
|
|
|
# dbf2[dbf2.columns[dbf2.columns.str.contains('type')]]
|
|
# dbf2[dbf2.columns[dbf2.columns.str.contains('phyto')]]
|
|
# dbf2[dbf2.columns[dbf2.columns.str.contains('phyto')]][~dbf2.phyto12.isna()]
|
|
# dbf2[dbf2.columns[dbf2.columns.str.contains('libelle')]]
|
|
# dbf2[dbf2.columns[dbf2.columns.str.contains('libelle')]][~dbf2.libelle13.isna()]
|
|
# dbf2[dbf2.columns[dbf2.columns.str.contains('codeveg')]]
|
|
# dbf2[dbf2.columns[dbf2.columns.str.contains('codeveg')]][~dbf2.codeveg13.isna()]
|
|
# dbf2.iloc[:,dbf2.columns.get_level_values(0)=='type']
|
|
# dbf2.iloc[:,dbf2.columns.get_level_values(0)=='statut']
|
|
|
|
|
|
# dbf2.columns = [x+str(j) for x,j in dbf2.columns]
|
|
# dbf2.part1 = dbf2.part1.astype(int)
|
|
|