Automatically output joined fields in input layer while using spatial join

Question

For example, I have two layers:

• A polygon layer city block with a ID field city block index.
• A point layer parking lot with a field parking lot size.

I want to know the total parking lot size for each feature in the land use layer, so I use the spatial join function. Here's my workflow:

1. Use join attributes by location (summary) to create a new temporary joined layer who contains a field called parking lot size_sum.
2. Go to the property panel of the city block layer and join the parking lot size_sum field as a new field called joined layer_parking lot size_sum.
3. In the city block layer, create a new field called total parking lot size .
4. Copy values from joined layer_parking lot size_sum and paste them into total parking lot size.
5. Delete the temporary joined layer.

All I want to do is to add a new field into the original city block layer by summarizing the parking lot size field. But I have to go through all the steps above. Is there a simpler way to do that?

Answer 1

There is a possibility using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...

With the following Query, it is possible to know the total 'parking lot size' for each 'city block'.

SELECT cb.*, SUM(pl."parking lot size") AS pl_sum
FROM "city block" AS cb
JOIN "parking lot" AS pl ON st_contains(cb.geometry, pl.geometry)
GROUP BY cb."city block index"