I am new to QGIS.

I have a point layer (water permits) and several buffer layers (different distances from WWTPs). I can use "join attibutes by location" and find out the number of points and the sum of one of the fields (water demand) within the areas, and so far so good.

However, I would like to aggregate water demand according to the values of another field (of the water permits layer) that describes the types of water users (industrial segment, cultures irrigated etc.). How can I do that without splitting the water demand layer by type of user? That's what first came to mind, but I guess there are clever ways to do it...

I'm using QGIS 2.18.18 (3.0 or later not running well here).

1 Answer 1


Common approaches would use either refFunctions plugin or Virtual Layer.

This is an example of Virtual Layer.

enter image description here

(1) Go to menu: Layer | Add Layer | Add / Edit Virtal Layer

(2) Import both of your point and polygon layers.

(3) Give a query syntax below, change layer/field names to match with yours, and click on [OK]

SELECT Buffer_1000m.*, 
       Count(water_permits.id) AS counts, 
       Sum(water_permits.demand) As demands
FROM   water_permits, Buffer_1000m
WHERE  st_intersects(water_permits.geometry, Buffer_1000m.geometry)
AND    water_permits.user_type = "cultures irrigated"

enter image description here

It creates a new virtual layer which attribute table would look like below:

enter image description here

  • Thanks, Kazuhito! That helped a lot. I was not clear enough at first place, but I am not working with a dissolved layer for the WWTPs and therefore need the results for each plant. Using your suggestion I can create a virtual layer for a specif WWTP (adding "AND buffer_1000m.ID = 'WWTP code'"), but could not think of a way to include all of them at the same time. Is there a manner I can create a virtual layer with one WWTP per row? Jul 29, 2018 at 14:36
  • Thanks @BrunoNogueiraFukasawa yes, it seems your buffer layer has an ID field. Then, you can add GROUP BY buffer_1000m.ID as the last line of above syntax.
    – Kazuhito
    Jul 29, 2018 at 14:53
  • 1
    thanks! I've been doing some search here and came to that solution as well. I've also managed to create count and sum columns for each type of user using (for user type=1, for example): count(case when water_permits.user_type = '1' then 1 else null end) as '1_count' and sum(case when water_permits.user_type = '1' then water_permits.demand else null end) as '1_demands'. Now I have a single table for each buffer layer with all the data a need. Thanks again! Jul 29, 2018 at 18:03

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.