def aspectRatio(grid_input,
grid_output,
roads_input,
built_input,
seg_dist,
seg_length,
buffer_size,
epsg,
project_encoding,
server_postgis,
port_number,
user_postgis,
password_postgis,
database_postgis,
schema_postgis,
path_time_log,
format_vector='ESRI Shapefile',
extension_vector=".shp",
save_results_intermediate=False,
overwrite=True):
print(bold + yellow + "Début du calcul de l'indicateur Aspect Ratio." +
endC + "\n")
timeLine(path_time_log, "Début du calcul de l'indicateur Aspect Ratio : ")
if debug >= 3:
print(bold + green + "aspectRatio() : Variables dans la fonction" +
endC)
print(cyan + "aspectRatio() : " + endC + "grid_input : " +
str(grid_input) + endC)
print(cyan + "aspectRatio() : " + endC + "grid_output : " +
str(grid_output) + endC)
print(cyan + "aspectRatio() : " + endC + "roads_input : " +
str(roads_input) + endC)
print(cyan + "aspectRatio() : " + endC + "built_input : " +
str(built_input) + endC)
print(cyan + "aspectRatio() : " + endC + "seg_dist : " +
str(seg_dist) + endC)
print(cyan + "aspectRatio() : " + endC + "seg_length : " +
str(seg_length) + endC)
print(cyan + "aspectRatio() : " + endC + "buffer_size : " +
str(buffer_size) + endC)
print(cyan + "aspectRatio() : " + endC + "epsg : " + str(epsg) + endC)
print(cyan + "aspectRatio() : " + endC + "project_encoding : " +
str(project_encoding) + endC)
print(cyan + "aspectRatio() : " + endC + "server_postgis : " +
str(server_postgis) + endC)
print(cyan + "aspectRatio() : " + endC + "port_number : " +
str(port_number) + endC)
print(cyan + "aspectRatio() : " + endC + "user_postgis : " +
str(user_postgis) + endC)
print(cyan + "aspectRatio() : " + endC + "password_postgis : " +
str(password_postgis) + endC)
print(cyan + "aspectRatio() : " + endC + "database_postgis : " +
str(database_postgis) + endC)
print(cyan + "aspectRatio() : " + endC + "schema_postgis : " +
str(schema_postgis) + endC)
print(cyan + "aspectRatio() : " + endC + "format_vector : " +
str(format_vector) + endC)
print(cyan + "aspectRatio() : " + endC + "extension_vector : " +
str(extension_vector) + endC)
print(cyan + "aspectRatio() : " + endC + "path_time_log : " +
str(path_time_log) + endC)
print(cyan + "aspectRatio() : " + endC +
"save_results_intermediate : " + str(save_results_intermediate) +
endC)
print(cyan + "aspectRatio() : " + endC + "overwrite : " +
str(overwrite) + endC)
# Constantes liées aux données PostGIS et GRASS
LOCATION = "LOCATION"
MAPSET = "MAPSET"
SUFFIX_SEGMENTED = "_segmented"
if not os.path.exists(grid_output) or overwrite:
############################################
### Préparation générale des traitements ###
############################################
if os.path.exists(grid_output):
removeVectorFile(grid_output)
temp_path = os.path.dirname(grid_output) + os.sep + "AspectRatio"
file_name = os.path.splitext(os.path.basename(roads_input))[0]
roads_segmented = temp_path + os.sep + file_name + SUFFIX_SEGMENTED + extension_vector
if os.path.exists(temp_path):
shutil.rmtree(temp_path)
# Variables d'environnement spécifiques à GRASS
gisbase = os.environ['GISBASE']
gisdb = "GRASS_database"
# Variables liées à GRASS permettant la construction de 'LOCATION' et 'MAPSET'
xmin, xmax, ymin, ymax = getEmpriseFile(roads_input, format_vector)
pixel_size_x, pixel_size_y = 1, 1
#####################################
### Préparation géodatabase GRASS ###
#####################################
print(bold + cyan + "Préparation de la géodatabase GRASS :" + endC)
timeLine(path_time_log, " Préparation de la géodatabase GRASS : ")
# Initialisation de la connexion à la géodatabase GRASS
gisbase, gisdb, location, mapset = initializeGrass(
temp_path, xmin, xmax, ymin, ymax, pixel_size_x, pixel_size_y,
epsg, gisbase, gisdb, LOCATION, MAPSET, True, overwrite)
###################################################
### Division des routes en segments de x mètres ###
###################################################
print(bold + cyan + "Segmentation des routes avec GRASS :" + endC)
timeLine(path_time_log, " Segmentation des routes avec GRASS : ")
# Segmentation du jeu de données route en segments de x mètres
splitGrass(roads_input, roads_segmented, seg_dist, format_vector,
overwrite)
cleanGrass(temp_path, gisdb, save_results_intermediate)
###########################################
### Préparation base de données PostGIS ###
###########################################
print(bold + cyan + "Préparation de la base de données PostGIS :" +
endC)
timeLine(path_time_log,
" Préparation de la base de données PostGIS : ")
# Création de la base de données PostGIS
# Conflits avec autres indicateurs (Height of Roughness Elements / Terrain Roughness Class)
createDatabase(database_postgis)
# Import des fichiers shapes maille, bati et routes segmentées dans la base de données PostGIS
table_name_maille = importVectorByOgr2ogr(database_postgis,
grid_input,
'ara_maille',
user_name=user_postgis,
password=password_postgis,
ip_host=server_postgis,
num_port=str(port_number),
schema_name=schema_postgis,
epsg=str(epsg),
codage=project_encoding)
table_name_bati = importVectorByOgr2ogr(database_postgis,
built_input,
'ara_bati',
user_name=user_postgis,
password=password_postgis,
ip_host=server_postgis,
num_port=str(port_number),
schema_name=schema_postgis,
epsg=str(epsg),
codage=project_encoding)
table_name_routes_seg = importVectorByOgr2ogr(
database_postgis,
roads_segmented,
'ara_routes_seg',
user_name=user_postgis,
password=password_postgis,
ip_host=server_postgis,
num_port=str(port_number),
schema_name=schema_postgis,
epsg=str(epsg),
codage=project_encoding)
# Connection à la base de données PostGIS et initialisation de 'cursor' (permet de récupérer des résultats de requêtes SQL pour les traiter en Python)
connection = openConnection(database_postgis,
user_postgis,
password_postgis,
server_postgis,
str(port_number),
schema_name=schema_postgis)
cursor = connection.cursor()
# Requête d'ajout de champ ID segment route dans la table routes_seg et création des index pour les shapes importés
query_preparation = """
ALTER TABLE %s ADD COLUMN id_seg serial;
--CREATE INDEX IF NOT EXISTS routes_seg_geom_gist ON %s USING GIST (geom);
--CREATE INDEX IF NOT EXISTS bati_geom_gist ON %s USING GIST (geom);
--CREATE INDEX IF NOT EXISTS maille_geom_gist ON %s USING GIST (geom);
""" % (table_name_routes_seg, table_name_routes_seg, table_name_bati,
table_name_maille)
if debug >= 2:
print(query_preparation)
executeQuery(connection, query_preparation)
##############################################################################
### Création des segments de y mètres perpendiculaires aux segments routes ###
##############################################################################
print(bold + cyan +
"Création des segments perpendiculaires aux routes :" + endC)
timeLine(path_time_log,
" Création des segments perpendiculaires aux routes : ")
# Début de la construction de la requête de création des segments perpendiculaires
query_seg_perp = "DROP TABLE IF EXISTS ara_seg_perp;\n"
query_seg_perp += "CREATE TABLE ara_seg_perp (id_seg text, id_perp text, xR float, yR float, xP float, yP float, geom geometry);\n"
query_seg_perp += "INSERT INTO ara_seg_perp VALUES\n"
# Récupération de la liste des identifiants segments routes
cursor.execute(
"SELECT id_seg FROM %s GROUP BY id_seg ORDER BY id_seg;" %
table_name_routes_seg)
id_seg_list = cursor.fetchall()
# Boucle sur les segments routes
nb_seg = len(id_seg_list)
treat_seg = 1
for id_seg in id_seg_list:
if debug >= 4:
print(bold + " Traitement du segment route : " + endC +
str(treat_seg) + "/" + str(nb_seg))
id_seg = id_seg[0]
# Table temporaire ne contenant qu'un segment route donné : ST_LineMerge(geom) permet de passer la géométrie de MultiLineString à LineString, pour éviter des problèmes de requêtes spatiales
query_temp1_seg = "DROP TABLE IF EXISTS ara_temp1_seg;\n"
query_temp1_seg += "CREATE TABLE ara_temp1_seg AS SELECT id_seg as id_seg, ST_LineMerge(geom) as geom FROM %s WHERE id_seg = %s;\n" % (
table_name_routes_seg, id_seg)
if debug >= 4:
print(query_temp1_seg)
executeQuery(connection, query_temp1_seg)
# Récupération du nombre de sommets du segment route (utile pour les segments routes en courbe, permet de récupérer le dernier point du segment)
cursor.execute("SELECT ST_NPoints(geom) FROM ara_temp1_seg;")
nb_points = cursor.fetchone()
# Récupération des coordonnées X et Y des points extrémités du segment route
query_xR1 = "SELECT ST_X(geom) as X FROM (SELECT ST_AsText(ST_PointN(geom,1)) as geom FROM ara_temp1_seg) as toto;"
cursor.execute(query_xR1)
xR1 = cursor.fetchone()
query_yR1 = "SELECT ST_Y(geom) as Y FROM (SELECT ST_AsText(ST_PointN(geom,1)) as geom FROM ara_temp1_seg) as toto;"
cursor.execute(query_yR1)
yR1 = cursor.fetchone()
query_xR2 = "SELECT ST_X(geom) as X FROM (SELECT ST_AsText(ST_PointN(geom,%s)) as geom FROM ara_temp1_seg) as toto;" % (
nb_points)
cursor.execute(query_xR2)
xR2 = cursor.fetchone()
query_yR2 = "SELECT ST_Y(geom) as Y FROM (SELECT ST_AsText(ST_PointN(geom,%s)) as geom FROM ara_temp1_seg) as toto;" % (
nb_points)
cursor.execute(query_yR2)
yR2 = cursor.fetchone()
# Transformation des coordonnées X et Y des points extrémités du segment route en valeurs numériques
xR1 = float(str(xR1)[1:-2])
yR1 = float(str(yR1)[1:-2])
xR2 = float(str(xR2)[1:-2])
yR2 = float(str(yR2)[1:-2])
if debug >= 4:
print(" xR1 = " + str(xR1))
print(" yR1 = " + str(yR1))
print(" xR2 = " + str(xR2))
print(" yR2 = " + str(yR2))
# Calcul des delta X et Y entre les points extrémités du segment route
dxR = xR1 - xR2
dyR = yR1 - yR2
if debug >= 4:
print(" dxR = " + str(dxR))
print(" dyR = " + str(dyR))
print("\n")
# Suppression des cas où le script créé des perpendiculaires tous les cm ! Bug lié à la segmentation des routes
dist_R1_R2 = math.sqrt((abs(dxR)**2) + (abs(dyR)**2))
if dist_R1_R2 >= (seg_dist / 2):
# Calcul de l'angle (= gisement) entre le Nord et le segment route
if dxR == 0 or dyR == 0:
if dxR == 0 and dyR > 0:
aR = 0
elif dxR > 0 and dyR == 0:
aR = 90
elif dxR == 0 and dyR < 0:
aR = 180
elif dxR < 0 and dyR == 0:
aR = 270
else:
aR = math.degrees(math.atan(dxR / dyR))
if aR < 0:
aR = aR + 360
if debug >= 4:
print(" aR = " + str(aR))
# Calcul des angles (= gisements) entre le Nord et les 2 segments perpendiculaires au segment route
aP1 = aR + 90
if aP1 < 0:
aP1 = aP1 + 360
if aP1 >= 360:
aP1 = aP1 - 360
aP2 = aR - 90
if aP2 < 0:
aP2 = aP2 + 360
if aP2 >= 360:
aP2 = aP2 - 360
if debug >= 4:
print(" aP1 = " + str(aP1))
print(" aP2 = " + str(aP2))
# Calculs des coordonnées des nouveaux points à l'extrémité de chaque segment perpendiculaire pour le segment route sélectionné
xP1 = xR1 + (seg_length * math.sin(math.radians(aP1)))
yP1 = yR1 + (seg_length * math.cos(math.radians(aP1)))
xP2 = xR1 + (seg_length * math.sin(math.radians(aP2)))
yP2 = yR1 + (seg_length * math.cos(math.radians(aP2)))
if debug >= 4:
print(" xP1 = " + str(xP1))
print(" yP1 = " + str(yP1))
print(" xP2 = " + str(xP2))
print(" yP2 = " + str(yP2))
print("\n")
# Construction de la requête de création des 2 segments perpendiculaires pour le segment route sélectionné
query_seg_perp += " ('%s', '%s_perp1', %s, %s, %s, %s, 'LINESTRING(%s %s, %s %s)'),\n" % (
str(id_seg), str(id_seg), xR1, yR1, xP1, yP1, xR1, yR1,
xP1, yP1)
query_seg_perp += " ('%s', '%s_perp2', %s, %s, %s, %s, 'LINESTRING(%s %s, %s %s)'),\n" % (
str(id_seg), str(id_seg), xR1, yR1, xP2, yP2, xR1, yR1,
xP2, yP2)
treat_seg += 1
# Fin de la construction de la requête de création des segments perpendiculaires et exécution de cette requête
query_seg_perp = query_seg_perp[:-2] + ";\n" # Transformer la virgule de la dernière ligne SQL en point-virgule (pour terminer la requête)
query_seg_perp += "ALTER TABLE ara_seg_perp ALTER COLUMN geom TYPE geometry(LINESTRING,%s) USING ST_SetSRID(geom,%s);\n" % (
epsg, epsg) # Mise à jour du système de coordonnées
query_seg_perp += "CREATE INDEX IF NOT EXISTS seg_perp_geom_gist ON ara_seg_perp USING GIST (geom);\n"
if debug >= 2:
print(query_seg_perp)
executeQuery(connection, query_seg_perp)
###################################################
### Intersect segments perpendiculaires et bâti ###
###################################################
print(
bold + cyan +
"Intersect entre les segments perpendiculaires et les bâtiments :"
+ endC)
timeLine(
path_time_log,
" Intersect entre les segments perpendiculaires et les bâtiments : "
)
# Requête d'intersect entre les segments perpendiculaires et les bâtiments
query_intersect = """
DROP TABLE IF EXISTS ara_intersect_bati;
CREATE TABLE ara_intersect_bati AS
SELECT r.id_seg as id_seg, r.id_perp as id_perp, r.xR as xR, r.yR as yR, b.HAUTEUR as haut_bati, ST_Intersection(r.geom, b.geom) as geom
FROM ara_seg_perp as r, %s as b
WHERE ST_Intersects(r.geom, b.geom);
ALTER TABLE ara_intersect_bati ADD COLUMN id_intersect serial;
CREATE INDEX IF NOT EXISTS intersect_bati_geom_gist ON ara_intersect_bati USING GIST (geom);
""" % table_name_bati
if debug >= 2:
print(query_intersect)
executeQuery(connection, query_intersect)
##################################################################################################################
### Récupération des demi-largeurs de rue et de la hauteur du 1er bâtiment pour chaque segment perpendiculaire ###
##################################################################################################################
print(
bold + cyan +
"Récupération des informations nécessaires au calcul du rapport d'aspect :"
+ endC)
timeLine(
path_time_log,
" Récupération des informations nécessaires au calcul du rapport d'aspect : "
)
# Début de la construction de la requête de création des points route, avec infos de demi-largeurs de rue et hauteurs des bâtiments
query_pt_route = "DROP TABLE IF EXISTS ara_asp_ratio_by_seg;\n"
query_pt_route += "CREATE TABLE ara_asp_ratio_by_seg (id_seg text, xR float, yR float, width1 float, height1 float, width2 float, height2 float, geom geometry);\n"
query_pt_route += "INSERT INTO ara_asp_ratio_by_seg VALUES\n"
# Récupération de la liste des identifiants segments routes (uniquement ceux qui intersectent du bâti)
cursor.execute(
"SELECT id_seg FROM ara_intersect_bati GROUP BY id_seg ORDER BY id_seg;"
)
id_seg_list = cursor.fetchall()
# Boucle sur les segments routes
nb_seg = len(id_seg_list)
treat_seg = 1
for id_seg in id_seg_list:
if debug >= 4:
print(bold + " Traitement du segment route : " + endC +
str(treat_seg) + "/" + str(nb_seg))
id_seg = id_seg[0]
# Table temporaire ne contenant que les intersects d'un segment route donné
query_temp2_seg = "DROP TABLE IF EXISTS ara_temp2_seg;\n"
query_temp2_seg += "CREATE TABLE ara_temp2_seg AS SELECT id_seg, id_perp, xR, yR, haut_bati, id_intersect, geom FROM ara_intersect_bati WHERE id_seg = '%s';\n" % (
id_seg)
if debug >= 4:
print(query_temp2_seg)
executeQuery(connection, query_temp2_seg)
# Récupération des coordonnées X et Y du point route du segment route associé
cursor.execute("SELECT xR FROM ara_temp2_seg;")
xR = cursor.fetchone()
cursor.execute("SELECT yR FROM ara_temp2_seg;")
yR = cursor.fetchone()
# Transformation des coordonnées X et Y du point route du segment route associé en valeurs numériques
xR = float(str(xR)[1:-2])
yR = float(str(yR)[1:-2])
if debug >= 4:
print(" xR = " + str(xR))
print(" yR = " + str(yR))
print("\n")
# Initialisation des variables demi-largeurs de rue et hauteur du 1er bâtiment intersecté
w1 = 0
h1 = 0
w2 = 0
h2 = 0
# Récupération de la liste des identifiants segments perpendiculaires de la table temp2_seg
cursor.execute(
"SELECT id_perp FROM ara_temp2_seg GROUP BY id_perp ORDER BY id_perp;"
)
id_perp_list = cursor.fetchall()
# Boucle sur les perpendiculaires (max 2) d'un segment route donné
for id_perp in id_perp_list:
# Récupération du numéro de perpendiculaire (1 ou 2 ~ droite ou gauche)
num_seg = float(str(id_perp)[-4:-3])
# Initialisation de listes contenant les demi-largeurs de rue et les hauteurs des bâtiments intersectés
length_list = []
haut_bati_list = []
# Table temporaire ne contenant que les intersects d'un segment perpendiculaire donné
query_temp2_perp = "DROP TABLE IF EXISTS ara_temp2_perp;\n"
query_temp2_perp += "CREATE TABLE ara_temp2_perp AS SELECT id_seg, id_perp, xR, yR, haut_bati, id_intersect, geom FROM ara_temp2_seg WHERE id_perp = '%s';\n" % (
id_perp)
if debug >= 4:
print(query_temp2_perp)
executeQuery(connection, query_temp2_perp)
# Récupération de la liste des identifiants segments intersects de la table temp2_perp
cursor.execute(
"SELECT id_intersect FROM ara_temp2_perp GROUP BY id_intersect ORDER BY id_intersect;"
)
id_intersect_list = cursor.fetchall()
# Boucle sur les intersects d'un segment perpendiculaire donné, d'un segment route donné
for id_intersect in id_intersect_list:
# Récupération des coordonnées X et Y des points extrémités de chaque segment intersect
query_xI1 = "SELECT ST_X(geom) as X FROM (SELECT ST_AsText(ST_PointN(geom,1)) as geom FROM ara_temp2_perp WHERE id_intersect = '%s') as toto;" % (
id_intersect)
cursor.execute(query_xI1)
xI1 = cursor.fetchone()
query_yI1 = "SELECT ST_Y(geom) as Y FROM (SELECT ST_AsText(ST_PointN(geom,1)) as geom FROM ara_temp2_perp WHERE id_intersect = '%s') as toto;" % (
id_intersect)
cursor.execute(query_yI1)
yI1 = cursor.fetchone()
query_xI2 = "SELECT ST_X(geom) as X FROM (SELECT ST_AsText(ST_PointN(geom,2)) as geom FROM ara_temp2_perp WHERE id_intersect = '%s') as toto;" % (
id_intersect)
cursor.execute(query_xI2)
xI2 = cursor.fetchone()
query_yI2 = "SELECT ST_Y(geom) as Y FROM (SELECT ST_AsText(ST_PointN(geom,2)) as geom FROM ara_temp2_perp WHERE id_intersect = '%s') as toto;" % (
id_intersect)
cursor.execute(query_yI2)
yI2 = cursor.fetchone()
# Transformation des coordonnées X et Y des points extrémités de chaque segment intersect en valeurs numériques
try:
xI1 = float(str(xI1)[1:-2])
yI1 = float(str(yI1)[1:-2])
xI2 = float(str(xI2)[1:-2])
yI2 = float(str(yI2)[1:-2])
except ValueError: # Python renvoie une valeur Null pour les bâtiments en U (= intersectés à plus de 2 points)
xI1 = yI1 = xI2 = yI2 = 0
if debug >= 4:
print(" xI1 = " + str(xI1))
print(" yI1 = " + str(yI1))
print(" xI2 = " + str(xI2))
print(" yI2 = " + str(yI2))
# Calcul des distances entre la route et chaque point du segment intersect
length_intersect1 = math.sqrt((abs(xR - xI1)**2) +
(abs(yR - yI1)**2))
length_intersect2 = math.sqrt((abs(xR - xI2)**2) +
(abs(yR - yI2)**2))
if debug >= 4:
print(" length_intersect1 = " +
str(length_intersect1))
print(" length_intersect2 = " +
str(length_intersect2))
# Récupération de la valeur de distance entre la route et le point d'intersect le plus proche (+ ajout dans la liste length_list)
length = min(length_intersect1, length_intersect2)
length_list.append(length)
if debug >= 4:
print(" length = " + str(length))
# Récupération de la hauteur du bâtiment correspondant à l'intersect (+ ajout dans la liste haut_bati_list)
query_haut_bati = "SELECT haut_bati FROM ara_temp2_perp WHERE id_intersect = '%s';" % (
id_intersect)
cursor.execute(query_haut_bati)
haut_bati = cursor.fetchone()
haut_bati = float(str(haut_bati)[10:-4])
haut_bati_list.append(haut_bati)
if debug >= 4:
print(" haut_bati = " + str(haut_bati))
print("\n")
# Pour un segment perpendiculaire donné, récupération de la distance minimale entre la route et les intersect avec le bâti
width = min(length_list)
if debug >= 4:
print(" width = " + str(width))
# Pour un segment perpendiculaire donné, récupération de la hauteur du bâtiment correspondant au segment intersect le plus proche de la route
height_position = length_list.index(width)
height = haut_bati_list[height_position]
if debug >= 4:
print(" height = " + str(height))
print("\n")
# MAJ des variables demi-largeurs de rue et hauteur du 1er bâtiment intersecté suivant le côté de la perpendiculaire par rapport à la route
if num_seg == 1:
w1 = width
h1 = height
elif num_seg == 2:
w2 = width
h2 = height
# Construction de la requête de création du point route pour le segment route donné
query_pt_route += " ('%s', %s, %s, %s, %s, %s, %s, 'POINT(%s %s)'),\n" % (
str(id_seg), xR, yR, w1, h1, w2, h2, xR, yR)
treat_seg += 1
# Fin de la construction de la requête de création des points route, avec infos de demi-largeurs de rue et hauteurs des bâtiments
query_pt_route = query_pt_route[:-2] + ";\n" # Transformer la virgule de la dernière ligne SQL en point-virgule (pour terminer la requête)
query_pt_route += "ALTER TABLE ara_asp_ratio_by_seg ALTER COLUMN geom TYPE geometry(POINT,%s) USING ST_SetSRID(geom,%s);\n" % (
epsg, epsg) # Mise à jour du système de coordonnées
query_pt_route += "CREATE INDEX IF NOT EXISTS asp_ratio_by_seg_geom_gist ON ara_asp_ratio_by_seg USING GIST (geom);\n"
if debug >= 2:
print(query_pt_route)
executeQuery(connection, query_pt_route)
###########################################################
### Calcul de l'indicateur et export de la table finale ###
###########################################################
print(bold + cyan +
"Calculs finaux de l'indicateur de rapport d'aspect :" + endC)
timeLine(path_time_log,
" Calculs finaux de l'indicateur de rapport d'aspect : ")
# Requête de calcul d'un rapport d'aspect par segment route
query_asp_ratio_by_seg = """
ALTER TABLE ara_asp_ratio_by_seg ADD COLUMN asp_ratio float;
UPDATE ara_asp_ratio_by_seg SET asp_ratio = 0;
UPDATE ara_asp_ratio_by_seg SET asp_ratio = ((height1 + height2) / 2) / (width1 + width2) WHERE height1 <> 0 AND height2 <> 0 AND width1 <> 0 AND width2 <> 0;
"""
if debug >= 2:
print(query_asp_ratio_by_seg)
executeQuery(connection, query_asp_ratio_by_seg)
# Requête de bufferisation du fichier maillage
query_buffer = """
DROP TABLE IF EXISTS ara_buffer;
CREATE TABLE ara_buffer AS
SELECT ID as ID, ST_Buffer(geom, %s) as geom
FROM %s;
CREATE INDEX IF NOT EXISTS buffer_geom_gist ON ara_buffer USING GIST (geom);
""" % (buffer_size, table_name_maille)
if debug >= 2:
print(query_buffer)
executeQuery(connection, query_buffer)
# Requête de calcul d'un rapport d'aspect par maille (via un intersect sur le maillage bufferisé)
query_asp_ratio_temp1 = """
DROP TABLE IF EXISTS ara_asp_ratio_temp1;
CREATE TABLE ara_asp_ratio_temp1 AS
SELECT m.ID as ID, avg(r.asp_ratio) as asp_ratio, m.geom as geom
FROM %s as m, ara_buffer as b, ara_asp_ratio_by_seg as r
WHERE ST_Intersects(b.geom, r.geom)
AND m.ID = b.ID
AND r.asp_ratio > 0
GROUP BY m.ID, m.geom;
CREATE INDEX IF NOT EXISTS asp_ratio_temp1_geom_gist ON ara_asp_ratio_temp1 USING GIST (geom);
""" % (table_name_maille)
if debug >= 2:
print(query_asp_ratio_temp1)
executeQuery(connection, query_asp_ratio_temp1)
# Rapport d'aspect pour les mailles n'intersectant pas de points-route avec un rapport d'aspect
query_asp_ratio_temp2 = """
DROP TABLE IF EXISTS ara_asp_ratio_temp2;
CREATE TABLE ara_asp_ratio_temp2 AS
SELECT DISTINCT ID as ID, geom as geom
FROM %s
WHERE ID NOT IN
(SELECT DISTINCT ID
FROM ara_asp_ratio_temp1);
ALTER TABLE ara_asp_ratio_temp2 ADD COLUMN asp_ratio float;
UPDATE ara_asp_ratio_temp2 SET asp_ratio = 0;
CREATE INDEX IF NOT EXISTS asp_ratio_temp2_geom_gist ON ara_asp_ratio_temp2 USING GIST (geom);
""" % table_name_maille
if debug >= 1:
print(query_asp_ratio_temp2)
executeQuery(connection, query_asp_ratio_temp2)
# Fusion des 2 tables précédentes pour retrouver l'ensemble des mailles de départ
query_asp_ratio = """
DROP TABLE IF EXISTS ara_asp_ratio;
CREATE TABLE ara_asp_ratio AS
SELECT ID, asp_ratio, geom
FROM ara_asp_ratio_temp1
UNION
SELECT ID, asp_ratio, geom
FROM ara_asp_ratio_temp2;
ALTER TABLE ara_asp_ratio ALTER COLUMN ID TYPE INTEGER;
"""
if debug >= 2:
print(query_asp_ratio)
executeQuery(connection, query_asp_ratio)
closeConnection(connection)
exportVectorByOgr2ogr(database_postgis,
grid_output,
'ara_asp_ratio',
user_name=user_postgis,
password=password_postgis,
ip_host=server_postgis,
num_port=str(port_number),
schema_name=schema_postgis,
format_type=format_vector)
##########################################
### Nettoyage des fichiers temporaires ###
##########################################
if not save_results_intermediate:
if os.path.exists(temp_path):
shutil.rmtree(temp_path)
# ~ dropDatabase(database_postgis) # Conflits avec autres indicateurs (Height of Roughness Elements / Terrain Roughness Class)
else:
print(bold + magenta + "Le calcul de Aspect Ratio a déjà eu lieu." +
endC)
print(bold + yellow + "Fin du calcul de l'indicateur Aspect Ratio." +
endC + "\n")
timeLine(path_time_log, "Fin du calcul de l'indicateur Aspect Ratio : ")
return
def soilOccupationChange(input_plot_vector, output_plot_vector, footprint_vector, input_tx_files_list, evolutions_list=['0:1:11000:10:50:and', '0:1:12000:10:50:and', '0:1:21000:10:50:and', '0:1:22000:10:50:and', '0:1:23000:10:50:and'], class_label_dico={11000:'Bati', 12000:'Route', 21000:'SolNu', 22000:'Eau', 23000:'Vegetation'}, epsg=2154, no_data_value=0, format_raster='GTiff', format_vector='ESRI Shapefile', extension_raster='.tif', extension_vector='.shp', postgis_ip_host='localhost', postgis_num_port=5432, postgis_user_name='postgres', postgis_password='******', postgis_database_name='database', postgis_schema_name='public', postgis_encoding='latin1', path_time_log='', save_results_intermediate=False, overwrite=True):
if debug >= 3:
print('\n' + bold + green + "Evolution de l'OCS par parcelle - Variables dans la fonction :" + endC)
print(cyan + " soilOccupationChange() : " + endC + "input_plot_vector : " + str(input_plot_vector) + endC)
print(cyan + " soilOccupationChange() : " + endC + "output_plot_vector : " + str(output_plot_vector) + endC)
print(cyan + " soilOccupationChange() : " + endC + "footprint_vector : " + str(footprint_vector) + endC)
print(cyan + " soilOccupationChange() : " + endC + "input_tx_files_list : " + str(input_tx_files_list) + endC)
print(cyan + " soilOccupationChange() : " + endC + "evolutions_list : " + str(evolutions_list) + endC)
print(cyan + " soilOccupationChange() : " + endC + "class_label_dico : " + str(class_label_dico) + endC)
print(cyan + " soilOccupationChange() : " + endC + "epsg : " + str(epsg) + endC)
print(cyan + " soilOccupationChange() : " + endC + "no_data_value : " + str(no_data_value) + endC)
print(cyan + " soilOccupationChange() : " + endC + "format_raster : " + str(format_raster) + endC)
print(cyan + " soilOccupationChange() : " + endC + "format_vector : " + str(format_vector) + endC)
print(cyan + " soilOccupationChange() : " + endC + "extension_raster : " + str(extension_raster) + endC)
print(cyan + " soilOccupationChange() : " + endC + "extension_vector : " + str(extension_vector) + endC)
print(cyan + " soilOccupationChange() : " + endC + "postgis_ip_host : " + str(postgis_ip_host) + endC)
print(cyan + " soilOccupationChange() : " + endC + "postgis_num_port : " + str(postgis_num_port) + endC)
print(cyan + " soilOccupationChange() : " + endC + "postgis_user_name : " + str(postgis_user_name) + endC)
print(cyan + " soilOccupationChange() : " + endC + "postgis_password : "******" soilOccupationChange() : " + endC + "postgis_database_name : " + str(postgis_database_name) + endC)
print(cyan + " soilOccupationChange() : " + endC + "postgis_schema_name : " + str(postgis_schema_name) + endC)
print(cyan + " soilOccupationChange() : " + endC + "postgis_encoding : " + str(postgis_encoding) + endC)
print(cyan + " soilOccupationChange() : " + endC + "path_time_log : " + str(path_time_log) + endC)
print(cyan + " soilOccupationChange() : " + endC + "save_results_intermediate : " + str(save_results_intermediate) + endC)
print(cyan + " soilOccupationChange() : " + endC + "overwrite : " + str(overwrite) + endC + '\n')
# Définition des constantes
EXTENSION_TEXT = '.txt'
SUFFIX_TEMP = '_temp'
SUFFIX_CUT = '_cut'
AREA_FIELD = 'st_area'
GEOM_FIELD = 'geom'
# Mise à jour du log
starting_event = "soilOccupationChange() : Début du traitement : "
timeLine(path_time_log, starting_event)
print(cyan + "soilOccupationChange() : " + bold + green + "DEBUT DES TRAITEMENTS" + endC + '\n')
# Définition des variables 'basename'
output_plot_basename = os.path.splitext(os.path.basename(output_plot_vector))[0]
# Définition des variables temp
temp_directory = os.path.dirname(output_plot_vector) + os.sep + output_plot_basename + SUFFIX_TEMP
plot_vector_cut = temp_directory + os.sep + output_plot_basename + SUFFIX_CUT + extension_vector
# Définition des variables PostGIS
plot_table = output_plot_basename.lower()
# Fichier .txt associé au fichier vecteur de sortie, sur la liste des évolutions quantifiées
output_evolution_text_file = os.path.splitext(output_plot_vector)[0] + EXTENSION_TEXT
# Nettoyage des traitements précédents
if debug >= 3:
print(cyan + "soilOccupationChange() : " + endC + "Nettoyage des traitements précédents." + endC + '\n')
removeVectorFile(output_plot_vector, format_vector=format_vector)
removeFile(output_evolution_text_file)
cleanTempData(temp_directory)
dropDatabase(postgis_database_name, user_name=postgis_user_name, password=postgis_password, ip_host=postgis_ip_host, num_port=postgis_num_port, schema_name=postgis_schema_name)
#############
# Etape 0/2 # Préparation des traitements
#############
print(cyan + "soilOccupationChange() : " + bold + green + "ETAPE 0/2 - Début de la préparation des traitements." + endC + '\n')
# Découpage du parcellaire à la zone d'étude
cutVector(footprint_vector, input_plot_vector, plot_vector_cut, overwrite=overwrite, format_vector=format_vector)
# Récupération du nom des champs dans le fichier source (pour isoler les champs nouvellement créés par la suite, et les renommer)
attr_names_list_origin = getAttributeNameList(plot_vector_cut, format_vector=format_vector)
new_attr_names_list_origin = attr_names_list_origin
# Préparation de PostGIS
createDatabase(postgis_database_name, user_name=postgis_user_name, password=postgis_password, ip_host=postgis_ip_host, num_port=postgis_num_port, schema_name=postgis_schema_name)
print(cyan + "soilOccupationChange() : " + bold + green + "ETAPE 0/2 - Fin de la préparation des traitements." + endC + '\n')
#############
# Etape 1/2 # Calculs des statistiques à tx
#############
print(cyan + "soilOccupationChange() : " + bold + green + "ETAPE 1/2 - Début des calculs des statistiques à tx." + endC + '\n')
len_tx = len(input_tx_files_list)
tx = 0
# Boucle sur les fichiers d'entrés à t0+x
for input_tx_file in input_tx_files_list:
if debug >= 3:
print(cyan + "soilOccupationChange() : " + endC + bold + "Calcul des statistiques à tx %s/%s." % (tx+1, len_tx) + endC + '\n')
# Statistiques OCS par parcelle
statisticsVectorRaster(input_tx_file, plot_vector_cut, "", 1, True, False, False, [], [], class_label_dico, path_time_log, clean_small_polygons=True, format_vector=format_vector, save_results_intermediate=save_results_intermediate, overwrite=overwrite)
# Récupération du nom des champs dans le fichier parcellaire (avec les champs créés précédemment dans CVR)
attr_names_list_tx = getAttributeNameList(plot_vector_cut, format_vector=format_vector)
# Isolement des nouveaux champs issus du CVR
fields_name_list = []
for attr_name in attr_names_list_tx:
if attr_name not in new_attr_names_list_origin:
fields_name_list.append(attr_name)
# Gestion des nouveaux noms des champs issus du CVR
new_fields_name_list = []
for field_name in fields_name_list:
new_field_name = 't%s_' % tx + field_name
new_field_name = new_field_name[:10]
new_fields_name_list.append(new_field_name)
new_attr_names_list_origin.append(new_field_name)
# Renommage des champs issus du CVR, pour le relancer par la suite sur d'autres dates
renameFieldsVector(plot_vector_cut, fields_name_list, new_fields_name_list, format_vector=format_vector)
tx += 1
print(cyan + "soilOccupationChange() : " + bold + green + "ETAPE 1/2 - Fin des calculs des statistiques à tx." + endC + '\n')
#############
# Etape 2/2 # Caractérisation des changements
#############
print(cyan + "soilOccupationChange() : " + bold + green + "ETAPE 2/2 - Début de la caractérisation des changements." + endC + '\n')
# Pré-traitements dans PostGIS
plot_table = importVectorByOgr2ogr(postgis_database_name, plot_vector_cut, plot_table, user_name=postgis_user_name, password=postgis_password, ip_host=postgis_ip_host, num_port=postgis_num_port, schema_name=postgis_schema_name, epsg=epsg, codage=postgis_encoding)
connection = openConnection(postgis_database_name, user_name=postgis_user_name, password=postgis_password, ip_host=postgis_ip_host, num_port=postgis_num_port, schema_name=postgis_schema_name)
# Requête SQL pour le calcul de la surface des parcelles
sql_query = "ALTER TABLE %s ADD COLUMN %s REAL;\n" % (plot_table, AREA_FIELD)
sql_query += "UPDATE %s SET %s = ST_Area(%s);\n" % (plot_table, AREA_FIELD, GEOM_FIELD)
# Boucle sur les évolutions à quantifier
temp_field = 1
for evolution in evolutions_list:
evolution_split = evolution.split(':')
idx_bef = int(evolution_split[0])
idx_aft = int(evolution_split[1])
label = int(evolution_split[2])
evol = abs(int(evolution_split[3]))
evol_s = abs(int(evolution_split[4]))
combi = evolution_split[5]
class_name = class_label_dico[label]
def_evo_field = "def_evo_%s" % str(temp_field)
if debug >= 3:
print(cyan + "soilOccupationChange() : " + endC + bold + "Caractérisation des changements t%s/t%s pour la classe '%s' (%s)." % (idx_bef, idx_aft, class_name, label) + endC + '\n')
if evol != 0 or evol_s != 0:
# Gestion de l'évolution via le taux
evol_str = str(evol) + ' %'
evo_field = "evo_%s" % str(temp_field)
t0_field = 't%s_' % idx_bef + class_name.lower()[:7]
t1_field = 't%s_' % idx_aft + class_name.lower()[:7]
# Gestion de l'évolution via la surface
evol_s_str = str(evol_s) + ' m²'
evo_s_field = "evo_s_%s" % str(temp_field)
t0_s_field = 't%s_s_' % idx_bef + class_name.lower()[:5]
t1_s_field = 't%s_s_' % idx_aft + class_name.lower()[:5]
# Requête SQL pour le calcul brut de l'évolution
sql_query += "ALTER TABLE %s ADD COLUMN %s REAL;\n" % (plot_table, evo_field)
sql_query += "UPDATE %s SET %s = %s - %s;\n" % (plot_table, evo_field, t1_field, t0_field)
sql_query += "ALTER TABLE %s ADD COLUMN %s REAL;\n" % (plot_table, evo_s_field)
sql_query += "UPDATE %s SET %s = %s - %s;\n" % (plot_table, evo_s_field, t1_s_field, t0_s_field)
sql_query += "ALTER TABLE %s ADD COLUMN %s VARCHAR;\n" % (plot_table, def_evo_field)
sql_query += "UPDATE %s SET %s = 't%s a t%s - %s - aucune evolution';\n" % (plot_table, def_evo_field, idx_bef, idx_aft, class_name)
# Si évolution à la fois via taux et via surface
if evol != 0 and evol_s != 0:
text_evol = "taux à %s" % evol_str
if combi == 'and':
text_evol += " ET "
elif combi == 'or':
text_evol += " OU "
text_evol += "surface à %s" % evol_s_str
sql_where_pos = "%s >= %s %s %s >= %s" % (evo_field, evol, combi, evo_s_field, evol_s)
sql_where_neg = "%s <= -%s %s %s <= -%s" % (evo_field, evol, combi, evo_s_field, evol_s)
# Si évolution uniquement via taux
elif evol != 0:
text_evol = "taux à %s" % evol_str
sql_where_pos = "%s >= %s" % (evo_field, evol)
sql_where_neg = "%s <= -%s" % (evo_field, evol)
# Si évolution uniquement via surface
elif evol_s != 0:
text_evol = "surface à %s" % evol_s_str
sql_where_pos = "%s >= %s" % (evo_s_field, evol_s)
sql_where_neg = "%s <= -%s" % (evo_s_field, evol_s)
sql_query += "UPDATE %s SET %s = 't%s a t%s - %s - evolution positive' WHERE %s;\n" % (plot_table, def_evo_field, idx_bef, idx_aft, class_name, sql_where_pos)
sql_query += "UPDATE %s SET %s = 't%s a t%s - %s - evolution negative' WHERE %s;\n" % (plot_table, def_evo_field, idx_bef, idx_aft, class_name, sql_where_neg)
# Ajout des paramètres de l'évolution quantifiée (temporalités, classe, taux/surface) au fichier texte de sortie
text = "%s --> évolution entre t%s et t%s, pour la classe '%s' (label %s) :\n" % (def_evo_field, idx_bef, idx_aft, class_name, label)
text += " %s --> taux d'évolution brut" % evo_field + " (%)\n"
text += " %s --> surface d'évolution brute" % evo_s_field + " (m²)\n"
text += "Evolution quantifiée : %s\n" % text_evol
appendTextFileCR(output_evolution_text_file, text)
temp_field += 1
# Traitements SQL de l'évolution des classes OCS
executeQuery(connection, sql_query)
closeConnection(connection)
exportVectorByOgr2ogr(postgis_database_name, output_plot_vector, plot_table, user_name=postgis_user_name, password=postgis_password, ip_host=postgis_ip_host, num_port=postgis_num_port, schema_name=postgis_schema_name, format_type=format_vector)
print(cyan + "soilOccupationChange() : " + bold + green + "ETAPE 2/2 - Fin de la caractérisation des changements." + endC + '\n')
# Suppression des fichiers temporaires
if not save_results_intermediate:
if debug >= 3:
print(cyan + "soilOccupationChange() : " + endC + "Suppression des fichiers temporaires." + endC + '\n')
deleteDir(temp_directory)
dropDatabase(postgis_database_name, user_name=postgis_user_name, password=postgis_password, ip_host=postgis_ip_host, num_port=postgis_num_port, schema_name=postgis_schema_name)
print(cyan + "soilOccupationChange() : " + bold + green + "FIN DES TRAITEMENTS" + endC + '\n')
# Mise à jour du log
ending_event = "soilOccupationChange() : Fin du traitement : "
timeLine(path_time_log, ending_event)
return
def verificationCorrection(vector_ref,
vectors_input_list,
vectors_output_list,
epsg,
project_encoding,
server_postgis,
port_number,
user_postgis,
password_postgis,
database_postgis,
schema_postgis,
path_time_log,
save_results_intermediate=False,
overwrite=True):
# Mise à jour du Log
starting_event = "verificationCorrection() : Verifie and correct vector starting : "
timeLine(path_time_log, starting_event)
print(endC)
print(bold + green + "## START : VERIFIE CORRECT VECTORS" + endC)
print(endC)
if debug >= 2:
print(bold + green +
"verificationCorrection() : Variables dans la fonction" + endC)
print(cyan + "verificationCorrection() : " + endC + "vector_ref : " +
str(vector_ref) + endC)
print(cyan + "verificationCorrection() : " + endC +
"vectors_input_list : " + str(vectors_input_list) + endC)
print(cyan + "verificationCorrection() : " + endC +
"vectors_output_list : " + str(vectors_output_list) + endC)
print(cyan + "verificationCorrection() : " + endC + "epsg : " +
str(epsg) + endC)
print(cyan + "verificationCorrection() : " + endC +
"project_encoding : " + str(project_encoding) + endC)
print(cyan + "verificationCorrection() : " + endC +
"server_postgis : " + str(server_postgis) + endC)
print(cyan + "verificationCorrection() : " + endC + "port_number : " +
str(port_number) + endC)
print(cyan + "verificationCorrection() : " + endC + "user_postgis : " +
str(user_postgis) + endC)
print(cyan + "verificationCorrection() : " + endC +
"password_postgis : " + str(password_postgis) + endC)
print(cyan + "verificationCorrection() : " + endC +
"database_postgis : " + str(database_postgis) + endC)
print(cyan + "verificationCorrection() : " + endC +
"schema_postgis : " + str(schema_postgis) + endC)
print(cyan + "verificationCorrection() : " + endC +
"path_time_log : " + str(path_time_log) + endC)
print(cyan + "verificationCorrection() : " + endC +
"save_results_intermediate : " + str(save_results_intermediate) +
endC)
print(cyan + "verificationCorrection() : " + endC + "overwrite : " +
str(overwrite) + endC)
# Constante suffix des tables de contrôle en SQL
SUFFIX_SUM = '_sum'
SUFFIX_100 = '_100'
SUFFIX_SURF = '_surf'
COLUMN_SUM = "sum"
schema_postgis = schema_postgis.lower()
table_reference_name = os.path.splitext(
os.path.basename(vector_ref))[0].lower()
table_reference_complete_name = schema_postgis + '.' + table_reference_name
# Préparation de la base de données
dropDatabase(
database_postgis,
user_name=user_postgis,
password=password_postgis,
ip_host=server_postgis,
num_port=str(port_number)
) # Suppression de la base de données (si elle existe d'un traitement précédent)
createDatabase(database_postgis,
user_name=user_postgis,
password=password_postgis,
ip_host=server_postgis,
num_port=str(port_number)) # Création de la base de données
connection = openConnection(database_postgis,
user_name=user_postgis,
password=password_postgis,
ip_host=server_postgis,
num_port=str(port_number),
schema='') # Connexion à la base de données
createSchema(connection,
schema_postgis) # Création du schéma, s'il n'existe pas
closeConnection(
connection
) # Déconnexion de la base de données (pour éviter les conflits avec les outils d'import de shape)
# Monter en base du fichier vecteur de référence
table_reference_complete_name = importVectorByOgr2ogr(
database_postgis,
vector_ref,
table_reference_complete_name,
user_name=user_postgis,
password=password_postgis,
ip_host=server_postgis,
num_port=str(port_number),
schema=schema_postgis,
epsg=str(epsg),
codage=project_encoding)
# Pour tous les fichiers à traiter
for idx_vector in range(len(vectors_input_list)):
vector_input = vectors_input_list[idx_vector]
vector_output = vectors_output_list[idx_vector]
table_input_name = schema_postgis + '.' + os.path.splitext(
os.path.basename(vector_input))[0].lower()
table_output_name = schema_postgis + '.' + os.path.splitext(
os.path.basename(vector_output))[0].lower()
# Test si le vecteur de sortie existe déjà et si il doit être écrasés
check = os.path.isfile(vector_output)
if check and not overwrite: # Si le fichier existe déjà et que overwrite n'est pas activé
print(bold + yellow + "File vector output : " + vector_output +
" already exists and will not be created again." + endC)
else:
if check:
try:
removeVectorFile(vector_output)
except Exception:
pass # si le fichier n'existe pas, il ne peut pas être supprimé : cette étape est ignorée
# Monter en base du fichier vecteur d'entrée
table_input_name = importVectorByOgr2ogr(database_postgis,
vector_input,
table_input_name,
user_name=user_postgis,
password=password_postgis,
ip_host=server_postgis,
num_port=str(port_number),
schema=schema_postgis,
epsg=str(epsg),
codage=project_encoding)
# Traitements SQL
#################
connection = openConnection(database_postgis,
user_name=user_postgis,
password=password_postgis,
ip_host=server_postgis,
num_port=str(port_number),
schema=schema_postgis)
# Création de la nouvelle table corrigée
query_create_table = """
CREATE TABLE %s AS
SELECT
CAST(id AS INTEGER) AS id,
CAST(label AS INTEGER) AS label,
CAST(datemaj AS TEXT) AS datemaj,
CAST(srcmaj AS TEXT) AS srcmaj,
CAST(round(img_sat) AS INTEGER) AS img_sat,
CAST(round(bd_carto) AS INTEGER) AS bd_carto,
CAST(round(bd_topo) AS INTEGER) AS bd_topo,
CAST(round(bd_foret) AS INTEGER) AS bd_foret,
CAST(round(clc) AS INTEGER) AS clc,
CAST(round(cvi) AS INTEGER) AS cvi,
CAST(round(rpg) AS INTEGER) AS rpg,
CAST(round(vrgprunus) AS INTEGER) AS vrgprunus,
CAST(round(oss_ign) AS INTEGER) AS oss_ign,
geom
FROM %s;
""" % (table_output_name, table_input_name)
executeQuery(connection, query_create_table)
# Mise à jour des colonne de la nouvelle table
query_update_colum = """
UPDATE %s SET img_sat = 100 - (bd_carto + bd_topo + bd_foret + clc + cvi + rpg + vrgprunus + oss_ign);
UPDATE %s SET bd_carto = bd_carto-1, img_sat = 0 WHERE (img_sat=-1) AND (bd_carto = GREATEST(bd_carto, bd_topo, bd_foret, clc, cvi, rpg, vrgprunus, oss_ign));
UPDATE %s SET bd_topo = bd_topo-1, img_sat = 0 WHERE (img_sat=-1) AND (bd_topo = GREATEST(bd_carto, bd_topo, bd_foret, clc, cvi, rpg, vrgprunus, oss_ign));
UPDATE %s SET bd_foret = bd_foret-1, img_sat = 0 WHERE (img_sat=-1) AND (bd_foret = GREATEST(bd_carto, bd_topo, bd_foret, clc, cvi, rpg, vrgprunus, oss_ign));
UPDATE %s SET clc = clc-1, img_sat = 0 WHERE (img_sat=-1) AND (clc = GREATEST(bd_carto, bd_topo, bd_foret, clc, cvi, rpg, vrgprunus, oss_ign));
UPDATE %s SET cvi = cvi-1, img_sat = 0 WHERE (img_sat=-1) AND (cvi = GREATEST(bd_carto, bd_topo, bd_foret, clc, cvi, rpg, vrgprunus, oss_ign));
UPDATE %s SET rpg = rpg-1, img_sat = 0 WHERE (img_sat=-1) AND (rpg = GREATEST(bd_carto, bd_topo, bd_foret, clc, cvi, rpg, vrgprunus, oss_ign));
UPDATE %s SET vrgprunus = vrgprunus-1, img_sat = 0 WHERE (img_sat=-1) AND (vrgprunus = GREATEST(bd_carto, bd_topo, bd_foret, clc, cvi, rpg, vrgprunus, oss_ign));
UPDATE %s SET oss_ign = oss_ign-1, img_sat = 0 WHERE (img_sat=-1) AND (oss_ign = GREATEST(bd_carto, bd_topo, bd_foret, clc, cvi, rpg, vrgprunus, oss_ign));
""" % (table_output_name, table_output_name, table_output_name,
table_output_name, table_output_name, table_output_name,
table_output_name, table_output_name, table_output_name)
executeQuery(connection, query_update_colum)
# Vérification de la somme des colonnes
pos = table_output_name.find("m2_") - 3
resolution = table_output_name[pos:pos + 3]
table_name = schema_postgis + '.' + "controle_" + table_reference_name + "_" + resolution + "m_cor"
table_verif_sum_name = table_name + SUFFIX_SUM
table_verif_sum100_name = table_name + SUFFIX_SUM + SUFFIX_100
table_verif_surf_name = table_name + SUFFIX_SURF
query_verif_sum = """
CREATE TABLE %s AS
SELECT id, img_sat, bd_carto + bd_topo + bd_foret + clc + cvi + rpg + vrgprunus + oss_ign AS sum
FROM %s;
""" % (table_verif_sum_name, table_output_name)
executeQuery(connection, query_verif_sum)
query_verif_sum100 = """
CREATE TABLE %s AS
SELECT id, sum
FROM %s
WHERE sum != 100 OR img_sat = -1;
""" % (table_verif_sum100_name, table_verif_sum_name)
executeQuery(connection, query_verif_sum100)
data_list = getData(connection, table_verif_sum100_name, COLUMN_SUM)
if len(data_list) > 0:
print(cyan + "verificationCorrection() : " + bold + red +
"Error column sum 100 not empty = " + str(len(data_list)) +
endC,
file=sys.stderr)
# Vérification des aires
query_verif_surf = """
CREATE TABLE %s AS
SELECT 'OCS_SAT', SUM(st_area(geom))
FROM %s;
INSERT INTO %s
SELECT '%s', st_area(geom)
FROM %s;
""" % (table_verif_surf_name, table_output_name, table_verif_surf_name,
table_reference_complete_name, table_reference_complete_name)
executeQuery(connection, query_verif_surf)
delta = 1
data_list = getData(connection, table_verif_surf_name, COLUMN_SUM)
data_list0 = float(str(data_list[0])[1:-2])
for data in data_list:
data = float(str(data)[1:-2])
data_ref_min = int(data_list0) - delta
data_ref_max = int(data_list0) + delta
if int(data) < data_ref_min or int(data) > data_ref_max:
print(cyan + "verificationCorrection() : " + bold + red +
"Error area comparaison, ref = " + str(data_list0) +
"m², data = " + str(data) + "m²" + endC,
file=sys.stderr)
# Correction la géométrie (topologie)
topologyCorrections(connection, table_output_name)
# Récupération de la base du fichier vecteur de sortie (et déconnexion de la base de données, pour éviter les conflits d'accès)
closeConnection(connection)
exportVectorByOgr2ogr(database_postgis,
vector_output,
table_output_name,
user_name=user_postgis,
password=password_postgis,
ip_host=server_postgis,
num_port=str(port_number),
schema=schema_postgis,
format_type='ESRI Shapefile')
# Suppression des fichiers intermédiaires
if not save_results_intermediate:
dropDatabase(database_postgis,
user_name=user_postgis,
password=password_postgis,
ip_host=server_postgis,
num_port=str(port_number))
print(endC)
print(bold + green + "## END : VERIFIE CORRECT VECTORS" + endC)
print(endC)
# Mise à jour du Log
ending_event = "verificationCorrection() : Verifie and correct vector ending : "
timeLine(path_time_log, ending_event)
return
def classificationLczSql(input_division, input_hre, input_ocs, output_lcz, id_field='id', epsg=2154, format_vector='ESRI Shapefile', postgis_ip_host='localhost', postgis_num_port=5432, postgis_user_name='postgres', postgis_password='******', postgis_database_name='lcz_db', postgis_schema_name='public', postgis_encoding='UTF-8', path_time_log='', save_results_intermediate=False, overwrite=True):
if debug >= 3:
print('\n' + bold + green + "Classification LCZ via SQL - Variables dans la fonction :" + endC)
print(cyan + " classificationLczSql() : " + endC + "input_division : " + str(input_division) + endC)
print(cyan + " classificationLczSql() : " + endC + "input_hre : " + str(input_hre) + endC)
print(cyan + " classificationLczSql() : " + endC + "input_ocs : " + str(input_ocs) + endC)
print(cyan + " classificationLczSql() : " + endC + "output_lcz : " + str(output_lcz) + endC)
print(cyan + " classificationLczSql() : " + endC + "id_field : " + str(id_field) + endC)
print(cyan + " classificationLczSql() : " + endC + "epsg : " + str(epsg) + endC)
print(cyan + " classificationLczSql() : " + endC + "format_vector : " + str(format_vector) + endC)
print(cyan + " classificationLczSql() : " + endC + "postgis_ip_host : " + str(postgis_ip_host) + endC)
print(cyan + " classificationLczSql() : " + endC + "postgis_num_port : " + str(postgis_num_port) + endC)
print(cyan + " classificationLczSql() : " + endC + "postgis_user_name : " + str(postgis_user_name) + endC)
print(cyan + " classificationLczSql() : " + endC + "postgis_password : "******" classificationLczSql() : " + endC + "postgis_database_name : " + str(postgis_database_name) + endC)
print(cyan + " classificationLczSql() : " + endC + "postgis_schema_name : " + str(postgis_schema_name) + endC)
print(cyan + " classificationLczSql() : " + endC + "postgis_encoding : " + str(postgis_encoding) + endC)
print(cyan + " classificationLczSql() : " + endC + "path_time_log : " + str(path_time_log) + endC)
print(cyan + " classificationLczSql() : " + endC + "save_results_intermediate : " + str(save_results_intermediate) + endC)
print(cyan + " classificationLczSql() : " + endC + "overwrite : " + str(overwrite) + endC + '\n')
# Définition des constantes
SUFFIX_TEMP = '_temp'
# Mise à jour du log
starting_event = "classificationLczSql() : Début du traitement : "
timeLine(path_time_log, starting_event)
print(cyan + "classificationLczSql() : " + bold + green + "DEBUT DES TRAITEMENTS" + endC + '\n')
# Définition des variables
hre_field = 'mean_h'
bur_field = 'built'
ror_field = 'mineral'
bsr_field = 'baresoil'
war_field = 'water'
ver_field = 'veget'
vhr_field = 'veg_h_rate'
lcz_field = 'lcz'
div_table = 'i_div'
hre_table = 'i_hre'
ocs_table = 'i_ocs'
lcz_table = 'o_lcz'
# Nettoyage des traitements précédents
if overwrite:
if debug >= 3:
print(cyan + "classificationLczSql() : " + endC + "Nettoyage des traitements précédents." + endC + '\n')
removeVectorFile(output_lcz)
dropDatabase(postgis_database_name, user_name=postgis_user_name, password=postgis_password, ip_host=postgis_ip_host, num_port=postgis_num_port, schema_name=postgis_schema_name)
else:
if os.path.exists(output_lcz):
print(cyan + "classificationLczSql() : " + bold + yellow + "Le fichier de sortie existe déjà et ne sera pas regénéré." + endC + '\n')
raise
pass
####################################################################
createDatabase(postgis_database_name, user_name=postgis_user_name, password=postgis_password, ip_host=postgis_ip_host, num_port=postgis_num_port, schema_name=postgis_schema_name)
div_table = importVectorByOgr2ogr(postgis_database_name, input_division, div_table, user_name=postgis_user_name, password=postgis_password, ip_host=postgis_ip_host, num_port=postgis_num_port, schema_name=postgis_schema_name, epsg=epsg, codage=postgis_encoding)
hre_table = importVectorByOgr2ogr(postgis_database_name, input_hre, hre_table, user_name=postgis_user_name, password=postgis_password, ip_host=postgis_ip_host, num_port=postgis_num_port, schema_name=postgis_schema_name, epsg=epsg, codage=postgis_encoding)
ocs_table = importVectorByOgr2ogr(postgis_database_name, input_ocs, ocs_table, user_name=postgis_user_name, password=postgis_password, ip_host=postgis_ip_host, num_port=postgis_num_port, schema_name=postgis_schema_name, epsg=epsg, codage=postgis_encoding)
connection = openConnection(postgis_database_name, user_name=postgis_user_name, password=postgis_password, ip_host=postgis_ip_host, num_port=postgis_num_port, schema_name=postgis_schema_name)
query = "DROP TABLE IF EXISTS %s;\n" % lcz_table
query += "CREATE TABLE %s AS\n" % lcz_table
query += " SELECT d.%s AS %s, h.%s AS hre, o.%s AS bur, o.%s AS ror, o.%s AS bsr, o.%s AS war, o.%s AS ver, o.%s AS vhr, d.geom AS geom\n" % (id_field, id_field, hre_field, bur_field, ror_field, bsr_field, war_field, ver_field, vhr_field)
query += " FROM %s AS d, %s AS h, %s AS o\n" % (div_table, hre_table, ocs_table)
query += " WHERE d.%s = h.%s AND d.%s = o.%s;\n" % (id_field, id_field, id_field, id_field)
query += "ALTER TABLE %s ADD COLUMN %s VARCHAR(8);\n" % (lcz_table, lcz_field)
query += "UPDATE %s SET %s = 'urban' WHERE bur > 5;\n" % (lcz_table, lcz_field)
query += "UPDATE %s SET %s = 'natural' WHERE bur <= 5;\n" % (lcz_table, lcz_field)
query += "UPDATE %s SET %s = 'low_ocs' WHERE %s = 'natural' AND (bur + ror + bsr + war + ver) <= 60;\n" % (lcz_table, lcz_field, lcz_field)
query += "UPDATE %s SET %s = '1' WHERE %s = 'urban' AND (hre > 25) AND (bur > 40);\n" % (lcz_table, lcz_field, lcz_field)
query += "UPDATE %s SET %s = '4' WHERE %s = 'urban' AND (hre > 25) AND (bur <= 40);\n" % (lcz_table, lcz_field, lcz_field)
query += "UPDATE %s SET %s = '2' WHERE %s = 'urban' AND (hre > 10 AND hre <= 25) AND (bur > 40);\n" % (lcz_table, lcz_field, lcz_field)
query += "UPDATE %s SET %s = '5' WHERE %s = 'urban' AND (hre > 10 AND hre <= 25) AND (bur <= 40);\n" % (lcz_table, lcz_field, lcz_field)
query += "UPDATE %s SET %s = '7' WHERE %s = 'urban' AND (hre <= 10) AND (bur > 70);\n" % (lcz_table, lcz_field, lcz_field)
query += "UPDATE %s SET %s = '3' WHERE %s = 'urban' AND (hre <= 10) AND (bur > 40 AND bur <= 70);\n" % (lcz_table, lcz_field, lcz_field)
query += "UPDATE %s SET %s = '6' WHERE %s = 'urban' AND (hre <= 10) AND (bur > 20 AND bur <= 40) AND (ver > 10);\n" % (lcz_table, lcz_field, lcz_field)
query += "UPDATE %s SET %s = '8' WHERE %s = 'urban' AND (hre <= 10) AND (bur > 20 AND bur <= 40) AND (ver <= 10);\n" % (lcz_table, lcz_field, lcz_field)
query += "UPDATE %s SET %s = '9' WHERE %s = 'urban' AND (hre <= 10) AND (bur <= 20);\n" % (lcz_table, lcz_field, lcz_field)
query += "UPDATE %s SET %s = 'A' WHERE %s = 'natural' AND ((ver >= ror) AND (ver >= bsr) AND (ver >= war)) AND (vhr > 50);\n" % (lcz_table, lcz_field, lcz_field)
query += "UPDATE %s SET %s = 'B' WHERE %s = 'natural' AND ((ver >= ror) AND (ver >= bsr) AND (ver >= war)) AND (vhr > 25 AND vhr <= 50);\n" % (lcz_table, lcz_field, lcz_field)
query += "UPDATE %s SET %s = 'C' WHERE %s = 'natural' AND ((ver >= ror) AND (ver >= bsr) AND (ver >= war)) AND (vhr > 10 AND vhr <= 25);\n" % (lcz_table, lcz_field, lcz_field)
query += "UPDATE %s SET %s = 'D' WHERE %s = 'natural' AND ((ver >= ror) AND (ver >= bsr) AND (ver >= war)) AND (vhr <= 10);\n" % (lcz_table, lcz_field, lcz_field)
query += "UPDATE %s SET %s = 'E' WHERE %s = 'natural' AND ((ror >= bsr) AND (ror >= war) AND (ror >= ver));\n" % (lcz_table, lcz_field, lcz_field)
query += "UPDATE %s SET %s = 'F' WHERE %s = 'natural' AND ((bsr >= ror) AND (bsr >= war) AND (bsr >= ver));\n" % (lcz_table, lcz_field, lcz_field)
query += "UPDATE %s SET %s = 'G' WHERE %s = 'natural' AND ((war >= ror) AND (war >= bsr) AND (war >= ver));\n" % (lcz_table, lcz_field, lcz_field)
query += "ALTER TABLE %s ALTER COLUMN %s TYPE INTEGER;\n" % (lcz_table, id_field)
query += "ALTER TABLE %s ALTER COLUMN hre TYPE NUMERIC(6,2);\n" % lcz_table
query += "ALTER TABLE %s ALTER COLUMN bur TYPE NUMERIC(6,2);\n" % lcz_table
query += "ALTER TABLE %s ALTER COLUMN ror TYPE NUMERIC(6,2);\n" % lcz_table
query += "ALTER TABLE %s ALTER COLUMN bsr TYPE NUMERIC(6,2);\n" % lcz_table
query += "ALTER TABLE %s ALTER COLUMN war TYPE NUMERIC(6,2);\n" % lcz_table
query += "ALTER TABLE %s ALTER COLUMN ver TYPE NUMERIC(6,2);\n" % lcz_table
query += "ALTER TABLE %s ALTER COLUMN vhr TYPE NUMERIC(6,2);\n" % lcz_table
if debug >= 3:
print('\n' + query)
executeQuery(connection, query)
closeConnection(connection)
exportVectorByOgr2ogr(postgis_database_name, output_lcz, lcz_table, user_name=postgis_user_name, password=postgis_password, ip_host=postgis_ip_host, num_port=postgis_num_port, schema_name=postgis_schema_name, format_type=format_vector)
####################################################################
# Suppression des fichiers temporaires
if not save_results_intermediate:
if debug >= 3:
print('\n' + cyan + "classificationLczSql() : " + endC + "Suppression des fichiers temporaires." + endC + '\n')
dropDatabase(postgis_database_name, user_name=postgis_user_name, password=postgis_password, ip_host=postgis_ip_host, num_port=postgis_num_port, schema_name=postgis_schema_name)
print(cyan + "classificationLczSql() : " + bold + green + "FIN DES TRAITEMENTS" + endC + '\n')
# Mise à jour du log
ending_event = "classificationLczSql() : Fin du traitement : "
timeLine(path_time_log, ending_event)
return 0
def heightOfRoughnessElements(grid_input, grid_output, built_input, height_field, id_field, epsg, project_encoding, server_postgis, port_number, user_postgis, password_postgis, database_postgis, schema_postgis, path_time_log, format_vector='ESRI Shapefile', save_results_intermediate=False, overwrite=True):
print(bold + yellow + "Début du calcul de l'indicateur Height of Roughness Elements." + endC + "\n")
timeLine(path_time_log, "Début du calcul de l'indicateur Height of Roughness Elements : ")
if debug >= 3:
print(bold + green + "heightOfRoughnessElements() : Variables dans la fonction" + endC)
print(cyan + "heightOfRoughnessElements() : " + endC + "grid_input : " + str(grid_input) + endC)
print(cyan + "heightOfRoughnessElements() : " + endC + "grid_output : " + str(grid_output) + endC)
print(cyan + "heightOfRoughnessElements() : " + endC + "built_input : " + str(built_input) + endC)
print(cyan + "heightOfRoughnessElements() : " + endC + "height_field : " + str(height_field) + endC)
print(cyan + "heightOfRoughnessElements() : " + endC + "id_field : " + str(id_field) + endC)
print(cyan + "heightOfRoughnessElements() : " + endC + "epsg : " + str(epsg) + endC)
print(cyan + "heightOfRoughnessElements() : " + endC + "project_encoding : " + str(project_encoding) + endC)
print(cyan + "heightOfRoughnessElements() : " + endC + "server_postgis : " + str(server_postgis) + endC)
print(cyan + "heightOfRoughnessElements() : " + endC + "port_number : " + str(port_number) + endC)
print(cyan + "heightOfRoughnessElements() : " + endC + "user_postgis : " + str(user_postgis) + endC)
print(cyan + "heightOfRoughnessElements() : " + endC + "password_postgis : " + str(password_postgis) + endC)
print(cyan + "heightOfRoughnessElements() : " + endC + "database_postgis : " + str(database_postgis) + endC)
print(cyan + "heightOfRoughnessElements() : " + endC + "schema_postgis : " + str(schema_postgis) + endC)
print(cyan + "heightOfRoughnessElements() : " + endC + "path_time_log : " + str(path_time_log) + endC)
print(cyan + "heightOfRoughnessElements() : " + endC + "format_vector : " + str(format_vector) + endC)
print(cyan + "heightOfRoughnessElements() : " + endC + "save_results_intermediate : " + str(save_results_intermediate) + endC)
print(cyan + "heightOfRoughnessElements() : " + endC + "overwrite : " + str(overwrite) + endC)
print("\n")
if not os.path.exists(grid_output) or overwrite:
############################################
### Préparation générale des traitements ###
############################################
print(bold + cyan + "Préparation au calcul de Height of Roughness Elements :" + endC)
timeLine(path_time_log, " Préparation au calcul de Height of Roughness Elements : ")
if os.path.exists(grid_output):
removeVectorFile(grid_output)
# Création de la base de données PostGIS
# ~ dropDatabase(database_postgis, user_name=user_postgis, password=password_postgis, ip_host=server_postgis, num_port=port_number, schema_name=schema_postgis) # Conflits avec autres indicateurs (Aspect Ratio / Terrain Roughness Class)
createDatabase(database_postgis, user_name=user_postgis, password=password_postgis, ip_host=server_postgis, num_port=port_number, schema_name=schema_postgis)
# Import des fichiers shapes maille et bati dans la base de données PostGIS
table_name_maille = importVectorByOgr2ogr(database_postgis, grid_input, 'hre_maille', user_name=user_postgis, password=password_postgis, ip_host=server_postgis, num_port=str(port_number), schema_name=schema_postgis, epsg=str(epsg), codage=project_encoding)
table_name_bati = importVectorByOgr2ogr(database_postgis, built_input, 'hre_bati', user_name=user_postgis, password=password_postgis, ip_host=server_postgis, num_port=str(port_number), schema_name=schema_postgis, epsg=str(epsg), codage=project_encoding)
# Gestion de l'ID
connection = openConnection(database_postgis, user_name=user_postgis, password=password_postgis, ip_host=server_postgis, num_port=port_number, schema_name=schema_postgis)
attr_names_list = getAttributeNameList(built_input, format_vector=format_vector)
if id_field not in attr_names_list:
id_field = 'ogc_fid'
# ~ id_query = "ALTER TABLE %s ADD COLUMN %s SERIAL PRIMARY KEY" % (table_name_bati, id_field)
# ~ executeQuery(connection, id_query)
###############################################
### Calcul de l'indicateur par requêtes SQL ###
###############################################
print(bold + cyan + "Calcul de Height of Roughness Elements :" + endC)
timeLine(path_time_log, " Calcul de Height of Roughness Elements : ")
# Création des index spatiaux (accélère les requêtes spatiales)
query = """
--CREATE INDEX IF NOT EXISTS maille_geom_gist ON %s USING GIST (geom);
--CREATE INDEX IF NOT EXISTS bati_geom_gist ON %s USING GIST (geom);
""" % (table_name_maille, table_name_bati)
# Intersect entre les tables maille et bâti (pour chaque maille, on récupère le bâti qui intersect)
query += """
DROP TABLE IF EXISTS hre_decoup;
CREATE TABLE hre_decoup AS
SELECT b.%s as ID, b.%s as hauteur, ST_Intersection(b.geom, m.geom) as geom
FROM %s as b, %s as m
WHERE ST_Intersects(b.geom, m.geom)
AND (ST_GeometryType(b.geom) = 'ST_Polygon' OR ST_GeometryType(b.geom) = 'ST_MultiPolygon')
AND (ST_GeometryType(m.geom) = 'ST_Polygon' OR ST_GeometryType(m.geom) = 'ST_MultiPolygon');
CREATE INDEX IF NOT EXISTS decoup_geom_gist ON hre_decoup USING GIST (geom);
""" % (id_field, height_field, table_name_bati, table_name_maille)
# Table intermédiaire de calculs d'indicateurs secondaires
query += """
DROP TABLE IF EXISTS hre_temp;
CREATE TABLE hre_temp AS
SELECT d.ID, st_area(d.geom) as surface, (st_area(d.geom) * d.hauteur) as volume, d.geom as geom
FROM hre_decoup as d;
CREATE INDEX IF NOT EXISTS temp_geom_gist ON hre_temp USING GIST (geom);
"""
# Table intermédiaire de calcul de mean_h seulement pour les mailles intersectant du bâti
query += """
DROP TABLE IF EXISTS hre_maille_bis;
CREATE TABLE hre_maille_bis AS
SELECT m.ID as ID, ((sum(t.volume) / count(t.geom)) / (sum(t.surface) / count(t.geom))) as mean_h, m.geom as geom
FROM %s as m, hre_temp as t
WHERE ST_Intersects(m.geom, t.geom)
AND (ST_GeometryType(m.geom) = 'ST_Polygon' OR ST_GeometryType(m.geom) = 'ST_MultiPolygon')
AND (ST_GeometryType(t.geom) = 'ST_Polygon' OR ST_GeometryType(t.geom) = 'ST_MultiPolygon')
GROUP BY m.ID, m.geom;
CREATE INDEX IF NOT EXISTS maille_bis_geom_gist ON hre_maille_bis USING GIST (geom);
""" % (table_name_maille)
# Table intermédiaire seulement pour les mailles n'intersectant pas de bâti (par défaut, mean_h = 0)
query += """
DROP TABLE IF EXISTS hre_maille_ter;
CREATE TABLE hre_maille_ter AS
SELECT DISTINCT ID as ID, geom as geom
FROM %s
WHERE ID NOT IN
(SELECT DISTINCT ID
FROM hre_maille_bis);
ALTER TABLE hre_maille_ter ADD mean_h DOUBLE PRECISION;
UPDATE hre_maille_ter SET mean_h = 0;
CREATE INDEX IF NOT EXISTS maille_ter_geom_gist ON hre_maille_ter USING GIST (geom);
""" % (table_name_maille)
# Union des 2 tables précédentes pour récupérer l'ensemble des polygones maille de départ
query += """
DROP TABLE IF EXISTS hre_height;
CREATE TABLE hre_height AS
SELECT ID, mean_h, geom
FROM hre_maille_bis
UNION
SELECT ID, mean_h, geom
FROM hre_maille_ter;
ALTER TABLE hre_height ALTER COLUMN ID TYPE INTEGER;
"""
# Exécution de la requête SQL
if debug >= 1:
print(query)
executeQuery(connection, query)
closeConnection(connection)
# Export en shape de la table contenant l'indicateur calculé
exportVectorByOgr2ogr(database_postgis, grid_output, 'hre_height', user_name=user_postgis, password=password_postgis, ip_host=server_postgis, num_port=str(port_number), schema_name=schema_postgis, format_type=format_vector)
##########################################
### Nettoyage des fichiers temporaires ###
##########################################
if not save_results_intermediate:
# ~ dropDatabase(database_postgis, user_name=user_postgis, password=password_postgis, ip_host=server_postgis, num_port=port_number, schema_name=schema_postgis) # Conflits avec autres indicateurs (Aspect Ratio / Terrain Roughness Class)
pass
else:
print(bold + magenta + "Le calcul de Height of Roughness Elements a déjà eu lieu." + endC)
print(bold + yellow + "Fin du calcul de l'indicateur Height of Roughness Elements." + endC + "\n")
timeLine(path_time_log, "Fin du calcul de l'indicateur Height of Roughness Elements : ")
return
def populationVulnerability(
input_division,
input_footprint,
input_population,
input_built,
output_vulnerability,
id_div='id',
id_pop='IdINSPIRE',
id_blt='ID',
stake_field='Ind',
health_vuln_field_list=['Ind_0_3', 'Ind_4_5', 'Ind_65_79', 'Ind_80p'],
social_vuln_field_list=['Men_pauv'],
height_field='HAUTEUR',
built_sql_filter="NATURE LIKE 'Indiff%renci%e' AND (USAGE1 LIKE 'Indiff%renci%e' OR USAGE1 LIKE 'R%sidentiel' OR USAGE2 LIKE 'R%sidentiel' OR USAGE2 IS NULL) AND HAUTEUR IS NOT NULL AND ST_Area(geom) >= 20",
epsg=2154,
format_vector='ESRI Shapefile',
postgis_ip_host='localhost',
postgis_num_port=5432,
postgis_user_name='postgres',
postgis_password='******',
postgis_database_name='uhi_vuln',
postgis_schema_name='public',
postgis_encoding='UTF-8',
path_time_log='',
save_results_intermediate=False,
overwrite=True):
if debug >= 3:
print('\n' + bold + green +
"Vulnérabilité des populations - Variables dans la fonction :" +
endC)
print(cyan + " populationVulnerability() : " + endC +
"input_division : " + str(input_division) + endC)
print(cyan + " populationVulnerability() : " + endC +
"input_footprint : " + str(input_footprint) + endC)
print(cyan + " populationVulnerability() : " + endC +
"input_population : " + str(input_population) + endC)
print(cyan + " populationVulnerability() : " + endC +
"input_built : " + str(input_built) + endC)
print(cyan + " populationVulnerability() : " + endC +
"output_vulnerability : " + str(output_vulnerability) + endC)
print(cyan + " populationVulnerability() : " + endC + "id_div : " +
str(id_div) + endC)
print(cyan + " populationVulnerability() : " + endC + "id_pop : " +
str(id_pop) + endC)
print(cyan + " populationVulnerability() : " + endC + "id_blt : " +
str(id_blt) + endC)
print(cyan + " populationVulnerability() : " + endC +
"stake_field : " + str(stake_field) + endC)
print(cyan + " populationVulnerability() : " + endC +
"health_vuln_field_list : " + str(health_vuln_field_list) + endC)
print(cyan + " populationVulnerability() : " + endC +
"social_vuln_field_list : " + str(social_vuln_field_list) + endC)
print(cyan + " populationVulnerability() : " + endC +
"height_field : " + str(height_field) + endC)
print(cyan + " populationVulnerability() : " + endC +
"built_sql_filter : " + str(built_sql_filter) + endC)
print(cyan + " populationVulnerability() : " + endC + "epsg : " +
str(epsg) + endC)
print(cyan + " populationVulnerability() : " + endC +
"format_vector : " + str(format_vector) + endC)
print(cyan + " populationVulnerability() : " + endC +
"postgis_ip_host : " + str(postgis_ip_host) + endC)
print(cyan + " populationVulnerability() : " + endC +
"postgis_num_port : " + str(postgis_num_port) + endC)
print(cyan + " populationVulnerability() : " + endC +
"postgis_user_name : " + str(postgis_user_name) + endC)
print(cyan + " populationVulnerability() : " + endC +
"postgis_password : "******" populationVulnerability() : " + endC +
"postgis_database_name : " + str(postgis_database_name) + endC)
print(cyan + " populationVulnerability() : " + endC +
"postgis_schema_name : " + str(postgis_schema_name) + endC)
print(cyan + " populationVulnerability() : " + endC +
"postgis_encoding : " + str(postgis_encoding) + endC)
print(cyan + " populationVulnerability() : " + endC +
"path_time_log : " + str(path_time_log) + endC)
print(cyan + " populationVulnerability() : " + endC +
"save_results_intermediate : " + str(save_results_intermediate) +
endC)
print(cyan + " populationVulnerability() : " + endC +
"overwrite : " + str(overwrite) + endC + '\n')
# Définition des constantes
PREFIX_STAND = 'S_'
STAKE_IND_FIELD = 'enjeu'
HEALTH_VULN_IND_FIELD = 'vuln_san'
SOCIAL_VULN_IND_FIELD = 'vuln_soc'
GLOBAL_VULN_IND_FIELD = 'vuln_glo'
# Mise à jour du log
starting_event = "populationVulnerability() : Début du traitement : "
timeLine(path_time_log, starting_event)
print(cyan + "populationVulnerability() : " + bold + green +
"DEBUT DES TRAITEMENTS" + endC + '\n')
# Définition des variables tables/champs PostGIS
div_table = 'i_division'
ftp_table = 'i_footprint'
pop_table = 'i_population'
blt_table = 'i_built'
vuln_table = 'o_vulnerability'
clean_pop_table = 't_clean_pop'
clean_blt_table = 't_clean_blt'
inter_pop_blt_table = 't_inter_pop_blt'
inter_popblt_div_table = 't_inter_popblt_div'
div_no_pop_table = 't_div_with_no_pop'
built_area_field = 'surf_bati'
floor_area_field = 'surf_habit'
inter_area_field = 'surf_inter'
# Définition de variables diverses
pop_fields_to_treat = [stake_field
] + health_vuln_field_list + social_vuln_field_list
pop_fields_to_keep = [id_pop] + pop_fields_to_treat
blt_fields_to_keep = [id_blt] + [height_field]
pop_fields_to_treat_str = stake_field
for health_vuln_field in health_vuln_field_list:
pop_fields_to_treat_str += ', ' + health_vuln_field
for social_vuln_field in social_vuln_field_list:
pop_fields_to_treat_str += ', ' + social_vuln_field
# Nettoyage des traitements précédents
if overwrite:
if debug >= 3:
print(cyan + "populationVulnerability() : " + endC +
"Nettoyage des traitements précédents." + endC + '\n')
removeVectorFile(output_vulnerability, format_vector=format_vector)
dropDatabase(postgis_database_name,
user_name=postgis_user_name,
password=postgis_password,
ip_host=postgis_ip_host,
num_port=postgis_num_port,
schema_name=postgis_schema_name)
else:
if os.path.exists(output_vulnerability):
print(cyan + "populationVulnerability() : " + bold + yellow +
"Le fichier de sortie existe déjà et ne sera pas regénéré." +
endC + '\n')
raise
pass
####################################################################
#############
# Etape 1/5 # Préparation de la base de données PostGIS
#############
print(
cyan + "populationVulnerability() : " + bold + green +
"ETAPE 1/5 - Début de la préparation de la base de données PostGIS." +
endC + '\n')
createDatabase(postgis_database_name,
user_name=postgis_user_name,
password=postgis_password,
ip_host=postgis_ip_host,
num_port=postgis_num_port,
schema_name=postgis_schema_name)
div_table = importVectorByOgr2ogr(postgis_database_name,
input_division,
div_table,
user_name=postgis_user_name,
password=postgis_password,
ip_host=postgis_ip_host,
num_port=postgis_num_port,
schema_name=postgis_schema_name,
epsg=epsg,
codage=postgis_encoding)
ftp_table = importVectorByOgr2ogr(postgis_database_name,
input_footprint,
ftp_table,
user_name=postgis_user_name,
password=postgis_password,
ip_host=postgis_ip_host,
num_port=postgis_num_port,
schema_name=postgis_schema_name,
epsg=epsg,
codage=postgis_encoding)
pop_table = importVectorByOgr2ogr(postgis_database_name,
input_population,
pop_table,
user_name=postgis_user_name,
password=postgis_password,
ip_host=postgis_ip_host,
num_port=postgis_num_port,
schema_name=postgis_schema_name,
epsg=epsg,
codage=postgis_encoding)
blt_table = importVectorByOgr2ogr(postgis_database_name,
input_built,
blt_table,
user_name=postgis_user_name,
password=postgis_password,
ip_host=postgis_ip_host,
num_port=postgis_num_port,
schema_name=postgis_schema_name,
epsg=epsg,
codage=postgis_encoding)
connection = openConnection(postgis_database_name,
user_name=postgis_user_name,
password=postgis_password,
ip_host=postgis_ip_host,
num_port=postgis_num_port,
schema_name=postgis_schema_name)
cursor = connection.cursor()
query = "CREATE INDEX IF NOT EXISTS %s_geom_gist ON %s USING GIST (geom);\n" % (
div_table, div_table)
query += "CREATE INDEX IF NOT EXISTS %s_geom_gist ON %s USING GIST (geom);\n" % (
ftp_table, ftp_table)
query += "CREATE INDEX IF NOT EXISTS %s_geom_gist ON %s USING GIST (geom);\n" % (
pop_table, pop_table)
query += "CREATE INDEX IF NOT EXISTS %s_geom_gist ON %s USING GIST (geom);\n" % (
blt_table, blt_table)
if debug >= 3:
print(query)
executeQuery(connection, query)
print(cyan + "populationVulnerability() : " + bold + green +
"ETAPE 1/5 - Fin de la préparation de la base de données PostGIS." +
endC + '\n')
#############
# Etape 2/5 # Préparation des données
#############
print(cyan + "populationVulnerability() : " + bold + green +
"ETAPE 2/5 - Début de la préparation des données." + endC + '\n')
# Préparation donnée bâti = sélection à la zone d'étude + suppression du bâti non-habitation + MAJ champ hauteur + ajout champ surface habitable
select_query = ""
for blt_field in blt_fields_to_keep:
select_query += "b.%s, " % blt_field
query = "DROP TABLE IF EXISTS %s;\n" % clean_blt_table
query += "CREATE TABLE %s AS\n" % clean_blt_table
query += " SELECT %s, b.geom\n" % select_query[:-2]
query += " FROM (\n"
query += " SELECT *\n"
query += " FROM %s\n" % blt_table
query += " WHERE %s\n" % built_sql_filter
query += " ) AS b, %s AS f\n" % ftp_table
query += " WHERE ST_Intersects(b.geom, f.geom);\n"
query += "CREATE INDEX IF NOT EXISTS %s_geom_gist ON %s USING GIST (geom);\n" % (
clean_blt_table, clean_blt_table)
query += "UPDATE %s SET %s = 3 WHERE %s < 3;\n" % (
clean_blt_table, height_field, height_field)
query += "ALTER TABLE %s ADD COLUMN %s NUMERIC(12,6);\n" % (
clean_blt_table, built_area_field)
query += "UPDATE %s SET %s = ST_Area(geom);\n" % (clean_blt_table,
built_area_field)
query += "ALTER TABLE %s ADD COLUMN %s NUMERIC(12,6);\n" % (
clean_blt_table, floor_area_field)
query += "UPDATE %s SET %s = ((%s * %s) / 3);\n" % (
clean_blt_table, floor_area_field, height_field, built_area_field)
if debug >= 3:
print(query)
executeQuery(connection, query)
blt_fields_to_keep.append(built_area_field)
blt_fields_to_keep.append(floor_area_field)
# Préparation donnée population = sélection à la zone d'étude + nettoyage de champs
select_query = ""
for pop_field in pop_fields_to_keep:
select_query += "p.%s, " % pop_field
query = "DROP TABLE IF EXISTS %s;\n" % clean_pop_table
query += "CREATE TABLE %s AS\n" % clean_pop_table
query += " SELECT %s, p.geom\n" % select_query[:-2]
query += " FROM %s AS p, %s AS f\n" % (pop_table, ftp_table)
query += " WHERE ST_Intersects(p.geom, f.geom);\n"
query += "CREATE INDEX IF NOT EXISTS %s_geom_gist ON %s USING GIST (geom);\n" % (
clean_pop_table, clean_pop_table)
if debug >= 3:
print(query)
executeQuery(connection, query)
print(cyan + "populationVulnerability() : " + bold + green +
"ETAPE 2/5 - Fin de la préparation des données." + endC + '\n')
#############
# Etape 3/5 # Intersect de données
#############
print(cyan + "populationVulnerability() : " + bold + green +
"ETAPE 3/5 - Début de l'intersect de données." + endC + '\n')
# Calcul du prorata de population par bâtiment
select_query = ""
for blt_field in blt_fields_to_keep:
select_query += "b.%s, " % blt_field
for pop_field in pop_fields_to_keep:
select_query += "p.%s, " % pop_field
query = "DROP TABLE IF EXISTS %s;\n" % inter_pop_blt_table
query += "CREATE TABLE %s AS\n" % inter_pop_blt_table
query += " SELECT %s, ST_Intersection(b.geom, p.geom) AS geom\n" % select_query[:
-2]
query += " FROM %s AS b, %s AS p\n" % (clean_blt_table, clean_pop_table)
query += " WHERE ST_Intersects(b.geom, p.geom);\n"
query += "CREATE INDEX IF NOT EXISTS %s_geom_gist ON %s USING GIST (geom);\n" % (
inter_pop_blt_table, inter_pop_blt_table)
query += "ALTER TABLE %s ADD COLUMN %s NUMERIC(12,6);\n" % (
inter_pop_blt_table, inter_area_field)
query += "UPDATE %s SET %s = ST_Area(geom);\n" % (inter_pop_blt_table,
inter_area_field)
for pop_field in pop_fields_to_treat:
query += "UPDATE %s SET %s = (%s * (%s / %s));\n" % (
inter_pop_blt_table, pop_field, pop_field, inter_area_field,
built_area_field)
if debug >= 3:
print(query)
executeQuery(connection, query)
# Somme de population par polygone division
select_query = "SUM(i.%s) AS %s, " % (floor_area_field, floor_area_field)
for pop_field in pop_fields_to_treat:
select_query += "SUM(i.%s) AS %s, " % (pop_field, pop_field)
query = "DROP TABLE IF EXISTS %s;\n" % inter_popblt_div_table
query += "CREATE TABLE %s AS\n" % inter_popblt_div_table
query += " SELECT d.%s, %s, d.geom\n" % (id_div, select_query[:-2])
query += " FROM %s AS d, %s AS i\n" % (div_table, inter_pop_blt_table)
query += " WHERE ST_Intersects(d.geom, i.geom)\n"
query += " GROUP BY d.%s, d.geom;\n" % id_div
query += "CREATE INDEX IF NOT EXISTS %s_geom_gist ON %s USING GIST (geom);\n" % (
inter_popblt_div_table, inter_popblt_div_table)
if debug >= 3:
print(query)
executeQuery(connection, query)
print(cyan + "populationVulnerability() : " + bold + green +
"ETAPE 3/5 - Fin de l'intersect de données." + endC + '\n')
#############
# Etape 4/5 # Préparation du fichier final
#############
print(cyan + "populationVulnerability() : " + bold + green +
"ETAPE 4/5 - Début de la préparation du fichier final." + endC +
'\n')
# Nouvelle table avec les géométries sans données population
query = "DROP TABLE IF EXISTS %s;\n" % div_no_pop_table
query += "CREATE TABLE %s AS\n" % div_no_pop_table
query += " SELECT DISTINCT %s, geom\n" % id_div
query += " FROM %s\n" % div_table
query += " WHERE %s NOT IN\n" % id_div
query += " (SELECT DISTINCT %s\n" % id_div
query += " FROM %s);\n" % inter_popblt_div_table
query += "CREATE INDEX IF NOT EXISTS %s_geom_gist ON %s USING GIST (geom);\n" % (
div_no_pop_table, div_no_pop_table)
query += "ALTER TABLE %s ADD COLUMN %s NUMERIC(12,6) DEFAULT 0;\n" % (
div_no_pop_table, floor_area_field)
for pop_field in pop_fields_to_treat:
query += "ALTER TABLE %s ADD COLUMN %s NUMERIC(8,6) DEFAULT 0;\n" % (
div_no_pop_table, pop_field)
if debug >= 3:
print(query)
executeQuery(connection, query)
# Union de tables pour reconstituer la table finale
query = "DROP TABLE IF EXISTS %s;\n" % vuln_table
query += "CREATE TABLE %s AS\n" % vuln_table
query += " SELECT %s, %s, %s, geom\n" % (id_div, floor_area_field,
pop_fields_to_treat_str)
query += " FROM %s\n" % inter_popblt_div_table
query += " UNION\n"
query += " SELECT %s, %s, %s, geom\n" % (id_div, floor_area_field,
pop_fields_to_treat_str)
query += " FROM %s;\n" % div_no_pop_table
query += "CREATE INDEX IF NOT EXISTS %s_geom_gist ON %s USING GIST (geom);\n" % (
vuln_table, vuln_table)
if debug >= 3:
print(query)
executeQuery(connection, query)
# Standardisation des données
for pop_field in pop_fields_to_treat:
pop_stand_field = PREFIX_STAND + pop_field[:8]
cursor.execute("SELECT min(%s) FROM %s;" % (pop_field, vuln_table))
min_value = cursor.fetchone()
cursor.execute("SELECT max(%s) FROM %s;" % (pop_field, vuln_table))
max_value = cursor.fetchone()
pop_stand_calc = "((%s - %s) / (%s - %s))" % (
pop_field, min_value[0], max_value[0], min_value[0])
query = "ALTER TABLE %s ADD COLUMN %s NUMERIC(8,6);\n" % (
vuln_table, pop_stand_field)
query += "UPDATE %s SET %s = %s;\n" % (vuln_table, pop_stand_field,
pop_stand_calc)
if debug >= 3:
print(query)
executeQuery(connection, query)
print(cyan + "populationVulnerability() : " + bold + green +
"ETAPE 4/5 - préparation du fichier final." + endC + '\n')
#############
# Etape 5/5 # Calcul des indicateurs
#############
print(cyan + "populationVulnerability() : " + bold + green +
"ETAPE 5/5 - Début du calcul des indicateurs." + endC + '\n')
# Calcul indice enjeu
stake_stand_field = PREFIX_STAND + stake_field[:8]
cursor.execute("SELECT max(%s) FROM %s;" % (stake_stand_field, vuln_table))
max_enjeu = cursor.fetchone()
query = "ALTER TABLE %s ADD COLUMN %s NUMERIC(8,6);\n" % (vuln_table,
STAKE_IND_FIELD)
query += "UPDATE %s SET %s = (%s / %s);\n" % (
vuln_table, STAKE_IND_FIELD, stake_stand_field, max_enjeu[0])
if debug >= 3:
print(query)
executeQuery(connection, query)
# Indice de vulnérabilité sanitaire
health_vuln_pop_sum = ""
for health_vuln_field in health_vuln_field_list:
pop_stand_field = PREFIX_STAND + health_vuln_field[:8]
health_vuln_pop_sum += "%s + " % pop_stand_field
health_vuln_pop_sum = health_vuln_pop_sum[:-3]
cursor.execute("SELECT max(%s) FROM %s;" %
(health_vuln_pop_sum, vuln_table))
max_health_vuln_pop_sum = cursor.fetchone()
query = "ALTER TABLE %s ADD COLUMN %s NUMERIC(8,6);\n" % (
vuln_table, HEALTH_VULN_IND_FIELD)
query += "UPDATE %s SET %s = ((%s) / %s);\n" % (
vuln_table, HEALTH_VULN_IND_FIELD, health_vuln_pop_sum,
max_health_vuln_pop_sum[0])
if debug >= 3:
print(query)
executeQuery(connection, query)
# Indice de vulnérabilité sociale
social_vuln_pop_sum = ""
for social_vuln_field in social_vuln_field_list:
pop_stand_field = PREFIX_STAND + social_vuln_field[:8]
social_vuln_pop_sum += "%s + " % pop_stand_field
social_vuln_pop_sum = social_vuln_pop_sum[:-3]
cursor.execute("SELECT max(%s) FROM %s;" %
(social_vuln_pop_sum, vuln_table))
max_social_vuln_pop_sum = cursor.fetchone()
query = "ALTER TABLE %s ADD COLUMN %s NUMERIC(8,6);\n" % (
vuln_table, SOCIAL_VULN_IND_FIELD)
query += "UPDATE %s SET %s = ((%s) / %s);\n" % (
vuln_table, SOCIAL_VULN_IND_FIELD, social_vuln_pop_sum,
max_social_vuln_pop_sum[0])
if debug >= 3:
print(query)
executeQuery(connection, query)
# Indice de vulnérabilité globale
cursor.execute("SELECT max(%s + %s) FROM %s;" %
(HEALTH_VULN_IND_FIELD, SOCIAL_VULN_IND_FIELD, vuln_table))
max_pop_vuln_glo_sum = cursor.fetchone()
query = "ALTER TABLE %s ADD COLUMN %s NUMERIC(8,6);\n" % (
vuln_table, GLOBAL_VULN_IND_FIELD)
query += "UPDATE %s SET %s = ((%s + %s) / %s);\n" % (
vuln_table, GLOBAL_VULN_IND_FIELD, HEALTH_VULN_IND_FIELD,
SOCIAL_VULN_IND_FIELD, max_pop_vuln_glo_sum[0])
if debug >= 3:
print(query)
executeQuery(connection, query)
print(cyan + "populationVulnerability() : " + bold + green +
"ETAPE 5/5 - Fin du calcul des indicateurs." + endC + '\n')
closeConnection(connection)
exportVectorByOgr2ogr(postgis_database_name,
output_vulnerability,
vuln_table,
user_name=postgis_user_name,
password=postgis_password,
ip_host=postgis_ip_host,
num_port=postgis_num_port,
schema_name=postgis_schema_name,
format_type=format_vector)
####################################################################
# Suppression des fichiers temporaires
if not save_results_intermediate:
if debug >= 3:
print(cyan + "populationVulnerability() : " + endC +
"Suppression des fichiers temporaires." + endC + '\n')
dropDatabase(postgis_database_name,
user_name=postgis_user_name,
password=postgis_password,
ip_host=postgis_ip_host,
num_port=postgis_num_port,
schema_name=postgis_schema_name)
print(cyan + "populationVulnerability() : " + bold + green +
"FIN DES TRAITEMENTS" + endC + '\n')
# Mise à jour du log
ending_event = "populationVulnerability() : Fin du traitement : "
timeLine(path_time_log, ending_event)
return 0
def traitementsPostGIS(urbanatlas_input, ucz_output, emprise_file, mask_file,
enter_with_mask, image_file, mnh_file, built_files_list,
hydrography_file, roads_files_list, rpg_file,
indicators_method, ucz_method, dbms_choice,
threshold_ndvi, threshold_ndvi_water, threshold_ndwi2,
threshold_bi_bottom, threshold_bi_top, path_time_log,
temp_directory):
############################################################
### Fin des calculs des indicateurs, à partir de PostGIS ###
############################################################
print(bold + yellow +
"Début de l'étape finale de calcul des indicateurs." + endC)
step = " Début de l'étape finale de calcul des indicateurs : "
timeLine(path_time_log, step)
database = os.path.splitext(
os.path.basename(ucz_output))[0].lower() # Base de données PostGIS
grid_ready_cleaned = temp_directory + os.sep + os.path.splitext(
os.path.basename(urbanatlas_input)
)[0] + "_cut_cleaned.shp" # Fichier shape maillage
built_shape = temp_directory + os.sep + "bati.shp" # Fichier shape bâti
grid_table = ucz_method.lower(
) + ".maille" # Table PostGIS pour le maillage
built_table = ucz_method.lower() + ".bati" # Table PostGIS pour le bâti
grid_codage = "latin1" # Encodage du fichier shape de maillage. Par défaut : latin1.
if indicators_method == "Resultats_classif":
built_codage = "utf-8" # Encodage du fichier shape de bâti issu du traitement de la classif supervisée. Par défaut : utf-8
built_height = "(mean/100)" # Nom du champ du fichier shape de bâti issu du traitement de la classif supervisée contenant l'information de hauteur. Par défaut : mean ('/100' ajouté pour retrouver une hauteur en mètres dans les calculs)
else:
built_codage = "latin1" # Encodage du fichier shape de bâti issu de la BD TOPO. Par défaut : latin1
built_height = "hauteur" # Nom du champ du fichier shape de bâti issu de la BD TOPO contenant l'information de hauteur. Par défaut : hauteur
### Mise en place de la base de données PostGIS et fin des calculs des indicateurs ###
createDatabase(
database,
user_name='postgres',
password='******',
ip_host='localhost',
num_port='5432') # Création de la base de données si elle n'existe pas
connection = openConnection(database,
user_name='postgres',
password='******',
ip_host='localhost',
num_port='5432',
schema_name='')
createSchema(connection,
ucz_method.lower()) # Création du schéma s'il n'existe pas
closeConnection(connection)
print(bold + cyan + " Import du fichier shape de maillage :" + endC)
grid_table = importShape(database,
grid_ready_cleaned,
grid_table,
user_name='postgres',
password='******',
ip_host='localhost',
num_port='5432',
schema_name=ucz_method.lower(),
epsg='2154',
codage=grid_codage)
print("\n")
print(bold + cyan + " Import du fichier shape du bâti :" + endC)
built_table = importShape(database,
built_shape,
built_table,
user_name='postgres',
password='******',
ip_host='localhost',
num_port='5432',
schema_name=ucz_method.lower(),
epsg='2154',
codage=built_codage)
print("\n")
connection = openConnection(database,
user_name='postgres',
password='******',
ip_host='localhost',
num_port='5432',
schema_name=ucz_method.lower()
) # Connection à la base de données PostGIS
# Calcul du périmètre et de la surface de façades de chaque bâtiment
print(
bold + cyan +
" Calcul du périmètre et de la surface de façades de chaque bâtiment :"
+ endC)
query = """
ALTER TABLE %s.bati DROP IF EXISTS perimeter;
ALTER TABLE %s.bati ADD perimeter NUMERIC(10,2);
UPDATE %s.bati SET perimeter = st_perimeter(geom);
ALTER TABLE %s.bati DROP IF EXISTS surf_fac;
ALTER TABLE %s.bati ADD surf_fac NUMERIC(10,2);
UPDATE %s.bati SET surf_fac = %s * perimeter;
""" % (ucz_method.lower(), ucz_method.lower(), ucz_method.lower(),
ucz_method.lower(), ucz_method.lower(), ucz_method.lower(),
built_height)
print(query + "\n")
executeQuery(connection, query)
# Calcul de la somme de surface de façades des bâtiments par maille
print(
bold + cyan +
" Calcul de la somme de surface de façades des bâtiments par maille :"
+ endC)
query = """
DROP TABLE IF EXISTS %s.temp0;
CREATE TABLE %s.temp0 AS
SELECT m.ID AS ID, sum(b.surf_fac) AS surf_fac
FROM %s.bati AS b, %s.maille AS m
WHERE st_intersects(b.geom, m.geom)
GROUP BY m.ID;
""" % (ucz_method.lower(), ucz_method.lower(), ucz_method.lower(),
ucz_method.lower())
print(query + "\n")
executeQuery(connection, query)
# Création d'une nouvelle table avec seulement les mailles qui intersectent du bâti
print(
bold + cyan +
" Création d'une nouvelle table avec seulement les mailles qui intersectent du bâti :"
+ endC)
query = """
DROP TABLE IF EXISTS %s.temp;
CREATE TABLE %s.temp AS
SELECT m.ID AS ID, m.imperm AS SI, m.s_nonbati AS surf_nonba, t.surf_fac AS surf_fac, (m.mean / 100) AS z0, m.geom AS geom
FROM %s.maille AS m, %s.temp0 AS t
WHERE t.ID = m.ID;
""" % (ucz_method.lower(), ucz_method.lower(), ucz_method.lower(),
ucz_method.lower())
print(query + "\n")
executeQuery(connection, query)
# Fin du calcul du rapport d'aspect
print(bold + cyan + " Fin du calcul du rapport d'aspect :" + endC)
query = """
ALTER TABLE %s.temp DROP IF EXISTS RA;
ALTER TABLE %s.temp ADD RA NUMERIC(10,2);
UPDATE %s.temp SET RA = (0.5 * (surf_fac / (surf_nonba + 0.0001)));
""" % (ucz_method.lower(), ucz_method.lower(), ucz_method.lower())
print(query + "\n")
executeQuery(connection, query)
# Fin du calcul de la classe de rugosité
print(bold + cyan + " Fin du calcul de la classe de rugosité :" + endC)
query = """
ALTER TABLE %s.temp DROP IF EXISTS Rug;
ALTER TABLE %s.temp ADD Rug INTEGER;
UPDATE %s.temp SET Rug = 1 WHERE z0 < 0.005;
UPDATE %s.temp SET Rug = 2 WHERE z0 >= 0.005 AND z0 < 0.03;
UPDATE %s.temp SET Rug = 3 WHERE z0 >= 0.03 AND z0 < 0.1;
UPDATE %s.temp SET Rug = 4 WHERE z0 >= 0.1 AND z0 < 0.25;
UPDATE %s.temp SET Rug = 5 WHERE z0 >= 0.25 AND z0 < 0.5;
UPDATE %s.temp SET Rug = 6 WHERE z0 >= 0.5 AND z0 < 1;
UPDATE %s.temp SET Rug = 7 WHERE z0 >= 1 AND z0 < 2;
UPDATE %s.temp SET Rug = 8 WHERE z0 >= 2;
""" % (ucz_method.lower(), ucz_method.lower(), ucz_method.lower(),
ucz_method.lower(), ucz_method.lower(), ucz_method.lower(),
ucz_method.lower(), ucz_method.lower(), ucz_method.lower(),
ucz_method.lower())
print(query + "\n")
executeQuery(connection, query)
# Création d'une nouvelle table avec les mailles qui n'intersectent pas de bâtiment, complémentaire de la précédente
print(
bold + cyan +
" Création d'une nouvelle table avec les mailles qui n'intersectent pas de bâtiment, complémentaire de la précédente :"
+ endC)
query = """
DROP TABLE IF EXISTS %s.temp_bis;
CREATE TABLE %s.temp_bis AS
SELECT DISTINCT ID, imperm AS SI, geom
FROM %s.maille
WHERE ID NOT IN
(SELECT DISTINCT ID
FROM %s.temp);
""" % (ucz_method.lower(), ucz_method.lower(), ucz_method.lower(),
ucz_method.lower())
print(query + "\n")
executeQuery(connection, query)
# Ajout + MAJ par défaut des champs 'rapport d'aspect' (à 0) et 'classe de rugosité' (à 1) dans cette nouvelle table
print(
bold + cyan +
" Ajout (et MAJ par défaut) des champs 'rapport d'aspect' (à 0) et 'classe de rugosité' (à 1) dans cette nouvelle table :"
+ endC)
query = """
ALTER TABLE %s.temp_bis DROP IF EXISTS RA;
ALTER TABLE %s.temp_bis ADD RA NUMERIC(10,2);
UPDATE %s.temp_bis SET RA = 0;
ALTER TABLE %s.temp_bis DROP IF EXISTS Rug;
ALTER TABLE %s.temp_bis ADD Rug INTEGER;
UPDATE %s.temp_bis SET Rug = 1;
""" % (ucz_method.lower(), ucz_method.lower(), ucz_method.lower(),
ucz_method.lower(), ucz_method.lower(), ucz_method.lower())
print(query + "\n")
executeQuery(connection, query)
closeConnection(
connection) # Fermeture de la connexion à la base de données PostGIS
step = " Fin de l'étape finale de calcul des indicateurs : "
timeLine(path_time_log, step)
print(bold + yellow + "Fin de l'étape finale de calcul des indicateurs." +
endC)
print("\n")
#######################################################################################
### Étape finale de cartographie en Zones Climatiques Urbaines, à partir de PostGIS ###
#######################################################################################
print(
bold + yellow +
"Début de l'étape finale de cartographie en Zones Climatiques Urbaines."
+ endC)
step = " Début de l'étape finale de cartographie en Zones Climatiques Urbaines : "
timeLine(path_time_log, step)
ucz_table = ucz_method.lower() + ".ucz" # Table PostGIS
### Tous les indicateurs sont calculés dans la base de données : étape finale d'attribution d'une classe d'UCZ à chaque polygone du maillage ###
connection = openConnection(database,
user_name='postgres',
password='******',
ip_host='localhost',
num_port='5432',
schema_name=ucz_method.lower()
) # Connection à la base de données PostGIS
# Création de la table finale où seront attribuées les classes d'UCZ
print(
bold + cyan +
" Création de la table finale où seront attribuées les classes d'UCZ :"
+ endC)
query = """
DROP TABLE IF EXISTS %s.ucz;
CREATE TABLE %s.ucz AS
SELECT ID, SI, RA, Rug, geom
FROM %s.temp
UNION
SELECT ID, SI, RA, Rug, geom
FROM %s.temp_bis;
ALTER TABLE %s.ucz DROP IF EXISTS UCZ;
ALTER TABLE %s.ucz ADD UCZ INTEGER;
ALTER TABLE %s.ucz ALTER COLUMN ID TYPE INTEGER;
""" % (ucz_method.lower(), ucz_method.lower(), ucz_method.lower(),
ucz_method.lower(), ucz_method.lower(), ucz_method.lower(),
ucz_method.lower())
print(query + "\n")
executeQuery(connection, query)
# Exécution des requêtes SQL d'attribution des classes d'UCZ
print(bold + cyan +
" Exécution des requêtes SQL d'attribution des classes d'UCZ :" +
endC)
query_ucz = choixSeuilsUCZ(ucz_method, 'PostGIS')
print(query_ucz + "\n")
executeQuery(connection, query_ucz)
closeConnection(
connection) # Fermeture de la connexion à la base de données PostGIS
# Export de la table UCZ en fichier shape
print(bold + cyan +
" Export de la table 'UCZ' en fichier shape '%s' :" %
(ucz_output) + endC)
exportShape(database,
ucz_output,
ucz_table,
user_name='postgres',
password='******',
ip_host='localhost',
num_port='5432',
schema_name=ucz_method.lower())
step = " Fin de l'étape finale de cartographie en Zones Climatiques Urbaines : "
timeLine(path_time_log, step)
print(
bold + yellow +
"Fin de l'étape finale de cartographie en Zones Climatiques Urbaines."
+ endC)
print("\n")
return
def terrainRoughnessClass(grid_input, grid_output, built_input, distance_lines, epsg, project_encoding, server_postgis, port_number, user_postgis, password_postgis, database_postgis, schema_postgis, path_time_log, format_vector='ESRI Shapefile', save_results_intermediate=False, overwrite=True):
print(bold + yellow + "Début du calcul de l'indicateur Terrain Roughness Class." + endC + "\n")
timeLine(path_time_log, "Début du calcul de l'indicateur Terrain Roughness Class : ")
if debug >= 3:
print(bold + green + "terrainRoughnessClass() : Variables dans la fonction" + endC)
print(cyan + "terrainRoughnessClass() : " + endC + "grid_input : " + str(grid_input) + endC)
print(cyan + "terrainRoughnessClass() : " + endC + "grid_output : " + str(grid_output) + endC)
print(cyan + "terrainRoughnessClass() : " + endC + "built_input : " + str(built_input) + endC)
print(cyan + "terrainRoughnessClass() : " + endC + "distance_lines : " + str(distance_lines) + endC)
print(cyan + "terrainRoughnessClass() : " + endC + "epsg : " + str(epsg) + endC)
print(cyan + "terrainRoughnessClass() : " + endC + "project_encoding : " + str(project_encoding) + endC)
print(cyan + "terrainRoughnessClass() : " + endC + "server_postgis : " + str(server_postgis) + endC)
print(cyan + "terrainRoughnessClass() : " + endC + "port_number : " + str(port_number) + endC)
print(cyan + "terrainRoughnessClass() : " + endC + "user_postgis : " + str(user_postgis) + endC)
print(cyan + "terrainRoughnessClass() : " + endC + "password_postgis : " + str(password_postgis) + endC)
print(cyan + "terrainRoughnessClass() : " + endC + "database_postgis : " + str(database_postgis) + endC)
print(cyan + "terrainRoughnessClass() : " + endC + "schema_postgis : " + str(schema_postgis) + endC)
print(cyan + "terrainRoughnessClass() : " + endC + "path_time_log : " + str(path_time_log) + endC)
print(cyan + "terrainRoughnessClass() : " + endC + "format_vector : " + str(format_vector) + endC)
print(cyan + "terrainRoughnessClass() : " + endC + "save_results_intermediate : " + str(save_results_intermediate) + endC)
print(cyan + "terrainRoughnessClass() : " + endC + "overwrite : " + str(overwrite) + endC)
print("\n")
if not os.path.exists(grid_output) or overwrite:
############################################
### Préparation générale des traitements ###
############################################
print(bold + cyan + "Préparation au calcul de Terrain Roughness Class :" + endC)
timeLine(path_time_log, " Préparation au calcul de Terrain Roughness Class : ")
if os.path.exists(grid_output):
removeVectorFile(grid_output)
# Création de la base de données PostGIS
# ~ dropDatabase(database_postgis, user_name=user_postgis, password=password_postgis, ip_host=server_postgis, num_port=port_number, schema_name=schema_postgis) # Conflits avec autres indicateurs (Aspect Ratio / Height of Roughness Elements)
createDatabase(database_postgis, user_name=user_postgis, password=password_postgis, ip_host=server_postgis, num_port=port_number, schema_name=schema_postgis)
# Import des fichiers shapes maille et bati dans la base de données PostGIS
table_name_maille = importVectorByOgr2ogr(database_postgis, grid_input, 'trc_maille', user_name=user_postgis, password=password_postgis, ip_host=server_postgis, num_port=str(port_number), schema_name=schema_postgis, epsg=str(epsg), codage=project_encoding)
table_name_bati = importVectorByOgr2ogr(database_postgis, built_input, 'trc_bati', user_name=user_postgis, password=password_postgis, ip_host=server_postgis, num_port=str(port_number), schema_name=schema_postgis, epsg=str(epsg), codage=project_encoding)
# Récupération de l'emprise de la zone d'étude, définie par le fichier maillage d'entrée
xmin,xmax,ymin,ymax = getEmpriseFile(grid_input, format_vector)
if debug >= 1:
print(bold + "Emprise du fichier '%s' :" % (grid_input) + endC)
print(" xmin = " + str(xmin))
print(" xmax = " + str(xmax))
print(" ymin = " + str(ymin))
print(" ymax = " + str(ymax))
# Création de la liste des valeurs de x à entrer dans la requêtes SQL de création de lignes
x_list = [xmin] # Initialisation de la liste
x = xmin # Définition de la valeur du 1er x à entrer dans la boucle
while x < (xmax - distance_lines): # On boucle tant que la valeur de x ne dépasse pas le xmax du fichier maillage en entrée
x = x + distance_lines
x_list.append(x) # Ajout de la nouvelle valeur de x dans la liste
if debug >= 2:
print(bold + "x_list : " + endC + str(x_list) + "\n")
# Création de la liste des valeurs de y à entrer dans la requêtes SQL de création de lignes
y_list = [ymax] # Initialisation de la liste
y = ymax # Définition de la valeur du 1er y à entrer dans la boucle
while y > (ymin + distance_lines): # On boucle tant que la valeur de y ne descend pas en-dessous du ymin du fichier maillage en entrée
y = y - distance_lines
y_list.append(y) # Ajout de la nouvelle valeur de y dans la liste
if debug >= 2:
print(bold + "y_list : " + endC + str(y_list) + "\n")
#################################################
### Création des lignes parallèles N-S et W-E ###
#################################################
print(bold + cyan + "Création des lignes parallèles N-S et W-E :" + endC)
timeLine(path_time_log, " Création des lignes parallèles N-S et W-E : ")
connection = openConnection(database_postgis, user_name=user_postgis, password=password_postgis, ip_host=server_postgis, num_port=port_number, schema_name=schema_postgis)
# Construction de la requête de création des lignes parallèles N-S
query_lines_NS = "DROP TABLE IF EXISTS trc_lines_NS;\n"
query_lines_NS += "CREATE TABLE trc_lines_NS (line text, geom geometry);\n"
query_lines_NS += "INSERT INTO trc_lines_NS VALUES\n"
# Boucle sur les valeurs de x dans la liste associée, pour construire la requête de création des lignes parallèles N-S
count_NS = 0
for x in x_list:
count_NS += 1
query_lines_NS += " ('line_NS_%s', 'LINESTRING(%s %s, %s %s)'),\n" % (count_NS, x, ymax, x, ymin)
# Fin de la requête de création des lignes parallèles N-S et exécution de cette requête
query_lines_NS = query_lines_NS[:-2] + ";\n" # Transformer la virgule de la dernière ligne SQL en point-virgule (pour terminer la requête)
query_lines_NS += "ALTER TABLE trc_lines_NS ALTER COLUMN geom TYPE geometry(LINESTRING,%s) USING ST_SetSRID(geom,%s);\n" % (epsg,epsg) # Mise à jour du système de coordonnées
if debug >= 1:
print(query_lines_NS)
executeQuery(connection, query_lines_NS)
# Construction de la requête de création des lignes parallèles W-E
query_lines_WE = "DROP TABLE IF EXISTS trc_lines_WE;\n"
query_lines_WE += "CREATE TABLE trc_lines_WE (line text, geom geometry);\n"
query_lines_WE += "INSERT INTO trc_lines_WE VALUES\n"
# Boucle sur les valeurs de y dans la liste associée, pour construire la requête de création des lignes parallèles W-E
count_WE = 0
for y in y_list:
count_WE += 1
query_lines_WE += " ('line_WE_%s', 'LINESTRING(%s %s, %s %s)'),\n" % (count_WE, xmin, y, xmax, y)
# Fin de la requête de création des lignes parallèles W-E et exécution de cette requête
query_lines_WE = query_lines_WE[:-2] + ";\n" # Transformer la virgule de la dernière ligne SQL en point-virgule (pour terminer la requête)
query_lines_WE += "ALTER TABLE trc_lines_WE ALTER COLUMN geom TYPE geometry(LINESTRING,%s) USING ST_SetSRID(geom,%s);\n" % (epsg,epsg) # Mise à jour du système de coordonnées
if debug >= 1:
print(query_lines_WE)
executeQuery(connection, query_lines_WE)
#####################################################################################################
### Découpage des bâtiments, et des lignes N-S et W-E à cheval sur plusieurs mailles (intersects) ###
#####################################################################################################
print(bold + cyan + "Lancement des requêtes d'intersect :" + endC)
timeLine(path_time_log, " Lancement des requêtes d'intersect : ")
query_intersect = """
--CREATE INDEX IF NOT EXISTS maille_geom_gist ON %s USING GIST (geom);
--CREATE INDEX IF NOT EXISTS bati_geom_gist ON %s USING GIST (geom);
CREATE INDEX IF NOT EXISTS lines_NS_geom_gist ON trc_lines_NS USING GIST (geom);
CREATE INDEX IF NOT EXISTS lines_WE_geom_gist ON trc_lines_WE USING GIST (geom);
DROP TABLE IF EXISTS trc_decoup;
CREATE TABLE trc_decoup AS
SELECT b.ID as ID, b.HAUTEUR as hauteur, ST_Intersection(b.geom, m.geom) as geom
FROM %s as b, %s as m
WHERE ST_Intersects(b.geom, m.geom);
CREATE INDEX IF NOT EXISTS decoup_geom_gist ON trc_decoup USING GIST (geom);
DROP TABLE IF EXISTS trc_decoupNS;
CREATE TABLE trc_decoupNS AS
SELECT m.ID as ID, l.line as line, ST_Intersection(l.geom, m.geom) as geom
FROM trc_lines_NS as l, %s as m
WHERE ST_Intersects(l.geom, m.geom);
CREATE INDEX IF NOT EXISTS decoupNS_geom_gist ON trc_decoupNS USING GIST (geom);
DROP TABLE IF EXISTS trc_decoupWE;
CREATE TABLE trc_decoupWE AS
SELECT m.ID as ID, l.line as line, ST_Intersection(l.geom, m.geom) as geom
FROM trc_lines_WE as l, %s as m
WHERE ST_Intersects(l.geom, m.geom);
CREATE INDEX IF NOT EXISTS decoupWE_geom_gist ON trc_decoupWE USING GIST (geom);
""" % (table_name_maille, table_name_bati, table_name_bati, table_name_maille, table_name_maille, table_name_maille)
if debug >= 1:
print(query_intersect)
executeQuery(connection, query_intersect)
#######################################################################################################################################################
### Calculs pour l'obtention des sous-indicateurs liés à l'intersect des bâtiments dans chaque maille : h, plan area ratio, hauteur de déplacement ###
#######################################################################################################################################################
print(bold + cyan + "Calcul des indicateurs secondaires liés aux bâtiments intersectant chaque maille :" + endC)
timeLine(path_time_log, " Calcul des indicateurs secondaires liés aux bâtiments intersectant chaque maille : ")
query_bati = """
DROP TABLE IF EXISTS trc_temp_a;
CREATE TABLE trc_temp_a AS
SELECT ID, hauteur, st_area(geom) as surface, geom
FROM trc_decoup;
ALTER TABLE trc_temp_a ADD volume DOUBLE PRECISION;
UPDATE trc_temp_a SET volume = (surface * hauteur);
ALTER TABLE trc_temp_a ADD VxH DOUBLE PRECISION;
UPDATE trc_temp_a SET VxH = (volume * hauteur);
CREATE INDEX IF NOT EXISTS temp_a_geom_gist ON trc_temp_a USING GIST (geom);
DROP TABLE IF EXISTS trc_temp_b;
CREATE TABLE trc_temp_b AS
SELECT m.ID as ID, (sum(a.VxH) / sum(a.volume)) as h, (sum(a.surface) / st_area(m.geom)) as PAR, m.geom as geom
FROM %s as m, trc_temp_a as a
WHERE ST_Intersects(m.geom, a.geom)
GROUP BY m.ID, m.geom;
ALTER TABLE trc_temp_b ADD zd DOUBLE PRECISION;
UPDATE trc_temp_b SET zd = (h * (PAR ^ 0.6));
CREATE INDEX IF NOT EXISTS temp_b_geom_gist ON trc_temp_b USING GIST (geom);
DROP TABLE IF EXISTS trc_temp_c;
CREATE TABLE trc_temp_c AS
SELECT DISTINCT ID, geom
FROM %s
WHERE ID NOT IN
(SELECT DISTINCT ID
FROM trc_temp_b);
ALTER TABLE trc_temp_c ADD h DOUBLE PRECISION;
UPDATE trc_temp_c SET h = 0;
ALTER TABLE trc_temp_c ADD PAR DOUBLE PRECISION;
UPDATE trc_temp_c SET PAR = 0;
ALTER TABLE trc_temp_c ADD zd DOUBLE PRECISION;
UPDATE trc_temp_c SET zd = 0;
CREATE INDEX IF NOT EXISTS temp_c_geom_gist ON trc_temp_c USING GIST (geom);
DROP TABLE IF EXISTS trc_temp;
CREATE TABLE trc_temp AS
SELECT ID, h, PAR, zd, geom
FROM trc_temp_b
UNION
SELECT ID, h, PAR, zd, geom
FROM trc_temp_c;
ALTER TABLE trc_temp ALTER COLUMN ID TYPE INTEGER;
CREATE INDEX IF NOT EXISTS temp_geom_gist ON trc_temp USING GIST (geom);
""" % (table_name_maille, table_name_maille)
if debug >= 1:
print(query_bati)
executeQuery(connection, query_bati)
#####################################################################################################################################################
### Calculs pour l'obtention des sous-indicateurs liés à l'intersect des bâtiments avec chaque ligne N-S, dans chaque maille : frontal area ratio ###
#####################################################################################################################################################
print(bold + cyan + "Calcul des indicateurs secondaires liés aux bâtiments intersectant chaque ligne N-S, intersectant chaque maille :" + endC)
timeLine(path_time_log, " Calcul des indicateurs secondaires liés aux bâtiments intersectant chaque ligne N-S, intersectant chaque maille : ")
query_NS = """
DROP TABLE IF EXISTS trc_tempNS_a;
CREATE TABLE trc_tempNS_a AS
SELECT ns.ID as ID, ns.line as line, max(d.hauteur) as max_haut, (%s * max(d.hauteur)) as FA, ns.geom as geom
FROM trc_decoup as d, trc_decoupNS as ns
WHERE ST_Intersects(d.geom, ns.geom)
GROUP BY ns.ID, ns.line, ns.geom;
CREATE INDEX IF NOT EXISTS tempNS_a_geom_gist ON trc_tempNS_a USING GIST (geom);
DROP TABLE IF EXISTS trc_tempNS_b;
CREATE TABLE trc_tempNS_b AS
SELECT m.ID as ID, (sum(a.FA) / st_area(m.geom)) as FAR, m.geom as geom
FROM trc_tempNS_a as a, %s as m
WHERE ST_Intersects(a.geom, m.geom)
GROUP BY m.ID, m.geom;
CREATE INDEX IF NOT EXISTS tempNS_b_geom_gist ON trc_tempNS_b USING GIST (geom);
DROP TABLE IF EXISTS trc_tempNS_c;
CREATE TABLE trc_tempNS_c AS
SELECT DISTINCT ID, geom
FROM %s
WHERE ID NOT IN
(SELECT DISTINCT ID
FROM trc_tempNS_b);
ALTER TABLE trc_tempNS_c ADD FAR DOUBLE PRECISION;
UPDATE trc_tempNS_c SET FAR = 0;
CREATE INDEX IF NOT EXISTS tempNS_c_geom_gist ON trc_tempNS_c USING GIST (geom);
DROP TABLE IF EXISTS trc_tempNS;
CREATE TABLE trc_tempNS AS
SELECT ID, FAR, geom
FROM trc_tempNS_b
UNION
SELECT ID, FAR, geom
FROM trc_tempNS_c;
ALTER TABLE trc_tempNS ALTER COLUMN ID TYPE INTEGER;
CREATE INDEX IF NOT EXISTS tempNS_geom_gist ON trc_tempNS USING GIST (geom);
""" % (distance_lines, table_name_maille, table_name_maille)
if debug >= 1:
print(query_NS)
executeQuery(connection, query_NS)
#####################################################################################################################################################
### Calculs pour l'obtention des sous-indicateurs liés à l'intersect des bâtiments avec chaque ligne W-E, dans chaque maille : frontal area ratio ###
#####################################################################################################################################################
print(bold + cyan + "Calcul des indicateurs secondaires liés aux bâtiments intersectant chaque ligne W-E, intersectant chaque maille :" + endC)
timeLine(path_time_log, " Calcul des indicateurs secondaires liés aux bâtiments intersectant chaque ligne W-E, intersectant chaque maille : ")
query_WE = """
DROP TABLE IF EXISTS trc_tempWE_a;
CREATE TABLE trc_tempWE_a AS
SELECT we.ID as ID, we.line as line, max(d.hauteur) as max_haut, (%s * max(d.hauteur)) as FA, we.geom as geom
FROM trc_decoup as d, trc_decoupWE as we
WHERE ST_Intersects(d.geom, we.geom)
GROUP BY we.ID, we.line, we.geom;
CREATE INDEX IF NOT EXISTS tempWE_a_geom_gist ON trc_tempWE_a USING GIST (geom);
DROP TABLE IF EXISTS trc_tempWE_b;
CREATE TABLE trc_tempWE_b AS
SELECT m.ID as ID, (sum(a.FA) / st_area(m.geom)) as FAR, m.geom as geom
FROM trc_tempWE_a as a, %s as m
WHERE ST_Intersects(a.geom, m.geom)
GROUP BY m.ID, m.geom;
CREATE INDEX IF NOT EXISTS tempWE_b_geom_gist ON trc_tempWE_b USING GIST (geom);
DROP TABLE IF EXISTS trc_tempWE_c;
CREATE TABLE trc_tempWE_c AS
SELECT DISTINCT ID, geom
FROM %s
WHERE ID NOT IN
(SELECT DISTINCT ID
FROM trc_tempWE_b);
ALTER TABLE trc_tempWE_c ADD FAR DOUBLE PRECISION;
UPDATE trc_tempWE_c SET FAR = 0;
CREATE INDEX IF NOT EXISTS tempWE_c_geom_gist ON trc_tempWE_c USING GIST (geom);
DROP TABLE IF EXISTS trc_tempWE;
CREATE TABLE trc_tempWE AS
SELECT ID, FAR, geom
FROM trc_tempWE_b
UNION
SELECT ID, FAR, geom
FROM trc_tempWE_c;
ALTER TABLE trc_tempWE ALTER COLUMN ID TYPE INTEGER;
CREATE INDEX IF NOT EXISTS tempWE_geom_gist ON trc_tempWE USING GIST (geom);
""" % (distance_lines, table_name_maille, table_name_maille)
if debug >= 1:
print(query_WE)
executeQuery(connection, query_WE)
########################################################################################################################
### Calculs finaux pour l'obtention de l'indicateur de classe de rugosité : longueur de rugosité, classe de rugosité ###
########################################################################################################################
print(bold + cyan + "Calculs finaux de l'indicateur de classe de rugosité :" + endC)
timeLine(path_time_log, " Calculs finaux de l'indicateur de classe de rugosité : ")
query_rugo = """
DROP TABLE IF EXISTS trc_rugo;
CREATE TABLE trc_rugo AS
SELECT t.ID as ID, t.h as h, t.PAR as PAR, t.zd as zd, ns.FAR as FAR_NS, we.FAR as FAR_WE, t.geom as geom
FROM trc_temp as t, trc_tempNS as ns, trc_tempWE as we
WHERE t.ID = ns.ID and ns.ID = WE.ID;
ALTER TABLE trc_rugo ALTER COLUMN ID TYPE INTEGER;
ALTER TABLE trc_rugo ADD COLUMN z0_NS DOUBLE PRECISION;
UPDATE trc_rugo SET z0_NS = ((h - zd) * exp(-sqrt(0.4 / FAR_NS))) WHERE FAR_NS > 0;
UPDATE trc_rugo SET z0_NS = 0 WHERE FAR_NS = 0;
ALTER TABLE trc_rugo ADD COLUMN z0_WE DOUBLE PRECISION;
UPDATE trc_rugo SET z0_WE = ((h - zd) * exp(-sqrt(0.4 / FAR_WE))) WHERE FAR_WE > 0;
UPDATE trc_rugo SET z0_WE = 0 WHERE FAR_WE = 0;
ALTER TABLE trc_rugo ADD COLUMN mean_z0 DOUBLE PRECISION;
UPDATE trc_rugo SET mean_z0 = (z0_NS + z0_WE) / 2;
ALTER TABLE trc_rugo ADD COLUMN cl_rugo integer;
UPDATE trc_rugo SET cl_rugo = 1 WHERE mean_z0 < 0.0025;
UPDATE trc_rugo SET cl_rugo = 2 WHERE mean_z0 >= 0.0025 and mean_z0 < 0.0175;
UPDATE trc_rugo SET cl_rugo = 3 WHERE mean_z0 >= 0.0175 and mean_z0 < 0.065;
UPDATE trc_rugo SET cl_rugo = 4 WHERE mean_z0 >= 0.065 and mean_z0 < 0.175;
UPDATE trc_rugo SET cl_rugo = 5 WHERE mean_z0 >= 0.175 and mean_z0 < 0.375;
UPDATE trc_rugo SET cl_rugo = 6 WHERE mean_z0 >= 0.375 and mean_z0 < 0.75;
UPDATE trc_rugo SET cl_rugo = 7 WHERE mean_z0 >= 0.75 and mean_z0 < 1.5;
UPDATE trc_rugo SET cl_rugo = 8 WHERE mean_z0 >= 1.5;
"""
if debug >= 1:
print(query_rugo)
executeQuery(connection, query_rugo)
closeConnection(connection)
exportVectorByOgr2ogr(database_postgis, grid_output, 'trc_rugo', user_name=user_postgis, password=password_postgis, ip_host=server_postgis, num_port=str(port_number), schema_name=schema_postgis, format_type=format_vector)
##########################################
### Nettoyage des fichiers temporaires ###
##########################################
if not save_results_intermediate:
# ~ dropDatabase(database_postgis, user_name=user_postgis, password=password_postgis, ip_host=server_postgis, num_port=port_number, schema_name=schema_postgis) # Conflits avec autres indicateurs (Aspect Ratio / Height of Roughness Elements)
pass
else:
print(bold + magenta + "Le calcul de Terrain Roughness Class a déjà eu lieu." + endC)
print(bold + yellow + "Fin du calcul de l'indicateur Terrain Roughness Class." + endC + "\n")
timeLine(path_time_log, "Fin du calcul de l'indicateur Terrain Roughness Class : ")
return