Getting all attributes summarized from one layer in area of another layer using QGIS?

Question

I have two shapefiles, one is the squares and other from the buildings on it.

The shapefile of buildings have differents attributes like the state of the building (separated in good state, regular state, bad state).

I want to the get all the attributes from the buildings summarized by square. For example: How many buildings are in good state or bad state per square.

Is there any tool in QGIS that does that?

I tried with spatial join, but did not work.

Answer 1

The Join attributes by location (summary) tool is what you need. It's in the Processing Toolbox.

Answer 2

You can achieve this using the DB Manager of QGIS.

You choose Database / Database Manager / Database Manager then Virtual Layers / Qgis Layers you can then try a query like the following :

SELECT id, state,count(*) from squares, buildings
where st_intersects(squares.geometry, buildings.geometry)
group by squares.id, buildings.state

Adapt the query according to your data structure (namings ...)

You can then load the result in the layer manager.

Answer 3

You can approach this in a number of different ways, but one way is to use the Statistics by Categories processing algorithm in QGIS.

You might use a Select by Location first to select the parcels that intersect the given "square" of interest, then run the Statistics by Categories algorithm with the Selected features only option checked.

You can choose what field to calculate statistics for, as well as what Field to use to categorize/group the data.

Answer 4

You can also use Field Calculator to create a field in your square layer that summarises data matching a certain criterion from your building layer.

In your square layer you would use something like this expression:

aggregate(layer:='building_layer_name',aggregate:='count',expression:="building_id",filter:=within($geometry,geometry(@parent)) AND "building_condition" = 'Good')

That will return a count of all the buildings completely within your square that are in Good condition. You would need to modify accordingly for some other condition, or to count buildings that intersect your square but are not necessarily completely within it (change within() to intersects())

To more efficiently get summarised data by condition for each square I would recommend SQL via the DB Manager as suggested by @snaileater