Query From Hell? – SQL Server Spatial Data e um cubo 3D

Quando lançaram a feature de Spatial Data do SQL Server fiquei impressionado com as possibilidades de trabalhar com tipos geométricos e geográficos no SQL Server, principalmente pelo fato de que naquele ano, eu estava na equipe de desenvolvimento de um projeto de geolocalização e roteirização, onde usávamos bastante features como Spatial Data e aplicações com mapas (Web e Mobile).

Só que desde aquela época, via que além das propriedades X e Y, existia a propriedade Z, que não afetava em nada os métodos para calculo de distancia e outros. Pois, logo que existe X Y Z, seria possível gravar dados geométricos não somente 2D no banco de dados, mas também 3D… Mas não, a propriedade Z realmente não permitia isso.

Para matar minha vontade de gerar “algo em 3D” no SQL Server, peguei uma biblioteca de uma aplicação Windows Forms do site (Visual C# Kicks – http://www.vcskicks.com), adequei a biblioteca para ser SAFE no SQL Server e trabalhar com Spatial Data, assim criei um pequeno monstro:

Infelizmente não consegui implementar 100% o algoritmo de rotação do objeto 3D que funcionou perfeitamente na aplicação Windows (http://www.vcskicks.com/3d-graphics-improved.php). Quem quiser conhecer a biblioteca modificada, pode conferi-la abaixo:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Types;

namespace Cube3D
{
    public class UserDefinedFunctions
    {
        [SqlFunction]
        public static SqlGeometry GetCube3DLines(int w, int h, int d, double x, double y)
        {
            var mainCube = new Math3D.Cube(w, h, d);

            var drawOrigin = new Point(x, y);

            return mainCube.DrawCube(drawOrigin).GetLines();
        }

        [SqlFunction(FillRowMethodName = "FillFaces", TableDefinition = "Face geometry")]
        public static IEnumerable GetCube3DFaces(int w, int h, int d, double x, double y)
        {
            var mainCube = new Math3D.Cube(w, h, d);

            var drawOrigin = new Point(x, y);

            return mainCube.DrawCube(drawOrigin).GetFaces();
        }

        public static void FillFaces(object obj, out SqlGeometry face)
        {
            face = (SqlGeometry)obj;
        }
    }

    public class Line
    {
        public Line(Point p1, Point p2)
        {
            P1 = p1;
            P2 = p2;
        }

        public Point P1 { get; set; }
        public Point P2 { get; set; }
    }

    public class Point
    {
        public Point(double x, double y)
        {
            X = x;
            Y = y;
        }

        public Point() { }

        public double X { get; set; }
        public double Y { get; set; }
    }

    public class Vector
    {
        public Vector(double x, double y, double z)
        {
            X = x;
            Y = y;
            Z = z;
        }

        public Vector() { }

        public double X { get; set; }
        public double Y { get; set; }
        public double Z { get; set; }
    }

    public class Rectangle
    {
        public List<Line> Lst = new List<Line>();

        public Rectangle(double width, double height)
        {
            Width = width;
            Height = height;
        }

        public double Width { get; set; }
        public double Height { get; set; }

        public void DrawLine(Point p1, Point p2)
        {
            Lst.Add(new Line(p1, p2));
        }

        public SqlGeometry GetLines()
        {
            var g = new SqlGeometryBuilder();

            g.SetSrid(0);

            g.BeginGeometry(OpenGisGeometryType.MultiLineString);

            foreach (var line in Lst)
            {
                g.BeginGeometry(OpenGisGeometryType.LineString);

                g.BeginFigure(line.P1.X, line.P1.Y);

                g.AddLine(line.P2.X, line.P2.Y);

                g.EndFigure();

                g.EndGeometry();
            }

            g.EndGeometry();

            return g.ConstructedGeometry;
        }

        public IEnumerable<SqlGeometry> GetFaces()
        {
            var first = new Line(new Point(), new Point());

            var g = new SqlGeometryBuilder();

            g.SetSrid(0);

            for (var i = 0; i < Lst.Count; i++)
            {
                var line = Lst[i];

                if (i % 4 == 0)
                {
                    g.BeginGeometry(OpenGisGeometryType.Polygon);

                    first = line;

                    g.BeginFigure(first.P1.X, first.P1.Y);
                }
                else
                {
                    g.AddLine(line.P1.X, line.P1.Y);
                }

                g.AddLine(line.P2.X, line.P2.Y);

                if (i % 4 != 3) continue;

                g.AddLine(first.P1.X, first.P1.Y);

                g.EndFigure();

                g.EndGeometry();

                yield return g.ConstructedGeometry;

                g = new SqlGeometryBuilder();

                g.SetSrid(0);
            }
        }
    }

    //=============================================
    // O código abaixo foi criado a partir de uma |
    // applicação Windows Forms do site:          |
    // Visual C# Kicks - http://www.vcskicks.com/ |
    //=============================================

    public class Math3D
    {
        private const double Piover180 = Math.PI / 180.0;

        public static Vector RotateX(Vector point3D, float degrees)
        {
            //[ a  b  c ] [ x ]   [ x*a + y*b + z*c ]
            //[ d  e  f ] [ y ] = [ x*d + y*e + z*f ]
            //[ g  h  i ] [ z ]   [ x*g + y*h + z*i ]

            //[ 1    0        0   ]
            //[ 0   cos(x)  sin(x)]
            //[ 0   -sin(x) cos(x)]

            var cDegrees = degrees * Piover180;
            var cosDegrees = Math.Cos(cDegrees);
            var sinDegrees = Math.Sin(cDegrees);

            var y = (point3D.Y * cosDegrees) + (point3D.Z * sinDegrees);
            var z = (point3D.Y * -sinDegrees) + (point3D.Z * cosDegrees);

            return new Vector(point3D.X, y, z);
        }

        public static Vector RotateY(Vector point3D, float degrees)
        {
            //[ cos(x)   0    sin(x)]
            //[   0      1      0   ]
            //[-sin(x)   0    cos(x)]

            var cDegrees = degrees * Piover180;
            var cosDegrees = Math.Cos(cDegrees);
            var sinDegrees = Math.Sin(cDegrees);

            var x = (point3D.X * cosDegrees) + (point3D.Z * sinDegrees);
            var z = (point3D.X * -sinDegrees) + (point3D.Z * cosDegrees);

            return new Vector(x, point3D.Y, z);
        }

        public static Vector RotateZ(Vector point3D, float degrees)
        {
            //[ cos(x)  sin(x) 0]
            //[ -sin(x) cos(x) 0]
            //[    0     0     1]

            var cDegrees = degrees * Piover180;
            var cosDegrees = Math.Cos(cDegrees);
            var sinDegrees = Math.Sin(cDegrees);

            var x = (point3D.X * cosDegrees) + (point3D.Y * sinDegrees);
            var y = (point3D.X * -sinDegrees) + (point3D.Y * cosDegrees);

            return new Vector(x, y, point3D.Z);
        }

        public static Vector Translate(Vector points3D, Vector oldOrigin, Vector newOrigin)
        {
            var difference = new Vector(newOrigin.X - oldOrigin.X, newOrigin.Y - oldOrigin.Y,
                                          newOrigin.Z - oldOrigin.Z);
            points3D.X += difference.X;
            points3D.Y += difference.Y;
            points3D.Z += difference.Z;
            return points3D;
        }

        public static Vector[] RotateX(Vector[] points3D, float degrees)
        {
            for (var i = 0; i < points3D.Length; i++)
            {
                points3D[i] = RotateX(points3D[i], degrees);
            }
            return points3D;
        }

        public static Vector[] RotateY(Vector[] points3D, float degrees)
        {
            for (var i = 0; i < points3D.Length; i++)
            {
                points3D[i] = RotateY(points3D[i], degrees);
            }
            return points3D;
        }

        public static Vector[] RotateZ(Vector[] points3D, float degrees)
        {
            for (var i = 0; i < points3D.Length; i++)
            {
                points3D[i] = RotateZ(points3D[i], degrees);
            }
            return points3D;
        }

        public static Vector[] Translate(Vector[] points3D, Vector oldOrigin, Vector newOrigin)
        {
            for (var i = 0; i < points3D.Length; i++)
            {
                points3D[i] = Translate(points3D[i], oldOrigin, newOrigin);
            }
            return points3D;
        }

        #region Nested type: Camera

        internal class Camera
        {
            public Vector Position = new Vector();
        }

        #endregion

        #region Nested type: Cube

        public class Cube
        {
            private readonly Vector _cubeOrigin;
            public int Depth;

            private bool _drawWires = true;
            private Face[] _faces;
            public int Height;
            public int Width;
            private float _xRotation;
            private float _yRotation;
            private float _zRotation;

            public float RotateX
            {
                get { return _xRotation; }
                set
                {
                    RotateCubeX(value - _xRotation);
                    _xRotation = value;
                }
            }

            public float RotateY
            {
                get { return _yRotation; }
                set
                {
                    RotateCubeY(value - _yRotation);
                    _yRotation = value;
                }
            }

            public float RotateZ
            {
                get { return _zRotation; }
                set
                {
                    RotateCubeZ(value - _zRotation);
                    _zRotation = value;
                }
            }

            public bool DrawWires
            {
                get { return _drawWires; }
                set { _drawWires = value; }
            }

            public bool FillFront { get; set; }

            public bool FillBack { get; set; }

            public bool FillLeft { get; set; }

            public bool FillRight { get; set; }

            public bool FillTop { get; set; }

            public bool FillBottom { get; set; }

            #region Initializers

            public Cube(int width, int height, int depth)
            {
                Width = width;
                Height = height;
                Depth = depth;

                _cubeOrigin = new Vector(Width / 2, Height / 2, Depth / 2);

                InitializeCube();
            }

            #endregion

            private void InitializeCube()
            {
                //Fill in the cube

                _faces = new Face[6]; //cube has 6 faces

                //Front Face --------------------------------------------
                _faces[0] = new Face { CubeSide = Face.Side.Front, Corners3D = new Vector[4] };
                _faces[0].Corners3D[0] = new Vector(0, 0, 0);
                _faces[0].Corners3D[1] = new Vector(0, Height, 0);
                _faces[0].Corners3D[2] = new Vector(Width, Height, 0);
                _faces[0].Corners3D[3] = new Vector(Width, 0, 0);
                _faces[0].Center = new Vector(Width / 2, Height / 2, 0);
                // -------------------------------------------------------

                //Back Face --------------------------------------------
                _faces[1] = new Face { CubeSide = Face.Side.Back, Corners3D = new Vector[4] };
                _faces[1].Corners3D[0] = new Vector(0, 0, Depth);
                _faces[1].Corners3D[1] = new Vector(0, Height, Depth);
                _faces[1].Corners3D[2] = new Vector(Width, Height, Depth);
                _faces[1].Corners3D[3] = new Vector(Width, 0, Depth);
                _faces[1].Center = new Vector(Width / 2, Height / 2, Depth);
                // -------------------------------------------------------

                //Left Face --------------------------------------------
                _faces[2] = new Face { CubeSide = Face.Side.Left, Corners3D = new Vector[4] };
                _faces[2].Corners3D[0] = new Vector(0, 0, 0);
                _faces[2].Corners3D[1] = new Vector(0, 0, Depth);
                _faces[2].Corners3D[2] = new Vector(0, Height, Depth);
                _faces[2].Corners3D[3] = new Vector(0, Height, 0);
                _faces[2].Center = new Vector(0, Height / 2, Depth / 2);
                // -------------------------------------------------------

                //Right Face --------------------------------------------
                _faces[3] = new Face { CubeSide = Face.Side.Right, Corners3D = new Vector[4] };
                _faces[3].Corners3D[0] = new Vector(Width, 0, 0);
                _faces[3].Corners3D[1] = new Vector(Width, 0, Depth);
                _faces[3].Corners3D[2] = new Vector(Width, Height, Depth);
                _faces[3].Corners3D[3] = new Vector(Width, Height, 0);
                _faces[3].Center = new Vector(Width, Height / 2, Depth / 2);
                // -------------------------------------------------------

                //Top Face --------------------------------------------
                _faces[4] = new Face { CubeSide = Face.Side.Top, Corners3D = new Vector[4] };
                _faces[4].Corners3D[0] = new Vector(0, 0, 0);
                _faces[4].Corners3D[1] = new Vector(0, 0, Depth);
                _faces[4].Corners3D[2] = new Vector(Width, 0, Depth);
                _faces[4].Corners3D[3] = new Vector(Width, 0, 0);
                _faces[4].Center = new Vector(Width / 2, 0, Depth / 2);
                // -------------------------------------------------------

                //Bottom Face --------------------------------------------
                _faces[5] = new Face { CubeSide = Face.Side.Bottom, Corners3D = new Vector[4] };
                _faces[5].Corners3D[0] = new Vector(0, Height, 0);
                _faces[5].Corners3D[1] = new Vector(0, Height, Depth);
                _faces[5].Corners3D[2] = new Vector(Width, Height, Depth);
                _faces[5].Corners3D[3] = new Vector(Width, Height, 0);
                _faces[5].Center = new Vector(Width / 2, Height, Depth / 2);
                // -------------------------------------------------------
            }

            //Calculates the 2D points of each face
            private void Update2DPoints(Point drawOrigin)
            {
                //Update the 2D points of all the faces
                for (var i = 0; i < _faces.Length; i++)
                {
                    Update2DPoints(drawOrigin, i);
                }
            }

            private void Update2DPoints(Point drawOrigin, int faceIndex)
            {
                //Calculates the projected coordinates of the 3D points in a cube face
                var point2D = new Point[4];

                //Convert 3D Points to 2D
                for (var i = 0; i < point2D.Length; i++)
                {
                    var vec = _faces[faceIndex].Corners3D[i];
                    point2D[i] = Get2D(vec, drawOrigin);
                }

                //Update face
                _faces[faceIndex].Corners2D = point2D;
            }

            //Rotating methods, has to translate the cube to the rotation point (center), rotate, and translate back

            private void RotateCubeX(float deltaX)
            {
                foreach (var t in _faces)
                {
                    //Apply rotation
                    //------Rotate points
                    var point0 = new Vector(0, 0, 0);
                    t.Corners3D = Translate(t.Corners3D, _cubeOrigin, point0); //Move corner to origin
                    t.Corners3D = Math3D.RotateX(t.Corners3D, deltaX);
                    t.Corners3D = Translate(t.Corners3D, point0, _cubeOrigin); //Move back

                    //-------Rotate center
                    t.Center = Translate(t.Center, _cubeOrigin, point0);
                    t.Center = Math3D.RotateX(t.Center, deltaX);
                    t.Center = Translate(t.Center, point0, _cubeOrigin);
                }
            }

            private void RotateCubeY(float deltaY)
            {
                foreach (var t in _faces)
                {
                    //Apply rotation
                    //------Rotate points
                    var point0 = new Vector(0, 0, 0);
                    t.Corners3D = Translate(t.Corners3D, _cubeOrigin, point0); //Move corner to origin
                    t.Corners3D = Math3D.RotateY(t.Corners3D, deltaY);
                    t.Corners3D = Translate(t.Corners3D, point0, _cubeOrigin); //Move back

                    //-------Rotate center
                    t.Center = Translate(t.Center, _cubeOrigin, point0);
                    t.Center = Math3D.RotateY(t.Center, deltaY);
                    t.Center = Translate(t.Center, point0, _cubeOrigin);
                }
            }

            private void RotateCubeZ(float deltaZ)
            {
                foreach (var t in _faces)
                {
                    //Apply rotation
                    //------Rotate points
                    var point0 = new Vector(0, 0, 0);
                    t.Corners3D = Translate(t.Corners3D, _cubeOrigin, point0); //Move corner to origin
                    t.Corners3D = Math3D.RotateZ(t.Corners3D, deltaZ);
                    t.Corners3D = Translate(t.Corners3D, point0, _cubeOrigin); //Move back

                    //-------Rotate center
                    t.Center = Translate(t.Center, _cubeOrigin, point0);
                    t.Center = Math3D.RotateZ(t.Center, deltaZ);
                    t.Center = Translate(t.Center, point0, _cubeOrigin);
                }
            }

            public Rectangle DrawCube(Point drawOrigin)
            {
                //Get the corresponding 2D
                Update2DPoints(drawOrigin);

                //Get the bounds of the final bitmap
                var bounds = GetDrawingBounds();
                bounds.Width += drawOrigin.X;
                bounds.Height += drawOrigin.Y;

                Array.Sort(_faces); //sort faces from closets to farthest

                for (var i = _faces.Length - 1; i >= 0; i--) //draw faces from back to front
                {
                    if (!_drawWires) continue;

                    bounds.DrawLine(_faces[i].Corners2D[0], _faces[i].Corners2D[1]);
                    bounds.DrawLine(_faces[i].Corners2D[1], _faces[i].Corners2D[2]);
                    bounds.DrawLine(_faces[i].Corners2D[2], _faces[i].Corners2D[3]);
                    bounds.DrawLine(_faces[i].Corners2D[3], _faces[i].Corners2D[0]);
                }

                return bounds;
            }

            //Converts 3D points to 2D points
            private Point Get2D(Vector vec, Point drawOrigin)
            {
                var point2D = Get2D(vec);
                return new Point(point2D.X + drawOrigin.X, point2D.Y + drawOrigin.Y);
            }

            private Point Get2D(Vector vec)
            {
                var returnPoint = new Point();

                var tempCam = new Camera { Position = { X = _cubeOrigin.X, Y = _cubeOrigin.Y, Z = _cubeOrigin.X / _cubeOrigin.X } };

                var zValue = -vec.Z - tempCam.Position.Z;

                returnPoint.X = (tempCam.Position.X - vec.X) / zValue;
                returnPoint.Y = (tempCam.Position.Y - vec.Y) / zValue;

                return returnPoint;
            }

            public Point[] GetFrontFace()
            {
                return GetFace(Face.Side.Front).Corners2D;
            }

            public Point[] GetBackFace()
            {
                return GetFace(Face.Side.Back).Corners2D;
            }

            public Point[] GetRightFace()
            {
                return GetFace(Face.Side.Right).Corners2D;
            }

            public Point[] GetLeftFace()
            {
                return GetFace(Face.Side.Left).Corners2D;
            }

            public Point[] GetTopFace()
            {
                return GetFace(Face.Side.Top).Corners2D;
            }

            public Point[] GetBottomFace()
            {
                return GetFace(Face.Side.Bottom).Corners2D;
            }

            private Face GetFace(Face.Side side)
            {
                return _faces.FirstOrDefault(t => t.CubeSide == side);
            }

            private Rectangle GetDrawingBounds()
            {
                //Find the farthest most points to calculate the size of the returning bitmap
                var left = double.MaxValue;
                var right = double.MinValue;
                var top = double.MaxValue;
                var bottom = double.MinValue;

                foreach (var t1 in _faces.SelectMany(t => t.Corners2D))
                {
                    if (t1.X < left)
                        left = t1.X;
                    if (t1.X > right)
                        right = t1.X;
                    if (t1.Y < top)
                        top = t1.Y;
                    if (t1.Y > bottom)
                        bottom = t1.Y;
                }

                return new Rectangle(Math.Round(right - left), Math.Round(bottom - top));
            }

            #region Nested type: Face

            internal class Face : IComparable<Face>
            {
                #region Side enum

                public enum Side
                {
                    Front,
                    Back,
                    Left,
                    Right,
                    Top,
                    Bottom
                }

                #endregion

                public Vector Center;

                public Point[] Corners2D;
                public Vector[] Corners3D;
                public Side CubeSide;

                #region IComparable<Face> Members

                public int CompareTo(Face otherFace)
                {
                    return (int)(Center.Z - otherFace.Center.Z); //In order of which is closest to the screen
                }

                #endregion
            }

            #endregion
        }

        #endregion
    }
}
Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s