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.
--WARNING! ERRORS ENCOUNTERED DURING SQL PARSING!
/** 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
Please Consider Subscribing
