Drawing triangles using SQL Server – Fractals

By | April 1, 2019

If you play sports you know how much fun it is to do trick shots every once in a while. Occasionally I find myself with time on my hands and I end up doing things on SQL Server that have absolutely no database value but it’s a fun exercise. This is one such time.

Fractals are a repetitive pattern that uses a single block that extends across multiple axis, a snowflake is one of the most relatable examples. Its most common use for most people would be art but it has powerful scientific applications as well e.g. Chaos Theory. One of the more famous such examples is Barnsleys Fern.

In the below example I use a similar approach to create a basic design. The idea here being we let chance decide what action we take but still end up with a very structured pattern. Here I use the Rand function in SQL server to pick a random number and then if it is 1,2,3 go half way to point 1 from wherever the origin is. If it is 4,5,6 then we move halfway to point 2 from the last position, if it is 7,8,9 then point 3 and so on. The end result will look like below

The script is attached below just for fun. You can obviously modify it to get other shapes and patterns for example you can start with four corners etc.

/** CREATE A TABLE TO STORE THE BOUNDARY COORDINATES.
THIS IS WHAT DEFINES THE KIND IMAGE YOU MIGHT END UP WITH***/
CREATE TABLE vertex 
( 
vertexid INT,
vertexgroup VARCHAR(100),
vertexdata NVARCHAR(1000)
)
GO 
 
/**INSERT A  JSON TEXT THAT DEFINES THE THREE POINTS 
WE WILL MOVE TOWARDS BASED ON THE RAND FUNCTION OUTPUT **/
 
INSERT INTO vertex
SELECT 1 , 'Triangle' , 
N'{
	"info":{
		"point1":{"xcor":"0","ycor":"0"},
		"point2":{"xcor":"10000","ycor":"0"},
		"point3":{"xcor":"0","ycor":"10000"}
			}
	}'
GO
/**CREATE A TABLE TO STORE THE POSITIONS GENERATED BY OUR LOGIC**/
CREATE TABLE tracks ( 
id INT IDENTITY(1,1) ,  
drawingid UNIQUEIDENTIFIER , 
sourcepoint geometry , 
destinationpoint geometry ) 
GO
-- HOUSE KEEPING FOR MULTIPLE RUNS, COPY EVERYTHING BELOW INTO A NEW WINDOW
TRUNCATE TABLE tracks
go
DECLARE @origin geometry  = geometry::Point(200,500,0)
DECLARE @p1xcor INT
DECLARE @p1ycor INT
DECLARE @p2xcor INT 
DECLARE @p2ycor INT 
DECLARE @p3xcor INT 
DECLARE @p3ycor INT 
DECLARE @drawingid UNIQUEIDENTIFIER =newid()
-- start WITH OUR SEED POSITION - IT CAN BE ANYWHERE WE LIKE
INSERT INTO tracks 
SELECT  @drawingid , geometry::Point(0,0,0), @origin
-- FETECH POSITIONS FROM OUR BOUNRDAY TABLE
SELECT 
@p1xcor= JSON_VALUE(vertexdata,'$.info.point1.xcor')  ,
@p1ycor =JSON_VALUE(vertexdata,'$.info.point1.ycor')  ,
@p2xcor= JSON_VALUE(vertexdata,'$.info.point2.xcor')  ,
@p2ycor= JSON_VALUE(vertexdata,'$.info.point2.ycor')  ,
@p3xcor= JSON_VALUE(vertexdata,'$.info.point3.xcor')  ,
@p3ycor= JSON_VALUE(vertexdata,'$.info.point3.ycor')  
FROM vertex 
WHERE ISJSON(vertexdata) > 0
AND vertexid= 1
 
DECLARE @p1 geometry  = geometry::Point(@p1xcor ,@p1ycor,0)
DECLARE @p2 geometry  = geometry::Point(@p2xcor ,@p2ycor,0)
DECLARE @p3 geometry  = geometry::Point(@p3xcor ,@p3ycor,0)
 
DECLARE @COUNT INT =0
WHILE @COUNT < 1000 -- MORE ITERATIONS THE BETTER BUT SSMS CANT DISPLAY IT
-- SO USE POWERBI AND SCATTER PLOT INSTEAD
BEGIN 
 
DECLARE @RAND INT = ROUND(RAND()*10,0)
 
IF @RAND in ( 1 ,2 , 3) 
	BEGIN 
		INSERT INTO tracks 
		SELECT TOP 1  @drawingid,   
		destinationpoint,
		geometry::Point((destinationpoint.STX + @p1.STX)/2 ,(destinationpoint.STY+@p1.STY)/2,0)  
		FROM tracks 
		ORDER BY id DESC 
	END
 
IF @RAND in ( 4,5,6) 
	BEGIN 
		INSERT INTO tracks 
		SELECT TOP 1  @drawingid , 
		destinationpoint,
		geometry::Point((destinationpoint.STX + @p2.STX)/2 ,(destinationpoint.STY+@p2.STY)/2,0)  
		FROM tracks 
		ORDER BY id DESC 
	END
 
IF @RAND in ( 7,8,9) 
	BEGIN 
		INSERT INTO tracks 
		SELECT TOP 1  @drawingid ,   
		destinationpoint,
		geometry::Point((destinationpoint.STX + @p3.STX)/2 ,(destinationpoint.STY+@p3.STY)/2,0)  
		FROM tracks 
		ORDER BY id DESC 
	END
 
SET @COUNT= @COUNT+1 
 
END 
-- SEE THE FINAL RESULT 
 
SELECT * FROM tracks