Happy New Year: Settlers of Catan in SQL
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.
Today, we'll be implementing the Almanac Variable Catan setup using SQL.
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.
We will create a small CTE to define the coordinates of each tile:
WITH tiles (rn, x, y) AS
(
VALUES
(1, 0, 0),
(2, 1, 0),
(3, 2, 0),
(4, 3, 1),
(5, 4, 2),
(6, 4, 3),
(7, 4, 4),
(8, 3, 4),
(9, 2, 4),
(10, 1, 3),
(11, 0, 2),
(12, 0, 1),
(13, 1, 1),
(14, 2, 1),
(15, 3, 2),
(16, 3, 3),
(17, 2, 3),
(18, 1, 2),
(19, 2, 2)
)
SELECT *
FROM tiles
| rn | x | y |
|---|---|---|
| 1 | 0 | 0 |
| 2 | 1 | 0 |
| 3 | 2 | 0 |
| 4 | 3 | 1 |
| 5 | 4 | 2 |
| 6 | 4 | 3 |
| 7 | 4 | 4 |
| 8 | 3 | 4 |
| 9 | 2 | 4 |
| 10 | 1 | 3 |
| 11 | 0 | 2 |
| 12 | 0 | 1 |
| 13 | 1 | 1 |
| 14 | 2 | 1 |
| 15 | 3 | 2 |
| 16 | 3 | 3 |
| 17 | 2 | 3 |
| 18 | 1 | 2 |
| 19 | 2 | 2 |
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():
WITH tiles (rn, x, y) AS
(
VALUES
(1, 0, 0),
(2, 1, 0),
(3, 2, 0),
(4, 3, 1),
(5, 4, 2),
(6, 4, 3),
(7, 4, 4),
(8, 3, 4),
(9, 2, 4),
(10, 1, 3),
(11, 0, 2),
(12, 0, 1),
(13, 1, 1),
(14, 2, 1),
(15, 3, 2),
(16, 3, 3),
(17, 2, 3),
(18, 1, 2),
(19, 2, 2)
),
resources AS
(
SELECT '////####^^^' || CHR(34) || CHR(34) || CHR(34) || CHR(34) || '... '::TEXT tile_resources
)
SELECT *
FROM tiles
JOIN (
SELECT ROW_NUMBER() OVER (ORDER BY RANDOM()) rn,
resource
FROM resources
CROSS JOIN
LATERAL
REGEXP_SPLIT_TO_TABLE(tile_resources, '') q (resource)
) tr
USING (rn)
| rn | x | y | resource |
|---|---|---|---|
| 1 | 0 | 0 | ^ |
| 2 | 1 | 0 | # |
| 3 | 2 | 0 | |
| 4 | 3 | 1 | # |
| 5 | 4 | 2 | # |
| 6 | 4 | 3 | " |
| 7 | 4 | 4 | " |
| 8 | 3 | 4 | / |
| 9 | 2 | 4 | . |
| 10 | 1 | 3 | " |
| 11 | 0 | 2 | ^ |
| 12 | 0 | 1 | . |
| 13 | 1 | 1 | # |
| 14 | 2 | 1 | ^ |
| 15 | 3 | 2 | / |
| 16 | 3 | 3 | / |
| 17 | 2 | 3 | " |
| 18 | 1 | 2 | / |
| 19 | 2 | 2 | . |
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.
Here's the recursive query:
SELECT SETSEED(0.201703);
WITH RECURSIVE
resources AS
(
SELECT '////####^^^' || CHR(34) || CHR(34) || CHR(34) || CHR(34) || '... '::TEXT tile_resources
),
tiles (rn, x, y) AS
(
VALUES
(1, 0, 0),
(2, 1, 0),
(3, 2, 0),
(4, 3, 1),
(5, 4, 2),
(6, 4, 3),
(7, 4, 4),
(8, 3, 4),
(9, 2, 4),
(10, 1, 3),
(11, 0, 2),
(12, 0, 1),
(13, 1, 1),
(14, 2, 1),
(15, 3, 2),
(16, 3, 3),
(17, 2, 3),
(18, 1, 2),
(19, 2, 2)
),
layout AS
(
SELECT *,
CASE resource
WHEN ' ' THEN
NULL
ELSE
rn + SUM(CASE resource WHEN ' ' THEN -1 ELSE 0 END) OVER (ORDER BY rn)
END score_rn
FROM tiles
JOIN (
SELECT ROW_NUMBER() OVER (ORDER BY RANDOM()) rn,
resource
FROM resources
CROSS JOIN
LATERAL
REGEXP_SPLIT_TO_TABLE(tile_resources, '') q (resource)
) tr
USING (rn)
),
score AS
(
SELECT 1 attempt,
ARRAY_AGG(s ORDER BY RANDOM()) score_array,
NULL::BIGINT desert
FROM generate_series(2, 12) s
CROSS JOIN
generate_series(1, 2) r
WHERE s <> 7
AND NOT (r = 2 AND s IN (2, 12))
UNION ALL
SELECT attempt + 1 attempt,
sa.score_array,
(
SELECT rn
FROM layout
WHERE score_rn IS NULL
) desert
FROM (
SELECT *
FROM score
WHERE EXISTS
(
SELECT NULL
FROM (
SELECT *
FROM UNNEST(score_array) WITH ORDINALITY q(s1, score_rn)
JOIN layout
USING (score_rn)
) sc1
JOIN (
SELECT *
FROM UNNEST(score_array) WITH ORDINALITY q(s2, score_rn)
JOIN layout
USING (score_rn)
) sc2
ON s1 IN (6, 8)
AND s2 IN (6, 8)
AND ((sc1.x - sc2.x), (sc1.y - sc2.y)) IN ((-1, -1), (-1, 0), (0, -1), (0, 1), (1, 0), (1, 1))
)
) s
CROSS JOIN
LATERAL
(
SELECT ARRAY_AGG(score ORDER BY RANDOM()) score_array
FROM UNNEST(score_array) WITH ORDINALITY q(score, score_rn)
) sa
),
score_good AS
(
SELECT score, score_rn
FROM (
SELECT *
FROM score
ORDER BY
attempt DESC
LIMIT 1
) s
CROSS JOIN
LATERAL
UNNEST(score_array) WITH ORDINALITY q (score, score_rn)
)
SELECT *
FROM score_good
| score | score_rn |
|---|---|
| 8 | 1 |
| 2 | 2 |
| 6 | 3 |
| 10 | 4 |
| 11 | 5 |
| 11 | 6 |
| 9 | 7 |
| 3 | 8 |
| 4 | 9 |
| 8 | 10 |
| 9 | 11 |
| 4 | 12 |
| 12 | 13 |
| 5 | 14 |
| 10 | 15 |
| 5 | 16 |
| 3 | 17 |
| 6 | 18 |
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:
SELECT SETSEED(0.201703);
WITH RECURSIVE
resources AS
(
SELECT '////####^^^' || CHR(34) || CHR(34) || CHR(34) || CHR(34) || '... '::TEXT tile_resources
),
tiles (rn, x, y) AS
(
VALUES
(1, 0, 0),
(2, 1, 0),
(3, 2, 0),
(4, 3, 1),
(5, 4, 2),
(6, 4, 3),
(7, 4, 4),
(8, 3, 4),
(9, 2, 4),
(10, 1, 3),
(11, 0, 2),
(12, 0, 1),
(13, 1, 1),
(14, 2, 1),
(15, 3, 2),
(16, 3, 3),
(17, 2, 3),
(18, 1, 2),
(19, 2, 2)
),
layout AS
(
SELECT *,
CASE resource
WHEN ' ' THEN
NULL
ELSE
rn + SUM(CASE resource WHEN ' ' THEN -1 ELSE 0 END) OVER (ORDER BY rn)
END score_rn
FROM tiles
JOIN (
SELECT ROW_NUMBER() OVER (ORDER BY RANDOM()) rn,
resource
FROM resources
CROSS JOIN
LATERAL
REGEXP_SPLIT_TO_TABLE(tile_resources, '') q (resource)
) tr
USING (rn)
),
score AS
(
SELECT 1 attempt,
ARRAY_AGG(s ORDER BY RANDOM()) score_array,
NULL::BIGINT desert
FROM generate_series(2, 12) s
CROSS JOIN
generate_series(1, 2) r
WHERE s <> 7
AND NOT (r = 2 AND s IN (2, 12))
UNION ALL
SELECT attempt + 1 attempt,
sa.score_array,
(
SELECT rn
FROM layout
WHERE score_rn IS NULL
) desert
FROM (
SELECT *
FROM score
WHERE EXISTS
(
SELECT NULL
FROM (
SELECT *
FROM UNNEST(score_array) WITH ORDINALITY q(s1, score_rn)
JOIN layout
USING (score_rn)
) sc1
JOIN (
SELECT *
FROM UNNEST(score_array) WITH ORDINALITY q(s2, score_rn)
JOIN layout
USING (score_rn)
) sc2
ON s1 IN (6, 8)
AND s2 IN (6, 8)
AND ((sc1.x - sc2.x), (sc1.y - sc2.y)) IN ((-1, -1), (-1, 0), (0, -1), (0, 1), (1, 0), (1, 1))
)
) s
CROSS JOIN
LATERAL
(
SELECT ARRAY_AGG(score ORDER BY RANDOM()) score_array
FROM UNNEST(s.score_array) WITH ORDINALITY q(score, score_rn)
) sa
),
score_good AS
(
SELECT score, score_rn, attempt
FROM (
SELECT *
FROM score
ORDER BY
attempt DESC
LIMIT 1
) s
CROSS JOIN
LATERAL
UNNEST(score_array) WITH ORDINALITY q (score, score_rn)
)
SELECT *
FROM layout
LEFT JOIN
score_good
USING (score_rn)
ORDER BY
rn;
| score_rn | rn | x | y | resource | score | attempt |
|---|---|---|---|---|---|---|
| 1 | 1 | 0 | 0 | # | 11 | 5 |
| 2 | 2 | 1 | 0 | " | 11 | 5 |
| 3 | 3 | 2 | 0 | . | 8 | 5 |
| 4 | 4 | 3 | 1 | # | 10 | 5 |
| 5 | 5 | 4 | 2 | / | 8 | 5 |
| 6 | 6 | 4 | 3 | " | 3 | 5 |
| 7 | 7 | 4 | 4 | ^ | 3 | 5 |
| None | 8 | 3 | 4 | None | None | |
| 8 | 9 | 2 | 4 | / | 4 | 5 |
| 9 | 10 | 1 | 3 | / | 5 | 5 |
| 10 | 11 | 0 | 2 | # | 9 | 5 |
| 11 | 12 | 0 | 1 | # | 6 | 5 |
| 12 | 13 | 1 | 1 | " | 9 | 5 |
| 13 | 14 | 2 | 1 | ^ | 4 | 5 |
| 14 | 15 | 3 | 2 | . | 2 | 5 |
| 15 | 16 | 3 | 3 | ^ | 5 | 5 |
| 16 | 17 | 2 | 3 | " | 10 | 5 |
| 17 | 18 | 1 | 2 | / | 12 | 5 |
| 18 | 19 | 2 | 2 | . | 6 | 5 |
We have all the data for the resource tiles now.
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):
WITH RECURSIVE
resources AS
(
SELECT '////####^^^' || CHR(34) || CHR(34) || CHR(34) || CHR(34) || '... '::TEXT tile_resources,
'/#^' || CHR(34) || '.????'::TEXT harbor_resources
),
harbors (rn, x, y, pier1, pier2) AS
(
VALUES
(1, -1, -1, 0, 1),
(2, 1, -1, 1, 2),
(3, 3, 0, 1, 2),
(4, 5, 2, 2, 3),
(5, 5, 4, 3, 4),
(6, 4, 5, 3, 4),
(7, 2, 5, 4, 5),
(8, 0, 3, 5, 0),
(9, -1, 1, 5, 0)
),
harbor_resources AS
(
SELECT '/#>".????'::TEXT harbor_resources
)
SELECT resource, rn, x, y, pier1, pier2
FROM harbors
CROSS JOIN
resources
JOIN LATERAL
(
SELECT resource, ROW_NUMBER() OVER (ORDER BY RANDOM()) rn
FROM REGEXP_SPLIT_TO_TABLE(harbor_resources, '') q (resource)
) q
USING (rn)
ORDER BY
RANDOM()
| resource | rn | x | y | pier1 | pier2 |
|---|---|---|---|---|---|
| # | 3 | 3 | 0 | 1 | 2 |
| " | 5 | 5 | 4 | 3 | 4 |
| ? | 1 | -1 | -1 | 0 | 1 |
| ? | 8 | 0 | 3 | 5 | 0 |
| ^ | 4 | 5 | 2 | 2 | 3 |
| / | 9 | -1 | 1 | 5 | 0 |
| ? | 7 | 2 | 5 | 4 | 5 |
| ? | 6 | 4 | 5 | 3 | 4 |
| . | 2 | 1 | -1 | 1 | 2 |
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.
Here's what we got:
SELECT SETSEED(0.201704);
WITH RECURSIVE
resources AS
(
SELECT '////####^^^' || CHR(34) || CHR(34) || CHR(34) || CHR(34) || '... '::TEXT tile_resources,
'/#^' || CHR(34) || '.????'::TEXT harbor_resources
),
tiles (rn, x, y) AS
(
VALUES
(1, 0, 0),
(2, 1, 0),
(3, 2, 0),
(4, 3, 1),
(5, 4, 2),
(6, 4, 3),
(7, 4, 4),
(8, 3, 4),
(9, 2, 4),
(10, 1, 3),
(11, 0, 2),
(12, 0, 1),
(13, 1, 1),
(14, 2, 1),
(15, 3, 2),
(16, 3, 3),
(17, 2, 3),
(18, 1, 2),
(19, 2, 2)
),
harbors (rn, x, y, pier1, pier2) AS
(
VALUES
(1, -1, -1, 0, 1),
(2, 1, -1, 1, 2),
(3, 3, 0, 1, 2),
(4, 5, 2, 2, 3),
(5, 5, 4, 3, 4),
(6, 4, 5, 3, 4),
(7, 2, 5, 4, 5),
(8, 0, 3, 5, 0),
(9, -1, 1, 5, 0)
),
score AS
(
SELECT 1 attempt,
ARRAY_AGG(s ORDER BY RANDOM()) score_array
FROM generate_series(2, 12) s
CROSS JOIN
generate_series(1, 2) r
WHERE s <> 7
AND NOT (r = 2 AND s IN (2, 12))
UNION ALL
SELECT attempt + 1 attempt,
sa.score_array
FROM (
SELECT *
FROM score
WHERE EXISTS
(
SELECT NULL
FROM (
SELECT *
FROM UNNEST(score_array) WITH ORDINALITY q(s1, rn)
JOIN tiles
USING (rn)
) sc1
JOIN (
SELECT *
FROM UNNEST(score_array) WITH ORDINALITY q(s2, rn)
JOIN tiles t
USING (rn)
) sc2
ON s1 IN (6, 8)
AND s2 IN (6, 8)
AND ((sc1.x - sc2.x), (sc1.y - sc2.y)) IN ((-1, -1), (-1, 0), (0, -1), (0, 1), (1, 0), (1, 1))
)
) s
CROSS JOIN
LATERAL
(
SELECT ARRAY_AGG(score ORDER BY RANDOM()) score_array
FROM UNNEST(score_array) WITH ORDINALITY q(score, score_rn)
) sa
),
score_good AS
(
SELECT score, score_rn
FROM (
SELECT *
FROM score
ORDER BY
attempt DESC
LIMIT 1
) s
CROSS JOIN
LATERAL
UNNEST(score_array) WITH ORDINALITY q (score, score_rn)
),
layout AS
(
SELECT *
FROM (
SELECT *,
CASE resource
WHEN ' ' THEN
NULL
ELSE
rn + SUM(CASE resource WHEN ' ' THEN -1 ELSE 0 END) OVER (ORDER BY rn)
END score_rn
FROM tiles
JOIN (
SELECT ROW_NUMBER() OVER (ORDER BY RANDOM()) rn,
resource
FROM resources
CROSS JOIN
LATERAL
REGEXP_SPLIT_TO_TABLE(tile_resources, '') q (resource)
) tr
USING (rn)
) t
LEFT JOIN
score_good
USING (score_rn)
ORDER BY
rn
)
SELECT row
FROM (
SELECT r,
STRING_AGG(COALESCE(letter, ' '), '' ORDER BY c) AS row
FROM generate_series(0, 70) r
CROSS JOIN
generate_series(0, 89) c
LEFT JOIN
(
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY r, c ORDER BY layer DESC) rn
FROM (
SELECT 10 height,
16 width
) d
CROSS JOIN
LATERAL
(
SELECT letter, r, c, layer
FROM layout
CROSS JOIN
LATERAL
(
SELECT height * x + 15 center_r,
width * y - (width / 2)::INT * x + 24 center_c
) c
CROSS JOIN
LATERAL
(
SELECT *
FROM (
SELECT 1 layer, resource letter, center_r + rs r, center_c + cs c
FROM (
SELECT height * 1.5 * 0.8 th, width * 0.9 tw
) t
CROSS JOIN
generate_series(-(th / 2)::INT, (th / 2)::INT) rs
CROSS JOIN
generate_series(-(tw / 2)::INT, (tw / 2)::INT ) cs
CROSS JOIN
LATERAL
(
SELECT rs::FLOAT / th rsf, cs::FLOAT / tw csf
) f
WHERE rsf BETWEEN -0.25 AND 0.25
OR
ABS(csf) BETWEEN 0 AND 1 - ABS(rsf * 2)
UNION ALL
SELECT 2 layer, ' ', center_r + rs r, center_c + cs c
FROM (
SELECT height * 1.5 * 0.35 th, width * 0.35 tw
) t
CROSS JOIN
generate_series(-(th / 2)::INT, (th / 2)::INT) rs
CROSS JOIN
generate_series(-(tw / 2)::INT, (tw / 2)::INT ) cs
CROSS JOIN
LATERAL
(
SELECT rs::FLOAT / th rsf, cs::FLOAT / tw csf
) f
WHERE rsf BETWEEN -0.25 AND 0.25
OR
ABS(csf) BETWEEN 0 AND 1 - ABS(rsf * 2)
UNION ALL
SELECT 3 layer, score_letter letter, center_r r, center_c + pos - 1 c
FROM REGEXP_SPLIT_TO_TABLE(score::TEXT, '') WITH ORDINALITY l(score_letter, pos)
) q
) q2
UNION ALL
SELECT letter, r, c, 4 layer
FROM harbors
JOIN LATERAL
(
SELECT resource, ROW_NUMBER() OVER (ORDER BY RANDOM()) rn
FROM resources
CROSS JOIN
LATERAL
REGEXP_SPLIT_TO_TABLE(harbor_resources, '') q (resource)
) q2
USING (rn)
CROSS JOIN
LATERAL
(
SELECT height * x + 15 center_r,
width * y - (width / 2)::INT * x + 25 center_c
) c
CROSS JOIN
LATERAL
(
SELECT resource letter, center_r r, center_c c
UNION ALL
SELECT letter, r, c
FROM (
SELECT pier1
UNION ALL
SELECT pier2
) p (pier)
CROSS JOIN
LATERAL
(
SELECT SUBSTRING('|\/|\/', (pier + 1), 1) letter,
center_r + ((ARRAY[0.4, 0.2, -0.2, -0.4, -0.2, 0.2])[pier + 1] * height * 1.5 * 0.8)::INT r,
center_c + ((ARRAY[0, 0.3, 0.3, 0, -0.3, -0.3])[pier + 1] * width * 0.9)::INT c
) pl
) p2
) q3
) l
WHERE rn = 1
) t
USING (r, c)
GROUP BY
r
) q
ORDER BY
r
| row |
|---|
| . ^ |
| \ / |
| / . " |
| | ///// ..... | """"" |
| ///////// ......... """"""""" |
| /////////////// ............... """"""""""""""" |
| ////// ////// ...... ...... """""" """""" |
| //// //// .... .... """" """" |
| //// 5 //// .... 11 .... """" 8 """" " |
| //// //// .... .... """" """" |
| ////// ////// ...... ...... """""" """""" / |
| /////////////// ............... """"""""""""""" |
| " ///////// " ......... ^ """"""""" " |
| """"" ///// """"" ..... ^^^^^ """"" """"" | |
| """"""""" / """"""""" . ^^^^^^^^^ " """"""""" |
| """"""""""""""" """"""""""""""" ^^^^^^^^^^^^^^^ """"""""""""""" |
| / """""" """""" """""" """""" ^^^^^^ ^^^^^^ """""" """""" |
| """" """" """" """" ^^^^ ^^^^ """" """" |
| ? """" 9 """" """" 5 """" ^^^^ 6 ^^^^ """" 4 """" |
| """" """" """" """" ^^^^ ^^^^ """" """" |
| \ """""" """""" """""" """""" ^^^^^^ ^^^^^^ """""" """""" |
| """"""""""""""" """"""""""""""" ^^^^^^^^^^^^^^^ """"""""""""""" |
| ^ """"""""" """"""""" / ^^^^^^^^^ . """"""""" # |
| ^^^^^ """"" """"" ///// ^^^^^ ..... """"" ##### |
| ^^^^^^^^^ " " ///////// ^ ......... " ######### |
| ^^^^^^^^^^^^^^^ /////////////// ............... ############### |
| ^^^^^^ ^^^^^^ ////// ////// ...... ...... ###### ###### \ |
| ^^^^ ^^^^ //// //// .... .... #### #### |
| ^^^^ 11 ^^^^ //// 3 //// .... 9 .... #### 12 #### ? |
| ^^^^ ^^^^ //// //// .... .... #### #### |
| ^^^^^^ ^^^^^^ ////// ////// ...... ...... ###### ###### / |
| ^^^^^^^^^^^^^^^ /////////////// ............... ############### |
| ^^^^^^^^^ # / ///////// # ......... # ######### |
| ^^^^^ ##### ///// ///// ##### ..... ##### ##### |
| ^ ######### ///////// / ######### . ######### # |
| ############### /////////////// ############### ############### |
| / ###### ###### ////// ////// ###### ###### ###### ###### |
| #### #### //// //// #### #### #### #### |
| / #### 6 #### //// 10 //// #### 4 #### #### 2 #### |
| #### #### //// //// #### #### #### #### |
| \ ###### ###### ////// ////// ###### ###### ###### ###### |
| ############### /////////////// ############### ############### |
| ######### / ///////// . ######### ^ ######### |
| ##### ///// ///// ..... ##### ^^^^^ ##### | |
| # ///////// / ......... # ^^^^^^^^^ # |
| /////////////// ............... ^^^^^^^^^^^^^^^ |
| ////// ////// ...... ...... ^^^^^^ ^^^^^^ \ |
| //// //// .... .... ^^^^ ^^^^ |
| //// 10 //// .... 8 .... ^^^^ 3 ^^^^ # |
| //// //// .... .... ^^^^ ^^^^ |
| ////// ////// ...... ...... ^^^^^^ ^^^^^^ |
| /////////////// ............... ^^^^^^^^^^^^^^^ |
| ///////// ......... ^^^^^^^^^ |
| | ///// ..... | ^^^^^ |
| / . ^ |
| / \ |
| ? ? |
You can view the queries here: https://github.com/quassnoi/explain-extended-2018
Let this New Year bring you the joy of playing with your friends and family!
Previous New Year posts:
Subscribe in a reader
saddiction
Phillipwot
9 Oct 19 at 15:11