4

Context: I'm writing a dynamic import tool for QGIS (2.2 to 2.5) with python. Datatype: Binary Datasets with environmental simulations from HPC which might contain millions of features and terabytes of attributes.

Current status: I wrote some python that extracts the features and attributes at a certain time step.

  • Loading Data for 100000 features takes < 0.5s via python.
  • Displaying them takes 30s.
  • Data structure: Double precision arrays

Now my question in short: Is it possible to speed up the change of attributes? Can I change 10 attributes for 100 000 features in one step? Rather then doing 1000000 loops, one per attribute?

I know QGIS would be fast enough, because: If I save my imported data as shapefile and reload it, the shapefile reader takes only ~2.5sec for 1 000 0000 attributes.

Here is my current code:

#---------------------------------------------------------------
# Create features
#
#
vl = QgsVectorLayer("polygon", myFile + " | Triangles", "memory")      # New memory layer
pr = vl.dataProvider()                                                 # Dataprovider

# Enter editing mode
vl.startEditing()

#------
# Attribut col
pr.addAttributes( [ QgsField("ID",  QVariant.Int) ] )   

#------
# Paint Triangles
i = 0 
for TRI in mesh.ELE:
    i += 1
    feat = QgsFeature()
    ep1 = TRI.P1-1
    ep2 = TRI.P2-1
    ep3 = TRI.P3-1
   feat.setGeometry(QgsGeometry.fromPolygon([[ \ QgsPoint(mesh.HEAD.X_origin+mesh.x[ep1],mesh.HEAD.Y_origin+mesh.y[ep1]), \
    QgsPoint(mesh.HEAD.X_origin+mesh.x[ep2],mesh.HEAD.Y_origin+mesh.y[ep2]), \
    QgsPoint(mesh.HEAD.X_origin+mesh.x[ep3],mesh.HEAD.Y_origin+mesh.y[ep3]), \
    QgsPoint(mesh.HEAD.X_origin+mesh.x[ep1],mesh.HEAD.Y_origin+mesh.y[ep1])]]))
    attrdict = [i,]
    feat.setAttributes(attrdict)
    (res, outFeats) = pr.addFeatures( [ feat ] )

vl.commitChanges()

#---------------------------------------------------------------    
# Append attributes
#
vl.startEditing()

col_id = 0
for n in self.dlg.selectionlist:

    col_id += 1

    # Load Values (1 String, 1 Double, Double Array with mesh.head.npoin values)
    aName,aTime,aVALUES = ReadSelafin_OneParaSet(n[0],n[1],mesh)        

    # New Attribute Col
    pr.addAttributes( [QgsField(aName, QVariant.Double) ] )

           #------
           # Add Values to Triangle Features in Values            
           i = 1 
           for TRI in mesh.ELE:

               ep1 = TRI.P1-1
               ep2 = TRI.P2-1
               ep3 = TRI.P3-1
               eleval = (aVALUES[ep1]+aVALUES[ep2]+aVALUES[ep3])/3

               attrs = { i : {col_id : eleval}}
               pr.changeAttributeValues( attrs )
               i += 1       

#---------------------------------------------------------------
# Commit changes
vl.commitChanges()
vl.updateExtents() 
QgsMapLayerRegistry.instance().addMapLayer(vl)
iface.mapCanvas().refresh()
1
  • I just want to ask this question again! After more than 3 years the problem is still unsolved!
    – UHM
    Jan 10, 2018 at 9:20

4 Answers 4

6

I had a similar problem where I was writing attributes into thousands of features and the writing would get progressively slower and slower as the loop iterated.

I found that if I periodically saved / closed off the edits and then restarted editing (after each row of the array in my case) this made things MUCH faster:

    if currentrow > lastrow:
      lastrow = currentrow
      boxlayer.updateExtents()
      boxlayer.commitChanges()
      boxlayer.startEditing()   

Using this method I was able to change a process that started at 0.02 second execution and increased to 2 second execution after 27k features to maintain a loop time of under 0.04 seconds average.

5

