Adding new fields with conditions and calculating the sum of lengths and numbers using PyQGIS

Question

I am developing a new plugin using Python in QGIS. I want to get an output with the result of a specific attribute table from another layer already existing in my project => layer "SUPPORT".

I want to return the sum of length(Longueur (m)) and count numbers (Nb tronçons) with the conditions:

if ('PROPRIETAI'='FT' and 'TYPE_STRUC'='TRANCHEE') => FT_SOUT
if ('PROPRIETAI'='FT' and 'TYPE_STRUC'='AERIEN') => FT_AERIEN
if ('PROPRIETAI'='FREE MOBILE' and 'TYPE_STRUC'='TRANCHEE') => FT_SOUT
if ('PROPRIETAI'='FREE MOBILE' and 'TYPE_STRUC'='AERIEN') => FT_AERIEN
if ('PROPRIETAI'='PRIVE') => PRIVE

I tried but did not get the values:

My code :

coucheSUPPORT= QgsProject.instance().mapLayersByName('SUPPORT')[0]
tableSUPPORT = QgsVectorLayer("None", "table_SUPPORT", "memory")
tableSUPPORT.dataProvider().addAttributes(
 [QgsField("Propriétaire", QVariant.String),QgsField("Longueur (m)", QVariant.Int),
 QgsField("Nb tronçons", QVariant.Int),
 ])
tableSUPPORT.updateFields()
 # Propriétaire 
t = ["FT_SOUT", "FT_AERIEN", "CREATION GC FREE RESEAU", "CREATION AERIEN FREE RESEAU","PRIVE"]
d = {t[0]: {"Longueur (m)": 0, "Nb tronçons": 0},
 t[1]: {"Longueur (m)": 0, "Nb tronçons": 0},
 t[2]: {"Longueur (m)": 0, "Nb tronçons": 0},
 t[3]: {"Longueur (m)": 0, "Nb tronçons": 0},
 t[4]: {"Longueur (m)": 0, "Nb tronçons": 0}}
for sup in coucheSUPPORT.getFeatures():
 stru = sup["TYPE_STRUC"]
 prop = sup["PROPRIETAI"]
 lgr = sup["LGR_REEL"]
 if stru =="TRANCHEE":
 if prop == "FT":
 d[t[0]]
 
 elif stru =="AERIEN":
 if prop=='FT':
 d[t[1]] 
 
 elif stru =="TRANCHEE":
 if prop == "FREE MOBILE":
 d[t[2]] 
 
 elif stru =="AERIEN":
 if prop=='FREE MOBILE':
 d[t[3]] 
 
 elif prop=="PRIVE":
 d[t[4]] 
 else: 
 print(f"Le support_technique {sup['LIBELLE']} possède un type de strcture ...") 
 else:
 print(f"Le support_technique {sup['LIBELLE']} possède un propriétaire ...")
for i in [0, 1, 2, 3,4]:
 feature = QgsFeature(tableSUPPORT.fields())
 feature.setAttributes([ t[i], d[t[i]]["FT_SOUT"], d[t[i]]["FT_AERIEN"], d[t[i]]["CREATION GC FREE RESEAU"],d[t[i]]["CREATION AERIEN FREE RESEAU"],d[t[i]]["PRIVE"] ])
 tableSUPPORT.dataProvider().addFeatures([feature])
QgsProject.instance().addMapLayer(tableSUPPORT)

this is the table result of this code :

Propriétaire	Longueur (m)	Nb tronçons
FT_SOUT	0	0
FT_AERIEN	0	0
CREATION GC FREE RESEAU	0	0
CREATION AERIEN FREE RESEAU	0	0
PRIVE	0	0

and this is the support attribute table that I want to extract the length sum and count number based on conditions:

LIBELLE	EMPRISE	TYPE_STRUC	PROPRIETAI	GESTIONNAI	LGR_REEL
GEC_RLS11_002_XXXX	RLS11_001	TRANCHEE	FT	FT	290,077129025183000
GEC_RLS11_003_XXXX	RLS11_001	TRANCHEE	FT	FT	94,3583481638687
GEC_RLS11_004_XXXX	RLS11_001	AERIEN	FT	FT	70,904855967867
GEC_RLS11_005_XXXX	RLS11_001	AERIEN	FT	FT	114,754872574739
GEC_RLS11_006_XXXX	RLS11_001	TRANCHEE	FREE MOBILE	FREE MOBILE	574,814175432379
GEC_RLS11_007_XXXX	RLS11_001	TRANCHEE	FREE MOBILE	FREE MOBILE	10,7499609293078
GEC_RLS11_008_XXXX	RLS11_001	AERIEN	FREE MOBILE	FREE MOBILE	87,8308661168212
GEC_RLS11_009_XXXX	RLS11_001	AERIEN	FREE MOBILE	FREE MOBILE	55,8906724941399
GEC_RLS11_010_XXXX	RLS11_001	TRANCHEE	PRIVE	PRIVE	17,1590102894468
GEC_RLS11_011_XXXX	RLS11_001	AERIEN	PRIVE	PRIVE	297,077129025183000

