Creating indexed vector grid in QGIS?

Question

at the moment I am trying to create an indexed vector grid via "vector -> research tools -> vector grid...", in the following format:

A1 A2 A3 ...
B1 B2 B3 ...
C1 C2 C3 ...
.. .. ..

The creation of the grid is no Problem, but I am stuck with labeling the individual polygons.

I already wrote an Excel Visual Basic Project, which creates a script for the field calculator and helps me to label each row from A to X. Now I want to label the columns with numbers from the first one to the last. Do you have any idea how I could use the @row_number to specify the column?

Here is the output of the script for the field calculator:

CASE
WHEN ID>-1 AND ID<5 THEN concat('A',@row_number)
ELSE
CASE
WHEN ID>4 AND ID<10 THEN concat('B',@row_number)
ELSE
CASE
WHEN ID>9 AND ID<15 THEN concat('C',@row_number)
ELSE
CASE
WHEN ID>14 AND ID<20 THEN concat('D',@row_number)
ELSE
CASE
WHEN ID>19 AND ID<25 THEN concat('E',@row_number)
END
END
END
END
END

Answer 1

You have two possibilities...

---

Existing plugin

there's already a plugin to do this called Create Indexed Vector Grid (which I wrote, mainly so I could do road atlas-style maps of cities).

Once installed it appears under Vector > Create Indexed Grid menu. It'll add the labels as fields to each cell for you (and optionally, add "headers" for your rows and columns).

It's experimental, so if you can't see it in plugins manager you'll need to enable the "show experimental plugins". There's a link to a blog post explaining how to use it in the plugin description in Plugin Manager.

---

With inbuilt create vector grid

That might not be suitable, though - the plugin only works on the canvas extent or layer extent, it doesn't let you set the extent manually.

Use the modulo operator to get the column number, and simple integer division to get the row number. That way you only need one expression, and don't need to write code to generate a script.

I created a grid using Create Vector Grid and assigned a new field with @row_number (called this field idd). As you can see it's 15 cells wide wide, and cells are numbered as follows...

Now, assuming you want 1-indexed (so you assigned a value of 1 to first cell)

• row number is to_int(floor(to_int(idd-1)/15))
• column number is (idd-1)%15, which we use to convert to a character

the following single expression should work

char(65+(to_int(floor(to_int(idd-1)/15)))) || substr('0123456789ABCDEF', ((idd-1)%15)+2,1) 

that turned out to be more complex than I thought, largely because the divide is a floating point operation, rather than an integer division (like // in python)

example...

Note that the definition of substr changed in QGIS 2.14 so this will need to be tweaked for older versions of QGIS.

Answer 2

Based on my approach I found another solution, which I want to contribute. So far I used the field calculator to create a new field for the index. As I realized I might need to do this task more than one time, I used Visual Basic to write the function based on the count of columns and fields.

The mathematic function for the column number is a simple linear function.

ID = column + sum of columns * row

Column = ID - sum of columns * row

The indexed vector grid will look like this (it's a cropped image, the index starts with A1):

Here you can find the code for excel:

Private Sub CommandButton1_Click()

Dim Feld As Integer
Feld = 0
Dim code As String
code = ""
Dim Zeile As Integer
Zeile = 0
Dim Bez As String
Bez = ""
Dim Bez2 As String
Bez2 = ""
Dim B1 As Integer
B1 = 0
Dim B2 As Integer
B2 = 0
Dim B3 As Integer
B3 = 0
Dim BezEnd As String
Dim SA As Integer
SA = 0
Dim ZI As Integer
ZI = 0

TextBox1.Value = ""

For Zeile = 1 To ActiveSheet.Cells(3, 2) 'write first part: CASE, WHEN, ELSE
    
    code = code + "CASE" & vbNewLine & "WHEN ID>" & Feld - 1 & " AND ID<" & Feld + Tabelle1.Cells(2, 2) & " THEN concat('"
    Feld = Feld + ActiveSheet.Cells(2, 2)
    If B1 = 26 Then 'count the alphabet from A to ZZZ
        B1 = 0
        B2 = B2 + 1
        Bez = Bez2 & Chr(64 + B2)
        If B2 = 26 Then
            B2 = 0
            B3 = B3 + 1
            Bez2 = Chr(64 + B3)
        End If
    End If
    B1 = B1 + 1
    BezEnd = Bez & Chr(64 + B1)
    
    'define column
    SA = ActiveSheet.Cells(2, 2)
         
    code = code + BezEnd & "',@row_number-" & SA & Chr(42) & ZI & ")"
    ZI = ZI + 1
    
    If Zeile <> ActiveSheet.Cells(3, 2) Then
        code = code & vbNewLine & "ELSE" & vbNewLine
    End If
    
Next

Zeile = 1 'reset row

For Zeile = 1 To ActiveSheet.Cells(3, 2) 'END
    code = code & vbNewLine & "END"
Next

TextBox1.Text = code 'write into TextBox
End Sub