96 lines
2.6 KiB
Python
96 lines
2.6 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: UTF-8 -*-.
|
|
|
|
import geopandas as gpd
|
|
import pandas as pd
|
|
from geoalchemy2 import Geometry
|
|
from sqlalchemy.engine import URL
|
|
from sqlalchemy import create_engine
|
|
import sys
|
|
|
|
|
|
# Liste des sites
|
|
lst_site = ['GOUT']
|
|
|
|
# Suppression des champs souhaités
|
|
delete = True
|
|
lst_cols_del = ['geometrie','date_debut_obs']
|
|
|
|
# Format d'extraction ['xls', 'xlsx', 'csv']
|
|
FORMAT = 'xlsx'
|
|
# Localistation du fichier de sortie
|
|
OUTPUT = '~/Documents/tmp/Jean_Luc/'
|
|
# Nom du fichier de sortie
|
|
NAME_OUT = 'observation_site_GOUT'
|
|
|
|
|
|
# Parametres bdd HOMER (out)
|
|
user_hom = 'cen_admin'
|
|
pwd_hom = '#CEN38@venir'
|
|
# user_hom = 'cgeier'
|
|
# pwd_hom = 'adm1n*sIcen'
|
|
adr_hom = '91.134.194.221'
|
|
port_hom = '5432'
|
|
base_hom = 'sicen2'
|
|
schema_hom = 'saisie'
|
|
table_hom = 'saisie_observation'
|
|
url_hom = URL.create('postgresql+psycopg2',
|
|
username=user_hom,
|
|
password=pwd_hom,
|
|
host=adr_hom,
|
|
database=base_hom,
|
|
)
|
|
engine_hom = create_engine(url_hom)
|
|
# engine_hom = create_engine('postgresql+psycopg2://{0}:{1}@{2}:{3}/{4}'.format(user_hom,pwd_hom,adr_hom,port_hom,base_hom), echo=False)
|
|
|
|
# Parametres bdd BART (in)
|
|
user_bart = 'cen_admin'
|
|
pwd_bart = '#CEN38@venir'
|
|
adr_bart = '192.168.0.3'
|
|
port_bart = '5432'
|
|
base_bart = 'bd_cen38'
|
|
schema_bart = 'sites'
|
|
table_bart = "c_sites_zonages"
|
|
url_bart = URL.create('postgresql+psycopg2',
|
|
username=user_bart,
|
|
password=pwd_bart,
|
|
host=adr_bart,
|
|
database=base_bart,
|
|
)
|
|
con_bart = create_engine(url_bart)
|
|
# con_bart = create_engine('postgresql+psycopg2://{0}:{1}@{2}:{3}/{4}'.format(user_bart,pwd_bart,adr_bart,port_bart,base_bart), echo=False)
|
|
|
|
epsg = '2154'
|
|
crs = 'EPSG:%s'%epsg
|
|
|
|
|
|
|
|
sql = "SELECT geom FROM {sch}.{tab} WHERE code_site in ('{lst_site}') AND type_zonage = 'ZO'".format(sch=schema_bart, tab=table_bart, lst_site="','".join(lst_site))
|
|
gdf_site = gpd.read_postgis(
|
|
sql = sql,
|
|
con = con_bart,
|
|
)
|
|
if gdf_site.shape[0] > 1 :
|
|
union = gdf_site.geom.cascaded_union
|
|
Sunion = gpd.GeoSeries(union)
|
|
gdf_union = gpd.GeoDataFrame(Sunion, geometry='geom', columns=['geom'], crs=crs)
|
|
gdf_site = gdf_union
|
|
|
|
|
|
sql = '''SELECT * FROM {sch}.{tab} WHERE ST_Intersects (geometrie, 'SRID={epsg};{poly}')'''.format(sch=schema_hom, tab=table_hom, epsg=epsg, poly=gdf_site.geom[0])
|
|
gdf_saisie = gpd.read_postgis(
|
|
sql = sql,
|
|
con = engine_hom,
|
|
geom_col = 'geometrie'
|
|
)
|
|
|
|
df = pd.DataFrame(gdf_saisie)
|
|
if delete:
|
|
df.drop(columns= lst_cols_del, inplace=True)
|
|
|
|
if FORMAT in ['xls', 'xlsx']:
|
|
df.to_excel(OUTPUT + NAME_OUT + '.' + FORMAT)
|
|
if FORMAT == 'csv':
|
|
df.to_file(OUTPUT + NAME_OUT + '.' + FORMAT)
|
|
|
|
sys.exit('END') |