What is missing in my code?

• Kasper: I provided an answer below, with 3 lines of code instead of your whole script. If you want to stick to your current approach: someone already told you that there is no place in your code where you add or count anything so there's no way it is going to return anything else than an error, or zeros.Your if statements do absolutely nothing: if prop == "FT": d[t[0]]. What do you expect to do with d[t[0]] ??

Answer 1

If you are familiar with SQL, @Kasper's answer is a shorter one. Otherwise, you can use the following script:

coucheSUPPORT = QgsProject.instance().mapLayersByName('SUPPORT')[0]
tableSUPPORT = QgsVectorLayer("None", "table_SUPPORT", "memory")
tableSUPPORT.dataProvider().addAttributes(
 [QgsField("Propriétaire", QVariant.String),
 QgsField("Longueur (m)", QVariant.Int),
 QgsField("Nb tronçons", QVariant.Int)])
tableSUPPORT.updateFields()
# Propriétaire
props = ["FT_SOUT", "FT_AERIEN", "CREATION GC FREE RESEAU",
 "CREATION AERIEN FREE RESEAU", "PRIVE"]
data = {p: {"Longueur (m)": 0, "Nb tronçons": 0} for p in props}
def process(prop, lgr):
 data[prop]["Longueur (m)"] += lgr
 data[prop]["Nb tronçons"] += 1
for sup in coucheSUPPORT.getFeatures():
 stru = sup["TYPE_STRUC"]
 prop = sup["PROPRIETAI"]
 lgr = sup["LGR_REEL"]
 if stru == "TRANCHEE":
 
 if prop == "FT":
 process("FT_SOUT", lgr)
 elif prop == "FREE MOBILE":
 process("CREATION GC FREE RESEAU", lgr)
 else:
 print(f"{sup['LIBELLE']} possède un type de strcture ...")
 elif stru == "AERIEN":
 
 if prop == "FT":
 process("FT_AERIEN", lgr)
 elif prop == 'FREE MOBILE':
 process("CREATION AERIEN FREE RESEAU", lgr)
 elif prop == "PRIVE":
 process("PRIVE", lgr)
 else:
 print(f"{sup['LIBELLE']} possède un type de strcture ...")
 
 else:
 print(f"{sup['LIBELLE']} possède un propriétaire ...")
for prop in props:
 feature = QgsFeature(tableSUPPORT.fields())
 feature.setAttributes([prop, data[prop]["Longueur (m)"], data[prop]["Nb tronçons"]])
 tableSUPPORT.dataProvider().addFeatures([feature])
QgsProject.instance().addMapLayer(tableSUPPORT)

Answer 2

What you can do is create a Virtual layer through Layer > Add Layer > Add/Edit Virtual Layer, with an SQL Query that will create the desired output, in the desired new Layer.

