Return all IDs of point features within a polygon to one field

Question

On QGIS 2.14.14 I am trying to compile Old Land Parcel ID codes on to New Land Parcel geometries. The Old Land Parcel IDs are recorded as point data and the New Land Parcel geometries are polygons. Most of the Old Land Parcel IDs are the same as the New Land Parcel IDs but some of the New Land Parcels have multiple Old Land Parcel IDs codes associated with them as the Old Land Parcels have been combined to make the New Land Parcels.

I would like to have a field on the New Land Parcel layer showing all the Old Field Parcel IDs associated with the New Field Parcel as a string. Or more simply put - return all IDs of point features within a polygon to one field.

I am currently using AutoFields to create a live updatable field as new land parcels will be added to the project as new areas are addressed. Within this, I have used the expression

geomcontains( 'Old Land Parcels', 'Old_Parcel')

The problem is this will only return the first Old Land Parcel Id it comes to. Is there a way to repeat this so all contained features are recorded in the one field as a string?

Point Shapefile Old Land Parcel field Old_Parcel Polygon Shapefile New Land Parcel field PARCEL_ID intended new field Old_Parcels(this has now changed to OldParcels due to character limit)

Answer 1

You can get the result using Virtual Layer in QGIS, Add the 2 layers using the Embedded Layers option. then write your query to do spatial join and get the old parcels concatenated:

SELECT new_parcel.id,
group_concat(old_parcel.id,',') as old_parcel_IDs  
FROM new_parcel LEFT OUTER JOIN old_parcel 
ON ST_Contains(new_parcel.geom,old_parcel.geom)  
GROUP BY new_parcel.id

Reference:
https://docs.qgis.org/2.14/en/docs/user_manual/working_with_vector/virtual_layers.html