187 lines
26 KiB
Python
187 lines
26 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: UTF-8 -*-
|
|
#Nom : : del_psduplicated.py
|
|
#Description : Suppessio, des géométries en doubon sous un nom différents et avec des erreurs d'intégration des données
|
|
#Copyright : 2022-07-20, CEN38
|
|
#Auteur : Colas Geier
|
|
#Version : 1.0
|
|
|
|
import geopandas as gpd
|
|
import pycen
|
|
from pycen import con,update_to_sql
|
|
|
|
df1 = gpd.read_postgis('SELECT * FROM ps.v_covered_ps ORDER BY 2',con,geom_col='geom_old')
|
|
df2 = gpd.read_postgis('SELECT * FROM ps.v_covered_ps ORDER BY 2',con,geom_col='geom_new')
|
|
# df.reset_index(inplace=True, drop=False)
|
|
df1.to_postgis('covered_ps_old',con,'ps','replace',False,geom_col='geom_old')
|
|
df2.to_postgis('covered_ps_new',con,'ps','replace',False,geom_col='geom_new')
|
|
|
|
df = df1.copy()
|
|
|
|
# ('38BDAU0907','38BIEV0032','38BIEV0033','38BDAU0905','38PCHA1088','38PCHA1089','38PCHA1002','38PCHA0908','38VERC3112','38PCHA0316','38VERC3171','38PCHA0325','38VERC2662','38PCHA0232','38PCHA0244','38VERC2732','38PCHA1086','38PCHA1087','38VERC3206','38PCHA0332','38PCHA0297','38VERC3013','38VERC2983','38PCHA0291','38VERC2961','38PCHA0286','38VERC3212','38PCHA0333','38PCHA0326','38VERC3175','38PCHA1005','38PCHA0018','38PCHA0474','38VERC3681','38PCHA0044','38PCHA1007','38PCHA0910','38PCHA1003','38PCHA0274','38VERC2902','38PCHA0032','38PCHA1006','38PCHA0121','38VERC1824','38VERC1743','38PCHA0111','38PCHA1004','38PCHA0928','38VERC1848','38PCHA0123','38PCHA0057','38PCHA1008','38PCHA0065','38PCHA1009','38BDAU0908','38VERC2008','38VERC2495','38BELL0154','38GRES0637','38VERC3451','38VERC0383','38VERC3222','38VERC3348','38GRES0629','38VERC2912','38CHAR0502','38CHAR0045','38VERC2058','38VERC1931','38MATH0010','38VERC1924','38CHAR0040','38GRES0006','38CHAR0900','38BELL0030','38VERC2163','38GRES0066','38VERC2100','38CHAR0049','38VERC2187','38GRES0077','38VERC2272','38VERC2807','38CHAR0079','38VERC2658','38CHAR0066','38CHAR0100','38VERC3008','38CHAR0903','38BELL0005','38CHAR0901','38CHAR0008','38VERC2414','38VERC0219','38VERC2514','38GRES0084','38VERC3076','38GRES0119','38VERC3311','38GRES0132','38VERC2718','38CHAR0072','38VERC3205','38CHAR0115','38VERC3108','38BELL0049','38VERC2377','38VERC0215','38VERC2387','38VERC0216','38VERC2431','38BELL0035','38VERC2408','38GRES0080','38GRES0686','38VERC3623','38CHAR0468','38VERC2384','38MATH0111','38VERC2649','38BELL0198','38VERC2661','38GRES0437','38VERC1748','38VERC2614','38GRES0472','38GRES0462','38VERC2446','38TRIE0215','38VERC2356','38VERC2329','38TRIE0211','38VERC2315','38TRIE0210','38TRIE0222','38VERC2405','38VERC2439','38TRIE0227','38TRIE0228','38VERC2443','38TRIE0229','38VERC2455','38CHAR0902','38TRIE0022','38GRES0141','38VERC3408','38VERC3159','38CHAR0113','38CHAR0001','38CHAR0899','38CHAR0905','38GRES0028','38CHAR0906','38CHAR0012','38CHAR0017','38CHAR0908','38GRES0136','38VERC3376','38VERC1668','38GRES0053','38GRES0127','38VERC3197','38VERC1549','38GRES0045','38GRES0030','38CHAR0907','38GRES1039','38GRES0031','38BIEV0004','38VERC3399','38CHAR0904','38GRES0025','38CHAR0029','38VERC1533','38CHAR0133','38VERC3354','38CHAR0031','38VERC1618','38VERC3369','38CHAR0135','38VERC3464','38GRES0143','38GRES0016','38GRES1038','38BELL0192','38VERC2556','38VERC2513','38CHAR0402','38BELL0164','38BELL0492','38MATH0024','38VERC2487','38VERC2646','38TRIE0250','38VERC3523','38TRIE0377','38BELL0489','38BELL0155','38BELL0491','38MCHA0009','38BELL0490','38BELL0162','38VERC1687','38CHAR0034','38VERC1721','38BELL0025','38BELL0016','38CHAR0909','38MATH0005','38VERC1756','38BELL0043','38VERC2641','38CHAR0027','38CHAR0910','38GRES0089','38VERC2601','38GRES0092','38VERC2629')
|
|
# ('38BIEV0032','38BDAU0907','38BDAU0905','38BIEV0033','38PCHA1089','38PCHA1088','38PCHA1002','38PCHA0908','38VERC3112','38PCHA0316','38VERC3171','38PCHA0325','38VERC2662','38PCHA0232','38VERC2732','38PCHA0244','38PCHA1086','38PCHA1087','38VERC3206','38PCHA0332','38VERC3013','38PCHA0297','38PCHA0291','38VERC2983','38VERC2961','38PCHA0286','38PCHA0333','38VERC3212','38PCHA0326','38VERC3175','38PCHA1005','38PCHA0018','38VERC3681','38PCHA0474','38PCHA1007','38PCHA0044','38PCHA0910','38PCHA1003','38VERC2902','38PCHA0274','38PCHA1006','38PCHA0032','38VERC1824','38PCHA0121','38PCHA0111','38VERC1743','38PCHA0928','38PCHA1004','38VERC1848','38PCHA0123','38PCHA0057','38PCHA1008','38PCHA1009','38PCHA0065','38BDAU0908','38VERC2008','38VERC2495','38BELL0154','38GRES0637','38VERC3451','38VERC0383','38VERC3222','38GRES0629','38VERC3348','38CHAR0502','38VERC2912','38VERC3623','38GRES0686','38CHAR0468','38VERC2384','38TRIE0215','38VERC2356','38VERC2329','38TRIE0211','38VERC2315','38TRIE0210','38VERC2405','38TRIE0222','38VERC2439','38TRIE0227','38VERC2443','38TRIE0228','38VERC2455','38TRIE0229','38VERC2487','38MATH0024','38VERC2646','38TRIE0250','38VERC3523','38TRIE0377')
|
|
# ('38BDAU0907','38BIEV0032','38BDAU0905','38BIEV0033','38PCHA1089','38PCHA1088','38PCHA1002','38PCHA0908','38PCHA0316','38VERC3112','38VERC3171','38PCHA0325','38PCHA0232','38VERC2662','38VERC2732','38PCHA0244','38PCHA1086','38PCHA1087','38VERC3206','38PCHA0332','38VERC3013','38PCHA0297','38PCHA0291','38VERC2983','38PCHA0286','38VERC2961','38PCHA0333','38VERC3212','38PCHA0326','38VERC3175','38PCHA0018','38PCHA1005','38PCHA0474','38VERC3681','38PCHA0044','38PCHA1007','38PCHA0910','38PCHA1003','38VERC2902','38PCHA0274','38PCHA0032','38PCHA1006','38VERC1824','38PCHA0121','38VERC1743','38PCHA0111','38PCHA0928','38PCHA1004','38PCHA0123','38VERC1848','38PCHA0057','38PCHA1008','38PCHA0065','38PCHA1009')
|
|
# ('38BDAU0907','38BIEV0032','38BDAU0905','38BIEV0033','38PCHA1088','38PCHA1089','38PCHA0316','38VERC3112','38PCHA0325','38VERC3171','38PCHA0232','38VERC2662','38PCHA0244','38VERC2732','38PCHA1087','38PCHA1086','38VERC3206','38PCHA0332','38VERC3013','38PCHA0297','38PCHA0291','38VERC2983','38PCHA0286','38VERC2961','38PCHA0333','38VERC3212','38VERC3175','38PCHA0326','38PCHA0018','38PCHA1005','38VERC3681','38PCHA0474','38PCHA0044','38PCHA1007','38PCHA0274','38VERC2902','38PCHA1006','38PCHA0032','38VERC1824','38PCHA0121','38VERC1743','38PCHA0111','38VERC1848','38PCHA0123','38PCHA0057','38PCHA1008','38PCHA0065','38PCHA1009')
|
|
# ('38BDAU0907','38BIEV0032','38BDAU0905','38BIEV0033','38PCHA1089','38PCHA1088','38PCHA0316','38VERC3112','38PCHA0325','38VERC3171','38PCHA0232','38VERC2662','38VERC2732','38PCHA0244','38PCHA1086','38PCHA1087','38VERC3206','38PCHA0332','38VERC3013','38PCHA0297','38VERC2983','38PCHA0291','38VERC2961','38PCHA0286','38PCHA0333','38VERC3212','38VERC3175','38PCHA0326','38PCHA0018','38PCHA1005','38PCHA0274','38VERC2902','38PCHA0121','38VERC1824','38PCHA0111','38VERC1743','38VERC1848','38PCHA0123','38PCHA1008','38PCHA0057','38PCHA1009','38PCHA0065')
|
|
# ('38BIEV0032','38BDAU0907','38BIEV0033','38BDAU0905','38PCHA1088','38PCHA1089','38PCHA1087','38PCHA1086')
|
|
|
|
v_ps = gpd.read_postgis(
|
|
"SELECT * FROM ps.v_pelouseseches WHERE site_code in ('%s') ORDER BY source,geom"%"','".join(df.id_site.tolist())
|
|
,pycen.con)
|
|
v_ps.set_index(['site_code','district_nat'],inplace=True)
|
|
v_ps.dropna(axis=1,how='all', inplace=True)
|
|
v_ps.fillna('NA', inplace=True)
|
|
|
|
for col in v_ps.columns[v_ps.columns.str.startswith('auteur')]:
|
|
tmp = v_ps[col].str.split(';')
|
|
for x in tmp: x.sort()
|
|
v_ps[col] = [';'.join(x) for x in tmp]
|
|
|
|
vp_clean = v_ps.drop_duplicates()
|
|
|
|
|
|
v_ps.loc[~v_ps.index.isin(vp_clean.index)]
|
|
v_ps.loc[~v_ps.index.isin(vp_clean.index)].droplevel(-1).index.tolist()
|
|
# ('38CHAR0915','38CHAR0919','38CHAR0922','38CHAR0936','38CHAR0938','38CHAR0940','38CHAR1195','38CHAR1197','38CHAR1203','38CHAR1220','38CHAR1221','38CHAR1222','38CHAR1223','38CHAR1224','38CHAR1225','38CHAR1226','38CHAR1227','38CHAR1228','38CHAR1229','38CHAR1230','38CHAR1231','38CHAR1232','38CHAR1233','38CHAR1234','38CHAR1235','38CHAR1236','38CHAR1237','38CHAR1238','38CHAR1239','38CHAR1240','38CHAR1241','38CHAR1242','38CHAR1243','38CHAR1244','38CHAR1245','38GRES1073','38GRES1074','38GRES1075','38MCHA0049','38PCHA1083','38PCHA1208','38PCHA1210','38PCHA1215','38PCHA1221','38TRIE1173','38TRIE2416','38TRIE2481','38TRIE2548','38TRIE2563','38TRIE2590','38TRIE2592','38TRIE2594','38TRIE2595','38TRIE2596','38TRIE2597','38TRIE2598','38TRIE2599','38TRIE2602','38TRIE2604','38TRIE2605','38TRIE2607','38TRIE2608','38TRIE2610','38TRIE2611','38TRIE2614','38TRIE2615','38TRIE2616','38TRIE2618','38TRIE2619','38TRIE2620','38TRIE2621','38TRIE2622','38TRIE2623','38TRIE2624','38TRIE2625','38TRIE2626','38TRIE2627','38TRIE2628','38TRIE2629','38TRIE2631','38TRIE2632','38TRIE2633','38TRIE2634','38TRIE2635','38TRIE2636','38TRIE2637','38TRIE2638','38TRIE2639','38TRIE2640','38TRIE2641','38TRIE2644','38TRIE2645','38TRIE2646','38TRIE2647','38TRIE2648','38TRIE2650','38TRIE2651','38TRIE2652','38TRIE2653','38TRIE2654','38TRIE2656','38TRIE2657','38TRIE2658','38TRIE2659','38TRIE2662','38TRIE2663','38TRIE2664','38TRIE2665','38TRIE2666','38TRIE2667','38TRIE2668','38TRIE2669','38TRIE2670','38TRIE2671','38TRIE2672','38TRIE2674','38TRIE2675','38TRIE2676','38TRIE2677','38TRIE2678','38TRIE2679','38TRIE2680','38TRIE2682','38TRIE2683','38TRIE2688','38TRIE2689','38TRIE2690','38TRIE2693','38TRIE2695','38TRIE2696','38TRIE2697','38TRIE2698','38TRIE2699','38TRIE2700','38TRIE2701','38TRIE2702','38TRIE2703','38TRIE2704','38TRIE2705','38TRIE2707','38TRIE2708','38TRIE2710','38TRIE2712','38TRIE2713','38TRIE2714','38TRIE2715','38TRIE2720','38TRIE2721','38TRIE2722','38TRIE2723','38TRIE2724','38TRIE2727','38TRIE2731','38TRIE2735','38TRIE2736','38TRIE2739','38TRIE2741','38TRIE2743','38TRIE2744','38TRIE2749','38TRIE2750','38TRIE2758','38TRIE2760','38TRIE2762','38TRIE2764','38TRIE2765','38TRIE2766','38TRIE2767','38TRIE2768','38TRIE2769','38TRIE2771','38TRIE2776','38TRIE2780','38TRIE2788','38VERC3691','38VERC3692','38VERC3698','38VERC3701','38VERC3903','38VERC3904','38VERC3906','38VERC3921','38VERC3924','38VERC3936','38VERC3937','38VERC3940','38VERC3941','38VERC3943','38VERC3945','38VERC3946','38VERC3948','38VERC3949','38VERC3950','38VERC3953','38VERC4017','38VERC4104','38VERC4123','38VERC4124','38VERC4180','38VERC4187','38VERC4188','38VERC4197','38VERC4205','38VERC4218','38VERC4290','38VERC4294','38VERC4364','38VERC4392','38VERC4396','38VERC4405','38VERC4406','38VERC4407','38VERC4408','38VERC4409','38VERC4446','38VERC4447','38VERC4448','38VERC4453','38VERC4462','38VERC4464','38VERC4469','38VERC4484','38VERC4485','38VERC4492','38VERC4498','38VERC4512','38VERC4526','38VERC4527','38VERC4530','38VERC4544','38VERC4558','38VERC4576','38VERC4577','38VERC4578','38VERC4579','38VERC4585','38VERC4588','38VERC4590','38VERC4627','38VERC4628','38VERC4636','38VERC4638','38VERC4639','38VERC4640','38VERC4643','38VERC4649','38VERC4650','38VERC4654','38VERC4655','38VERC4656','38VERC4657','38VERC4658','38VERC4659','38VERC4660','38VERC4661','38VERC4662','38VERC4663','38VERC4664','38VERC4665','38VERC4666','38VERC4667','38VERC4668','38VERC4669','38VERC4670','38VERC4671','38VERC4672','38VERC4673','38VERC4674','38VERC4675','38VERC4676','38VERC4678','38VERC4679','38VERC4680','38VERC4681','38VERC4685','38VERC4686','38VERC4692')
|
|
|
|
|
|
'38GRES1029','38GRES1031','38VERC0811','38VERC0861','38VERC1045','38VERC1427','38VERC1433','38VERC1453','38VERC1456','38VERC1465','38VERC1489','38VERC1494','38VERC3704','38VERC3708','38VERC4129','38VERC4134'
|
|
'38BIEV0032','38BDAU0907','38BDAU0905','38BIEV0033','38PCHA1089','38PCHA1088','38PCHA1002','38PCHA0908','38VERC3112','38PCHA0316','38PCHA0906','38PCHA1001','38PCHA0325','38VERC3171','38PCHA0232','38VERC2662','38VERC2732','38PCHA0244','38PCHA1086','38PCHA1087','38PCHA0332','38VERC3206','38VERC3013','38PCHA0297','38VERC2983','38PCHA0291','38VERC2961','38PCHA0286','38PCHA0333','38VERC3212','38VERC3175','38PCHA0326','38PCHA0018','38PCHA1005','38PCHA0474','38VERC3681','38PCHA0044','38PCHA1007','38PCHA0910','38PCHA1003','38PCHA0274','38VERC2902','38PCHA1006','38PCHA0032','38PCHA0121','38VERC1824','38PCHA0111','38VERC1743','38PCHA1004','38PCHA0928','38PCHA0123','38VERC1848','38PCHA1008','38PCHA0057','38PCHA0065','38PCHA1009','38VERC2008','38BDAU0908','38BELL0154','38VERC2495','38GRES0637','38VERC3451','38VERC0383','38VERC3222','38VERC3348','38GRES0629','38VERC2912','38CHAR0502','38CHAR0045','38VERC2058','38VERC1931','38MATH0010','38CHAR0040','38VERC1924','38GRES0006','38CHAR0900','38VERC2163','38BELL0030','38VERC2100','38GRES0066','38VERC2187','38CHAR0049','38VERC2272','38GRES0077','38VERC2807','38CHAR0079','38VERC2658','38CHAR0066','38CHAR0100','38VERC3008','38BELL0005','38CHAR0903','38CHAR0008','38CHAR0901','38VERC0219','38VERC2414','38VERC2514','38GRES0084','38VERC3076','38GRES0119','38GRES0132','38VERC3311','38CHAR0072','38VERC2718','38CHAR0115','38VERC3205','38VERC3108','38BELL0049','38VERC0215','38VERC2377','38VERC2387','38VERC0216','38BELL0035','38VERC2431','38GRES0080','38VERC2408','38GRES0686','38VERC3623','38VERC2384','38CHAR0468','38MATH0111','38VERC2649','38VERC2661','38BELL0198','38VERC1748','38GRES0437','38GRES0472','38VERC2614','38GRES0462','38VERC2446','38TRIE0215','38VERC2356','38TRIE0211','38VERC2329','38VERC2315','38TRIE0210','38TRIE0222','38VERC2405','38VERC2439','38TRIE0227','38VERC2443','38TRIE0228','38VERC2455','38TRIE0229','38TRIE0022','38CHAR0902','38GRES0141','38VERC3408','38CHAR0113','38VERC3159','38CHAR0001','38CHAR0899','38CHAR0905','38GRES0028','38CHAR0012','38CHAR0906','38CHAR0017','38CHAR0908','38GRES0214','38VERC2141','38VERC3072','38VERC0290','38VERC3376','38GRES0136','38GRES0053','38VERC1668','38GRES0127','38VERC3197','38GRES0183','38GRES1040','38VERC1549','38GRES0045','38GRES0030','38CHAR0907','38GRES1039','38GRES0031','38VERC3399','38BIEV0004','38CHAR0904','38GRES0025','38VERC1533','38CHAR0029','38CHAR0133','38VERC3354','38CHAR0031','38VERC1618','38MATH0083','38VERC1830','38GRES0203','38VERC1858','38MATH0082','38VERC1820','38CHAR0176','38GRES1041','38GRES0251','38VERC2935','38VERC0254','38VERC1926','38VERC3369','38CHAR0135','38VERC3464','38GRES0143','38GRES1038','38GRES0016','38VERC3139','38GRES0278','38VERC2556','38BELL0192','38VERC2513','38CHAR0402','38BELL0164','38BELL0492','38MATH0024','38VERC2487','38TRIE0250','38VERC2646','38TRIE0377','38VERC3523','38BELL0489','38BELL0155','38BELL0491','38MCHA0009','38BELL0162','38BELL0490','38CHAR0034','38VERC1687','38VERC1721','38BELL0025','38BELL0016','38CHAR0909','38MATH0005','38VERC1756','38VERC2641','38BELL0043','38CHAR0027','38CHAR0910','38VERC2601','38GRES0089','38GRES0092','38VERC2629'
|
|
|
|
s = gpd.pd.Series([
|
|
'CHA01','CHA01','CHA17','CHA17','STAP01','STAP01','CHE30','CHE30','STBO03','STBO03','STAN26','STAN26','STAN36','STAN36','STAN34','STAN34','STBO05','STBO05','STBO06','STBO06','STLA039','STLA039','STAN22','STAN22','MON50','MON50','MON56','MON56','MON55','MON55','STLA063','STLA063','STLA070','STLA070',
|
|
])
|
|
|
|
sql = '''
|
|
SELECT
|
|
v1.site_code,
|
|
v1.geom,
|
|
v1."source",
|
|
v1.rmq_usage_process,
|
|
CASE WHEN v1.id_origine IS NULL THEN v2.id_origine ELSE v1.id_origine END id_origine
|
|
--v1.id_origine --, v2.geom geom_new
|
|
FROM ps.v_pelouseseches v1, ps.v_pelouseseches v2
|
|
WHERE v1.geom = v2.geom
|
|
AND v1.site_code <> v2.site_code
|
|
ORDER BY 3,2;
|
|
'''
|
|
upd = gpd.read_postgis(sql,con)
|
|
upd.drop_duplicates(inplace=True)
|
|
upd.rename(columns={'site_code':'id_site'},inplace=True)
|
|
upd.loc[upd.source=='PS_CHAMBARAN_CEN38_2013','rmq_usage_process'] = \
|
|
upd.loc[upd.source=='PS_CHAMBARAN_CEN38_2013','rmq_usage_process'].replace(["\|",'\/','fasciès'],[" x "," x ",'faciès'],regex=True)
|
|
upd = upd[['id_site','rmq_usage_process']]
|
|
update_to_sql(upd,con,'r_sites_geom','sites','id_site')
|
|
|
|
'38MCHA0009','38BELL0491','38BELL0164','38BELL0492','38BELL0154','38VERC2495','38GRES1041','38CHAR0176','38VERC2614','38GRES0472','38VERC2649','38MATH0111','38BELL0198','38VERC2661','38GRES0437','38VERC1748','38MATH0082','38VERC1820','38VERC1830','38MATH0083','38PCHA1087','38PCHA1088','38PCHA1086','38PCHA1089','38VERC1858','38GRES0203','38BDAU0908','38VERC2008','38VERC1926','38VERC0254','38GRES0462','38VERC2446','38VERC2513','38CHAR0402','38BELL0192','38VERC2556','38VERC2384','38CHAR0468','38CHAR0502','38VERC2912','38GRES0214','38VERC2141','38VERC2935','38GRES0251','38VERC3222','38VERC0383','38VERC3072','38VERC0290','38GRES0637','38VERC3451','38VERC3348','38GRES0629','38GRES0278','38VERC3139','38VERC3623','38GRES0686','38BELL0489','38BELL0155','38BDAU0905','38BDAU0907','38BIEV0032','38BIEV0033','38GRES0183','38GRES1040','38BELL0490','38BELL0162','38VERC2629','38GRES0092','38VERC2058','38CHAR0045','38CHAR0115','38VERC3205','38CHAR0901','38CHAR0008','38VERC2431','38BELL0035','38GRES0080','38VERC2408','38VERC0216','38VERC2387','38VERC0215','38VERC2377','38BELL0043','38VERC2641','38CHAR0072','38VERC2718','38CHAR0066','38VERC2658','38CHAR0100','38VERC3008','38BELL0049','38VERC3108','38BELL0005','38CHAR0903','38GRES0119','38VERC3076','38GRES0084','38VERC2514','38CHAR0909','38BELL0016','38VERC3311','38GRES0132','38VERC1687','38CHAR0034','38VERC1756','38MATH0005','38VERC2807','38CHAR0079','38VERC2414','38VERC0219','38BELL0025','38VERC1721','38CHAR0910','38CHAR0027','38GRES0077','38VERC2272','38VERC2187','38CHAR0049','38GRES0066','38VERC2100','38VERC2163','38BELL0030','38GRES0089','38VERC2601','38GRES0006','38CHAR0900','38CHAR0040','38VERC1924','38MATH0010','38VERC1931','38VERC3112','38PCHA0316','38VERC3171','38PCHA0325','38PCHA0906','38PCHA1001','38VERC2732','38PCHA0244','38TRIE0250','38VERC2646','38VERC3399','38BIEV0004','38CHAR0904','38GRES0025','38CHAR0031','38VERC1618','38CHAR0908','38CHAR0017','38GRES0028','38CHAR0905','38TRIE0022','38CHAR0902','38GRES0141','38VERC3408','38CHAR0029','38VERC1533','38VERC3354','38CHAR0133','38GRES0143','38VERC3464','38GRES1038','38GRES0016','38VERC3369','38CHAR0135','38VERC1668','38GRES0053','38VERC3376','38GRES0136','38CHAR0001','38CHAR0899','38CHAR0113','38VERC3159','38VERC1549','38GRES0045','38GRES0127','38VERC3197','38CHAR0907','38GRES0030','38GRES1039','38GRES0031','38CHAR0012','38CHAR0906','38VERC1824','38PCHA0121','38PCHA0928','38PCHA1004','38VERC1848','38PCHA0123','38PCHA0111','38VERC1743','38VERC2487','38MATH0024','38VERC2315','38TRIE0210','38TRIE0211','38VERC2329','38TRIE0215','38VERC2356','38TRIE0222','38VERC2405','38TRIE0377','38VERC3523','38VERC2439','38TRIE0227','38TRIE0229','38VERC2455','38TRIE0228','38VERC2443','38VERC2902','38PCHA0274','38VERC3013','38PCHA0297','38PCHA0286','38VERC2961','38VERC2983','38PCHA0291','38VERC2662','38PCHA0232','38VERC3206','38PCHA0332','38VERC3212','38PCHA0333','38PCHA0326','38VERC3175','38PCHA1003','38PCHA0910','38PCHA1007','38PCHA0044','38VERC3681','38PCHA0474','38PCHA1006','38PCHA0032','38PCHA0018','38PCHA1005','38PCHA0057','38PCHA1008','38PCHA1009','38PCHA0065','38PCHA0908','38PCHA1002'
|
|
|
|
drop_site = [
|
|
'38VERC2141','38VERC3072','38GRES1040','38VERC1830','38VERC1858',''
|
|
]
|
|
'38VERC2141','38VERC3072','38GRES1040','38VERC1830','38VERC1858','38VERC1820','38GRES1041','38VERC2935','38VERC1926','38VERC3139'
|
|
('38VERC2058','38VERC1931','38VERC1924','38CHAR0900','38VERC2163','38VERC2100','38VERC2187','38VERC2272','38VERC2807','38VERC2658','38VERC3008','38CHAR0903','38CHAR0901','38VERC2414','38VERC2514','38VERC3076','38VERC3311','38VERC2718','38VERC3205','38VERC3108','38VERC2377','38VERC2387','38VERC2431','38VERC2408','38CHAR0902','38VERC3408','38VERC3159','38CHAR0899','38CHAR0905','38CHAR0906','38CHAR0908','38VERC3376','38VERC1668','38VERC3197','38VERC1549','38CHAR0907','38GRES1039','38VERC3399','38CHAR0904','38VERC1533','38VERC3354','38VERC1618','38VERC3369','38VERC3464','38GRES1038','38VERC1687','38VERC1721','38CHAR0909','38VERC1756','38VERC2641','38CHAR0910','38VERC2601','38VERC2629')
|
|
('38VERC2649','38VERC2661','38VERC1748','38VERC2614','38VERC2446','38VERC2556','38VERC2513','38BELL0492','38BELL0489','38BELL0491','38BELL0490')
|
|
'38VERC2495','38VERC2008'
|
|
('38VERC2356','38VERC2329','38VERC2315','38VERC2405','38VERC2439','38VERC2443','38VERC2455','38VERC2487','38VERC2646','38VERC3523')
|
|
('38VERC3451','38VERC3222','38VERC3348','38VERC2912','38VERC3623','38VERC2384')
|
|
'38VERC3681','38PCHA1007','38PCHA1006'
|
|
('38VERC3112','38VERC3171','38VERC2662','38VERC2732','38VERC3206','38VERC3013','38VERC2983','38VERC2961','38VERC3212','38VERC3175','38PCHA1005','38VERC2902','38VERC1824','38VERC1743','38VERC1848','38PCHA1008','38PCHA1009')
|
|
('38PCHA1087','38PCHA1088')
|
|
|
|
|
|
|
|
v_ps[(v_ps.source=='PS_CHAMBARAN_CEN38_2013')& ~(v_ps.auteur_site.isin([
|
|
'COSQUER Mélanie (CEN Isère)'
|
|
]))].reset_index().site_code.tolist()
|
|
|
|
|
|
|
|
|
|
|
|
|
|
path='/home/colas/Documents/9_PROJETS/2_PS/CBNA/Trieves_Vercors/'
|
|
file = 'ps_aInserer_for INS1.gpkg'
|
|
df = gpd.read_file(path+file)
|
|
df.rename_geometry('geom', inplace=True)
|
|
|
|
df['date'] = '2014-01-01'
|
|
df['date'] = gpd.pd.to_datetime(df['date'])
|
|
df['structure'] = 'CBNA'
|
|
# df.rename(columns={"obs":'auteur'}, inplace=True)
|
|
df['auteur'] = 'CBNA'
|
|
df.loc[df.statut=="Habitat d'intérêt communautaire",'statut'] = \
|
|
"Communautaire"
|
|
df.loc[df.statut=="Habitat d'intérêt communautaire retenu prioritaire",'statut'] = \
|
|
"Prioritaire"
|
|
df.loc[df.statut=="Habitat d'intérêt communautaire, retenu prioritaire",'statut'] = \
|
|
"Prioritaire"
|
|
df.loc[
|
|
df.statut=="""Habitat d'intérêt communautaire, retenu prioritaire pour les sites riches en orchidées""",
|
|
'statut'] = "Prioritaire"
|
|
df.loc[
|
|
df.statut=="""Habitat d'intérêt communautaire retenu prioritaire pour les sites riches en orchidées""",
|
|
'statut'] = "Prioritaire"
|
|
df.loc[
|
|
df.statut=="""Habitat communautaire, retenu prioritaire pour les sites riches en orchidées""",
|
|
'statut'] = "Prioritaire"
|
|
df.loc[df.statut=="Habitat d'intérêt communautaire retenu prioritaire",'statut'] = \
|
|
"Prioritaire"
|
|
df.to_file(path+file,driver='GPKG')
|
|
|
|
'38TRIE1043', '38TRIE1058', '38VERC1189', '38VERC1190','38VERC1191', '38VERC1196', '38VERC1199', '38VERC1200','38VERC1201', '38VERC1202', '38VERC1203', '38VERC1204','38VERC1205', '38VERC1209', '38VERC1210', '38VERC1211','38VERC1212', '38VERC1213', '38VERC1214', '38VERC1216','38VERC1218', '38VERC1227', '38VERC3738', '38VERC3952','38VERC4550', '38VERC4551', '38VERC4552', '38VERC4553','38VERC4554', '38VERC4555', '38VERC4556', '38VERC4557','38VERC4559', '38VERC4560', '38VERC4563', '38VERC4564','38VERC4565', '38VERC4566', '38VERC4568', '38VERC4569','38VERC4570', '38VERC4571', '38VERC4572', '38VERC4573','38VERC4574'
|
|
df_maj.loc[df_maj.id_site.isin(['38TRIE0788']),'code_hab2'] = '41.13 (43)'
|
|
|
|
|
|
|
|
|
|
df = gpd.read_postgis('SELECT * FROM pelouse_seche."PB_codehab_nonPresent_dans_corineBiotope"',pycen.con_bdcen)
|
|
df = df.replace(['38.81','31.8 t'],['31.81','31.8122'],regex=True)
|
|
df.date = gpd.pd.to_datetime(df.date)
|
|
df = df[~df.id_site.isin(lst_error2)]
|
|
'38PCHA0291','38PCHA0333','38PCHA0326','38MATH0083','38PCHA0910','38GRES0025','38CHAR0029','38PCHA0111','38GRES0030','38CHAR0012','38GRES0028','38CHAR0027','38GRES0053','38MATH0005','38BELL0030','38BELL0025','38CHAR0049','38MATH0010','38CHAR0079','38GRES0119','38GRES0127','38BELL0049','38BIEV0004','38GRES0006','38CHAR0176','38GRES0031','38GRES0183','38VERC0254','38GRES0214','38VERC0290','38GRES0278','38MATH0082','38GRES0203','38GRES0251','38CHAR0031','38VERC0383','38GRES0136','38GRES0084','38CHAR0135','38CHAR0133','38GRES0045','38GRES0141','38BELL0192','38BDAU0847','38CHAR0402'
|
|
update_to_sql(
|
|
df[['id_site','remarques']].rename(columns={'remarques':'rmq_interet_patri'}),
|
|
con,'r_sites_geom','sites','id_site'
|
|
)
|
|
|
|
|
|
df = gpd.read_postgis('SELECT * FROM pelouse_seche."PB_codehabCBNA_nonPresent_dans_corineBiotope"',pycen.con_bdcen)
|
|
df = df.replace(['43.171','43.13','31.4B','37.831','52 à 54'],['43','43','31.4','31.831','54'],regex=True)
|
|
'38CHAR1238','38CHAR1091','38TRIE2732','38OISA0273','38OISA0272','38GROU0015','38OISA0274','38VERC3785','38TRIE1399','38TRIE1402','38TRIE2443','38TRIE2444','38VERC1467','38VERC1382','38VERC1059','38TRIE0942','38TRIE0788','38OISA0190','38OISA0188','38OISA0189','38VERC0918','38TRIE1010','38TRIE0943','38BVIS0042','38VERC1253','38VERC1322','38VERC1323','38VERC3787'
|
|
|
|
|
|
|
|
|
|
gpd.read_postgis("SELECT * FROM sites.r_sites_geom WHERE id_site IN ('%s')"%"','".join(DF.id_site),pycen.con)
|
|
|
|
|
|
'38OISA0245','38TRIE1045','38TRIE1165','38TRIE2421','38VERC1401','38VERC1447','38VERC3718','38VERC3766','38VERC4003'
|
|
'38VERC4697','38VERC4698','38VERC4699','38VERC4700','38VERC4701','38VERC4704','38VERC4705','38VERC4706','38VERC4707','38VERC4708','38VERC4709','38VERC4710','38VERC4711','38VERC4712','38VERC4713','38VERC4714','38VERC4715','38VERC4716','38VERC4717','38VERC4718','38VERC4719','38VERC4720','38VERC4721','38VERC4722','38VERC4723','38VERC4724','38VERC4726','38VERC4727','38VERC4729','38VERC4730','38VERC4731','38VERC4732','38VERC4733','38VERC4734','38VERC4735','38VERC4736','38VERC4737','38VERC4738','38VERC4739','38VERC4741','38VERC4743','38VERC4746','38VERC4747','38VERC4748'
|
|
|
|
|
|
cvr = gpd.read_postgis("SELECT * FROM ps.v_pelouseseches WHERE site_code IN ('38VERC4740','38VERC4725','38VERC1271','38VERC4739')",pycen.con)
|
|
df = gpd.read_postgis("SELECT * FROM ps.v_pelouseseches WHERE site_code IN ('38VERC1249','38VERC1251','38VERC1272')",pycen.con)
|
|
|
|
tt = gpd.overlay(df,cvr.unary_union,how='difference')
|
|
# tt = df.copy()
|
|
tt = pycen.tools.Polygons_to_MultiPolygon(tt)
|
|
tt.drop(columns=['auteur_geom', 'auteur_attrs'],inplace=True)
|
|
tt.drop(columns=['date_geom', 'date_attrs'],inplace=True)
|
|
tt.rename(columns={'auteur_site':'auteur'},inplace=True)
|
|
tt.rename(columns={'date_site':'date'},inplace=True)
|
|
tt.columns = tt.columns.str.replace('cb_hab','code_hab',regex=True)
|
|
tt.rename(columns={'site_code':'id_site'},inplace=True)
|
|
tt[['auteur','structure']] = tt.auteur.str.split('(',expand=True).sort_index()
|
|
tt.auteur = tt.auteur.str.strip()
|
|
tt.structure = tt.structure.str.strip().str.replace('\)','',regex=True)
|
|
# tt.auteur = 'GRANGE Benjamin'
|
|
# tt['structure'] = 'CEN Isère'
|
|
|
|
|
|
'38VERC4697','38VERC4698','38VERC4699','38VERC4700','38VERC4701','38VERC4704','38VERC4705','38VERC4706','38VERC4707','38VERC4708','38VERC4709','38VERC4710','38VERC4711','38VERC4712','38VERC4713','38VERC4714','38VERC4715','38VERC4716','38VERC4717','38VERC4718','38VERC4719','38VERC4720','38VERC4721','38VERC4722','38VERC4723','38VERC4724','38VERC4726','38VERC4727','38VERC4729','38VERC4730','38VERC4731','38VERC4732','38VERC4733','38VERC4734','38VERC4735','38VERC4736','38VERC4737','38VERC4738','38VERC4739','38VERC4741','38VERC4743','38VERC4746','38VERC4747','38VERC4748'
|
|
|
|
|
|
'38VERC4702','38VERC4703','38VERC4725','38VERC4728','38VERC4740','38VERC4742','38VERC4744','38VERC4745'
|
|
'38BVIS0047','38VERC4082','38VERC4083','38VERC0618','38VERC1266','38VERC1267','38VERC1232','38VERC1240','38VERC4158','38VERC4158','38VERC4068','38VERC4086','38VERC4083'
|
|
|
|
'38BVIS0049', '38VERC1231', '38VERC1234', '38VERC1237','38VERC1241', '38VERC1246', '38VERC1247', '38VERC1248','38VERC1250', '38VERC1255', '38VERC1256', '38VERC1257','38VERC1261', '38VERC1262', '38VERC1263', '38VERC1265','38VERC1268', '38VERC1269', '38VERC1271', '38VERC1306','38VERC1322', '38VERC4051', '38VERC4052', '38VERC4056','38VERC4057', '38VERC4084' |