92 lines
3.2 KiB
Python
92 lines
3.2 KiB
Python
from pycen import con_bdcen,con_fon,update_to_sql
|
|
import geopandas as gpd
|
|
from datetime import datetime as dt
|
|
|
|
# GET parcelles gérées à jour
|
|
sql = "SELECT * FROM sites.c_sites_zonages WHERE code_site = 'LEZE'"
|
|
sql = "SELECT * FROM foncier.c_anim_foncier_v2"
|
|
parc_site = gpd.read_postgis(sql,con_bdcen)
|
|
parc_site[(~parc_site.classif_prop.str.contains('cen',case=False))&(parc_site.maitrise.str.contains('acqui',case=False))].to_csv('/media/colas/SRV/FICHIERS/TRANSFERTS-EQUIPE/AB/verif_anim_foncier_v2.csv',index=False)
|
|
# parc_site.loc[parc_site.maitrise=='ACQUI PAR LE CEN ISERE','maitrise'] = 'ACQUIS PAR LE CEN ISERE'
|
|
# update_to_sql(
|
|
# parc_site[['par_id','maitrise']],con_bdcen,'c_anim_foncier_v2','foncier','par_id'
|
|
# )
|
|
|
|
# GET sites
|
|
sql = "SELECT * FROM sites.sites"
|
|
site_fon = gpd.read_postgis(sql,con_fon)
|
|
site_zi = site_fon[site_fon.site_id.str.endswith('ZI')]
|
|
site_zo = site_fon[site_fon.site_id.str.endswith('ZO')]
|
|
|
|
|
|
# GET cadastre from bd_cen
|
|
sql = '''
|
|
WITH prop AS (
|
|
SELECT
|
|
dnuper,gtoper,ccogrm,ddenom
|
|
FROM {sch}."proprios"
|
|
ORDER BY 2,3,4
|
|
)
|
|
SELECT --DISTINCT ON (t1.par_id,t1.geom, t1.dcntpa,t1.typprop_id,t1.ccopre,t1.ccosec,t1.dnupla)
|
|
t1.par_id,
|
|
t1.geom,
|
|
t1.dcntpa,
|
|
t1.typprop_id,
|
|
t1.ccopre,
|
|
t1.ccosec "section",
|
|
t1.dnupla::text,
|
|
c.nom nom_com,
|
|
substring(t1.par_id from 12 for 4) "numero",
|
|
substring(t1.codcom from 1 for 2) code_dep,
|
|
substring(t1.codcom from 3 for 3) code_com,
|
|
STRING_AGG(distinct t6.gtoper::text,',') gtoper,
|
|
STRING_AGG(distinct t6.ccogrm::text,',') ccogrm,
|
|
STRING_AGG(distinct CONCAT(TRIM(t6.gtoper::text)||COALESCE('_'||TRIM(t6.ccogrm::text),'')),',') type_prop,
|
|
--t6.dnuper,
|
|
STRING_AGG(distinct t6.ddenom::text,', ') nom_proprio
|
|
FROM {sch}."{t1}" t1
|
|
JOIN ({sch}."lots" t2
|
|
JOIN ({sch}."cadastre" t3
|
|
JOIN ({sch}."cptprop" t4
|
|
JOIN ({sch}."r_prop_cptprop" t5
|
|
JOIN prop t6 USING (dnuper))
|
|
USING (dnupro))
|
|
USING (dnupro))
|
|
USING (lot_id))
|
|
USING (par_id)
|
|
JOIN administratif.communes c on t1.codcom = c.code_insee '''.format(
|
|
sch='cadastre',
|
|
t1='parcelles'
|
|
)
|
|
|
|
sql_grp = '''GROUP BY 1,2,3,4,5,6,8'''
|
|
|
|
|
|
# Parcelles gestion
|
|
csv_parc = gpd.pd.read_csv('/home/colas/Documents/9_PROJETS/0_FONCIER/CEN38/LEZE_parcelles_a_rajouter_CSV.csv')
|
|
csv_parc['par_id'] = csv_parc.geo_parcel.str[:2]+csv_parc.geo_parcel.str[3:]
|
|
lst_parc = csv_parc.par_id.tolist()
|
|
sql_whr = "WHERE t1.par_id IN ('{par_ids}') ".format(par_ids="','".join(lst_parc))
|
|
sql_gst = sql + sql_whr + sql_grp
|
|
parc = gpd.read_postgis(sql_gst,con_fon)
|
|
parc['last_upd_parc'] = dt.now().date().isoformat()
|
|
parc.loc[parc.type_prop.str.startswith(','),'type_prop'] = parc[parc.type_prop.str.startswith(',')].type_prop.str[1:]
|
|
parc['classif_prop'] = 'Privé et entreprises'
|
|
parc.loc[parc.type_prop=='1','classif_prop'] = 'Privé et entreprises'
|
|
|
|
parc2 = parc.sjoin(site_fon[['site_id','geom']],how='left')\
|
|
.replace({'site_id':{'_ZI|_ZO':''}},regex=True)\
|
|
.drop(columns=['index_right'])\
|
|
.drop_duplicates()\
|
|
.reset_index(drop=True)
|
|
|
|
|
|
# Envoie bd-cen-38.foncier.c_anim_foncier_v2
|
|
parc2.to_postgis(
|
|
name = 'c_anim_foncier_v2',
|
|
con = con_bdcen,
|
|
schema = 'foncier',
|
|
if_exists = 'append',
|
|
index=False
|
|
)
|