Filling field with values from field of another table using QGIS

Question

I am using QGIS 3.28.9.

I need to complete a field from a building's layer with the field's values from a plot's layer, based on the location of the buildings. At first I thought of doing it through the "Join attributes by location" tool, but I have some restrictions that difficult this. I'm working directly in the layers on the database, so any solution which requires to create a new layer is not possible for me. Also, I'm not allowed to create new fields in the attribute table, I can only modify the values of the existing columns which I have been doing manually.

Said this, I've tried doing some things, but without succeeding:

1. Use the "Join attributes by location" tool to create a new layer with the values I need, then try to pass the values to the original layer through an id field (this is where i have problems). I tried doing this by editing the column with an expression, following the next idea:

if( "object_id(layer1)" = "object_id(layer2)" , "field_value(layer2)" , "field_value(layer1)"

(If the object id is the same in both tables, the register takes the value from table 2, if not, it maintains the original value (NULL)). However, I couldn't find the way to cite the second layer in the script.

2. I tried doing a relation through Layer Properties / Attribute Form, to complete the field's values directly with the intersecting plots values, following the answer from this post QGIS 3.28 : Relational value form (spatial intersection)

While I could do it, I found some problems with it: The result is a selection list with two options: open and close brackets (), and the value from the plots layer (see image below). Apparently I have to select the value in each register manually. Also, I couldn't find a way to make this values the definitive values of the field. If I delete the relation the values disappear. Finally, and most important, I can't find a way to do this only for the selected elements form the layer, which is a problem, because I don't want to do it to the whole layer (It would change the values in another areas I'm not working with and ti would be a disaster).

3. I didn't try it, but maybe it's possible to do this using PostGIS. However, my knowledge about this is very limited.

Answer 1

With an expression you can update an existing field. Select "Update existing field" in the Field calculator.

If you want to select the buildings to update, you can also tick on "Only update selected features".

A useful function to this task is overlay_intersects.

Using an expression parameter (the name of the target field in your case), it returns an array of all features whose geometry intersects with the geometry of the feature being evaluated (each building).
It accepts a sort_by_intersection_size parameter, so you can sort the returned array by descending intersection size. The first element will be the value of the expression field for the target layer feature with largest overlap.
You can use slice notation to get only the first element ([0]) of the returned array:

overlay_intersects(
 layer:='plots',
 expression:="nomenclatura", 
 sort_by_intersection_size:='des')[0]

Answer 2

If you are fine with using PyQGIS in the console, you could do the following steps:

SETTING

For this example you have in the plot layer (source) a field called sourceField and in the building layer (target) a field called targetField which you want to update. In this example they are both string (text) but that doesn't matter as long as they are both from the same type. Otherwise you would have to retype them or the values.

Now you can select one plot:

and run the following code in Python console in QGIS:

lyrPlot = iface.activeLayer() # plot layer
featuresPlot = lyrPlot.selectedFeatures() # selected featurs from plot 
sourceField_idx = lyrPlot.fields().indexOf('sourceField') # source field of plot layer

lyrBuilding = QgsProject.instance().mapLayersByName('building')[0] # name of building layer
targetField_idx = lyrBuilding.fields().indexOf('targetField') # target field of building layer

with edit(lyrBuilding):
    for p in featuresPlot:
        for b in lyrBuilding.getFeatures():
            if b.geometry().within(p.geometry()): # intersects, contains, within ...
                b[targetField_idx] = p[sourceField_idx]
                lyrBuilding.updateFeature(b)

The attributes of the building layer are updated with the selected features from the plot layer.#

In this code line you could change your relation you want to check (intersect, within, contains).

if b.geometry().within(p.geometry()):

UPDATE on COMMENT

If overlapping is an issue for you and you want to sort your results by the most overlapping plot, you can use the following code:

We use a Python dictionary in memory buildingPlotArea = {} and save the overlapping area() as value and id() as key of the building. For each item in the loop we check if the overlapping area is bigger than the saved values. If it is bigger we overwrite the attribute with the new one.

lyrPlot = iface.activeLayer() # plot layer
featuresPlot = lyrPlot.selectedFeatures() # selected featurs from plot 
sourceField_idx = lyrPlot.fields().indexOf('sourceField') # source field of plot layer

lyrBuilding = QgsProject.instance().mapLayersByName('building')[0] # name of building layer
targetField_idx = lyrBuilding.fields().indexOf('targetField') # target field of building layer

buildingPlotArea = {}

with edit(lyrBuilding):
    for p in featuresPlot:
        for b in lyrBuilding.getFeatures():
            if b.geometry().intersection(p.geometry()): # intersects, contains, within ...
                intersectionArea = b.geometry().intersection(p.geometry()).area()
                buildingID = str(b.id())
                if (buildingID in buildingPlotArea) and (buildingPlotArea[buildingID] < intersectionArea):
                    buildingPlotArea[buildingID] = intersectionArea
                    b[targetField_idx] = p[sourceField_idx]
                    lyrBuilding.updateFeature(b)
                elif (buildingID in buildingPlotArea) and (buildingPlotArea[buildingID] >= intersectionArea):
                    break
                else:
                    buildingPlotArea[buildingID] = intersectionArea
                    b[targetField_idx] = p[sourceField_idx]
                    lyrBuilding.updateFeature(b)

You can save the code in the console and reuse it.