Find the most populous city within regular hexagon

Question

I have a map of europe as hexagonal grid. For each hexagon I have center(lat and long) and constant size. Is there a way to determine the most populous city within each hexagon? It may not be completely accurate so each city should be just point with a coordinates and no real area.

Answer 1

If you loaded your data into something like PostGIS, and your Hexagons had a unique ID or similar unique value, you could use the ST_Intersects function to intersect the hexagons and the Cities.

Then use a row_number function with a partition and order clause to essentially group the Cities by the Hexagon ID they fall within and order them in descending order of population. This then assigns the highest populated City a row_number value of 1, the next gets 2, etc.

Then you wrap a subquery around it, and select features that have a rownum of 1

Here's an example:

select * from (
select
cities.name
, cities.Population
, hexagon.hexagonID
, row_number() over (partition by hexagons.hexagonID order by cities.population desc) as rownum
from public."Cities" as cities
join public."Hexagons" as hexagons on ST_Intersects(cities.geom, hexagons.geom)
order by hexagons.hexagonID) as mainpull

where mainpull.rownum = 1