I have 2 cleaned shapefiles, one containing building footprints and one land parcel shapes, both have attributes. I want to do a spatial join when data from the land parcel is merged into the footprint layer. However, as you can see, some footprints transcend across several parcels. As the data in the parcels are roughly similarly, I would like to do the join based on the building polygon's centroid, so that each footprint has data from 1 parcel shape. I know ArcGIS is able to do this with spatial join tool, anyone knows if QGIS can do this too? I looked at "join by location" but it doesn't seem to have the centroid function.
2 Answers
You can create a virtual layer with the desired join condition
go to the menu Layer > Add Layer > Add/Edit Virtual Layer...
and enter the query
SELECT b.*, p.field1, p.field2
FROM buildings b
JOIN parcels p ON ST_CONTAINS(p.geometry, ST_CENTROID(b.geometry))
Note that it is safer to list the fields of interest from the 2nd layer (parcels) instead of using p.*
, as it ensure that the resulting layer contains a single geometry field - from the 1st layer (building).
-
Thank you for the 'Note'. Perhaps one tiny question, why are you skipping
AS
, i.e.FROM buildings AS b
?– TarasAug 23, 2019 at 5:47 -
Another solution based on existing processing algorithms. You can achieve this by doing 2 spatial joins.
- Extract centeroids via Vector Geometry > Centroids
- Join the centroids layer with parcels layers, call this
centroid_join
- Join the building footprints layer with centroid join.
It is advisable to explicitly specify the fields in each join, so you don't end up with many duplicate fields.