QGIS Extract by Expression - Only subgroups w/ time duration between consecutive observations < 4h

Question

I have several point layers with the attributes CLUSTER_ID (groups points in different clusters by unique ID) and datetime_lcl (timestamps). In each layer, I'd like to extract only the clusters in which time duration between consecutive points does not surpass 4 hours. If this is the case, all points belonging to the same CLUSTER_ID are to be ignored from the extraction.

As an example, the input:

datetime_lcl           CLUSTER_ID 

05/06/2023 11:00:18         1
05/06/2023 12:00:17         1
05/06/2023 18:00:00         1

05/06/2023 09:30:01         2
05/06/2023 10:00:20         2
05/06/2023 13:00:11         2

Would output:

datetime_lcl           CLUSTER_ID 

05/06/2023 09:30:01         2
05/06/2023 10:00:20         2
05/06/2023 13:00:11         2

This step will be embedded into a longer graphical model. I will be using the Extract by Expression tool with a custom function, but I don't know how to write such a condition in a way that returns TRUE for all features in valid clusters. What I have at the moment cannot be correctly interpreted by Extract by Expression:

from qgis.core import QgsFeatureRequest
from datetime import timedelta

@qgsfunction(args='auto', group='Custom')
def extract_cluster(layer, feature, parent):
    # Get all unique cluster IDs from the layer
    cluster_ids = set([f['CLUSTER_ID'] for f in layer.getFeatures()])

    selected_features = []

    # Loop through different cluster IDs
    for cluster_id in cluster_ids:
        features_in_cluster = layer.getFeatures(QgsFeatureRequest().setFilterExpression(f'CLUSTER_ID = {cluster_id}'))

        # Initialize the cluster result as True
        cluster_result = True

        # Check if the cluster is valid based on the specified conditions
        sorted_features = sorted(features_in_cluster, key=lambda f: f['datetime_lcl'])

        for i in range(len(sorted_features) - 1):
            current_time = sorted_features[i]['datetime_lcl']
            next_time = sorted_features[i + 1]['datetime_lcl']
            duration = next_time - current_time

            if duration > timedelta(hours=4):
                # Set the cluster result to False if any points in the cluster do not meet the condition
                cluster_result = False
                break  # Exit the loop if a point is found that doesn't meet the condition

        # If the cluster is valid, add all its features to the selected features list
        if cluster_result = True:
            for feature in features_in_cluster:
                selected_features.append(feature)

    # Return the selected features
    return selected_features

How do I adapt this code to act as a custom function in Extract by Expression to extract all points from cluster IDs that meet my requirements?

Answer 1

If you are open to an expression-only solution (no Python) you can use this expression:

maximum(
    to_datetime("datetime_lcl", 'dd/MM/yyyy HH:mm:ss'),
    group_by:="CLUSTER_ID"
) - 
minimum(
    to_datetime("datetime_lcl", 'dd/MM/yyyy HH:mm:ss'),
    group_by:="CLUSTER_ID"
) < to_interval('4 hours')

The expression subtracts the minimum datetime_lcl from the maximum datetime_lcl of each CLUSTER_ID group and checks if the result is less than 4 hours.

Note:

If your datetime_lcl field is already of DateTime type, you can omit the to_datetime() functions like so:

maximum(
    "datetime_lcl",
    group_by:="CLUSTER_ID"
) - 
minimum(
    "datetime_lcl",
    group_by:="CLUSTER_ID"
) < to_interval('4 hours')

Result:

With additional clusters: