Generating row and column IDs for a hexagon grid

Recently when working on a project that uses a hexagon grid, I used the awesome mmqgis plugin to QGIS to produce the grid. The one small shortcoming with the tool is that I would prefer to have a cell identifier that is somehow related back to the position of the cell in the grid. I wanted an ID similar to a cartesian grid, specifying cells by row, then column. An integer “object id” wasn’t good enough.

The mmqgis plugin produces the grid according to your specifications as a shapefile in the current map projection. The plugin does not, for some reason, writes out an empty .prj file. I moved it into PostgreSQL using og2ogr and explicitly defined the projection (in this case, Web Mercator – 3857) while doing so.

$ ogr2ogr --config PG_USE_COPY YES 
    -f PGDump hex.sql hex.shp 
    -lco GEOMETRY_NAME=shape 
    -lco FID=objectid 
    -s_srs "EPSG:3857" 
    -t_srs "EPSG:3857”

I then began to work on creating my ID column. First, I would need to calculate a sequential number for both the row and the column.

ALTER TABLE hex ADD COLUMN rowid INT;
ALTER TABLE hex ADD COLUMN colid INT;

I would then need a sequence to generate the series of numbers used to count off the rows and columns.

CREATE TEMPORARY SEQUENCE idxid START WITH 1;

Now, I can use an update and a self-join to come up with a list of numbers for each column of hexagons. Hexagons are identified based on the integer value of the X coordinate of the cell’s center point. Joining the table to itself allows me to use the center points as the join criteria on a select distinct of centroid X values.

UPDATE hex SET colid = a.columnid
FROM (SELECT nextval('idxid') as columnid, b.centroidint
  FROM (SELECT DISTINCT ST_X(ST_CENTROID(shape))::int as centroidint 
    FROM hex 
    ORDER BY 1 ASC) b
  ) a
WHERE a.centroidint = ST_X(ST_CENTROID(shape))::int;

After calculating all of the column values, I restart the sequence and calculate the row values.

ALTER SEQUENCE idxid RESTART WITH 1;

UPDATE hex SET rowid = a.rowid
FROM (SELECT nextval('idxid') as rowid, b.centroidint
  FROM (SELECT DISTINCT ST_Y(ST_CENTROID(shape))::int as centroidint 
    FROM hex
    ORDER BY 1 ASC) b
  ) a
WHERE a.centroidint = ST_Y(ST_CENTROID(shape))::int;

Once I have a row and a column number for every cell, calculating the field is a straightforward update using concatenate.

ALTER TABLE hex ADD COLUMN hexid CHARACTER VARYING (10);
UPDATE hex SET hexid = 'r'||rowid::text||'c'||colid::text;

While writing this, I started to think about how I would go about doing this using ArcGIS. It was relatively quick to do – about 10 minutes from “I could use a different ID column” to the column being calculated. I am still not sure how I would do it using Arc. If you know a way to do this in ArcGIS, I’d love to hear about it – please leave a comment below.
You don’t have to do all of your GIS work in a database, but know that it can make life easier in many cases.