3

I want to do the following with some fields in the attribute table but I have no idea how to do it: Each row is about a parcel and its selling price, it also contains its address, cadastral ID etc. The problem is that sometimes, one sale included several parcels, in this case the total price of the sale is included in each parcel row. As I would like to get the price/sqm of the land sale, it obviously gets some wrong result since the price is for all the parcels in one sale, not each individual parcel.

For instance, some land was sold for 1000 euros (just for the sake of explanation). The land is located 1, Olive Street. This land includes 3 parcels A, B and C with 300, 200 and 100 sqm respectively for a total of 600 sqm. In this case, the table of attributes look like this:

Parcel_A | 300 | 1000 | 1 | Olive Street

Parcel_B | 200 | 1000 | 1 | Olive Street

Parcel_C | 100 | 1000 | 1 | Olive Street

Right now if I just do 1000/300 = 3,3 euros/sqm this is of course wrong since 1000 euros is the total price of this piece of land which includes parcels A, B and C.

I would like to transform the previous table and arrive at the following result :

Parcel_A_Parcel_B_Parcel_C | 600 | 1000 | 1 | Olive Street | 1,66 (1,66 being the price/sqm of the total piece of land).

I guess I have to do something like this (just in terms of logic) : if the address (street name and street number) is the same, concatenate Parcel Name field, add area, keep just one instance (any instance since they will be similar) of area, street name and street number. And then (ideally), replace the original rows by the resulting row (since I won't need the "old" rows anymore). Also, in case a piece of land only includes one parcel, it should of course keep it in place since there is no issue in this case.

How do I use the field calculator to perform this kind of thing (if it is even possible)?

1 Answer 1

4

You can achieve this using a virtual layer.

It would group the records by civic number + street name (though if you can group by ID do so), union the geometries, sum the area and do the division using this sum.

Go to the menu Layer > Add Layer > Add/Edit Virtual Layer... and enter the following query. Feel free to add more fields from your layer, and to adjust the names!

SELECT group_concat(name,'_') as new_name,
       st_union(geometry) as geometry, 
       sum(parcel_area) tot_area, 
       min(total_price) as total_price, 
       1.0*min(total_price)/sum(parcel_area) as price_per_area_unit,
       civic_number,
       street_name
FROM myLayer
GROUP BY civic_number, street_name

enter image description here

7
  • That's awesome, I'm going to try it now, thank you so much! Why do you use "min" here? I see that it means "minimum" but doesn't it apply to numbers only? Or does it say to just use one string among many similar strings? Mar 10, 2021 at 19:21
  • And I was also wondering about the 1.0*min. Thank you! Mar 10, 2021 at 19:28
  • 1
    Because we aggregate, we must either use the field in the group by (like the street name) or apply a function to keep/construct a single output. Here it is the same value in every record, so we can keep the min, the max, the mean etc, it doesn't matter. It is applied on the price field, which is (should!) a number
    – JGH
    Mar 10, 2021 at 19:40
  • 1
    If you divide 2 integers, the output is an integer. By multiplying by 1.0 first, it makes the computation to use floats and therefore will output digits
    – JGH
    Mar 10, 2021 at 19:42
  • 1
    you can change the group by clause to be sale_id instead of the address components
    – JGH
    May 31, 2021 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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