173 lines
8.6 KiB
Python
173 lines
8.6 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: UTF-8 -*-
|
|
|
|
from statistics import geometric_mean
|
|
import geopandas as gpd
|
|
from shapely import wkb
|
|
from pycen import con,con_bdcen,update_to_sql
|
|
from pycen.tools import Polygons_to_MultiPolygon
|
|
from sys import exit
|
|
|
|
def recreate_history(n_site,o_site):
|
|
req = '''
|
|
UPDATE sites.sites SET date_fin = (
|
|
SELECT max(date_deb) FROM sites.sites WHERE id IN ('{site_new}')
|
|
) WHERE "id" IN ('{site_old}');
|
|
INSERT INTO sites.r_site_maj (id_site_new,id_site_old) VALUES ('{site_new}','{site_old}');
|
|
'''.format(site_new=n_site,site_old=o_site)
|
|
with con.begin() as cnx:
|
|
cnx.execute(req)
|
|
|
|
|
|
def modif_geomcover(old_geom, new_geom,how='maj',rcv=0.999):
|
|
# print(i)
|
|
sql = """
|
|
with w1 as (
|
|
SELECT s1.site_code, s1.geom geom_in, ST_ExteriorRing((ST_Dump(ST_Buffer(s2.geom,0.001))).geom) geom_split, s2.geom geom_over
|
|
FROM (select * from ps.v_pelouseseches where site_code = '{geom_old}') s1, (select * from ps.v_pelouseseches where site_code = '{geom_new}') s2
|
|
),
|
|
w2 as (
|
|
SELECT site_code, geom_in, ST_Collect(geom_split) geom_split, geom_over from w1 group by 1,2,4
|
|
),
|
|
w3 as (
|
|
SELECT site_code, geom_in,round(st_area(geom_in)) area_in, (ST_Dump(ST_Split(geom_in,geom_split))).geom geom_split, geom_over from w2
|
|
)
|
|
SELECT site_code, geom_in,area_in,round(st_area(st_union(geom_split))) area_split, st_multi(st_union(geom_split)) geom_split, geom_over from w3
|
|
WHERE ST_Within(geom_split,ST_Buffer(geom_over,0.02)) = False
|
|
group by 1,2,3,6;
|
|
""".format(geom_old=old_geom,geom_new=new_geom)
|
|
res_split = gpd.read_postgis(sql,con, geom_col='geom_split')
|
|
res_split.geom_in = [wkb.loads(x,hex=True) for x in res_split.geom_in]
|
|
res_split.set_geometry('geom_in',inplace=True, crs=2154)
|
|
if res_split.empty:
|
|
print([old_geom,new_geom,'EMPTY'])
|
|
else:
|
|
if res_split.area_split[0] < (res_split.area_in[0]*rcv) :
|
|
spl = res_split.area_split[0]
|
|
_in = res_split.area_in[0]
|
|
print([old_geom,new_geom,_in,spl,(_in/spl)])
|
|
# continue
|
|
# raise ValueError(j)
|
|
else:
|
|
# print('OK')
|
|
gdf = gpd.read_postgis("SELECT * FROM sites.r_sites_geom WHERE id_site = '%s'"%old_geom, con)
|
|
# df_aut = gpd.pd.read_sql_query('')
|
|
gdf.date = gpd.pd.to_datetime(gdf.date)
|
|
gdf.geom = res_split.geom_split
|
|
|
|
if how=='maj':
|
|
gdf = gdf.loc[gdf.groupby('id_site').date.idxmax(),['id','geom']]
|
|
update_to_sql(gdf, con, 'r_sites_geom', 'sites', 'id')
|
|
elif how=='insert':
|
|
gdf = gdf.loc[gdf.groupby('id_site').date.idxmax()]
|
|
gdf.drop(columns=['id','date_insert'],inplace=True,errors='ignore')
|
|
gdf.to_postgis(
|
|
name='r_sites_geom', con=con, schema='sites', if_exists='append', geom_col='geom'
|
|
)
|
|
print('END insert')
|
|
else:
|
|
raise ValueError([old_geom, new_geom])
|
|
|
|
|
|
sql = """SELECT
|
|
v1.site_code site_code_old, v1.geom geom_old, v2.site_code site_code_new, v2.geom geom_new,
|
|
v1."source" source_old,v2."source" source_new, v1.id_origine orig_old,v2.id_origine orig_new
|
|
FROM ps.v_pelouseseches v1, ps.v_pelouseseches v2
|
|
WHERE ST_OVERLAPS(v1.geom,v2.geom) = TRUE
|
|
AND v1.date_geom < v2.date_geom
|
|
AND v1.site_code <> v2.site_code;"""
|
|
df = gpd.read_postgis(sql,con,geom_col='geom_new')
|
|
df.geom_old = [wkb.loads(x,hex=True) for x in df.geom_old]
|
|
df.set_geometry('geom_old',inplace=True, crs=2154)
|
|
|
|
v_ps = gpd.read_postgis('SELECT * FROM ps.v_pelouseseches', con)
|
|
|
|
|
|
sql = """SELECT
|
|
v1.site_code site_code_old, v1.geom geom_old, v2.site_code site_code_new, v2.geom geom_new,
|
|
v1."source" source_old,v2."source" source_new, v1.id_origine orig_old,v2.id_origine orig_new
|
|
FROM ps.v_pelouseseches v1, ps.v_pelouseseches v2
|
|
WHERE ST_OVERLAPS(v1.geom,v2.geom) = TRUE
|
|
AND v1.date_geom = v2.date_geom
|
|
AND v1.site_code <> v2.site_code;"""
|
|
dfx = gpd.read_postgis(sql,con,geom_col='geom_new')
|
|
print(dfx.shape[0])
|
|
dfx.apply(lambda x: modif_geomcover(old_geom=x.site_code_old,new_geom=x.site_code_new,how='maj',rcv=0.999),axis=1)
|
|
|
|
idem = df[(df.geom_old.area.round()/df.geom_new.area.round()).between(0.98,1.02) & (df.source_old=='cr_CHARTREUSE_habitats_CBNA_2000-2012')]
|
|
idem.apply(lambda x: recreate_history(o_site=x.site_code_old,n_site=x.site_code_new),axis=1)
|
|
|
|
df[df.source_old=='cr_VERCORS_habitats_CBNA_1999-2007']\
|
|
.apply(lambda x: modif_geomcover(old_geom=x.site_code_old,new_geom=x.site_code_new,how='maj',rcv=0.999),axis=1)
|
|
df[df.source_old=='cr_VERCORS_habitats_CBNA_1999-2007']\
|
|
.apply(lambda x: modif_geomcover(old_geom=x.site_code_old,new_geom=x.site_code_new,how='insert',rcv=0.9),axis=1)
|
|
# modif_geomcover('38VERC0654', '38VERC4644',how='insert',rcv=0.9)
|
|
# recreate_history(o_site='38VERC0760',n_site='38VERC3866')
|
|
# recreate_history(o_site='38VERC0712',n_site='38VERC3859')
|
|
# recreate_history(o_site='38VERC0687',n_site='38VERC3756')
|
|
# recreate_history(o_site='38VERC0779',n_site='38VERC3768')
|
|
# recreate_history(o_site='38VERC4193',n_site='38VERC4635')
|
|
# recreate_history(o_site='38VERC4204',n_site='38VERC4637')
|
|
# recreate_history(o_site='38VERC4242',n_site='38VERC4642')
|
|
# recreate_history(o_site='38VERC4253',n_site='38VERC4644')
|
|
# recreate_history(o_site='38VERC4258',n_site='38VERC4645')
|
|
# recreate_history(o_site='38CHAR0735',n_site='38CHAR0045')
|
|
# recreate_history(o_site='38CHAR0766',n_site='38GRES0136')
|
|
# recreate_history(o_site='38CHAR0694',n_site='38VERC0215')
|
|
# recreate_history(o_site='38TRIE2441',n_site='38TRIE1337')
|
|
# recreate_history(o_site='38VERC0651',n_site='38VERC3762')
|
|
# recreate_history(o_site='38VERC0663',n_site='38VERC3846')
|
|
# recreate_history(o_site='38VERC0671',n_site='38VERC3849')
|
|
# recreate_history(o_site='38VERC0672',n_site='38VERC3851')
|
|
# recreate_history(o_site='38VERC4260',n_site='38VERC4646')
|
|
# recreate_history(o_site='38VERC4268',n_site='38VERC4647')
|
|
# recreate_history(o_site='38VERC4270',n_site='38VERC4648')
|
|
# recreate_history(o_site='38CHAR0677',n_site='38CHAR0100')
|
|
# recreate_history(o_site='38CHAR0699',n_site='38CHAR0072')
|
|
# recreate_history(o_site='38CREM0404',n_site='38CREM0104')
|
|
# recreate_history(o_site='38CREM0405',n_site='38CREM0105')
|
|
# recreate_history(o_site='38CREM0412',n_site='38CREM0178')
|
|
# recreate_history(o_site='38CREM0417',n_site='38CREM0114')
|
|
# recreate_history(o_site='38CREM0420',n_site='38CREM0064')
|
|
# recreate_history(o_site='38VERC0735',n_site='38VERC3862')
|
|
# recreate_history(o_site='38VERC0744',n_site='38VERC3764')
|
|
# recreate_history(o_site='38VERC0753',n_site='38VERC3865')
|
|
# recreate_history(o_site='38VERC1194',n_site='38VERC3735')
|
|
# recreate_history(o_site='38VERC1198',n_site='38VERC3736')
|
|
# recreate_history(o_site='38VERC1207',n_site='38VERC3738')
|
|
# recreate_history(o_site='38TRIE1015',n_site='38VERC3714')
|
|
# recreate_history(o_site='38TRIE2467',n_site='38TRIE2630')
|
|
# recreate_history(o_site='38PCHA0994',n_site='38GRES0844')
|
|
# recreate_history(o_site='38PCHA0994',n_site='38GRES0844')
|
|
# recreate_history(o_site='38VERC1236',n_site='38VERC4705')
|
|
# recreate_history(o_site='38VERC1233',n_site='38VERC4706')
|
|
# recreate_history(o_site='38VERC1242',n_site='38VERC1237')
|
|
# recreate_history(o_site='38VERC1243',n_site='38VERC4726')
|
|
# recreate_history(o_site='38VERC1260',n_site='38VERC4724')
|
|
# recreate_history(o_site='38VERC4027',n_site='38VERC4743')
|
|
# recreate_history(o_site='38VERC4085',n_site='38VERC4084')
|
|
|
|
# for i,j in df.iterrows():
|
|
# update_to_sql(gdf, con, 'r_sites_geom', 'sites', 'id')
|
|
|
|
|
|
|
|
df.apply(lambda x: modif_geomcover(old_geom=x.site_code_old,new_geom=x.site_code_new),axis=1)
|
|
# df.iloc[57:].apply(lambda x: modif_geomcover(old_geom=x.site_code_old,new_geom=x.site_code_new),axis=1)
|
|
|
|
df2 = df[df.source_old=='PS_Beaumont_2018_Drac_Nature.shp']
|
|
df2 = df[df.source_old=='cr_VERCORS_habitats_CBNA_1999-2007']
|
|
df2.apply(lambda x: modif_geomcover(old_geom=x.site_code_old,new_geom=x.site_code_new),axis=1)
|
|
# 38TRIE0678 38TRIE0721
|
|
|
|
|
|
vrc = df[df.source_old=='cr_VERCORS_habitats_CBNA_1999-2007'].copy()
|
|
s_vrc = gpd.read_postgis('SELECT * FROM pelouse_seche."ps_TRIEVES+VERCORS_habitats_CBNA_2014";',con=con_bdcen)
|
|
p_vrc = gpd.read_postgis('SELECT v.*,ST_PointOnSurface(v.geom) geom_point FROM pelouse_seche."ps_TRIEVES+VERCORS_habitats_CBNA_2014" v;',con=con_bdcen, geom_col='geom_point')
|
|
pvrc = gpd.read_postgis("SELECT v.*,ST_PointOnSurface(v.geom) geom_point FROM ps.v_pelouseseches v WHERE v.site_code IN ('%s');"%("','".join(vrc.site_code_old)),con=con, geom_col='geom_point')
|
|
t1 = s_vrc.geom.intersects(pvrc.geom_point.unary_union)
|
|
t2 = p_vrc.geom_point.intersects(vrc.geom_old.unary_union)
|
|
INS = s_vrc[t1|t2].copy()
|
|
INS.to_postgis(
|
|
'ps_covered',con,geom_col='geom',if_exists='replace'
|
|
) |