1. This content is always kept up-to-date whenever the Support Layer content is updated (meaning you don't need to re-create this layer each time, nor do you need to run a script to do the case/aggregation every time, the SQL query does it for you)

2. You can create this Virtual Layer programmatically from your script

3. Any other process/Layer can use the always up-to-date data in this Virtual Layer

---

You have a Layer called Support, with the Attribute table you mention.

enter image description here

You want another Layer (called tableSupport) to list the count, sum(length) by specific categories (case based on attributes Proprietaire and Type_Struc)

enter image description here

The SQL query is:

select 
case
 when s.PROPRIETAI='FT' and s.TYPE_STRUC='TRANCHEE' then 'FT_SOUT' 
 when s.PROPRIETAI='FT' and s.TYPE_STRUC='AERIEN' then 'FT_AERIEN'
 when s.PROPRIETAI='FREE MOBILE' and s.TYPE_STRUC='TRANCHEE' then 'CREATION GC FREE RESEAU'
 when s.PROPRIETAI='FREE MOBILE' and s.TYPE_STRUC='AERIEN' then 'CREATION AERIEN FREE RESEAU'
 when s.PROPRIETAI='PRIVE' then 'PRIVE' end as Proprietaire,
 sum(s.LGR_REEL) as Longueur,
 count(*) as Nb_troncons
FROM Support s
Group By Proprietaire

Here's the consolidated table tableSUpport

enter image description here

So, once this Virtual Layer is created once in the Project, it will automatically keep the relevant aggregations.

If you want to create that layer automatically from your script, you will need to include this in your script:

from qgis.core import QgsVectorLayer, QgsProject
vlayer = QgsVectorLayer("?query=<query string>", "tableSupportFromScript", "virtual")
QgsProject.instance().addMapLayer(vlayer)

In order to get the exact value of <query string>, you can look at the Source information of the Virtual Layer you have manually created:

enter image description here

Running the 3 lines of code to create the Virtual Layer programmatically as tableSupportFromScript:

enter image description here

Here's the string for the above SQL query:

?query=select%20%0Acase%0A%20when%20s.PROPRIETAI%3D'FT'%20and%20s.TYPE_STRUC%3D'TRANCHEE'%20then%20'FT_SOUT'%20%0A%20when%20s.PROPRIETAI%3D'FT'%20and%20s.TYPE_STRUC%3D'AERIEN'%20then%20'FT_AERIEN'%0A%20when%20s.PROPRIETAI%3D'FREE%20MOBILE'%20and%20s.TYPE_STRUC%3D'TRANCHEE'%20then%20'CREATION%20GC%20FREE%20RESEAU'%0A%20when%20s.PROPRIETAI%3D'FREE%20MOBILE'%20and%20s.TYPE_STRUC%3D'AERIEN'%20then%20'CREATION%20AERIEN%20FREE%20RESEAU'%0A%20when%20s.PROPRIETAI%3D'PRIVE'%20then%20'PRIVE'%20end%20as%20Proprietaire,%0A%0A%20sum(s.LGR_REEL)%20as%20Longueur,%0A%20count(*)%20as%20Nn_troncons%0A%0A%0A%0Afrom%20Support%20s%20group%20by%20Proprietaire

In short, replace the whole code sample you provided by the follwing :

from qgis.core import QgsVectorLayer, QgsProject
vlayer = QgsVectorLayer("?query=select%20%0Acase%0A%20when%20s.PROPRIETAI%3D'FT'%20and%20s.TYPE_STRUC%3D'TRANCHEE'%20then%20'FT_SOUT'%20%0A%20when%20s.PROPRIETAI%3D'FT'%20and%20s.TYPE_STRUC%3D'AERIEN'%20then%20'FT_AERIEN'%0A%20when%20s.PROPRIETAI%3D'FREE%20MOBILE'%20and%20s.TYPE_STRUC%3D'TRANCHEE'%20then%20'CREATION%20GC%20FREE%20RESEAU'%0A%20when%20s.PROPRIETAI%3D'FREE%20MOBILE'%20and%20s.TYPE_STRUC%3D'AERIEN'%20then%20'CREATION%20AERIEN%20FREE%20RESEAU'%0A%20when%20s.PROPRIETAI%3D'PRIVE'%20then%20'PRIVE'%20end%20as%20Proprietaire,%0A%0A%20sum(s.LGR_REEL)%20as%20Longueur,%0A%20count(*)%20as%20Nn_troncons%0A%0A%0A%0Afrom%20Support%20s%20group%20by%20Proprietaire", "tableSupportFromScript", "virtual")
QgsProject.instance().addMapLayer(vlayer)

• Fatine MOBARIK: Kasper I appreciate your answer, it's very useful but I already tried to do it with sql and it didn't work as I hoped, because when the layer doesn't have for example: when I don't have a 'support' that has "PROPRIETAI"='PRIVE' the Prive row won't be added with values 0 0, and that's not what I am looking for.Thanks a lot.
• Kasper: With a "WITH empty_table as Select ... (Static table values with your categories and sum/count=0)" joined on the current query you should be able to create a table with lines even when there are no entries in Support. Something like this
• Fatine MOBARIK: Really! I didn't know that, I already generate these tables with sql and I didn't know that, and I create srcipt with python and I lost more than a week for that. Thanks it's great Thanks a lot for the information it's nice of you.
• user183925: Is group by missing in SQL query?
• Kasper: @user183925: it was in the code snippet, not in the query string. I updated the Answer, thanks