OK - I totally forgot about this question that I had previously answered and I currently have a new answer which is at least 10x as fast.

The answer is this: Do not modify the attributes directly.

Collect all of the data you want for all of the attributes into a pandas dataframe. generate a list of features:

for row in dataframe:
  feat = QgsFeature()
  feat.setAttributes(row.values.tolist())
  feat.setGeometry(featgeom)
  feats.append(feat) 

Then delete all the features from your layer and add your new features in:

layer.deleteFeatures(boxlayer.allFeatureIds())
layer_provider.addFeatures(feats)

This will solve your problem (and anyone who comes looking for this post in the future!)

2

THe short answer is: I can;t see a way. However, changing attributes on a feature is time consuming at times (I have have a hard time figuring out why it happens). Thus, this should speed up your code a bit:

pr.addAttributes( [QgsField(aName, QVariant.Double) ] )
attrs={}
       #------
       # Add Values to Triangle Features in Values            
       i = 1 
       for TRI in mesh.ELE:

           ep1 = TRI.P1-1
           ep2 = TRI.P2-1
           ep3 = TRI.P3-1
           eleval = (aVALUES[ep1]+aVALUES[ep2]+aVALUES[ep3])/3

           attrs[i]= {col_id : eleval}

           i += 1
pr.changeAttributeValues( attrs )   
3
  • 1
    It's a real shame that there seems to be no direct way to replicate the mechanism of the 'calculate' tool in the attribute gui from within a python script. The calculation tool appears instant but the very same calculation carried out a 'feature iterator loop' in python is painfully slow.
    – Mr Purple
    Nov 25, 2014 at 1:42
  • Thanks for the idea, I tried it, but with limited speed up. Isn't there a way for a direct memory access? I load the same data with python in Paraview which takes 10sec for 600 000 triangles. QGIS takes >1h ... And, not only changing attributes, but also shift painting triangles or shifting geometry.
    – UHM
    Nov 25, 2014 at 7:16
  • I am not aware of that possibility, and it might be the case that you would have to do it through QGIS' C API. Another issue to work with is the notoriously slow python loops. Maybe playing with different iterators might help your cause. Nov 25, 2014 at 22:46
2

I think this question is answered by A.A. Changing local saved files is slow with changeAttributeValues but on a layer in memory it's faster. I compared different ways of changing attributes of a local layer.

Methods

  • changeAttributeValues
  • change attribute values of Shapefile with dbf package
  • change attribute values by creating new layer with parts of old layer and do join by attributes and save as SQLite
  • change attribute values by sql
  • change attribute values by creating new layer with geometry of old layer, do join by attribute with creating new SQLite Datasource (old and new values) and save as SQLite

Conclusion

The fastest way is change values of an existing local layer is to create a new layer with parts of old layer and do join by attributes and save it. But in sql there might be options to do it faster (I red about 50k changes/sec).

Edit: The fastest way can be improved if the data table is created with sql. If the data creation is not included in timings the table creation, save, join and save takes 10.9s for Python and 2.4s for sql at 10M Values here.

Timings

  • create layer with 20.0k features and 20 attributes 0.7 s
  • changeAttributeValues on layer with 20.0k features and 20 attributes 12 s
  • save changed layer as ESRI Shapefile 1.2 s
  • change attribute values of Shapefile with dbf package 11.5 s
  • change attribute values by creating new layer with parts of old layer and do join by attribute and save as SQLite 3.3 s
  • change attribute values by sql 33.4 s
  • change attribute values by creating new layer with geometry of old layer, do join by attribute with creating new SQLite Datasource (old and new values) and save as SQLite 2.7 s

Code

import os
from time import perf_counter as pc
import dbf
import sqlite3

pathtowrite=os.getenv('USERPROFILE') + "\\downloads\\"
xvals=100

