1

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? enter image description hereenter image description here

Input into create virtual layer

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)

3

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

  • Thank you so much for your help Shiko, I did not know about Virtual Layers and I can see they are going to be really useful for me. I am still getting an error "Query preparation error on PRAGMA table_info(_tview)" do you know what this means – P Dreyer Dec 20 '17 at 11:45
  • @PDreyer Virtual layer is quite useful, I have used several times when want do spatial queries on several layers. Enjoy :) – Shiko Dec 20 '17 at 11:46
  • @PDreyer please mark it as answer if it worked – Shiko Dec 20 '17 at 12:44
  • 1
    I have not got it working yet, I keep getting a "Query preparation error on PRAGMA table_info(_tview)" error, I will post a screenshot above showing my workings. – P Dreyer Dec 21 '17 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.