Learn more about SQL Server tools

mssqltips logo

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 this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more


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


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

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.

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]
rho=sqrt(x*x + y*y + z*z),

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:

next webcast 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    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


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.



Learn more about SQL Server tools