Is there a way to use windowing functions (lag, lead) within the QGIS Field Calculator?

Question

I am keen to compute a windowing function on values in QGIS. I was not able to find a lag/lead function. I think this could be emulated using @rownumber and $currentfeature, but I am not expert in using these.

I need to find a value for an attribute for a current feature. This seems to be possible using attribute($currentfeature,"fieldname"). I am unsure how to specify something like the previous feature, equivalent to treating the list of values for an attribute in a feature collection (pseudocode):

fc[@rownumber]["fieldname"]

I could then do fc[@rownumber-offset]["fieldname"], assuming it evaluates to NULL for the first entry. Is there any way to do this? I am sure people computing say acceleration on trajecotry data would deal with this.

• kudos: if the answer hints at treatment of the sorting in formats that do not support this well (shapefile).

Answer 1

If I understand well your request, you may create a simple custom function for accomplishing the task.

If you never used a custom function within the Field Calculator, please give a look at this post I recently wrote: How to create custom functions in QGIS using the Function Editor.

Having said that, you can use this custom function:

@qgsfunction(args='auto', group='Custom')
def my_calculation(my_layer, lag, lead, curr_row, feature, parent):

    layer=QgsMapLayerRegistry.instance().mapLayersByName(my_layer)[0]
    feature_count = layer.featureCount() # For evaluating the maximum number of 'rows'

    all_feats = {}
    row = 1
    for feat in layer.getFeatures():
        all_feats[row] = feat
        row += 1

    # Store all the features of interest in a list
    my_feats = [all_feats[x] for x in xrange(curr_row - lag, curr_row + lead + 1) if x > 0 and x <= feature_count]

    # Now, do what do you want with them
    # For example, let's compute the sum of the numbers stored in the field "pop":
    my_res = sum([k['pop'] for k in my_feats ]) # It is just an example, replace this with your calculations

    return my_res

You only need to replace the my_res variable with the calculation you want to do.

For activating the custom function, type an expression similar to this one:

my_calculation('my_layer', 2, 2, @row_number)

where the first parameter is the name of the layer you want to use, the second and the third ones are the lag and lead values and the last parameter specifies the current row number (leave it unchanged).