2

I am trying to add the most populated towns within a number of regions as an attribute to those regions. IE, I want a column on the polygon sheet listing the most populated town in that polygon.

I followed instructions I found via google and was able to make an attribute on the town's point layer which either reads "N/A" or the name of a region if that town is that region's highest populated town. This was achieved using zonal statistics via the town layer's population attribute.

The problem is I have no idea how to take this data and transfer it to the polygon layer. This is my first GIS project of any kind, so I would prefer answers that don't skip steps or abstract parts of the workflow.

4

1 Answer 1

3

For this I would recommend using a Virtual Layer (Database --> DB Manager --> Virtual Layers --> Project layers). Assume you have a layer cities and a layer regions. First you want to find the city with the highest population (inner query). Then you want to join the cities on the regions using st_within and group it by some unique value of the regions layer - in the sample I am using regions.NAME. QGIS supports the SQLite SQL syntax in virtual layers

select
    *
from
    (
        select name, population, geometry
        from( 
         select 
            name, 
            population,
            geometry,
            ROW_NUMBER() OVER(PARTITION BY name ORDER BY population desc)
          as rn
          from cities 
        ) as a
        where rn = 1
    ) as cities,
    regions
where 
    st_within(cities.geometry, regions.geometry)
group by
    regions."NAME"

Then you can load the virtual new layer using Load as new layer. If you want to work with the data later it is recommended to export the virtual layer.

2

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.