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.