Drawing SQL Cubes

<= Drawing spatial Spirals

Drawing SQL Sphere =>

Hey, there is a time to go level up and instead of drawing Spirals, Fractals and other cool stuff I decided to go 3D!

So, the first my try will be drawing 3D cubes.
As you know, SQL is not an Object Orienting Programming language, and I can't just simply create an Object "Cube" with certain properties. To create a Cube I need a Stored Procedure:

SET NOCOUNT ON
GO
IF OBJECT_ID('tempdb.dbo.#3DCube') IS NOT NULL
DROP PROCEDURE #3DCube
GO
CREATE PROCEDURE #3DCube
@a FLOAT = 10, /*Length of a side*/
@kx FLOAT = .6,/*X - Declination*/
@ky FLOAT = .3,/*Y - Declination*/
@sx FLOAT = 0, /*X - Position of the first corner*/
@sy FLOAT = 0, /*Y - Position of the first corner*/
@sz FLOAT = 0 /*Z - Position of the first corner*/
AS
DECLARE @cube TABLE (x FLOAT, y FLOAT, z FLOAT, pid TINYINT IDENTITY(1,1));
DECLARE @g TABLE (g GEOMETRY);
;WITH x as (SELECT p = @sx UNION ALL SELECT @sx + @a)
       , y as (SELECT p = @sy UNION ALL SELECT @sy + @a)
       , z as (SELECT p = @sz UNION ALL SELECT @sz + @a)
INSERT INTO @cube SELECT * FROM x,y,z
 
;WITH Edges as (
       SELECT DISTINCT
              Pin1 = CASE WHEN c1.pid < c2.pid THEN c1.pid ELSE c2.pid END,
              Pin2 = CASE WHEN c1.pid < c2.pid THEN c2.pid ELSE c1.pid END
       FROM @cube as c1
       INNER JOIN @cube as c2 ON
              (c1.x != c2.x and c1.y = c2.y and c1.z = c2.z) OR
              (c1.x = c2.x and ((c1.y = c2.y and c1.z != c2.z) OR (c1.y != c2.y and c1.z = c2.z) ))
)
INSERT INTO @g SELECT
       CONVERT(GEOMETRY,'LINESTRING('
       +CAST(c1.x+c1.z*@kx as VARCHAR)+' '+CAST(c1.y+c1.z*@ky as VARCHAR) + ','
       +CAST(c2.x+c2.z*@kx as VARCHAR)+' '+CAST(c2.y+c2.z*@ky as VARCHAR)
       +')').STBuffer(0.01) as g  
FROM Edges as e
INNER JOIN @cube as c1 ON c1.pid = e.Pin1
INNER JOIN @cube as c2 ON c2.pid = e.Pin2
UNION ALL
SELECT CONVERT(GEOMETRY,'POINT('
       +CAST(x+z*@kx as VARCHAR)+' '
       +CAST(y+z*@ky as VARCHAR)
       +')').STBuffer(0.1)
FROM @cube
 
SELECT geometry::UnionAggregate(g) FROM @g
GO

"#3DCube" stored procedure is created as "temp" one. Like temporary table. It will disappear after your connection is dropped and it is unavailable from any other connections. In other words it is invisible for everybody else.
If you want to be reusable, just change it a permanent one and place it in your spatial database.

That procedure has 6 parameters:
@a - Length of a cube side;
@kx and @ky - X & Y - Declinations. These parameters are changing your "Point of a View".
@sx, @sy & @sz - X,Y&Z - Position of the first corner

If you run this procedure without parameters it wont't be so cool. Just default cube:

EXEC #3DCube

Lets unleash the power of the 3D Cube! Draw more than one:

DECLARE @g TABLE (g GEOMETRY);
INSERT INTO @g EXEC #3DCube 1, 0.5, 0.2, 0, 0, 0
INSERT INTO @g EXEC #3DCube .8, 0.5, 0.2, 2, 2, -1
INSERT INTO @g EXEC #3DCube .9, 0.5, 0.2, -3, 1, 0
INSERT INTO @g EXEC #3DCube .7, 0.5, 0.2, -1, 1.5, 0
INSERT INTO @g EXEC #3DCube 1.1, 0.5, 0.2, -2, -0.5, -1
SELECT * FROM @g

As you can see declinations of all cubes are the same, because we are looking at them from the same point of a view. Lets change the angle of a view to the same group of cubes:

DECLARE @g TABLE (g GEOMETRY);
INSERT INTO @g EXEC #3DCube 1, 0.3, 0.7, 0, 0, 0
INSERT INTO @g EXEC #3DCube .8, 0.3, 0.7, 2, 2, -1
INSERT INTO @g EXEC #3DCube .9, 0.3, 0.7, -3, 1, 0
INSERT INTO @g EXEC #3DCube .7, 0.3, 0.7, -1, 1.5, 0
INSERT INTO @g EXEC #3DCube 1.1, 0.3, 0.7, -2, -0.5, -1
SELECT * FROM @g

Here is some magic: Cubical Optical Illusion:

DECLARE @g TABLE (g GEOMETRY);
INSERT INTO @g EXEC #3DCube 1, 0.6, 0.3, 0, 0, 0
INSERT INTO @g EXEC #3DCube 1, 0.6, 0.3, 0, 0, 1.5
INSERT INTO @g EXEC #3DCube 1, 0.6, 0.3, 0, 0, 3
SELECT * FROM @g

As you can see from the code, all cubes have the same size, but on the picture the blue cube looks bigger.

Now will play some manual perspective with the same three cubes:

DECLARE @g TABLE (g GEOMETRY);
INSERT INTO @g EXEC #3DCube 1, 0.6, 0.3, 0, 0, 0
INSERT INTO @g EXEC #3DCube .8, 0.6, 0.3, 1.8, 0.9, 0
INSERT INTO @g EXEC #3DCube .6, 0.6, 0.3, 3.4, 1.7, 0
SELECT * FROM @g

Not the best representation of perspective, but would you expect even that from SQL Server Management studio?