Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Transforming Cartesian Coordinates to Spherical Coordinates in SQL Server with T-SQL


By:   |   Read Comments (1)   |   Related Tips: More > T-SQL

Attend a SQL Server Conference for FREE >> click to learn more


Problem

I need to transform Cartesian coordinate data in a SQL Server table to spherical coordinates. How can I do this in T-SQL?

Solution

During a recent research project working with triaxial accelerometers, I needed to convert force measurement data in Cartesian coordinates to spherical coordinates. In this tip, I will show you how this can be done.

For this tip, let's begin by creating a table and inserting some example rows. The CREATE TABLE statement below has a primary key column, the three Cartesian coordinate columns (x, y and z) and the three spherical coordinate columns (rho, phi and theta).

CREATE TABLE [dbo].[tblCoordinateData]
(
  pKey integer not null identity(1,1) primary key,
  x float,
  y float,
  z float,
  rho float,
  phi float,
  theta float
) ON [PRIMARY]

The INSERT statements below will use 1E-12 to approximate zero. I have chosen to do this to avoid divide by zero errors when we execute the T-SQL that will perform the conversion. After inserting the rows, we will select all columns and rows in our table to verify all is well.

insert into [dbo].[tblCoordinateData] (x,y,z) values (1,0.000000000001,0.000000000001)
insert into [dbo].[tblCoordinateData] (x,y,z) values (0.000000000001,1,0.000000000001)
insert into [dbo].[tblCoordinateData] (x,y,z) values (0.000000000001,0.000000000001,1)
insert into [dbo].[tblCoordinateData] (x,y,z) values (0.000000000001,1,1)
insert into [dbo].[tblCoordinateData] (x,y,z) values (0.000000000001,-2*sqrt(2),0.000000000001)
insert into [dbo].[tblCoordinateData] (x,y,z) values (1,1,1)
insert into [dbo].[tblCoordinateData] (x,y,z) values (3,4,5)

select * from [dbo].[tblCoordinateData]


Examining values inserted into the table.

Now we are ready to perform our coordinate system transformations. We will use the following formulas for our conversions:

  • rho = sqrt(x*x + y*y + z*z)
  • phi = atan(sqrt((x*x)+(y*y))/z) as phi
  • theta = tan(y/x).

We will first perform a SELECT statement to verify our calculations are correct. The formulas above are nested in the DEGREES function to convert phi and theta from radians to degrees. The DEGREES function is nested in the ROUND function to help in displaying the results from the query. The T-SQL for the SELECT statement is shown below.

select 
round(x,3) as x,
round(y,3) as y,
round(z,3) as z,
round(sqrt(x*x + y*y + z*z),3) as rho,
round(degrees(atan(sqrt((x*x)+(y*y))/z)),3) as phi,
round(degrees(atan(y/x)),3) as theta
from [dbo].[tblCoordinateData]

The results from the SELECT statement are in the next image.

The results from the SELECT statement

In the T-SQL code below, we will execute an UPDATE statement to populate our table. Notice how the ROUND functions have been removed.

update [dbo].[tblCoordinateData]
set 
rho=sqrt(x*x + y*y + z*z),
phi=degrees(atan(sqrt((x*x)+(y*y))/z)),
theta=degrees(atan(y/x))

The screen snip below shows the results from the SELECT statement on our updated table.

Selecting from the updated table
Next Steps

As you implement this code, make sure you take into consideration code to avoid divide by zero errors. Also, make sure the report renders correctly on the report server. Finally, please check out these other tips and tutorials on T-SQL on MSSQLTips.com.



Last Update:


signup button

next tip button



About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, July 27, 2016 - 12:05:23 PM - Timothy A Wiseman Back To Top

I like this tip.  While T-SQL is generally not the best language to do mathematics in, this is simple enough that being able to do it in a set based manner can be convenient when dealing with large data sets, especially if you will be storing the results instead of of using and then discarding them.

 

Thanks.


Learn more about SQL Server tools