# clean temp for new run
lyrs=["lyr","lyr2","lyr3","lyr22","lyr222","lyr33","lyr333","lyr4","lyr444"]
files=["new.sqlite","joined.sqlite","joined_sql.sqlite","changeattributevalues_try_dbf.cpg","changeattributevalues_try_dbf.dbf","changeattributevalues_try_dbf.prj","changeattributevalues_try_dbf.shp","changeattributevalues_try_dbf.shx","temp_data_layer.sqlite"]
for lyr0 in lyrs:
    try:
        exec("QgsProject.instance().removeMapLayer("+lyr0+")")
    except:
        pass
for file in files:
    try:
        os.remove(pathtowrite + file)
    except:
        pass

if os.path.exists(pathtowrite+"temp_data_layer.sqlite"):
    print("temp is not cleaned because of python processing stream - run again")
else:
    pc1=pc()

    # create a new memory layer
    lyr = QgsVectorLayer("Polygon?crs=EPSG:25832", "new_memory_layer", "memory")
    lyr_p=lyr.dataProvider()

    ifield=[]
    for f in range(20):
        ifield.append(QgsField("ID" + str(f),  QVariant.Int))

    lyr_p.addAttributes( ifield )
    lyr.updateFields()

    testdata=[f for f in range(xvals)]

    ifeat=[]
    for f in range(len(testdata)):
        ff = QgsFeature()
        ff.setFields(lyr_p.fields())
        for g in range(20):
            ff[g] = testdata[f]
        ifeat.append(ff)
    lyr_p.addFeatures(ifeat)

    options = QgsVectorFileWriter.SaveVectorOptions()
    options.actioanOnExistingFile = QgsVectorFileWriter.CreateOrOverwriteLayer
    options.EditionCapability = QgsVectorFileWriter.CanAddNewLayer
    options.driverName="SQLite"
    options.layerName = lyr.name()
    writer = QgsVectorFileWriter.writeAsVectorFormat(lyr, pathtowrite + "new", options)

    lyr=QgsVectorLayer(pathtowrite + "new.sqlite","new","ogr")
    lyr.dataProvider().setEncoding(u'UTF-8')
    QgsProject.instance().addMapLayer(lyr)
    lyr_p=lyr.dataProvider()


    pc2=pc()




    # change attributes of existing local layer
    testdata=[f for f in range(xvals)]

    ifeat=[]
    lyr.startEditing()
    k=0
    for f in range(len(testdata)):
        iifeat=""
        for g in range(20):
            iifeat+=str(g+1) + ":" + str(testdata[f]) +","
        iifeat=iifeat[:-1]
        exec("attrs = { "+str(f+1)+" : {"+iifeat+"}}")
        lyr_p.changeAttributeValues( attrs )
    #    k+=1
    #    if k>500:
    #        break
    lyr.commitChanges()


    pc3=pc()

    #save to shapefile
    options = QgsVectorFileWriter.SaveVectorOptions()
    options.actioanOnExistingFile = QgsVectorFileWriter.CreateOrOverwriteLayer
    options.EditionCapability = QgsVectorFileWriter.CanAddNewLayer
    options.driverName="ESRI Shapefile"
    options.layerName = "changeattributevalues_try_dbf"
    writer = QgsVectorFileWriter.writeAsVectorFormat(lyr, pathtowrite + "changeattributevalues_try_dbf", options)


    pc4=pc()

    # change attributes of existing local layer with dbf package (not fast enough)
    testdata=[f for f in range(xvals)]
    fpath=pathtowrite + "changeattributevalues_try_dbf.dbf"
    dbf_table = dbf.Table(fpath, codepage='utf8')
    dbf_table.open(mode=dbf.READ_WRITE)
    k=0
    for f in dbf_table:
        with f:
            f[:]=[testdata[k] for f in range(21)]
    #        k+=1
    #        if k>500:
    #            break
    dbf_table.close()

    pc5=pc()


    ## change attributes of existing local layer with creating newlayer with part of existing layer and join values

    lyr2 = QgsVectorLayer("Polygon?crs=EPSG:25832", "new_memory_layer_with_part_of_old_memory_layer", "memory")
    lyr_p2=lyr2.dataProvider()

    ifield=[]
    for f in range(2):
        ifield.append(QgsField("ID0" + str(f),  QVariant.Int))

    lyr_p2.addAttributes( ifield )
    lyr2.updateFields()

    ifeat=[]
    for f in lyr.getFeatures():
        ff = QgsFeature()
        ff.setFields(lyr_p.fields())
        for g in range(2):
            ff[g] = f[g]
        ifeat.append(ff)
    lyr_p2.addFeatures(ifeat)
    lyr2.commitChanges()


    # create new data layer for join

    lyr3 = QgsVectorLayer("Polygon?crs=EPSG:25832", "table_for_join", "memory")
    lyr_p3=lyr3.dataProvider()

    ifield=[]
    for f in range(19):
        ifield.append(QgsField("ID" + str(f),  QVariant.Int))

    lyr_p3.addAttributes( ifield )
    lyr3.updateFields()

    testdata_new=[f for f in range(xvals)]

    ifeat=[]
    for f in range(len(testdata)):
        ff = QgsFeature()
        ff.setFields(lyr_p.fields())
        for g in range(19):
            ff[g] = testdata_new[f]
        ifeat.append(ff)

    lyr_p3.addFeatures(ifeat)
    QgsProject.instance().addMapLayer(lyr3)

    # perform join operation
    shpField='ID01'
    csvField='ID1'
    #joinObject = QgsVectorLayerJoinInfo()
    #joinObject.joinLayerId = lyr3.id()
    #joinObject.joinFieldName = csvField
    #joinObject.targetFieldName = shpField
    #joinObject.memoryCache = True
    #lyr2.addJoin(joinObject)

    output=processing.run("native:joinattributestable", \
    {'INPUT':lyr2,'FIELD':shpField,\
    'INPUT_2':lyr3,'FIELD_2':csvField,\
    'FIELDS_TO_COPY':[],'METHOD':1,'DISCARD_NONMATCHING':False,'PREFIX':'','OUTPUT':'TEMPORARY_OUTPUT'})
    lyr22=output["OUTPUT"]

    options = QgsVectorFileWriter.SaveVectorOptions()
    options.actioanOnExistingFile = QgsVectorFileWriter.CreateOrOverwriteLayer
    options.EditionCapability = QgsVectorFileWriter.CanAddNewLayer
    options.driverName="SQLite"
    options.layerName = lyr2.name()
    writer = QgsVectorFileWriter.writeAsVectorFormat(lyr22, pathtowrite + "joined", options)

    lyr4=QgsVectorLayer(pathtowrite + "joined.sqlite","joined","ogr")
    lyr4.dataProvider().setEncoding(u'UTF-8')
    QgsProject.instance().addMapLayer(lyr4)

    pc6=pc()


    ## change attributes of existing local layer with sql

    conn = sqlite3.connect(pathtowrite + "new.sqlite")
    c = conn.cursor()

    recordList=[]
    testdata_new=[f*2 for f in range(xvals)]
    for f in range(len(testdata)):
        update_vals=[]
        for g in range(10):
            update_vals.append(testdata_new[f])
        update_vals.append(f)
        recordList.append(tuple(update_vals))

    sqlite_update_query = "update new_memory_layer set "
    for f in range(10,20):
        sqlite_update_query+="ID"+str(f)+" = ?, "
    sqlite_update_query=sqlite_update_query[:-2]    
    sqlite_update_query+=" where ID1 = ?"

    #https://stackoverflow.com/questions/52142645/how-to-improve-sqlite-insert-performance-in-python-3-6
    ##parameters should improve speed, ensure the power and OS is healthy otherwise data loss, no improvement here
    #c.execute("PRAGMA synchronous = OFF")
    #c.execute("PRAGMA journal_mode = OFF")

    #https://stackoverflow.com/questions/35013453/apsw-or-sqlite3-very-slow-insert-on-executemany
    c.execute("BEGIN TRANSACTION")
    c.executemany(sqlite_update_query, recordList)
    c.execute("COMMIT")

    conn.commit()
    conn.close()

    pc7=pc()

    ## change attribute values by creating new layer with geometry of old layer, do join by attribute with creating new SQLite Datasource (old and new values) and save as SQLite

    lyr222 = QgsVectorLayer("Polygon?crs=EPSG:25832", "new_memory_layer_with_part_of_old_memory_layer (only geoms)", "memory")
    lyr_p222=lyr222.dataProvider()

    ifield=[]
    iter0=[f for f in range(2)]
    for f in iter0:
        ifield.append(QgsField("ID0" + str(f),  QVariant.Int))

    lyr_p222.addAttributes( ifield )
    lyr222.updateFields()

    ifeat=[]
    lyr222.startEditing()
    feats=lyr.getFeatures()
    for f in feats:
        ff = QgsFeature()
        ff.setFields(lyr_p.fields())
        for g in range(2):
            ff[g] = f[g]
        ifeat.append(ff)
    lyr_p222.addFeatures(ifeat)
    lyr222.commitChanges()


    # create new data layer for join (local SQLite)

    ifield=""
    values=""
    field_count=19
    for f in range(field_count):
        ifield+="ID" + str(f) + ", "
        values+="?,"
    ifield=ifield[:-2]
    values=values[:-1]

    recordList=[]
    testdata=[f for f in range(xvals)]
    for f in range(len(testdata)):
        update_vals=[]
        for g in range(field_count):
            update_vals.append(testdata[f])
        recordList.append(tuple(update_vals))

    connection = sqlite3.connect(pathtowrite + "temp_data_layer.sqlite")
    connection.execute('CREATE TABLE events(' + ifield + ')')
    connection.executemany(
        'INSERT INTO events VALUES ('+values+')',
        recordList
    )

    connection.commit()
    connection.close()

    lyr33 = QgsVectorLayer(pathtowrite + "temp_data_layer.sqlite", "local_table_for_join", "ogr")
    QgsProject.instance().addMapLayer(lyr33)

    # perform join operation
    shpField='ID01'
    csvField='ID1'


    output=processing.run("native:joinattributestable", \
    {'INPUT':lyr222,'FIELD':shpField,\
    'INPUT_2':lyr33,'FIELD_2':csvField,\
    'FIELDS_TO_COPY':[],'METHOD':1,'DISCARD_NONMATCHING':False,'PREFIX':'','OUTPUT':'TEMPORARY_OUTPUT'})

    lyr22=output["OUTPUT"]

    options = QgsVectorFileWriter.SaveVectorOptions()
    options.actioanOnExistingFile = QgsVectorFileWriter.CreateOrOverwriteLayer
    options.EditionCapability = QgsVectorFileWriter.CanAddNewLayer
    options.driverName="SQLite"
    options.layerName = lyr222.name()
    writer = QgsVectorFileWriter.writeAsVectorFormat(lyr22, pathtowrite + "joined_sql", options)

    lyr444=QgsVectorLayer(pathtowrite + "joined_sql.sqlite","joined_sql","ogr")
    lyr444.dataProvider().setEncoding(u'UTF-8')
    QgsProject.instance().addMapLayer(lyr444)
    
    pc8=pc()
    
    print("create layer with " +str(round(xvals/10**3,0))+"k features and 20 attributes  ", round(pc2-pc1,1), "s")
    print("changeattributes on layer with " +str(round(xvals/10**3,0))+"k features and 20 attributes  ", round(pc3-pc2,1), "s")
    print("save changed layer as ESRI Shapefile ", round(pc4-pc3,1), "s")
    print("change attribute values of Shapefile with dbf package ", round(pc5-pc4,1), "s")
    print("change attribute values by creating new layer with parts of old layer and do join by attribute and save as SQLite ", round(pc6-pc5,1), "s")
    print("change attribute values by sql ", round(pc7-pc6,1), "s")
    print("change attribute values by creating new layer with geometry of old layer, do join by attribute with creating new SQLite Datasource (old and new values) and save as SQLite ", round(pc8-pc7,2), "s")
1
  • Nice Analysis. Valuable perspective.
    – A.A
    Aug 6, 2021 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.