This winter is anomalously warm in Europe, there is no snow and no New Year mood. So today we will be drawing a snowflake in PostgreSQL.

#1. A little theory

Core of a snowflake is six large symmetrical ice crystals growing from the common center. Out of these larger crystals other, smaller, crystals grow.

The overall shape of the snowflake is defined by how large do crystals grow and where exactly are they attached to each other.

These things are defined by fluctuations in air and temperature conditions around the snowflake. Because the flake itself is very small, in any given moment the conditions are nearly identical around each crystal, that's why the offspring crystals pop up in almost same places and grow to almost same lengths. Different flakes, though, constantly move to and from each other and are subject to very different fluctuations, and that's why they grow so unique.

Except for the root crystals (of which there are six), the child icicles grow in symmetrical pairs. More than that, each branch grows their own children (also in pairs), so on each step there are twice as many crystals, but they all share almost same length and angle. This gives the snowflake its symmetrical look.

So we can easily see that, despite the fact there may be many child crystals, the shape of a snowflake is defined by a relatively small number of parameters: how many children each crystal produces, where are they attached to it, at which angle they grow and to which length.

Now, let's try to model it.

#2. Defining parameters

To begin with, we will assume the length of the initial larger crystal as 1, and its angle as 0. Later we may easily scale and rotate the crystal.

There can be any number of child crystals (or, rather, pairs of child crystals), each pair attaching to its parent in a random place and growing at random angle. However, this places and angles, though random, are shared across the snowflake: if one pair grows here and at this angle, the similar pairs will grow on the same place and at the same angle on their parents.

This means that there are 6 * (2 ^ level) twins of each crystal, where level is the number of its ancestors. There are 6 root crystals, 12 first-level crystals in the first first-level pairs (each growing in the same place at the same angle to the same length on root crystals), 12 first-level crystals in the second first-level pairs (again, sharing all parameters with themselves but not with the first pairs), 24 first-first grandchildren, 24 first-second grandchildren etc.

So, to define the snowflake shape, we need to define the number of pairs on each step, and for each pair define its place on the parent crystal, angle and length.

These parameters are random but have some constraints. Child crystals are shorter than their parents; they grow at sharp angles, and the number of pairs is limited. Since the parameters of each crystal depend on its parent, we would need a recursive query for that:

Here, id is an array defining the path to the crystal pairs: {0} is the root branch, {0, 0} is the first child pair, {0, 1, 0} is the first child of the second child etc.

cut defines the position where the crystals are attached to the parent: 0.356672627385706 in the second row means the first child will grow at 35.6% of the root branches' length.

len is the child length (in respect to the root branch, not immediate parent), angle is the angle at which they grow (in respect to the immediate parent), and spikes is the number of children pairs for the given crystal. level, I hope, is self-explanatory.

#3. Defining crystal coordinates

Now that we have the shape of our flake defined we need to build the actual coordinates for each crystal. To do this we again would need a recursive query.

First, we should make a set of branches on each level. We would start from a single root branch (it will be easy to clone it later) and generate a set of branches on each level. Each record in this set would correspond to an actual snowflake crystal:

This is just a copy of the parameters recordset with each parameter duplicated as many times as the crystal level requires. There is one root branch, two copies of level 1 branches, four copies of level 2 branches etc. Each instance is defined by the parameter branch.

To calculate coordinates of each crystal we need to traverse to it from the top.

The coordinates of the root branch are known: they are (0, 0), (1, 0) (by definition).

To build the first pair (two crystals, each 28.5% long, growing at 35.6% from the beginning of the root branch at angles 51° 18' and −51° 18', respectively) we would need to take the coordinates of the parent, find the start point (it would be (Xs + (Xe - Xs) * cut, Ys + (Ye - Ys) * cut) and then the coordinates of the end point (by adding len * COS(α) and len * SIN(α) to X and Y of the start point, respectively). α in this formula is in respect to the coordinate grid, not to the parent, and to find it we just need to sum all angles from the ancestors.

This is best done with another recursive query. On each step we should find immediate children of the current parent.

This can be easily achieved using the following join condition: child.id > parent.id AND child.id <= parend.id || spikes AND ARRAY_LENGTH(child.level, 1) = ARRAY_LENGTH(parent.level, 1) + 1. This condition employs PostgreSQL array arithmetics: if we have parent.id = {0, 2, 1} with 3 children (spikes = 2), then the condition would return {0, 2, 1, 0}, {0, 2, 1, 1} and {0, 2, 1, 2}. This hierarchy model is called materialized path, and some day I will write a post about it.

Since the id alone defines the parameters, not the instance, we need to add once more condition to find the instances. Crystals 0 and 1 would have child pairs 0, 1 and 2, 3, respectively, so we'll include child.branch BETWEEN p.branch * 2 AND child.branch * 2 + 1 into the join condition.

One more thing to do is to find whether we should add positive or negative angle. It's simple: even branches are negative, odd ones are positive.

#4. Visualizing

The most tedious part about SQL graphics is visualizing them. To do this, we'll employ PostgreSQL's geometrical functions.

Each crystal can be represented as a path between its start and end points. This can be used by constructing a line segment (LSEG(POINT, POINT)) using two point constructors (POINT(DOUBLE PRECISION, DOUBLE PRECISION)) and converting it to a path. Unfortunately, PostgreSQL does not allow direct lseg to path conversion but the latter can be easily constructed from the TEXT representation of an lseg.

We have six root branches so each crystal should be cloned to make six copies. It's easiest to make it by using PATH * POINT operator: it rotates and scales the PATH around (0, 0) so that (1, 0) becomes POINT. To construct the points, we will generate six rotation angles with step of 60° and will multiply the path by POINT(COS(α), SIN(α)). These multiplications will preserve lengths.

Finally we need to actually display the snowflake on the screen. To do this, we will generate a set of 80 × 80 records(x and y), defining the grid from (-1, -1) to (1, 1) with step of 1/40 units. Then we'll see if there is at least one crystal within distance of 1/40 units from each cell on the grid (using POINT PATH distance operator and EXISTS). If there is, we will return a number sign (#) for this cell, otherwise a space.

Then we'll group the cells by lines (y), concatenate columns (using ARRAY_AGG and ARRAY_TO_STRING) and output the lines.

I recently started working with PostgreSQL and randomly stumbled onto your site. I recognized your name from when I was active on StackOverflow. Just wanted to say hi. Also, the “snowflake test” will be added to my interviewing repertoire. You are a wizard.