Using PostgreSQL Recursive SQL and the PL/pgSQL Function to Draw Fractal Graphs
By digoal
Background
The fractal formula is simple, but it can change into endless self-similar states.
- In addition, fractals can create dimensions, such as one-dimensional line segments. Through fractal, it can be covered with the entire surface into a two-dimensional plane. This law applies to the biological field, such as the biological polymer (protein), with amino acids as the basic unit.
1. The fractal formula is listed below:
z=z^2+c
z can be singular, plural, or even multidimensional.
2. Complex number operation
(a+bi)(c+di) = (ac-bd)+(ad+bc)i
(a+bi) + (c+di) = (a+c)+(b+d)i
(a+bi) - (c+di) = (a-c)+(b-d)i
3. How to generate fractal patterns
When z0 is fixed and different values of c are taken, the divergence speed of z is different. For example, for a certain c, z will tend to be infinite after 100 times. For a certain c, no matter how many iterations, z will jump within a certain range.
Then, take a piece of white cloth as the base map, map the coordinates of c to this two-dimensional white cloth, and use color to mark the divergence velocity of the z value caused by the c value. The darker the value is, the less divergent the z value is. The pixels of c on the complex plane form a fractal pattern.
Let's take a simple look at how the iteration value of z is generated when c and z0 are fixed and observe the divergence speed of z.
WITH RECURSIVE t(n, zr, zi, cr, ci) AS (
VALUES (1, 0.15::float8, 0.15::float8, -0.75::float8, 0.0::float8)
UNION ALL
SELECT n+1, zr*zr - zi*zi + cr, zr*zi + zi*zr + ci, cr, ci FROM t WHERE n < 100
)
SELECT n,zr,zi FROM t;
do language plpgsql $$
declare
zr float8 := 0.15;
zi float8 := 0.15;
cr float8 := -0.75;
ci float8 := 0.0;
tmpr float8;
tmpi float8;
begin
for i in 1..100 loop
raise notice '%, %i', zr, zi;
tmpr := zr*zr - zi*zi + cr;
tmpi := zr*zi + zi*zr + ci;
zr := tmpr;
zi := tmpi;
end loop;
raise notice '%, %i', zr, zi;
end;
$$;
┌─────┬──────────────────────┬────────────────────────┐
│ n │ zr │ zi │
├─────┼──────────────────────┼────────────────────────┤
│ 1 │ 0.15 │ 0.15 │
│ 2 │ -0.75 │ 0.045 │
│ 3 │ -0.18952500000000005 │ -0.0675 │
│ 4 │ -0.7186365243749999 │ 0.025585875000000008 │
│ 5 │ -0.2342161828337358 │ -0.036773888566186415 │
│ 6 │ -0.6964950985790723 │ 0.017226079615850688 │
│ 7 │ -0.26519131547425995 │ -0.023995760040345746 │
│ 8 │ -0.6802493626969454 │ 0.012726934341807938 │
│ 9 │ -0.28742277940814037 │ -0.017314977950201437 │
│ 10 │ -0.6676879543387154 │ 0.009953438175675124 │
│ 11 │ -0.30429186656249846 │ -0.013291581548306798 │
│ 12 │ -0.657583126083966 │ 0.008089040317803877 │
│ 13 │ -0.317649864862902 │ -0.010638432838401423 │
│ 14 │ -0.6492117396058372 │ 0.006758593506942541 │
│ 15 │ -0.3285697957441547 │ -0.008775516495861767 │
│ 16 │ -0.6421188990144137 │ 0.005766739325189522 │
│ 17 │ -0.3377165748109619 │ -0.007405864612787637 │
│ 18 │ -0.6360023619286149 │ 0.005002166461088703 │
│ 19 │ -0.34552601729052757 │ -0.006362779368025033 │
│ 20 │ -0.6306522563366322 │ 0.0043970116278640595 │
...
│ 96 │ -0.5671955589513737 │ 0.0005535901325261927 │
│ 97 │ -0.4282895043678735 │ -0.0006279877292963179 │
│ 98 │ -0.5665684948169094 │ 0.0005379211066588526 │
│ 99 │ -0.4290004300400187 │ -0.0006095383034599045 │
│ 100 │ -0.5659590025624224 │ 0.0005229843886203249 │
└─────┴──────────────────────┴────────────────────────┘
After 100 iterations, z is still not diverging.
4. Mandelbrot Set Fractal Graphics (God's Fingerprint): Fix z0 and draw complex planes with different c as pixel points.

If we fix z0=0, the iteration result of the function is different for different complex numbers c, which correspond to points on a plane graph. We can use a graph to determine whether the iteration of the function f(z)=z^2+c from z0=0 will diverge to infinity for a certain complex number c. By using different colors to represent different divergence speeds, we can create the Mandelbrot set fractal graph.
do language plpgsql $$
declare
zr numeric := 0.0; -- z0r
zi numeric := 0.0; -- z0i
tmpr numeric;
tmpi numeric;
i int;
begin
<<label_x>>
for x in -300..300 loop -- cr, indicating that the x-axis pixel range of the white cloth is -300 to 300.
<<label_y>>
for y in -200..200 loop -- ci, indicating that the y-axis pixel range of the white cloth is -200 to 200.
<<label_i>>
for k in 1..200 loop -- z divergence speed, i represents color depth; iteration is 200 times at most, 200 times is black, and one time may be close to white.
tmpr := zr*zr - zi*zi + x::numeric/300.0::numeric;
tmpi := zr*zi + zi*zr + y::numeric/200.0::numeric;
zr := tmpr;
zi := tmpi;
i := k;
exit label_i when sqrt(zr*zr + zi*zi) > 2; -- the number of iterations of z ends at | z |>2, because z will diverge infinitely at this time.
end loop label_i ;
raise notice 'cr:%, ci:%, i:%', x, y, i;
zr := 0.0; -- z0r
zi := 0.0; -- z0i
end loop label_y ;
end loop label_x ;
end;
$$;
cr is the x coordinate, ci is the y coordinate, and i is the color depth.
NOTICE: cr:-300, ci:-200, i:3
NOTICE: cr:-300, ci:-199, i:3
NOTICE: cr:-300, ci:-198, i:3
NOTICE: cr:-300, ci:-197, i:3
NOTICE: cr:-300, ci:-196, i:3
NOTICE: cr:-300, ci:-195, i:3
NOTICE: cr:-300, ci:-194, i:3
NOTICE: cr:-300, ci:-193, i:3
NOTICE: cr:-300, ci:-192, i:3
NOTICE: cr:-300, ci:-191, i:3
NOTICE: cr:-300, ci:-190, i:3
NOTICE: cr:-300, ci:-189, i:3
NOTICE: cr:-300, ci:-188, i:3
NOTICE: cr:-300, ci:-187, i:3
NOTICE: cr:-300, ci:-186, i:3
NOTICE: cr:-300, ci:-185, i:3
NOTICE: cr:-300, ci:-184, i:3
NOTICE: cr:-300, ci:-183, i:3
NOTICE: cr:-300, ci:-182, i:3
NOTICE: cr:-300, ci:-181, i:3
NOTICE: cr:-300, ci:-180, i:3
NOTICE: cr:-300, ci:-179, i:3
NOTICE: cr:-300, ci:-178, i:3
NOTICE: cr:-300, ci:-177, i:3
NOTICE: cr:-300, ci:-176, i:3
NOTICE: cr:-300, ci:-175, i:3
NOTICE: cr:-300, ci:-174, i:3
NOTICE: cr:-300, ci:-173, i:3
NOTICE: cr:-300, ci:-172, i:3
NOTICE: cr:-300, ci:-171, i:3
NOTICE: cr:-300, ci:-170, i:3
NOTICE: cr:-300, ci:-169, i:3
NOTICE: cr:-300, ci:-168, i:3
NOTICE: cr:-300, ci:-167, i:3
NOTICE: cr:-300, ci:-166, i:3
NOTICE: cr:-300, ci:-165, i:3
NOTICE: cr:-300, ci:-164, i:3
NOTICE: cr:-300, ci:-163, i:3
NOTICE: cr:-300, ci:-162, i:3
NOTICE: cr:-300, ci:-161, i:3
NOTICE: cr:-300, ci:-160, i:3
NOTICE: cr:-300, ci:-159, i:3
NOTICE: cr:-300, ci:-158, i:3
NOTICE: cr:-300, ci:-157, i:3
NOTICE: cr:-300, ci:-156, i:3
NOTICE: cr:-300, ci:-155, i:3
NOTICE: cr:-300, ci:-154, i:3
NOTICE: cr:-300, ci:-153, i:3
NOTICE: cr:-300, ci:-152, i:3
NOTICE: cr:-300, ci:-151, i:3
NOTICE: cr:-300, ci:-150, i:3
NOTICE: cr:-300, ci:-149, i:3
NOTICE: cr:-300, ci:-148, i:4
NOTICE: cr:-300, ci:-147, i:4
NOTICE: cr:-300, ci:-146, i:4
NOTICE: cr:-300, ci:-145, i:4
NOTICE: cr:-300, ci:-144, i:4
NOTICE: cr:-300, ci:-143, i:4
NOTICE: cr:-300, ci:-142, i:4
NOTICE: cr:-300, ci:-141, i:4
NOTICE: cr:-300, ci:-140, i:4
NOTICE: cr:-300, ci:-139, i:4
NOTICE: cr:-300, ci:-138, i:4
NOTICE: cr:-300, ci:-137, i:4
NOTICE: cr:-300, ci:-136, i:4
NOTICE: cr:-300, ci:-135, i:4
NOTICE: cr:-300, ci:-134, i:4
NOTICE: cr:-300, ci:-133, i:4
NOTICE: cr:-300, ci:-132, i:4
NOTICE: cr:-300, ci:-131, i:4
NOTICE: cr:-300, ci:-130, i:4
NOTICE: cr:-300, ci:-129, i:4
NOTICE: cr:-300, ci:-128, i:4
NOTICE: cr:-300, ci:-127, i:4
NOTICE: cr:-300, ci:-126, i:4
NOTICE: cr:-300, ci:-125, i:4
NOTICE: cr:-300, ci:-124, i:4
NOTICE: cr:-300, ci:-123, i:4
NOTICE: cr:-300, ci:-122, i:4
NOTICE: cr:-300, ci:-121, i:4
NOTICE: cr:-300, ci:-120, i:4
NOTICE: cr:-300, ci:-119, i:5
NOTICE: cr:-300, ci:-118, i:5
NOTICE: cr:-300, ci:-117, i:5
NOTICE: cr:-300, ci:-116, i:5
NOTICE: cr:-300, ci:-115, i:5
NOTICE: cr:-300, ci:-114, i:5
NOTICE: cr:-300, ci:-113, i:5
NOTICE: cr:-300, ci:-112, i:5
NOTICE: cr:-300, ci:-111, i:5
NOTICE: cr:-300, ci:-110, i:5
NOTICE: cr:-300, ci:-109, i:5
NOTICE: cr:-300, ci:-108, i:5
NOTICE: cr:-300, ci:-107, i:5
NOTICE: cr:-300, ci:-106, i:5
NOTICE: cr:-300, ci:-105, i:5
NOTICE: cr:-300, ci:-104, i:5
NOTICE: cr:-300, ci:-103, i:5
NOTICE: cr:-300, ci:-102, i:5
NOTICE: cr:-300, ci:-101, i:5
NOTICE: cr:-300, ci:-100, i:5
NOTICE: cr:-300, ci:-99, i:5
NOTICE: cr:-300, ci:-98, i:5
NOTICE: cr:-300, ci:-97, i:5
NOTICE: cr:-300, ci:-96, i:5
NOTICE: cr:-300, ci:-95, i:5
NOTICE: cr:-300, ci:-94, i:5
NOTICE: cr:-300, ci:-93, i:5
NOTICE: cr:-300, ci:-92, i:5
NOTICE: cr:-300, ci:-91, i:5
NOTICE: cr:-300, ci:-90, i:5
NOTICE: cr:-300, ci:-89, i:5
NOTICE: cr:-300, ci:-88, i:5
NOTICE: cr:-300, ci:-87, i:6
NOTICE: cr:-300, ci:-86, i:6
NOTICE: cr:-300, ci:-85, i:6
NOTICE: cr:-300, ci:-84, i:6
NOTICE: cr:-300, ci:-83, i:6
NOTICE: cr:-300, ci:-82, i:7
NOTICE: cr:-300, ci:-81, i:7
NOTICE: cr:-300, ci:-80, i:7
NOTICE: cr:-300, ci:-79, i:8
NOTICE: cr:-300, ci:-78, i:8
NOTICE: cr:-300, ci:-77, i:8
NOTICE: cr:-300, ci:-76, i:8
NOTICE: cr:-300, ci:-75, i:8
NOTICE: cr:-300, ci:-74, i:9
NOTICE: cr:-300, ci:-73, i:9
NOTICE: cr:-300, ci:-72, i:10
NOTICE: cr:-300, ci:-71, i:10
NOTICE: cr:-300, ci:-70, i:10
NOTICE: cr:-300, ci:-69, i:10
NOTICE: cr:-300, ci:-68, i:11
NOTICE: cr:-300, ci:-67, i:11
NOTICE: cr:-300, ci:-66, i:12
NOTICE: cr:-300, ci:-65, i:13
NOTICE: cr:-300, ci:-64, i:15
NOTICE: cr:-300, ci:-63, i:18
NOTICE: cr:-300, ci:-62, i:23
NOTICE: cr:-300, ci:-61, i:37
NOTICE: cr:-300, ci:-60, i:35
NOTICE: cr:-300, ci:-59, i:34
NOTICE: cr:-300, ci:-58, i:59
NOTICE: cr:-300, ci:-57, i:200
NOTICE: cr:-300, ci:-56, i:200
.....
5. Julia set: Fixed c and draw complex planes with different z0 as pixel points.

For example, the Julia set at f(z)=z^2+(-0.75, i0) time. Consider the complex function f(z)=z ^ 2 + c, where different complex numbers c correspond to different Julia sets.
For example, in one dimension of real numbers, for a fixed c = -0.75, when z0 belongs to a [-1.5, 1.5], the z value will never exceed a certain range.
However, when z0 is less than -1.5 or greater than 1.5, the z value will eventually tend to infinity. Extend this function to the entire complex number range. For the complex number z0=x + iy, taking different values of x and y, the result of the function iteration is different.
- For some z0, the function value is constrained within a certain range.
- For some other z0, the function value diverges to infinity.
Since complex numbers correspond to points on a plane, we can represent them in a plane graph: which z0 function values end up tending to infinity and which z0 function values do not end up tending to infinity.
We use dark gray to indicate z0, which does not make the function value tend to infinity; for other z0, we use different colors to distinguish different divergence speeds. Since the function value must diverge when it is |z|>2 at some point, the divergence speed is defined here as – the fewer iterations that make |z| greater than 2, the faster the divergence speed.
References (In Chinese)
http://www.matrix67.com/blog/archives/4570
http://www.matrix67.com/blog/archives/6231
https://www.cnblogs.com/anderslly/archive/2008/10/10/mandelbrot-set-by-fsharp.html
http://www.matrix67.com/blog/archives/292
Read previous post:
Configure a pgcat for the PolarDB Read/Write Splitting Connection Pool
Read next post:
Comments
Related Products
-
PolarDB for PostgreSQL
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn More -
AnalyticDB for PostgreSQL
An online MPP warehousing service based on the Greenplum Database open source program
Learn More -
ApsaraDB RDS for SQL Server
An on-demand database hosting service for SQL Server with automated monitoring, backup and disaster recovery capabilities
Learn More -
Qoder
From Assisted to Autonomous — Adapting to Every Programming Need
Learn More
More Posts by digoal
- Tips for Executing SQL Statements in Batch: Which Is Better, gexec or Dynamic SQL?
- What Could Be the Factor Leading to the Slow Execution of the First SQL Statement?
- How to Remove Duplicate and Similar Contents in Vector Databases
- How to Implement Parallelism and Concurrency Control (Queue) in Shell
- How to Determine Whether the Current PostgreSQL Database is in a Consistent State?
- PolarDB for PostgreSQL | Retrieval and Matching-Degree Sorting by Tags on Blogs and Websites
- Three Key Details and Principles of PostgreSQL Online Backup & Recovery
- PolarDB for PostgreSQL | Database Performance Optimization for High-Concurrency Queue Processing
- Implementing PostgreSQL Hook: Stats on Tables with Full Scans & Corresponding SQLs
- Optimization on Memory Usage During Rust Cargo Code Compiling