Cannot query fields during a join in QGIS using python to only include specific fields after the join

by Oski Turanoglu   Last Updated January 14, 2018 00:22 AM

I'm having trouble using a sub-set on the fields from a layer to only include those specific fields in the resultant attribute table after the conclusion of a join. The code actually works, however the result is completely wrong, with the attribute names not matching up with the corresponding feature values (please see images for further clarification). The code below is the block that performs the subset.

# Put the fields the user wants to include in the target layer's attribute
# table into a list and remove the ";"
census_demo = Other_Census_Data.split(';')

# Set properties for the join
targetField = 'CTUID'
inField = 'CTUID'
joinObject = QgsVectorJoinInfo()
joinObject.joinLayerId = censusLyr.id()
joinObject.joinFieldName = inField
joinObject.targetFieldName = targetField
joinObject.memoryCache = True
joinObject.setJoinFieldNamesSubset(census_demo)  # Include only fields from the list.
joinObject.prefix = ''
print(targetLyr.addJoin(joinObject))  # You should get True as response.
targetLyr.addJoin(joinObject)

The subset didn't not work as intended, so I also tried to simply delete the unnecessary attributes after the join, however that too does not work. The block deletes all the attributes from target layer correctly, however it does not delete any from the newly joined layer, despite the code not running any errors.

fields = []  # Fields to be removed from attribute table will be added here
fieldnames = {'CTUID', 'Primary', 'Secondary', 'PRI_POP', 'SEC_POP'}  # None joined layers field names are in set, yet are not deleted
for field in huff_model.fields():
    if field.name() not in fieldnames:
        fields.append(huff_model.fieldNameIndex(field.name()))
        # print(fields.append(huff_model.fieldNameIndex(field.name())))

huff_model.deleteAttributes(fields) 

FULL CODE

Processing Script Parameters and Imports

##Huff_Model_Layer=vector
##Mall=field Huff_Model_Layer
##Census_Layer=vector
##Other_Census_Data=multiple field Census_Layer  # User decides which fields to include in the final attribute table
##Mall_Layer_Name= string test
##PDF_file=output file pdf

from qgis.core import *
from qgis.gui import *
from qgis.utils import *
from PyQt4.QtCore import *
from PyQt4.QtGui import *
import os

Creating the layer

# Load the huff model layer
ori_huff_model = processing.getObject(Huff_Model_Layer)
# Load the census layer
census_layer = processing.getObject(Census_Layer)

# If script was run previous, remove the previously created layer to keep 
# layer panel with only one newly created layer at a time
try:
    last_layer = QgsMapLayerRegistry.instance().mapLayersByName(Mall_Layer_Name)[0]
    QgsMapLayerRegistry.instance().removeMapLayers([last_layer])
    del last_layer
except IndexError:
    child1 = root.children()[1]
    if child1.name() == census_layer.name():
        ids = root.findLayerIds()
        last_layer = root.findLayer(ids[0])
        print last_layer.name()
        root.removeChildNode(last_layer)

# Make a copy of the huff model layer in memory to work with so that all 
# changes made to copy only keeping original data intact
huff_model = QgsVectorLayer("Polygon?crs=epsg:4326", Mall_Layer_Name, "memory")
huff_model_data = huff_model.dataProvider()

huff_model.startEditing()

attr = ori_huff_model.dataProvider().fields().toList()
huff_model_data.addAttributes(attr)
huff_model.updateFields()

feat = QgsFeature()
for elem in ori_huff_model.getFeatures():
    feat.setGeometry(elem.geometry())
    feat.setAttributes(elem.attributes())
    huff_model.addFeatures([feat])
    huff_model.updateExtents()

# Choose the mall (field in table) to find the primary and secondary market 
# areas 
mall = ori_huff_model.fieldNameIndex(Mall)
for field in huff_model.fields():
    field_id = huff_model.fieldNameIndex(field.name())
    if mall == field.name():
        mall = huff_model.fieldNameIndex(field.name())
        break

col_num = int(mall)

# Create two new fields that will only show the primary and secondary market 
# areas based on what is chosen as primary and secondary market 
# probabilities
primary = QgsField('Primary', QVariant.Double)
huff_model.addAttribute(primary)
index_pri = huff_model.fieldNameIndex('Primary')

secondary = QgsField('Secondary', QVariant.Double)
huff_model.addAttribute(secondary)
index_sec = huff_model.fieldNameIndex('Secondary')

# Will hold population data based on primary and secondary markets 
# multiplied by the population for each census tract.
pri_pop = QgsField('PRI_POP', QVariant.Int)
huff_model.addAttribute(pri_pop)
idx_pri_pop = huff_model.fieldNameIndex('PRI_POP')

sec_pop = QgsField('SEC_POP', QVariant.Int)
huff_model.addAttribute(sec_pop)
idx_sec_pop = huff_model.fieldNameIndex('SEC_POP')

# Find the relevant features for the chosen mall
for feature in huff_model.getFeatures():
    probability = feature.attributes()
    ctuid = feature["CTUID"]
    if not probability[col_num]:
        print ("This mall does not exist")
    else:
        # Primary market values will go into the "Primary" field
        if probability[col_num] >= .6:
            huff_model.changeAttributeValue(feature.id(), index_pri, probability[col_num])
        # Secondary market values will go into the "Secondary" field
        if .4 <= probability[col_num] < .6:
            huff_model.changeAttributeValue(feature.id(), index_sec, probability[col_num])

