Giving grid cells unique ID of numbers and letters by rows and columns

Question

In QGIS 3.16 I have a grid made up of c.900 squares and I want to give each square in the grid an individual name.

I want rows to be numbers and columns to have letters so when they meet that is our grid square "ID" - for example 'G7', 'Z10'.

Anyone any tips on how to do this?

See also here: https://gis.stackexchange.com/a/361838/88814 – Babel Jun 10 '21 at 18:26 — Babel, Jun 10 '21 at 18:26

MrXsquared · Answer 1 · 2021-06-10T22:55:19.790

Here is an expression you can use. It works if your grid is properly ordered, meaning all centroids of one column have the same x coordinate all centroids of one row have the same y coordinate. Note the limitations when using letters instead of numbers: There are only 26 capital letters in the latin alphabet, so your grid should not exceed 26 columns. If you do not want this limitation, take a look at this answer.

with_variable('n_cols',array_distinct(array_agg(x(centroid($geometry)))),
with_variable('n_rows',array_distinct(array_agg(y(centroid($geometry)))),
with_variable('char',array_foreach(generate_series(0,25),char(@element+65)),
@char[array_find(@n_cols,x(centroid($geometry)))]
||
array_find(@n_rows,y(centroid($geometry)))
)))

Result:

How, why and when this works:

This expressions works, if your grid is properly created. Means every centroid of one column has the same x coordinate as every other centroid in this column. Same goes for rows: it works if every centroid has the same y-coordinate as every other centroid of this row.

We are using these coordinates to determine how many columns and how many rows a grid has in total. We need this to properly name rows and columns.

For the naming we just check in at which array position the current centroid coordinate is. For x we are using a letter lookup from A to Z and for y just the number. You could add +1 here if you dont want to start it from 0.

So lets explain the expression:

array_distinct(array_agg(x(centroid($geometry)))) creats an array of all x coordinates of the centroids and keeps only distinct values. The length of the array is the number of colums. The number of rows works the same, just using the y coordinate.

array_foreach(generate_series(0,25),char(@element+65)) creates the letter lookup. char() gets the character from the unicode list, you can lookup here: https://en.wikipedia.org/wiki/List_of_Unicode_characters. A has the value 65. So we start at this position (+65). @element is the current array position of the current centroid coordinate.

PS: I don't know how to explain this better at the moment. If you have a specific question just let me know and I will try to clarify.

If it's a 30 x 30 grid OP better likes themselves some ASCII... — geozelot, Jun 10 '21 at 15:56
@geozelot yep, but thats the general issue when using letters... — MrXsquared, Jun 10 '21 at 15:57
...or create the lookup yourself if you are not too lazy to it and replace array_foreach(generate_series(0,24),char(@element+65)) with e.g. array('A','B',...,'ZY','ZZ') — MrXsquared, Jun 10 '21 at 16:31
If it has to be alpha then numeric, then you'd need a variant of base64 in the alpha, and the second position could only have ten values (before translation to two digits) — Vince, Jun 10 '21 at 18:07

score 2 · Answer 2 · answered Jun 10 '21 at 22:52

If you do not want a character limitation, you can implement this answer from SO as custom function, like this:

from qgis.core import *
from qgis.gui import *
@qgsfunction(args='auto', group='Custom')
def number_to_char(n, upper, feature, parent):
    x = 'a'
    if upper:
        x = x.upper()
    if n < 1:
        raise ValueError("Number must be positive")
    result = ""
    while True:
        if n > 26:
            n, r = divmod(n - 1, 26)
            result = chr(r + ord(x)) + result
        else:
            result = chr(n + ord(x) - 1) + result
            return result

and then use this expression:

with_variable('n_cols',array_distinct(array_agg(x(centroid($geometry)))),
with_variable('n_rows',array_distinct(array_agg(y(centroid($geometry)))),
number_to_char(array_find(@n_cols,x(centroid($geometry)))+1,True)
||
to_string(array_find(@n_rows,y(centroid($geometry)))+1)
))

Result:

score 2 · Answer 3 · edited Dec 11 '23 at 06:30

MrXsquared's answer was very useful and solved my requirement, with the exception that I needed a grid origin for the labels in the southwest corner of the grid, with numbers increasing to the north (I'm in the Southern Hemisphere if that makes a difference). I did a bit more research on array functions and came up with the following expression to replace the one above:

with_variable(
    'n_cols',
    array_distinct(array_agg(x($geometry))),
    with_variable(
        'n_rows',
        array_distinct(array_agg(y($geometry))),
        number_to_char(array_find(@n_cols, x($geometry))+1, True) || 
        to_string(array_find(array_reverse(@n_rows), y($geometry))+1)
    )
)

The centroid function is unnecessary, as the array_agg function defaults to the centroid for non-point vector arrays.

Giving grid cells unique ID of numbers and letters by rows and columns

3 Answers3

Linked