def import_donnes_base(bdd, schema, table_graph,table_vertex, localisation='boulot' ):
"""
OUvrir une connexion vers le servuer de reference et recuperer les donn�es
en entree :
bdd : string de reference de la connexion, selon le midule Outils , fichier Id_connexions, et module Connexion_transferts
schema : schema contenant les tables de graph et de vertex
table_graph : table contennat le referentiel (cf pgr_createtopology)
table_vertex : table contenant la ecsription des vertex (cf pgr_analyzegraph)
localisation : juste un moyen d'appeler le bon fichier d'identifiants de connexions en fonction de l'ordi sur lequel je bosse
en sortie :
df : dataframe telle que telcharg�es depuis la bdd
"""
#avant tout verf que toute les colonnes necessaires au traitements sont presents
flag_col, col_manquante=Outils.check_colonne_in_table_bdd(bdd,localisation, schema, table_graph,*list_colonnes_necessaires)
if not flag_col :
raise ManqueColonneError(col_manquante)
with ct.ConnexionBdd(bdd,localisation=localisation) as c :
requete1=f"""with jointure as (
select t.*, v1.cnt nb_intrsct_src, st_astext(v1.the_geom) as src_geom, v2.cnt as nb_intrsct_tgt, st_astext(v2.the_geom) as tgt_geom
from {schema}.{table_graph} t
left join {schema}.{table_vertex} v1 on t.source=v1.id
left join {schema}.{table_vertex} v2 on t.target=v2.id
)
select j.* from jointure j, zone_test_agreg z
where st_intersects(z.geom, j.geom)"""
requete2=f"""select t.*, v1.cnt nb_intrsct_src, st_astext(v1.the_geom) as src_geom, v2.cnt as nb_intrsct_tgt, st_astext(v2.the_geom) as tgt_geom
from {schema}.{table_graph} t
left join {schema}.{table_vertex} v1 on t.source=v1.id
left join {schema}.{table_vertex} v2 on t.target=v2.id"""
df = gp.read_postgis(requete2, c.sqlAlchemyConn)
return df
def corresp_te_bretelle(bdd,
df_lignes,
tronc_elem_synth,
dist_agreg=50,
localisation='boulot'):
"""
regrouper les troncon elementaires des bretelles
en entree :
df_lignes : df des lignes ign issue de identifier_rd_pt avec id_ign en index
tronc_elem_synth : df des tronc elem, issu de carac_te()
dist_agreg : integer : distance d'agreg entre les troncons
en sortie :
corresp_bret : dfde correspondance entre l'ancen tronc elem et le nouveau, uniquement pour les lignes concernees
"""
#select tronc_elem bretelles
list_id_bretelle = df_lignes.loc[df_lignes['nature'] ==
'Bretelle'].index.tolist()
tronc_bretelle = tronc_elem_synth.loc[tronc_elem_synth.apply(
lambda x: any([a for a in x['id'] if a in list_id_bretelle]),
axis=1)].copy()
#geom en wkt pour tranfert dans bdd
tronc_bretelle_wkt = tronc_bretelle.reset_index()[['idtronc',
'geom']].copy()
tronc_bretelle_wkt['geom'] = tronc_bretelle_wkt.apply(
lambda x: x['geom'].wkt, axis=1)
#on bascule en sql pour utiliser le cluster de postgres qui est plus puissant car il gere tout type de geom et donc la distance entre les geoms devient plus precise
#cree la table qui va accueillir les donnees de bretelles
metadata = MetaData()
bretel_table = Table('bretelle', metadata,
Column('idtronc', Integer, primary_key=True),
Column('geom', Geometry()))
requete = f"SELECT idtronc, ST_ClusterDBSCAN(geom, eps := {dist_agreg}, minpoints := 2) over () AS cid FROM public.bretelle"
#creation dans Bdd puis utilisation sql puis retour
with ct.ConnexionBdd(bdd, localisation) as c:
try:
bretel_table.drop(c.engine)
except:
pass
bretel_table.create(c.engine)
c.sqlAlchemyConn.execute(bretel_table.insert(),
tronc_bretelle_wkt.to_dict('records'))
df_cluster = pd.read_sql_query(requete, c.sqlAlchemyConn)
#creation de la tble de correspondance
bretlle_a_grp = df_cluster.loc[~df_cluster.cid.isna()].copy()
corresp_bret = bretlle_a_grp.merge(
bretlle_a_grp.groupby('cid')['idtronc'].min().reset_index(), on='cid')
corresp_bret = corresp_bret.loc[
corresp_bret['idtronc_x'] != corresp_bret['idtronc_y']].copy()[[
'idtronc_x', 'idtronc_y'
]].set_index('idtronc_x')
return corresp_bret
def ouvrir_fichier_lapi_final(date_debut, date_fin) :
"""ouvrir les donnees lapi depuis la Bdd 'lapi' sur le serveur partage GTI
l'ouvertur se fait par appel d'une connexionBdd Python (scripts de travail ici https://github.com/nantodevison/Outils/blob/master/Outils/Martin_Perso/Connexion_Transfert.py)
en entree : date_debut : string de type YYYY-MM-DD hh:mm:ss
date_fin: string de type YYYY-MM-DD hh:mm:ss
en sortie : dataframe pandas des passages df_passage, des plaques d'immatriculation non cryptee df_plaque, des immats avec les attributs d'identification vl/pl
"""
with ct.ConnexionBdd('gti_lapi_final') as c :
requete_passage=f"select case when camera_id=13 or camera_id=14 then 13 when camera_id=15 or camera_id=16 then 15 else camera_id end::integer as camera_id , created, immatriculation as immat, fiability, l, state from data.te_passage where created between '{date_debut}' and '{date_fin}'"
df_passage=pd.read_sql_query(requete_passage, c.sqlAlchemyConn)
requete_plaque=f"select plaque_ouverte, chiffree from data.te_plaque_courte"
df_plaque=pd.read_sql_query(requete_plaque, c.sqlAlchemyConn)
requete_immat=f"select immatriculation,pl_siv,pl_3barriere,pl_2barriere,pl_1barriere,pl_mmr75,vul_siv,vul_mmr75,vl_siv,vl_mmr75 from data.te_immatriculation"
df_immat=pd.read_sql_query(requete_immat, c.sqlAlchemyConn)
return df_passage,df_plaque, df_immat
def epurer_graph(bdd,id_name, schema, table, table_vertex):
"""
enlever d'un graph en bdd les voies de catégorie 5 qui intersectent des voies de catégorie 4 ou plus
attention, cela modifie les tables directement dans postgis.
attention mm pb pour multilignes que dans creer_graph()
en entree :
bdd : string, bdd postgis utilisee pour faire le graph
id_name : string : nom de la colonne contenant l'id_uniq
schema : string, nom du schema ou stocke le graph teporaire dans postgis
table : string, nom dde la table ou stocke le graph teporaire dans postgis
table_vertex : string, nom de l table des vertex ou stocke le graph teporaire dans postgis
"""
with ct.ConnexionBdd(bdd) as c:
vertex=pd.read_sql(f'select * from {schema}.{table_vertex}',c.sqlAlchemyConn)
lignes=gp.GeoDataFrame.from_postgis(f'select * from {schema}.{table}',c.sqlAlchemyConn,geom_col='geom',crs='epsg:2154')
lignes_filtrees=epurer_graph_trouver_lignes_vertex(vertex, lignes)[0]
#la repasser dans la table postgis
creer_graph(lignes_filtrees,bdd,id_name,schema, table, table_vertex)
def import_donnes_base(ref_connexion):
"""
OUvrir une connexion vers le servuer de reference et recuperer les données
en entree :
ref_connexion : string de reference de la connexion, selon le midule Outils , fichier Id_connexions, et module Connexion_transferts
en sortie :
df : dataframe telle que telchargées depuis la bdd
"""
with ct.ConnexionBdd('local_otv') as c:
requete = """with jointure as (
select t.*, v1.cnt nb_intrsct_src, st_astext(v1.the_geom) as src_geom, v2.cnt as nb_intrsct_tgt, st_astext(v2.the_geom) as tgt_geom
from public.traf2015_bdt17_ed15_l t
left join public.traf2015_bdt17_ed15_l_vertices_pgr v1 on t.source=v1.id
left join public.traf2015_bdt17_ed15_l_vertices_pgr v2 on t.target=v2.id
)
select j.* from jointure j, zone_test_agreg z
where st_intersects(z.geom, j.geom)"""
df = gp.read_postgis(requete, c.connexionPsy)
return df
def check_colonne_in_table_bdd(bdd, schema_r, table_r,*colonnes) :
"""
verifier qu'une table d'une bdd contient les colonnes ciblees
in :
bdd : string : descriptions de la bdd, cf modules id_connexion
schema_r : string : le nom du schema supportant la table
table_r : le nom de la table
colonnes : le nom des colonnes devant etre dans la table, separe par une virgule
out :
flag : booleen : true si toute les colonnes sont das la table, False sinon
list_colonne_manquante : lisrte des colonnes qui manque
"""
with ct.ConnexionBdd(bdd) as c :
m=MetaData(bind=c.engine,schema=schema_r)
m.reflect()
inspector=inspect(c.engine)
for t in m.tables.keys() :
if t==f'{schema_r}.{table_r}' :
columns=[c['name'] for c in inspector.get_columns(table_r, schema=schema_r)]
if all([e in columns for e in colonnes]) :
return True,[]
else :
return False,[e for e in colonnes if e not in columns]
def creer_graph(gdf, bdd,id_name='id', schema='public', table='graph_temp', table_vertex='graph_temp_vertices_pgr',localisation='boulot'):
"""
creer un graph a partir d'une geodataframe en utilisant les ofnctions de postgis.
attention, pas de return, la table reste dans postgis.
attention aussi si les lignes en entree sont des multilignes : seule la 1ere composante estconservee
en entree :
gdf : geodataframe de geopandas
bdd : string, bdd postgis utilisee pour faire le graph
id_name : string : nom de la colonne contenant l'id_uniq
schema : string, nom du schema ou stocke le graph teporaire dans postgis
table : string, nom dde la table ou stocke le graph teporaire dans postgis
table_vertex : string, nom de l table des vertex ou stocke le graph teporaire dans postgis
"""
gdf_w=gdf.copy()
#verifier que l'identifiant est un entier
if gdf[id_name].dtype!=np.int64 :
raise TypeError('l''id doit etre converti en entier')
#verifier qu'on a bien une geoDataFrame
if not isinstance(gdf_w,gp.GeoDataFrame) :
raise TypeError('if faut convertir les donnees en GeoDataFrame')
#trouver le nom de la geom
geom_name=gdf_w.geometry.name
#passer les donnees en 2D et en linestring si Multi
gdf_w[geom_name]=gdf_w.geometry.apply(lambda x : LineString([xy[0:2] for xy in list(x[0].coords)]) if x.geom_type=='MultiLineString' else
LineString([xy[0:2] for xy in list(x.coords)]))
#type de geom ets rid
geo_type=gdf_w.geometry.geom_type.unique()[0].upper()
geo_srid=int(gdf_w.crs.to_string().split(':')[1])
#passer la geom en texte pour export dans postgis
gdf_w[geom_name] = gdf_w[geom_name].apply(lambda x: WKTElement(x.wkt, srid=geo_srid))
with ct.ConnexionBdd(bdd, localisation=localisation) as c:
#supprimer table si elle existe
rqt=f"drop table if exists {schema}.{table} ; drop table if exists {schema}.{table_vertex} "
c.sqlAlchemyConn.execute(rqt)
print(f'creer_graph : donnees mise en forme, connexion ouverte ; {datetime.now()}')
#passer les donnees
gdf_w.to_sql(table,c.sqlAlchemyConn,schema=schema, index=False,
dtype={geom_name:Geometry(geo_type, srid=geo_srid)})
print(f'creer_graph : donnees transferees dans la base postgis ; {datetime.now()}')
rqt_modif_geom=f"""alter table {schema}.{table} rename column {geom_name} to geom ;
alter table {schema}.{table} alter column geom type geometry(MULTILINESTRING,{geo_srid})
using st_Multi(geom)""" if geom_name!='geom' else f"""
alter table {schema}.{table} alter column geom type geometry(MULTILINESTRING,{geo_srid})
using st_Multi(geom) ; """
rqt_modif_attr=f"""alter table {schema}.{table} alter column "source" TYPE int8 ;
alter table {schema}.{table} alter column "target" TYPE int8"""
c.sqlAlchemyConn.execute(rqt_modif_geom)
c.sqlAlchemyConn.execute(rqt_modif_attr)
print(f'creer_graph : geometrie modifiee ; {datetime.now()}')
#creer le graph : soit source et target existent deja et on les remets a null, soit on les crees
if all([a in gdf_w.columns for a in ['source', 'target']]) :
rqt_creation_graph=f"""update {schema}.{table} set source=null::integer, target=null::integer ;
select pgr_createTopology('{schema}.{table}', 0.001,'geom','{id_name}')"""
elif all([a not in gdf_w.columns for a in ['source', 'target']]):
rqt_creation_graph=f"""alter table {schema}.{table} add column source int, add column target int ;
select pgr_createTopology('{schema}.{table}', 0.001,'geom','{id_name}')"""
c.sqlAlchemyConn.execute(rqt_creation_graph)
print(f'creer_graph : topologie cree ; {datetime.now()}')
rqt_anlyse_graph=f"SELECT pgr_analyzeGraph('{schema}.{table}', 0.001,'geom','{id_name}')"
c.curs.execute(rqt_anlyse_graph)#je le fait avec psycopg2 car avec sql acchemy ça ne passe pas
print(f'creer_graph : graph cree ; {datetime.now()}')
c.connexionPsy.commit()
Module de creation de troncon homogene
'''
import matplotlib
import geopandas as gp
import pandas as pd
import numpy as np
from datetime import datetime
import Connexion_Transfert as ct
from shapely.wkt import loads
from shapely.ops import polygonize, linemerge
import Outils
#from psycopg2 import extras
# ouvrir connexion, recuperer donnees
with ct.ConnexionBdd('local_otv') as c:
df = gp.read_postgis(
"select t.*, v1.cnt nb_intrsct_src, st_astext(v1.the_geom) as src_geom, v2.cnt as nb_intrsct_tgt, st_astext(v2.the_geom) as tgt_geom from public.traf2015_bdt17_ed15_l t left join public.traf2015_bdt17_ed15_l_vertices_pgr v1 on t.\"source\"=v1.id left join public.traf2015_bdt17_ed15_l_vertices_pgr v2 on t.target=v2.id ",
c.connexionPsy)
#variables generales
nature_2_chaussees = ['Route à 2 chaussées', 'Quasi-autoroute', 'Autoroute']
#donnees generale du jdd
df.set_index(
'id_ign', inplace=True
) # passer l'id_ign commme index ; necessaire sinon pb avec geometrie vu comme texte
df.crs = {'init': 'epsg:2154'}
df2_chaussees = df.loc[df['nature'].isin(
nature_2_chaussees)] #isoler les troncon de voies decrits par 2 lignes