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

By: Dallas Snider | Comments (1) | Related: > TSQL

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.

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.

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.

About the author

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

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

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.