119 lines
3.7 KiB
Python
119 lines
3.7 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: UTF-8 -*-
|
|
|
|
from pycen.tools import Polygons_to_MultiPolygon
|
|
from pycen import con
|
|
import geopandas as gpd
|
|
from sqlalchemy import create_engine, text
|
|
from geoalchemy2 import Geometry
|
|
|
|
user_cad = 'cgeier' # utilisateur de connexion à la bdd
|
|
pwd_cad = 'adm1n*bdCen' # mot de passe de la bdd
|
|
adr_cad = '91.134.194.221' # adresse ip de la bdd
|
|
port_cad = '5432' # port de la bdd
|
|
base_cad = 'rhomeo' # nom de la bdd
|
|
schema_cad = 'bdtopo3'
|
|
con = create_engine('postgresql+psycopg2://{0}:{1}@{2}:{3}/{4}'.format(user_cad,pwd_cad,adr_cad,port_cad,base_cad), echo=False)
|
|
|
|
|
|
|
|
file = '/media/colas/Disk2/5_BDD/RPG/1_DONNEES_LIVRAISON_2021/RPG_2-0_SHP_LAMB93_R84_2021-01-01/ILOTS_ANONYMES.shp'
|
|
dic = {
|
|
'CD_SSBV': 'cdssbv',
|
|
'LIB_SSBV': 'nom',
|
|
'CD_COMGEO': 'cd_comgeo',
|
|
'CD_CTB': 'cd_ctb',
|
|
'CD_': 'cd_',
|
|
'CD_SUBUNIT': 'cd_subunit',
|
|
'geometry': 'geom',
|
|
}
|
|
|
|
name_table = 'ilots_anonymes_2021'
|
|
name_schema = 'rpgnew'
|
|
# Parametres bdd OUT
|
|
# user_zh = 'postgres'
|
|
# pwd_zh = 'tutu'
|
|
# adr_zh = '192.168.60.10'
|
|
# base_zh = 'bd_cen'
|
|
# con_zh = create_engine('postgresql+psycopg2://{0}:{1}@{2}/{3}'.format(user_zh,pwd_zh,adr_zh,base_zh), echo=False)
|
|
|
|
|
|
select_cols = list(dic.values())
|
|
|
|
df = gpd.read_file(file)
|
|
# df = df.set_geometry('geom')
|
|
df.rename_geometry('geom', inplace=True)
|
|
df.columns = df.columns.str.lower()
|
|
|
|
if not df.crs:
|
|
df.set_crs(epsg=2154, inplace=True)
|
|
|
|
if df.crs.srs != 'epsg:2154':
|
|
df.to_crs(epsg=2154, inplace=True)
|
|
|
|
# df.rename(columns=dic, inplace=True)
|
|
# df = df[select_cols]
|
|
|
|
|
|
if 'id' not in df.columns:
|
|
df.index.name = 'id'
|
|
df.reset_index(drop=False, inplace=True)
|
|
|
|
|
|
if 'Polygon' in df.geom_type.unique() and 'MultiPolygon' in df.geom_type.unique():
|
|
df = Polygons_to_MultiPolygon(df)
|
|
# from shapely.geometry.multipolygon import MultiPolygon
|
|
# tmp = df.loc[df.geom_type == 'Polygon'].copy()
|
|
# geom = [MultiPolygon([x]) for x in tmp.loc[tmp.geom_type == 'Polygon','geom']]
|
|
# tmp = tmp.set_geometry(geom)
|
|
# df = gpd.pd.concat([df.drop(tmp.index), tmp]).sort_values('id').reset_index(drop=True)
|
|
|
|
if 'LineString' in df.geom_type.unique() and 'MultiLineString' in df.geom_type.unique():
|
|
from shapely.geometry.multilinestring import MultiLineString
|
|
tmp = df.loc[df.geom_type == 'LineString'].copy()
|
|
geom = [MultiLineString([x]) for x in tmp.loc[tmp.geom_type == 'LineString','geom']]
|
|
tmp = tmp.set_geometry(geom)
|
|
df = gpd.pd.concat([df.drop(tmp.index), tmp]).sort_values('id').reset_index(drop=True)
|
|
|
|
con.begin()
|
|
df.to_postgis(
|
|
name = name_table,
|
|
con = con,
|
|
schema = name_schema,
|
|
if_exists = 'replace',
|
|
chunksize = 100000,
|
|
dtype={'geom':Geometry(srid=2154)}
|
|
)
|
|
|
|
sql = """ALTER TABLE {sch}.{tab} OWNER TO cen_admin;
|
|
GRANT ALL ON TABLE {sch}.{tab} TO cen_admin;
|
|
GRANT ALL ON TABLE {sch}.{tab} TO grp_admin;
|
|
--GRANT SELECT ON TABLE {sch}.{tab} TO grp_consult;
|
|
""".format(sch=name_schema,tab=name_table)
|
|
with con.begin() as cnx:
|
|
cnx.execute(sql)
|
|
|
|
sql = """
|
|
REVOKE SELECT ON TABLE {sch}.{tab} FROM grp_consult;
|
|
""".format(sch=name_schema,tab=name_table)
|
|
with con.begin() as cnx:
|
|
cnx.execute(sql)
|
|
|
|
if 'rpg' in name_table:
|
|
|
|
v_tab = 'v_'+name_table.replace('reg','')+'isere'
|
|
lst_col = ','.join(tuple('s.'+df.columns))
|
|
|
|
sql = """CREATE OR REPLACE VIEW {sch}.{v_tab}
|
|
AS SELECT {cols}
|
|
FROM {sch}.{tab} s,
|
|
ref_territoire.dept_isere reg
|
|
WHERE st_intersects(s.geom, reg.geom);
|
|
|
|
ALTER TABLE {sch}.{v_tab} OWNER TO cen_admin;
|
|
GRANT ALL ON TABLE {sch}.{v_tab} TO cen_admin;
|
|
GRANT ALL ON TABLE {sch}.{v_tab} TO grp_admin;
|
|
GRANT SELECT ON TABLE {sch}.{v_tab} TO grp_consult;""".format(sch=name_schema,tab=name_table,v_tab=v_tab,cols=lst_col)
|
|
with con.begin() as cnx:
|
|
cnx.execute(sql)
|