188 lines
6.3 KiB
Python
188 lines
6.3 KiB
Python
from pycen import con_bdcen,con_fon
|
|
import geopandas as gpd
|
|
from datetime import datetime as dt
|
|
|
|
# GET parcelles gérées à jour
|
|
sql = "SELECT * FROM parcelles_foncier_tofede"
|
|
parc_site = gpd.read_postgis(sql,con_bdcen)
|
|
parc_smrg = parc_site[[
|
|
'par_id','id','Parc_MCE','docs_off_num',
|
|
'Nbr_lots_BND','partiel_conv_ou_acq',
|
|
'code_mfu1','code_mfu2','bnd','mesure_compens',
|
|
'pour_part'
|
|
]]
|
|
|
|
# GET couche animation fonciere
|
|
sql = """SELECT
|
|
c.*,
|
|
substring(c.num_unique_v2 from 1 for 5) || substring(c.num_unique_v2 from 13 for 3) || substring(c.num_unique_v2 from 6 for 6) par_id_v2
|
|
FROM foncier.c_animation_fonciere_sites c"""
|
|
anim_fon = gpd.read_postgis(sql,con_bdcen)
|
|
anim_mrg = anim_fon[['par_id_v2',
|
|
'id','maitrise','date_der_c','indivision',
|
|
'contact','avis_propr','commentair','nom_propri'
|
|
]].rename(
|
|
columns={
|
|
'nom_propri':'nom_proprio_old'
|
|
}
|
|
)
|
|
|
|
# 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')]
|
|
|
|
lst_pmili = parc_site[parc_site.id.isna()].par_id.tolist()
|
|
|
|
# 3880,3884,3885,3883,3886,3859,3860,3856,3858,3854
|
|
# par_id / lot_id
|
|
# '38222000ZB0013','38222000ZB0015','38222000ZB0016','38222000ZB0017','38222000ZB0023','38222000ZB0041','38222000ZB0042','38222000ZB0043','38222000ZB0058','38222000ZB0059'
|
|
# vl_id
|
|
# '3822200101','3822200126','3822200126','3822200126','3822200108','3822200106','3822200106','3822200106','3822200089','3822200089'
|
|
|
|
# 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
|
|
lst_parc = parc_site.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_gst = gpd.read_postgis(sql_gst,con_fon)
|
|
parc_gst_tmp = parc_gst.merge(parc_smrg,on='par_id')
|
|
parc_gst_2 = parc_gst_tmp.merge(anim_mrg,on='id')
|
|
|
|
|
|
# parcelles sites, NON PRESENTES dans parcelles gérées
|
|
geo = site_fon.unary_union
|
|
sql_whr = """
|
|
WHERE ST_Intersects (t1.geom, 'SRID={epsg};{poly}')
|
|
AND t1.par_id NOT IN ('{par_ids}')""".format(
|
|
epsg = 2154,
|
|
poly = geo,
|
|
par_ids="','".join(lst_parc)
|
|
)
|
|
sql_veil = sql + sql_whr + sql_grp
|
|
parc_veil = gpd.read_postgis(sql_veil,con_fon)
|
|
parc_veil2 = parc_veil.merge(anim_mrg,left_on='par_id',right_on='par_id_v2',how='left')
|
|
|
|
|
|
# Parcelles Hors site
|
|
lst_id = [
|
|
*parc_veil2.id.dropna().astype(int).tolist(),
|
|
*parc_gst_2.id.dropna().astype(int).tolist()
|
|
]
|
|
lst_parid_HS = anim_fon[~anim_fon.id.isin(lst_id)].par_id_v2.dropna().tolist()
|
|
sql_whr = "WHERE t1.par_id IN ('{par_ids}') ".format(par_ids="','".join(lst_parid_HS))
|
|
sql_hs = sql + sql_whr + sql_grp
|
|
parc_hs = gpd.read_postgis(sql_hs,con_fon)
|
|
# parc_hs2 = parc_hs.merge(anim_mrg,left_on='par_id',right_on='par_id_v2')
|
|
parc_hs2 = parc_hs[parc_hs.is_valid].merge(anim_mrg,left_on='par_id',right_on='par_id_v2')
|
|
INVALID_parc_hs = parc_hs[~parc_hs.is_valid].merge(anim_mrg,left_on='par_id',right_on='par_id_v2')
|
|
|
|
# parc_gst_2[parc_gst_2.type_prop.str.startswith(',')].iloc[:,:15]
|
|
# parc_veil2[parc_veil2.type_prop.str.startswith(',')].iloc[:,:15]
|
|
# parc_hs2[parc_hs2.type_prop.str.startswith(',')]
|
|
|
|
# Merge parcelles
|
|
parc = gpd.pd.concat([parc_gst_2,parc_veil2,parc_hs2])\
|
|
.reset_index(drop=True)
|
|
parc.loc[parc.type_prop.str.startswith(','),'type_prop'] = parc[parc.type_prop.str.startswith(',')].type_prop.str[1:]
|
|
|
|
# Jointure id_site
|
|
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)
|
|
|
|
|
|
# Drop manuel des doublons
|
|
parc_CRAS = ['381370000B0249','381370000B0241','381370000B0248','381370000B0250','381370000B0582']
|
|
parc_MONA = ['383100000A0693']
|
|
id_drop1 = parc2[parc2.par_id.isin(parc_CRAS) & (parc2.site_id != 'CRAS')].index
|
|
id_drop2 = parc2[parc2.par_id.isin(parc_MONA) & (parc2.site_id != 'MONA')].index
|
|
lst_id_drop = [*id_drop1,*id_drop2]
|
|
parc2.drop(lst_id_drop,inplace=True)
|
|
parc2.loc[parc2.par_id == '381370000B0566','site_id'] = 'CRAS'
|
|
|
|
|
|
# Jointure type_zone
|
|
parc2['zi'] = parc2.intersects(site_zi.unary_union)
|
|
parc2['zo'] = parc2.intersects(site_zo.unary_union)
|
|
parc2.loc[parc2.zi,'type_zone'] = 'ZI'
|
|
parc2.loc[parc2.zo & (parc2.type_zone != 'ZI'),'type_zone'] = 'ZO'
|
|
parc2.drop(columns = ['zi','zo'],inplace=True)
|
|
|
|
|
|
# PARC = gpd.pd.concat([parc2,parc_hs2])
|
|
|
|
|
|
parc2['last_upd_parc'] = dt.now().date().isoformat()
|
|
# Envoie bd-cen-38.foncier.c_anim_foncier_v2
|
|
parc2.to_postgis(
|
|
name = 'c_anim_foncier_v2',
|
|
con = con_bdcen,
|
|
schema = 'foncier',
|
|
if_exists = 'replace',
|
|
index=False
|
|
)
|
|
|
|
INVALID_parc_hs.drop(columns='geom').to_sql(
|
|
name = 'c_anim_foncier_v2',
|
|
con = con_bdcen,
|
|
schema = 'foncier',
|
|
if_exists = 'append',
|
|
index=False
|
|
)
|
|
|
|
sql = """
|
|
ALTER TABLE foncier.c_anim_foncier_v2 ADD PRIMARY KEY (par_id);
|
|
GRANT ALL ON TABLE foncier.c_anim_foncier_v2 TO abavarot;
|
|
GRANT ALL ON TABLE foncier.c_anim_foncier_v2 TO grp_admin;
|
|
GRANT ALL ON TABLE foncier.c_anim_foncier_v2 TO cen_admin;
|
|
GRANT SELECT ON TABLE foncier.c_anim_foncier_v2 TO grp_consult;
|
|
"""
|
|
with con_bdcen.begin() as cnx:
|
|
cnx.execute(sql)
|