[GIS] SQL query in Python console to sum column based on values of other two columns

pyqgisqgis-2sqlsummarizingvirtual-layer

We have 100's of shapefiles for which we have to generate excel reports based on a few (combination of) parameters in the attribute table. I am using SQL query to achieve this and when I run the query in the "Create Virtual layer" process, it works fine and the output has been generated, which will be saved as an excel file. Instead of this process, I am trying to run this through the Python console. Though the code didn't show any error, the output is not being printed. Do I have to change the SQL query format in the code?

Image 1: For the shapefile final_bdry, Total_Area has to be calculated based on two other columns – MS_Area, Ground_Con (similar to Pivot_Table in excel, the sum of a column based on a unique count of the other two columns). No issues with the query and I am getting the desired output in the virtual_layer.
.

Image 2: But, if I run the same query within the Python console, the result is "None"

Ref: Used this as ref SQL queries in shapefiles using python/gdal return none result

Best Answer

You could try directly inserting your SQL query when creating a Virtual layer:

query = """ SELECT "MS_Area", "Ground_Con", sum("Area_Ha") as Total /*:ini*/ from final_bdry group by "MS_Area", "Ground_Con" """

vlayer = QgsVectorLayer( "?query={}".format(query), "vlayer", "virtual" )
QgsMapLayerRegistry.instance().addMapLayer(vlayer)

Example:

UPDATE for QGIS 3.0

QgsMapLayerRegistry object was changed in QGIS 3.0 for QgsProject. So, last line in 3.0 should be:

QgsProject.instance().addMapLayer(vlayer)

ArcGIS:

Calculate a new string field (lets call it "END_XY_COMBO") that will be a combined value of your END_X and END_Y values for each row. Assuming your X/Y values are in number format, you'll need to convert to string, so calculate that new field like:

END_XY_COMBO = str(!END_X!) + "-" + str(!END_Y!).

This will combine the two X/Y values separated by a dash.
Then export this table as a second copy of itself.
Then join the second copy to the first copy on the "END_XY_COMBO" field, then run a Select By Attributes, and select the records from table1 where: TABLE1.ObjectID <> "TABLE2.ObjectID".

This should select all records where the END_X and END_Y values are all the same, but the Object IDs are different.

Microsoft Access:

Bring your table into Access, then make a second copy of it.
In Table 1 create a new text flag field called "Match_Flag".
Open the query builder in design view and add Table 1 and Table 2.
Create a join between Table 1 and Table 2 on the "END_X" field, and create a second join between them on the "END_Y" field (using one-to-one join, meaning values have to match in both fields in both tables).
Set the query type to Update Query.
Add the "Match_Flag" field from Table 1 and set it to update to "Y".
Add the ObjectID field from Table 1, leave the "Update To:" part blank, but set the criteria to be: <> [Table2]![ObjectID]. - This will flag all records in table 1 where the END_X and END_Y values are the same, but the ObjectID values are different.
Then you can export Table 1 from Access as a .dbf (needs to have a table name shorter than 8 characters and all field names 10 characters or less - you can make the table name longer after the export b/c ArcMap doesn't care, but Access does), then join the .dbf back to your original table in ArcMap on ObjectID, and select the records where Match_Flag = "Y".

PyQGIS – Summing Values in One Field

In programming, there are several ways of solving a problem.

That's also the case for summing values of a field in QGIS. But there is one that is the recommended way, truly leveraging the PyQGIS API.

Aggregating field values using PyQGIS

To get the sum of a field you can use:

total_sum = layer.aggregate(QgsAggregateCalculator.Sum, "my_field_name")

Simpler and faster.

Note: Since the result is a tuple (result_value, boolean_result), you just call total_sum[0] and you are done.

Read the docs to know the list of aggregate types you can use.

You can even pass filter expressions to the aggregates. Really powerful.

Note: this is available since QGIS 2.16.

Best Answer

Related Solutions

[GIS] Selecting features in an ArcGIS attribute table based on values of other features in the same table using SQL

ArcGIS:

Microsoft Access:

PyQGIS – Summing Values in One Field

Aggregating field values using PyQGIS

Related Question