2

I am working on a project in QGIS, where I have two point layers, named "Infrastructures" and "Inspections". In my system, whenever an infrastructure is inspected, a new point feature is created on the "Inspections" layer at the position of the infrastructure. Over time, several points can stack atop one another, each representing a different inspection of the same infrastructure. There are also some cases, where a inspection is made without any infrastructure (somewhere in the field).

Now, I need the "Infrastructures" layer to interact with the "Inspections" layer, specifically, I want to retrieve the date of the last inspection for each infrastructure. Currently, I am achieving this with virtual fields using the following expression:

array_min(overlay_equals('Inspections',round(day(age(now(),"inspection_date")),0)))

However, this expression seems to be quite compute-intensive. When I open the attribute table, there's a noticeable delay even though both CPU and RAM usage remain low. Currently, I have around 1000 entries in each table.

The overarching goal is to always see the age of the last inspection on the feature in the "Infrastructures" layer.

I am looking for ways to optimize this expression, or perhaps a more efficient method to achieve the desired outcome. How can I make this more efficient?

4
  • why use two tables when you could just store last inspected in the infrastructure table?
    – Ian Turton
    Nov 2, 2023 at 9:36
  • I need something like a chronicle: I need to know how often the infrastructure was controlled and what problems were solved in the past. The inspection layer has a lot of attributes including photos and so on.
    – MartinMap
    Nov 2, 2023 at 9:39
  • So have an inspections table for that, but always update the infrastructure with last inspection date and id so you have a link back to the inspection table.
    – Ian Turton
    Nov 2, 2023 at 9:41
  • Okay, could you go into more detail? Maybe I'm totally stuck with my solution. I'm open to other ideas. The fact is: the infrastructures already have their own IDs, and the inspections do too.
    – MartinMap
    Nov 2, 2023 at 9:47

1 Answer 1

4

There are several problems with your approach that makes it inefficient. This is what you can do to make it more efficient:

  1. Don't use virtual fields, they are updated for each feature automatically whenever an underlying parameter changes, a huge waste of resources. You would only want to update the field when you actually add a new inspection. You could e.g. do it manually after adding a new inspection or define an action.

  2. Calculating spatial relationships can become very resource intensive for larger layers. So instead of a spatial query overlay_equals(), create a new infrastructure_location_id field on your inspection layer with the id of the infrastructe it inspects: overlay_equals ('infrastructure', $id)[0]

    Like this, you can join the layers based on this id, not location, what is more efficient. Define a default value in the Layer Properties > Attributes Form with the above expression so that the value is automatically generated.

  3. Then, to get the most recent inspection, simply search for the maximum value of the inspection date for each location, you don't have to complecate with age() function. So instead of your expression, array_max(overlay_equals('inspections',"date")) would return the most recent inspection date.

Combining points 2 and 3, you can use this expression:

array_max( 
    aggregate( 
        'inspections',
        'array_agg',
        "date",
        filter:=attribute(@parent,'fid')=infrastructure_location_id
    )
)
2
  • Yes, this is working, but changes in the inspections layer won't affect the infrastructure field for the most recent date if it is not a virtual field. Alternatively, I would need a trigger to recalculate after the inspections layer has been modified.
    – MartinMap
    Nov 3, 2023 at 7:50
  • 1
    I don't know how huge and complex your data are, but maybe implementing steps 2 and 3 with virtual fields reduces performance to a degree that works for you? If not, then I think you indeed should think about how to create a tigger that updates when you modify the inspections layer. Maybe you should ask a new question focusing on this aspect because here, you ask about how to optimize the expression.
    – Babel
    Nov 3, 2023 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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