1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
import pandas as pd
from sqlalchemy import create_engine
import time
engine = create_engine('postgresql://postgres:0000@localhost:5432/maison', echo=False)
print("---------EXTRACTION DES DEPARTEMENTS EN LISTE---------")
le_temps = time.time()
liste_query = engine.execute("select distinct left(insee_com, 2) from commune order by 1")
liste_df = pd.DataFrame(liste_query.fetchall())
liste_df.columns = ["col"]
liste = liste_df.col.unique()
print("Terminé en %s secondes" %(round(time.time() - le_temps, 2)))
for i in range(0, len(liste)):
le_temps_begin = time.time()
print("\n\n----------PREPARATION POUR DEPARTEMENT %s" %liste[i])
engine.execute("create table if not exists bati_%s (id text, geom geometry, fid bigint, nature text, usage1 text, usage2 text, leger text, etat text, date_creat text, date_maj text, date_app text, date_conf text, source text, id_source text, prec_plani text, prec_alti text, nb_logts text, nb_etages text, mat_murs text, mat_toits text, hauteur text, z_min_sol text, z_min_toit text, z_max_toit text, z_max_sol text, origin_bat text, app_ff text, insee_com text, surface_m2 numeric, est_intersecte text );" %liste[i])
for j in range(1, 96): #96 CORRESPOND AU NOMBRE DE COUCHES QUI POSSEDENT LES BATIMENTS, TOUS DEPARTEMENTS MELANGES
le_temps = time.time()
print("\n----------PREPARATION POUR table_%s" %j)
engine.execute("insert into bati_%s(id , geom , fid, nature , usage1 , usage2 , leger , etat , date_creat , date_maj , date_app , date_conf , source , id_source , prec_plani , prec_alti , nb_logts , nb_etages , mat_murs , mat_toits , hauteur , z_min_sol , z_min_toit , z_max_toit, z_max_sol , origin_bat , app_ff , insee_com , surface_m2, est_intersecte ) (select id , geom , fid, nature , usage1 , usage2 , leger , etat , date_creat , date_maj , date_app , date_conf , source , id_source , prec_plani , prec_alti , nb_logts , nb_etages , mat_murs , mat_toits , hauteur , z_min_sol , z_min_toit , z_max_toit, z_max_sol , origin_bat , app_ff , insee_com , surface_m2, est_intersecte from public.table_%s where id not in (select id from bati_%s) and left(insee_com, 2) = '%s')" %(liste[i], j, liste[i], liste[i]))
print("Terminé en %s secondes -- ELEMENT NUMERO %s" %(round(time.time() - le_temps, 2), i))
print("\n\n--------------------DEPARTEMENT %s TERMINE EN %s SECONDES !!-------------------"%(liste[i], round(time.time() - le_temps_begin, 2))) |
