173 lines
5.2 KiB
Python
173 lines
5.2 KiB
Python
from sqlalchemy import create_engine, text,PrimaryKeyConstraint
|
|
from geoalchemy2 import Geometry
|
|
import geopandas as gpd
|
|
from os import listdir,path
|
|
from pycen import con_rho as con
|
|
|
|
# user_cad = 'xxxxxxx' # utilisateur de connexion à la bdd
|
|
# pwd_cad = 'xxxxxxx' # 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)
|
|
|
|
|
|
def get_list_table(con,sch="public"):
|
|
with con.begin() as cnx:
|
|
return cnx.dialect.get_table_names(cnx,schema=sch)
|
|
|
|
def get_columns(con,sch,tab):
|
|
with con.begin() as cnx:
|
|
return cnx.dialect.get_columns(cnx,tab,sch)
|
|
|
|
def get_table_pkey(con,sch,tab):
|
|
with con.begin() as cnx:
|
|
return cnx.dialect.get_foreign_keys(cnx,tab,sch)
|
|
|
|
|
|
# for tab in ['spatial_ref_sys',#'info_metadonnees','metadonnees_lot','metadonnees_theme',
|
|
# 'layer_styles']:
|
|
# sql = """ALTER TABLE bdtopo3.%s
|
|
# SET SCHEMA public;"""%tab
|
|
# with con.begin() as cnx:
|
|
# cnx.execute(sql)
|
|
# print('Table %s SET TO SCHEMA bdtopo3'%(tab))
|
|
|
|
lst_zip = [
|
|
'/vsizip//home/colas/Craig/CEN_38/BD Vectorielles/IGN - BDTOPO - 202309/bdtopo_dep07_202309_geopackage.zip/BDT_3-3_GPKG_LAMB93_D007-ED2023-09-15/BDT_3-3_GPKG_LAMB93_D007-ED2023-09-15.gpkg',
|
|
'/vsizip//home/colas/Craig/CEN_38/BD Vectorielles/IGN - BDTOPO - 202309/bdtopo_dep26_202309_geopackage.zip/BDT_3-3_GPKG_LAMB93_D026-ED2023-09-15/BDT_3-3_GPKG_LAMB93_D026-ED2023-09-15.gpkg',
|
|
'/vsizip//home/colas/Craig/CEN_38/BD Vectorielles/IGN - BDTOPO - 202309/bdtopo_dep42_202309_geopackage.zip/BDT_3-3_GPKG_LAMB93_D042-ED2023-09-15/BDT_3-3_GPKG_LAMB93_D042-ED2023-09-15.gpkg'
|
|
]
|
|
|
|
lst_tab = [
|
|
'cimetiere', # BATI
|
|
'construction_surfacique', # BATI
|
|
'reservoir',
|
|
'batiment',
|
|
'troncon_de_route',
|
|
'piste_d_aerodrome',
|
|
'troncon_de_voie_ferree',
|
|
'terrain_de_sport',
|
|
]
|
|
# cimetiere
|
|
# construction_surfacique
|
|
# reservoir
|
|
# batiment
|
|
# terrain_de_sport
|
|
|
|
|
|
dict_cols = {
|
|
'id':'cleabs'
|
|
}
|
|
|
|
|
|
PATH = "/home/colas/Documents/5_BDD/6_IGN/BDTOPO/BDTOPO_3-3_TOUSTHEMES_SHP_LAMB93_D074_2023-09-15/BDTOPO/1_DONNEES_LIVRAISON_2023-09-00196/BDT_3-3_SHP_LAMB93_D074-ED2023-09-15/BATI"
|
|
PATH2 = '../TRANSPORT'
|
|
for tab in lst_tab:
|
|
file = tab.upper()+'.shp'
|
|
if file in listdir(path.join(PATH)):
|
|
df = gpd.read_file(path.join(PATH,file))
|
|
df.columns = df.columns.str.lower()
|
|
df.set_index('id',inplace=True)
|
|
# df.rename(columns=dict_cols,inplace=True)
|
|
print('Load %s'%tab)
|
|
else: continue
|
|
|
|
# columns_type = get_columns(con,schema_cad,tab)
|
|
# geom_type = [x for x in columns_type if x['name']=='geometrie']
|
|
# print('Ident geom_type OK')
|
|
|
|
# if geom_type:
|
|
# geom_type = geom_type[0]
|
|
|
|
# if df.geometry.name != geom_type['name']:
|
|
# df.rename_geometry(geom_type['name'],inplace=True)
|
|
if df.geometry.name != 'geom':
|
|
df.rename_geometry('geom',inplace=True)
|
|
|
|
has_z = True if df.has_z.all() else False
|
|
unique_geom = df.geom_type.unique()
|
|
geom_type = df.geom_type[0].upper() if len(unique_geom) == 1 else 'GEOMETRY'
|
|
geom_type = geom_type+'Z'if has_z else geom_type
|
|
|
|
(df
|
|
.to_wkt()
|
|
.to_sql(
|
|
tab,
|
|
con,
|
|
schema_cad,
|
|
if_exists='append',
|
|
index=True,
|
|
dtype={
|
|
'geom':Geometry(geometry_type=geom_type,srid=2154)
|
|
}
|
|
)
|
|
)
|
|
print(tab)
|
|
|
|
|
|
# get_table_pkey(con,schema_cad,tab)
|
|
# get_columns(con,schema_cad,tab)
|
|
|
|
## 38
|
|
# Load troncon_de_voie_ferree
|
|
# Ident geom_type OK
|
|
# troncon_de_voie_ferree
|
|
# Load terrain_de_sport
|
|
# Ident geom_type OK
|
|
# terrain_de_sport
|
|
## 26
|
|
# Load cimetiere
|
|
# Ident geom_type OK
|
|
# cimetiere
|
|
# Load construction_surfacique
|
|
# Ident geom_type OK
|
|
# construction_surfacique
|
|
# Load reservoir
|
|
# Ident geom_type OK
|
|
# reservoir
|
|
# Load batiment
|
|
# Ident geom_type OK
|
|
# batiment
|
|
|
|
## NO INSERT
|
|
# Load troncon_de_route
|
|
# Ident geom_type OK
|
|
|
|
|
|
for Zip in lst_zip:
|
|
print('\n'+Zip+'\n')
|
|
for tab in get_list_table(con,schema_cad):
|
|
if tab not in lst_tab:
|
|
continue
|
|
if Zip in lst_zip[:2] or (
|
|
Zip==lst_zip[2] and tab in [
|
|
'cimetiere',
|
|
'construction_surfacique',
|
|
'reservoir',
|
|
'batiment',
|
|
]):
|
|
continue
|
|
df = gpd.read_file(Zip,layer=tab)
|
|
print('Load %s'%tab)
|
|
|
|
columns_type = get_columns(con,schema_cad,tab)
|
|
geom_type = [x for x in columns_type if x['name']=='geometrie']
|
|
print('Ident geom_type OK')
|
|
|
|
if geom_type:
|
|
geom_type = geom_type[0]
|
|
|
|
if df.geometry.name != geom_type['name']:
|
|
df.rename_geometry(geom_type['name'],inplace=True)
|
|
|
|
df.to_wkt().to_sql(
|
|
tab,
|
|
con,
|
|
schema_cad,
|
|
if_exists='append',
|
|
index=False,
|
|
dtype={geom_type['name']:geom_type['type']} if geom_type else None
|
|
)
|
|
print(tab) |