Triangles Everywhere

After seeing the Mandelbrot fractal implemented in SQL and drawn as ASCII art. I wanted to see if anyone had implemented any fractals with the Geometry datatype. The closest that I found was Drawing Fractals with SQL Server Spatial by Alastair Aitchison. Which is a nice example Sierpinski triangle fractal as a CLR function, but I wanted to see if I could I could do it in pure SQL.

	/*
	Description: A T-SQL implementation of Sierpinski's Triangles, just to see if I could do it.
	Requires: SQL Server 2012
	Inspired by: Alastair Aitchison SQLCLR implementation http://alastaira.wordpress.com/2012/03/06/drawing-fractals-with-sql-server-spatial/
	Author: Matthew Naul
	*/
	DECLARE
	@itrations INT = 12,
	@seed geometry = geometry::STGeomFromText ('LINESTRING (9 12, 0 0, 18 0, 9 12)',0);
	WITH SierpinskiTriangle(lvl,[Ax],[Ay],[Bx],[By],[Cx],[Cy], shape) AS (
	SELECT
	1 lvl,
	base.[Ax], base.[Ay],
	base.[Bx], base.[By],
	base.[Cx], base.[Cy],
	CONCAT('MULTIPOLYGON( (( ', [Ax],' ',[Ay],',',
	[Bx],' ',[By],',',
	[Cx],' ',[Cy],',',
	[Ax],' ',[Ay],')) )') as shape
	FROM (
	SELECT
	@seed.STPointN(1).STX as [Ax], @seed.STPointN(1).STY as [Ay],
	@seed.STPointN(2).STX as [Bx], @seed.STPointN(2).STY as [By],
	@seed.STPointN(3).STX as [Cx], @seed.STPointN(3).STY as [Cy]
	) AS base
	UNION ALL
	SELECT
	itr.lvl+1 AS lvl,
	new.[Ax], new.[Ay],
	new.[Bx], new.[By],
	new.[Cx], new.[Cy],
	CONCAT('MULTIPOLYGON( (( ', new.[Ax],' ',new.[Ay],',',
	new.[Bx],' ',new.[By],',',
	new.[Cx],' ',new.[Cy],',',
	new.[Ax],' ',new.[Ay],')) )') as shape
	FROM SierpinskiTriangle AS itr
	CROSS APPLY(
	SELECT
	(itr.[Bx] + itr.[Cx]) * .5 as [A0x], (itr.[By] + itr.[Cy]) *.5 as [A0y],
	(itr.[Cx] + itr.[Ax]) * .5 as [B0x], (itr.[Cy] + itr.[Ay]) *.5 as [B0y],
	(itr.[Ax] + itr.[Bx]) * .5 as [C0x], (itr.[Ay] + itr.[By]) *.5 as [C0y]
	) AS mid
	CROSS APPLY(
	SELECT 1, itr.[Ax] , itr.[Ay] , mid.[C0x], mid.[C0y], mid.[B0x], mid.[B0y] UNION ALL
	SELECT 2, mid.[C0x], mid.[C0y], itr.[Bx] , itr.[By] , mid.[A0x], mid.[A0y] UNION ALL
	SELECT 3, mid.[B0x], mid.[B0y], mid.[A0x], mid.[A0y], itr.[Cx] , itr.[Cy]
	) AS new(number, [Ax], [Ay], [Bx], [By], [Cx], [Cy])
	WHERE
	([A0x] > .00001 ) and --triagnel with inside geometry type error. Too small, bail
	itr.lvl < @itrations
	)
	SELECT
	sys.GeometryUnionAggregate(geometry::STMPolyFromText(shape,0))
	FROM
	SierpinskiTriangle
	WHERE
	lvl = @itrations

view raw sierpinskis_tri.sql hosted with ❤ by GitHub

Let me know if you have suggestions for making it go faster

Fun with serialization

One of my favorite things to do with SQL Sever is XML. There are just all kinds to idiosyncrasies; however, after spending a substantial amount of time formatting xmlthere where a few things I didn't want to have to find again.

	/*
	Descriptions: reference for all the xml shapping I've learned
	Author: Mattehw Naul
	Explanation:
	for each person do a correlated query for phone numbers,
	format them mixing attributes and element values,
	serialize to xml with "TYPE" so that we return on column of type XML root tag (phonelist)
	name the cross apply result,
	when phonelist(col) is null add nil phonelist node
	*/
	-- quick inline test data
	WITH
	Person(id, FirstName, LastName) AS
	(
	SELECT 1, 'Bob', 'Smith' UNION ALL
	SELECT 2, 'Jane', 'Doe' UNION ALL
	SELECT 3, 'John', 'Jones'
	),
	PhoneTable(personid, ord, phonetype, number) AS
	(
	SELECT 1, 1, 'home', '806-555-1234' UNION ALL
	SELECT 1, 2, 'work', '806-555-2843' UNION ALL
	SELECT 2, 1, 'work', '806-555-0282' UNION ALL
	SELECT 2, 2, 'cell', '806-555-9028' UNION ALL
	SELECT 2, 3, 'home', '806-555-2103'
	)
	SELECT
	FirstName
	,LastName
	,calc.phonelist
	FROM Person
	-- wrapping the sub-select in `cross apply` is what gives us a nil atrib on list tag
	CROSS APPLY (
	/* Reference:
	Books On-Line : Columns with the Name of an XPath Node Test
	http://technet.microsoft.com/en-us/library/bb522573.aspx
	*/
	SELECT
	ord AS "@ord", --tag attribute
	phonetype AS "@type", --tag attribute
	number AS "text()" --body of tag function see below
	FROM PhoneTable
	where
	Person.id = PhoneTable.personid
	FOR XML PATH('phonenumber'),ROOT('phonelist'), TYPE
	) AS calc(phonelist)
	FOR XML PATH('customer'), ROOT('bookofbusiness'), ELEMENTS XSINIL

view raw serialization_xml.sql hosted with ❤ by GitHub

	<!-- Result: -->
	<bookofbusiness xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<customer>
	<firstname>Bob</firstname>
	<lastname>Smith</lastname>
	<phonelist>
	<phonenumber ord="1" type="home">806-555-1234</phonenumber>
	<phonenumber ord="2" type="work">806-555-2843</phonenumber>
	</phonelist>
	</customer>
	<customer>
	<firstname>Jane</firstname>
	<lastname>Doe</lastname>
	<phonelist>
	<phonenumber ord="1" type="work">806-555-0282</phonenumber>
	<phonenumber ord="2" type="cell">806-555-9028</phonenumber>
	<phonenumber ord="3" type="home">806-555-2103</phonenumber>
	</phonelist>
	</customer>
	<customer>
	<firstname>John</firstname>
	<lastname>Jones</lastname>
	<phonelist xsi:nil="true"></phonelist>
	</customer>
	</bookofbusiness>

view raw serializeation_xml_result.xml hosted with ❤ by GitHub

Initial post

You are likely to find nothing profound here, just ill-advised tools that I have built while working on SQL Server and PostgreSQL.