Skip to content

SQL Queries

The following examples are not mine but this is soooo cool :)

Mandelbrot Set

Source: https://blog.jooq.org/fun-with-postgis-mandelbrot-set-game-of-life-and-more/

With the code below, use different values for "dims" to zoom in, e.g.

dims (r1, r2, i1, i2, s, it, p) as (values (
  (-0.925-0.032)::float, (-0.925+0.032)::float, 
  (0.266-0.032)::float, (0.266+0.032)::float, 
  0.00005::float, 100, 256.0::float)
)
Mandelbrot Set

``` sql WITH RECURSIVE

-- These are the dimensions that you can play around with dims (r1, r2, i1, i2, s, it, p) AS ( VALUES (

  -- The dimensions of the real axis
  -2::float, 1::float,

  -- The dimensions of the imaginary axis
  -1.5::float, 1.5::float,

  -- The step size on each axis, per pixel or sprite
  0.01::float,

  -- The maximum number of iterations
  100,

  -- "Infinity", i.e. when to stop
  256.0::float
)

),

-- The square again, as before sprites (s) AS (VALUES (st_polygonfromtext('polygon ((0 0, 0 1, 1 1, 1 0, 0 0))')) ),

-- The number plane, as ints n1 (r, i) AS ( SELECT r, i FROM dims, generate_series((r1 / s)::int, (r2 / s)::int) r, generate_series((i1 / s)::int, (i2 / s)::int) i ),

-- The number plane as scaled floats n2 (r, i) AS ( SELECT r::float * s::float, i::float * s::float FROM dims, n1 ),

-- The recursive calculation of the Mandelbrot formula -- zn = (zn-1)^2 + c l (cr, ci, zr, zi, g, it, p) AS ( SELECT r, i, 0::float, 0::float, 0, it, p FROM n2, dims UNION ALL SELECT cr, ci, zrzr - zizi + cr, 2zrzi + ci, g + 1, it, p FROM l

-- The recursions stops when we reach the maximum
WHERE g < it

-- Or, when we reach "infinity"
AND zr*zr + zi*zi < p

),

-- Find the last calculated value per point in the -- complex number plane c (cr, ci), discard the others x (cr, ci, zr, zi, g) AS ( SELECT DISTINCT ON (cr, ci) cr, ci, zr, zi, g FROM l ORDER BY cr, ci, g DESC )

-- Turn every calculated point into a square SELECT st_union( st_translate(sprites.s, round(cr / dims.s), round(ci / dims.s)) ) FROM x, sprites, dims

-- Retain only the points not belonging to the Mandelbrot set -- You can also inverse the equation to retain the points that -- belong to the set WHERE zrzr + zizi > p; ```

Example output:

mandelbrot

Christmas Tree

ASCII art christmas tree using Recursion and CTEs.

christmas-tree

sql WITH small_tree(tree_depth,pine) AS ( SELECT 1 tree_depth, rpad(' ',10,' ') || '*' || rpad(' ',20,' ') || '*' || rpad(' ',20,' ') || '*' pine FROM dual UNION ALL SELECT small_tree.tree_depth +1 tree_depth, rpad(' ',10-small_tree.tree_depth,' ') || rpad('*',small_tree.tree_depth+1,'.') || lpad('*',small_tree.tree_depth,'.') || rpad(' ',20-small_tree.tree_depth-tree_depth,' ') || rpad('*',small_tree.tree_depth+1,'.') || lpad('*',small_tree.tree_depth,'.') || rpad(' ',20-small_tree.tree_depth-tree_depth,' ') || rpad('*',small_tree.tree_depth+1,'.') || lpad('*',small_tree.tree_depth,'.') pine FROM small_tree where small_tree.tree_depth < 10 ) SELECT pine FROM small_tree;

christmas-tree

txt * * * *.* *.* *.* *...* *...* *...* *.....* *.....* *.....* *.......* *.......* *.......* *.........* *.........* *.........* *...........* *...........* *...........* *.............* *.............* *.............* *...............* *...............* *...............* *.................* *.................* *.................*

Man with a Hat

Draw a man with a hat using recursive queries and Common Table Expressions (CTEs).

Man with a Hat

```sql WITH RECURSIVE hat AS ( SELECT CAST(REPEAT(' ', 11) || '/V\' AS VARCHAR(100)) AS hat_pattern, 1 AS level

UNION ALL

SELECT CAST( REPEAT(' ', 10-level) || '/' || REPEAT('V', 2 * level + 1) || 'V\' AS VARCHAR(100)) AS repeated_pattern, hat.level + 1 FROM hat WHERE level < 6 )

SELECT hat_pattern FROM hat

UNION ALL

SELECT CAST( REPEAT(' ', 5) || '|' || ' ' || '|' AS VARCHAR(100)) AS forehead

UNION ALL

SELECT CAST( REPEAT(' ', 5) || '|' || ' O / O ' || '|' AS VARCHAR(100)) AS eyes

UNION ALL

SELECT CAST( REPEAT(' ', 5) || '|' || ' /_ ' || '|' AS VARCHAR(100)) AS nose

UNION ALL SELECT CAST( REPEAT(' ', 5) || '|' || ' ~~~ ' || '|' AS VARCHAR(100)) AS mouth

UNION ALL SELECT CAST( REPEAT(' ', 5) || '|' || ' { | } ' || '|' AS VARCHAR(100)) AS chin; ```

sql-hat-guy