One of the best New Year presents I've ever got was a copy of the German-style board game, The Settlers of Catan.

This game has brought me and my friends many an hour of good entertainment.

The game is played on a hexagon field with 19 hexagon tiles (3 + 4 + 5 + 4 + 3), which have to be randomly put into appropriate places. In addition, 18 of those tiles have a score token on it, which has to be put there, also randomly, albeit with some limitations. Finally, 9 more pieces (harbors) have to be randomly put to their places, which are printed on the game field.

Tiles

We will be using trapezoidal encoding for the tiles, so that each tile is defined by its position on two axes at 60° to each other, starting at the NW corner. The x axis is diagonal (SW to NE), the y axis is horizontal. The hexagons are pointy topped.

Resources

We'll be using / for wheat, # for wood, > for rock, " for sheep and . for clay. For the desert we will be using empty space. Those characters are not that descriptive but they make good distinct ASCII art patterns.

We will just put all available resources into a string then shuffle the string using ORDER BY RANDOM():

Scores

Each tile (except the desert) should have a score token on it. The scores are from 2 to 12 (those two dice can yield). Score 7 is not used, there are one token each for the scores 2 to 12 and two tokens each for the rest of the scores. This makes 18 tokens in total.

The tricky part is that the rules say the high-score tiles (6 and 8) should not be adjacent to each other. This means that pure random distribution (the one we were using for the resource tiles) would not work here.

The Almanac says: "You may have to swap tokens to ensure that no red numbers are on adjacent hexes". However, the swapping algorithms, unless carefully elaborated, are notorious to introduce bias, so that some layouts might be slightly under-represented while the others are over-represented.

For this reason, if we see a problem with our score layout, we will not try to fix it, and will just generate another one from scratch, until it's good enough.

To do this, we first will generate an array of scores in random order. Then we will unnest it and join with the tiles and resources tables we generated on the previous steps. One tricky part is that we have 19 tiles but 18 scores, so we have to join them skipping the desert tile, so that each score record gets joined with correct tile record. We will work around this with a simple analytic function which skips a number after the desert tile.

Once we have joined tiles and scores, we need to make sure that no 6's or 8's are on adjacent tiles. For this we will make a self-join of the scores and tiles tables, employing some hexagon grid properties.

How do we know two tiles are adjacent? First of all, the have to be within one unit from each other on either axis, meaning that the difference of either coordinate should be from -1 to 1. Second, they (naturally) should not match, that is at least one coordinate should differ between the tiles. Third (and this is how hexagon grids are different from orthogonal grids), while every square on an orthogonal grid has eight neighbors, a hexagon only has six. It means that even if two coordinates are no more than one unit apart, the tiles can still not be neighbors. This is indeed true for tiles (-1, 1) and (1, -1).

So for every tile, the list of its possible neighbors on the grid is (-1, -1), (-1, 0), (0, -1), (0, 1), (1, 0) and (1, 1). The numbers here are of course offsets from the original tile's coordinates. This list is quite concise, so we'll just employ some tuple predicates to make a join condition. We will reject every layout where exist two tiles which are both either 6 or 8, and the differences between the tiles' coordinates make one of the tuples in the list above.

"Rejecting" a layout means we re-shuffle the original scores array and feed it to another iteration of the recursive CTE. Then we rinse and repeat until we get us a good layout.

I've added a call to `SETSEED` above so that the results are reproducible.

We can see that it took the query 7 attempts to generate a valid score layout. The first setup had 6 and 8 scores on adjacent tiles 7 and 16; the second one had two 8's in a row on tiles 17 and 18; etc.

Once we have a valid score layout we can join it to the rest of our tables:

Harbors

Harbors are pretty straightforward: they are completely random and their positions are fixed. We just need to put them into a string, split the string and randomly re-order the resulting table.

Harbors are technically not placed on hexagons, however, they are exactly there where the hexagons' centers would be if there were hexagons outside the main field. We can encode their positions using the grid coordinates as well. We will also add two numbers for the piers orientations (they will help us with the visualization later):

Putting it all together

Right now we have all information we need to run the game. However, it would help to visualize the layout.

We'll draw the hexagons using different characters for different types of resources, and place the score token inside each hexagon. We will also put a smaller empty hexagon inside each large one, so that the score token would be clearly visible. Finally, we will put the harbors and their piers on the map.

To do that, we will need to generate character maps for every piece of information we are about to put on the screen.

For the hexagons we will first generate a square using a cross-join of two GENERATE_SERIES then filter it so as to make it a hexagon.

The centers of each hexagons are calculated from their x and y coordinates: x is taken as is; y is shifted by half the respective x value (remember that our axes are at 60° to each other).

The filtering condition for the hexagon is this: if the character is within -1/4 to +1/4 of height, starting from the center, we output it without further ado; if it's between -1/2 to -1/4 (or 1/4 to 1/2 on the other side), we only output it if its horizontal coordinate is no more than twice the distance from the top or bottom, respectively. The first part forms a rectangle inside the hexagon; the second one forms two triangles on the top and on the bottom.

The smaller hexagons are generated using the same formula, only their total height and width are smaller.

The score is printed inside each hexagon's center. We can't just output it as is the way we do with the resource characters, because some of the scores are two-digit. To deal with this, we should break each score into separate characters and put each character into its own place on the screen.

We also need to do harbors and piers. The harbor resource characters are just placed at the coordinates on the grid (with the above formulas in mind). The piers are shifted by approximate values of sines and cosines of their respective angles (encoded as 60° factors). We do not calculate the sines and cosines directly, and rather use approximation tables.

Some of the resulting characters will overlap: for instance, the center of each tile will contain the character from the larger hexagon, the smaller hexagon and the resource. To work around this, we will assign the layer number to each character. The larger hexagons are on the layer 1, the smaller ones on the layer 2, and, finally, the score character are on the topmost layer 3. If several characters have the same coordinates, the one with the highest layer number wins. We will be doing all of them in a single query, that's why the harbors and piers will need to have the layers too, even if they don't overlap with anything. We will assign them the layer 4.

Once the character maps are ready, the rest is business as usual: we generate the field by cross joining two GENERATE_SERIES together, left join the result with the character maps and replace the NULL values from the left join with spaces. Then we group the characters by rows and output the rows in order.