1

I am new to QGIS and very excited about all its features. We want to match gridcells to a region, when a gridcell lies in more than one region we want to assign the largest region to the gridcell.

Up till now we have been working with "connect with attributes on basis of place" to assign the region name to the gridcell. We are not familiar with R or Python.

Is there anyway we can do this without using these programs?

  • which version of QGIS are u using ? – snaileater Mar 28 at 9:15
2

You can achieve this using the DB Manager of QGIS.

You choose Database / Database Manager / Database Manager then Virtual Layers / Qgis Layers

You can then try the following query :

select grid.id,max(st_area(intersection(reg.geometry,grid.geometry))),reg.name
from grid, region_layer reg
where st_intersects(grid.geometry, reg.geometry)
group by grid.id

grid_layer being your grid layer and reg_layer being your region layer...

This query generates the id of the cell followed by the surface of intersection and the region's name.

You can then load the result in the layer manager. You can also join this resulting layer to your grid layer if you want to insert the region's name in your initial table.

PS : adapt the query according to your data structure ...


According to your data structure the query should look like this :

select prio_gid.gid,
max(st_area(intersection(GADM36_KEN_1.geometry,prio_gid.geometry))),GADM36_KEN_1.NAME_1
from prio_gid, GADM36_KEN_1
where st_intersects(prio_gid.geometry,GADM36_KEN_1.geometry)
group by prio_gid.gid
| improve this answer | |
  • Thank you so much for your help. We are struggeling to adapt the query to our data structure. Would you be able to help us? We have a layer with gridcells called "prio_gid", within that layer the gridcell number is in the colom "gid" of our attribute table. Our region layer is called "GADM36_KEN_1" and within that layer the name of the region is in the colom "NAME_1" of our attribute table. – Eline Mar 30 at 8:32
  • just to make sure I have asked my question clearly, some more explination on what we want to do. We want to match regions to our gridcells. The regions are larger than the gridcells and eventually we want all gridcells to be assigned a region. When a gridcell is in two regions we want the gridcell to get the name of the largest region. – Eline Mar 30 at 8:32
  • we have now written the code 'select gid.id, max(st_area(intersection(NAME_1.geometry,gid.geometry))),NAME_1.name from priogrid_cell, gadm36_KEN_1 reg where st_intersects(gid.geometry, NAME_1.geometry) group by gid.id' – Eline Mar 30 at 8:34
  • And get this error term: Query execution error on DROP TABLE IF EXISTS ""; CREATE VIRTUAL TABLE "" USING QgsVLayer('virtual','',UTF-8): 1 - Provider error: virtual: Malformed URL – Eline Mar 30 at 8:34
  • i edited my answer ! – snaileater Mar 30 at 10:02

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.