Originally from: tweet, LinkedIn post.
I post a new PostgreSQL "howto" article every day. Join me in this journey – subscribe, provide feedback, share!
This original idea and implementation was done by Kirill Borovikov (kilor) – I just reformatted and slightly tuned it, extending the character set.
Here is the query:
with recursive t as (
select
0 as x,
0 as y,
'{"{0,0}"}'::text[] as c,
0 as i
union all
(
with z as (
select
dn.x,
dn.y,
t.c,
t.i
from t,
lateral (
select
((random() * 2 - 1) * 100)::integer as x,
((random() * 2 - 1) * 100)::integer as y
) as p,
lateral (
select *
from (
select
(unnest::text[])[1]::integer as x,
(unnest::text[])[2]::integer as y
from unnest(t.c::text[])
) as t
order by sqrt((x - p.x) ^ 2 + (y - p.y) ^ 2)
limit 1
) as n,
lateral (
select
n.x + dx as x,
n.y + dy as y
from
generate_series(-1, 1) as dx,
generate_series(-1, 1) as dy
where (dx, dy) <> (0, 0)
order by
case
when (p.x, p.y) = (n.x, n.y) then 0
else abs(
acos(
((p.x - n.x) * dx + (p.y - n.y) * dy)
/ sqrt((p.x - n.x) ^ 2 + (p.y - n.y) ^ 2)
/ sqrt(dx ^ 2 + dy ^ 2)
)
)
end
limit 1
) as dn
)
select
z.x,
z.y,
z.c || array[z.x, z.y]::text,
z.i + 1
from z
where z.i < (1 << 10)
)
),
map as (
select
gx as x,
gy as y,
(
select sqrt((gx - T.x) ^ 2 + (gy - T.y) ^ 2) v
from t
order by v
limit 1
) as v
from
generate_series(-40, 40) as gx,
generate_series(-30, 30) as gy
),
gr as (
select regexp_split_to_array('@%#*+=-:. ', '') as s
)
select
string_agg(
coalesce(s[(v * (array_length(s, 1) - 1))::integer + 1], ' '),
' '
order by x
) as frozen
from
(
select
x,
y,
v::double precision / max(v) over() as v
from map
) as t,
gr
group by y
order by y;Every time it draws a new frozen pattern, here are several examples:
Happy holiday season 🎅