Spatial Join also Based on Attributes using QGIS

Question

I have two data sets:

• points with a timestamp
• polygons representing neighbourhoods

I need to compute how many points are in each neighbourhood polygon, based on a time range. So in other words, how many points are in zone A between 9 am and 10 am and then how many are there between 10 am and 11 am.

Currently, I am separating the points into separate layers - one layer for 9 am to 10 am then I do a spatial join. Then another layer for 10 am - 11 am and another join. This is exceptionally tedious and insufficient to do this same analysis across a week for example.

Any suggestions?

Answer 1

You can use the Field Calculator to count the number of points that are contained in each polygon and that respect a filter. This will not solve the necessity to repeat the operation for each time range but it avoid to create new layers for each group.

With the expression

aggregate(
layer:='point',
aggregate:='count',
expression:=$id,
filter:=contains(geometry(@parent), $geometry)
and "time" > '09:00:00' and "time" <'10:00:00')

the system will count all the points inside each polygon having a time period comprised between 09 and 10.

You can use this expression in the polygon Label to visualise the number of points or, if you need to save this info in a new field, use it in the Field Calculator to create a new field for each time range just changing the time option in the expression.

Answer 2

This is job for a Virtual Layer (Layer|Add Layer|Add/Edit Virtual Layer) because it gives you the grouping abilities of SQL.

Without knowing your layer and column names the general form of the SQL will be:

SELECT polygon_layer.zone,
       strftime('%Y-%m-%dT%H:00:00.000', point_layer.timestamp) as 'hour',
       COUNT(point_layer.geometry) as 'count',
       polygon_layer.geometry
FROM polygon_layer, point_layer
WHERE ST_Contains(polygon_layer.geometry, point_layer.geometry)
GROUP BY polygon_layer.zone, strftime('%Y-%m-%dT%H:00:00.000', point_layer.timestamp), polygon_layer.geometry

Just substitute the correct table names for polygon_layer and point_layer and the correct column names for polygon_layer.zone and point_layer.timestamp.

The strftime function formats the time as a string with zeroes for the minutes and seconds - essentially rounding the times down to the nearest hour so it can be used for grouping.