## Drawing Fractals with SQL Server Spatial

I can’t think of any practical purpose for this code, but here’s a recursive SQLCLR procedure to draw a Sierpinski triangle fractal (or, at least, an approximation of one to a given level of detail) in SQL Server just because you can.

```using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Types;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Data;

namespace ProSpatial
{
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void SierpinskiTriangle(int size)
{

// Set properties of exterior equilateral triangle
int w = size;  // Width. e.g. 512
int h = (int)(w * Math.Sqrt(3) / 2);  // Height
int[] x = { 0, w, w/2 };  // x vertices
int[] y = { h, h, 0 };  // y vertices

// Create a new SqlGeometry Instance to hold the output
SqlGeometry Triangles = new SqlGeometry();
Triangles.STSrid = 0;

// Start recursion
Triangles = drawSierpinskiTriangle(x, y, w/2, 2, Triangles);

// Send the results back to SQL Server
SendResults(Triangles);
}

private static SqlGeometry drawSierpinskiTriangle(int[] x, int[] y, int d, int dMin, SqlGeometry Triangles)
{

// If triangles are too small to render then make this the last recursion
if (d <= dMin)
{
// Create a new triangle and add it to the collection
SqlGeometry Polygon = TriangleFromPoints(x[0], y[0], x[1], y[1],  x[2], y[2]);
Triangles = Triangles.STUnion(Polygon);
}
else
{
// Calculate centre of each side
int xMc = (x[0] + x[1]) / 2, yMc = (y[0] + y[1]) / 2;
int xMb = (x[0] + x[2]) / 2, yMb = (y[0] + y[2]) / 2;
int xMa = (x[1] + x[2]) / 2, yMa = (y[1] + y[2]) / 2;

// Subdivide into three new triangles
int[] xNew1 = { x[0], xMc, xMb };
int[] yNew1 = { y[0], yMc, yMb };
Triangles = drawSierpinskiTriangle(xNew1, yNew1, d / 2, dMin, Triangles);

int[] xNew2 = { x[1], xMc, xMa };
int[] yNew2 = { y[1], yMc, yMa };
Triangles = drawSierpinskiTriangle(xNew2, yNew2, d / 2, dMin, Triangles);

int[] xNew3 = { x[2], xMb, xMa };
int[] yNew3 = { y[2], yMb, yMa };
Triangles = drawSierpinskiTriangle(xNew3, yNew3, d / 2, dMin, Triangles);
}

// Recursion finished - return the result
return Triangles;
}

// Send the results back to the client
private static void SendResults(SqlGeometry Triangles)
{
// Define the metadata of the results column

// Create a record based on this metadata
record.SetValue(0, Triangles);

// Send the results back to the client
SqlContext.Pipe.Send(record);
}

// Construct a triangle from 3 vertices
private static SqlGeometry TriangleFromPoints(double x0, double y0, double x1, double y1, double x2, double y2)
{
SqlGeometryBuilder TriangleBuilder = new SqlGeometryBuilder();
TriangleBuilder.SetSrid(0);
TriangleBuilder.BeginGeometry(OpenGisGeometryType.Polygon);
TriangleBuilder.BeginFigure(x0, y0);
TriangleBuilder.EndFigure();
TriangleBuilder.EndGeometry();
return TriangleBuilder.ConstructedGeometry;
}

}
}
```

Import the assembly, register the procedure, and then execute in SQL Server. It requires a single parameter, size, which determines the overall scale of the result.

```/* Import Assembly */
CREATE ASSEMBLY SierpinskiTriangle
FROM 'c:\users\alastair\documents\SierpinskiTriangle.dll'
WITH PERMISSION_SET = SAFE;
GO

/* Register function */
CREATE PROCEDURE dbo.SierpinskiTriangle(@size int)
AS EXTERNAL NAME SierpinskiTriangle.[ProSpatial.StoredProcedures].SierpinskiTriangle;
GO

/* Execute Procedure */
EXEC dbo.SierpinskiTriangle 512;

```

When executed with a size of 512, the result is a single MultiPolygon instance containing a set of Sierpinski triangles as follows:

This entry was posted in Spatial, SQL Server and tagged , , , , , . Bookmark the permalink.

### 4 Responses to Drawing Fractals with SQL Server Spatial

1. Matthew Naul says:

Inspired by your post an alternative implementation for SQL Server 2012.
http://mattnaul.blogspot.com/2015/08/after-seeing-mandelbrot-fractal.html