# Delete all other features (rows) that do not have a probability in the  
# primary and the secondary fields
expr = QgsExpression("\"Primary\" is NULL and \"Secondary\" is NULL")
for f in huff_model.getFeatures(QgsFeatureRequest(expr)):
    huff_model.deleteFeature(f.id())

huff_model.updateExtents()
huff_model.commitChanges()

Setting up the Join

# Add newly created layer to map to prepare for the join
reg = QgsMapLayerRegistry.instance()
reg.addMapLayer(huff_model)

# Set the appropriate layers for the join. Mall_Layer_Name is the name for 
# the huff_model layer 
targetLyr = QgsMapLayerRegistry.instance().mapLayersByName(Mall_Layer_Name)[0]
censusLyr = QgsMapLayerRegistry.instance().mapLayersByName(census_layer.name())[0]

# Moves the census layer to the second position in the layer panel as a 
# conditional to remove the newly created layer on a subsequent run of the 
# script.
mycensusLyr = root.findLayer(censusLyr.id())
censusClone = mycensusLyr.clone()
parent = mycensusLyr.parent()
parent.insertChildNode(1, censusClone)
parent.removeChildNode(mycensusLyr)

# Put the fields the user wants to include in the target layer's attribute
# table into a list and remove the ";"
census_demo = Other_Census_Data.split(';')

# Set properties for the join
targetField = 'CTUID'
inField = 'CTUID'
joinObject = QgsVectorJoinInfo()
joinObject.joinLayerId = censusLyr.id()
joinObject.joinFieldName = inField
joinObject.targetFieldName = targetField
joinObject.memoryCache = True
joinObject.setJoinFieldNamesSubset(census_demo)  # Include only fields from the list.
joinObject.prefix = ''
print(targetLyr.addJoin(joinObject))  # You should get True as response.
targetLyr.addJoin(joinObject)

huff_model.startEditing()

# Expression used to populate the "PRI_POP" field.
expression = QgsExpression("to_int(\"Primary\" * \"POP06\")")
expression.prepare(huff_model.pendingFields())

for feature in huff_model.getFeatures():
    value = expression.evaluate(feature)
    feature[idx_pri_pop] = value
    huff_model.updateFeature(feature)

# Expression used to populate the "PRI_POP" field.
expression2 = QgsExpression("to_int(\"Secondary\" * \"POP06\")")
expression2.prepare(huff_model.pendingFields())

for feature in huff_model.getFeatures():
    value = expression2.evaluate(feature)
    feature[idx_sec_pop] = value
    huff_model.updateFeature(feature)

# Delete all other columns except for the CTUID, Primary, Secondary columns. 
# For some reason, this has to be here AFTER the join, otherwise the newly  
# calculated features don't show up in "PRI_POP" and "SEC_POP.
# It also doesn't delete fields NOT included in the set in the newly joined 
# table. It does however delete the fields in target layer (huff_model)  
# before the join that are not in the set.
fields = []

fieldnames = {'CTUID', 'Primary', 'Secondary', 'PRI_POP', 'SEC_POP'}
for field in huff_model.fields():
    if field.name() not in fieldnames:
        fields.append(huff_model.fieldNameIndex(field.name()))
        # print(fields.append(huff_model.fieldNameIndex(field.name())))

huff_model.deleteAttributes(fields)
huff_model.commitChanges()

Creating the PDF output

# Make sure that the newly created and joined layer is the active layer.
huff_model = iface.activeLayer()

# Create the attribute using the map composer in QGIS and show the output in 
# PDF format.
mapRenderer = iface.mapCanvas().mapRenderer()
c = QgsComposition(mapRenderer)
c.setPlotStyle(QgsComposition.Print)
x, y = 0, 0
w, h = c.paperWidth(), c.paperHeight()
composerMap = QgsComposerMap(c, x, y, w, h)

table = QgsComposerAttributeTable(c)
table.setComposerMap(composerMap)
table.setScale(.85)
table.setVectorLayer(huff_model)
table.setMaximumNumberOfFeatures(huff_model.featureCount())
c.addItem(table)

# Checks for the existence of a file with the same name and removes it.
if os.path.isfile(PDF_file):
    os.remove(PDF_file)

printer = QPrinter()
printer.setOutputFormat(QPrinter.PdfFormat)
printer.setOutputFileName(PDF_file)
printer.setPaperSize(QSizeF(c.paperWidth(), c.paperHeight()), QPrinter.Millimeter)
printer.setFullPage(True)

pdfPainter = QPainter(printer)
paperRectMM = printer.pageRect(QPrinter.Millimeter)
paperRectPixel = printer.pageRect(QPrinter.DevicePixel)
c.render(pdfPainter, paperRectPixel, paperRectMM)
pdfPainter.end()

Below is an image of the joined attribute table without using a subset and the original census layer's attribute table for comparison. The field names match up with the correct feature values.

Matching_Fields_Before_Sub_Set

enter image description here

Below is an image of the joined attribute table with a subset and the original census layer's attribute table for comparison. In this particular case, I included all the fields in the subset (none were excluded), however the feature values did not match up correctly with fields they were suppose to be under even when all the fields were included. The only time this works is if two or less fields are included in the subset. Any more, and a similar result occurs, with the values and fields mis-matching.

Mis-Matching_Fields_After_Sub_Set

enter image description here



Related Questions




Definition query on GDB annotation file- ArcGIS 10.3.1

Updated November 20, 2017 12:22 PM