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 ) 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]
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.
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.
- Avoiding erroneous results when using T-SQL Trigonometric Functions in SQL Server 2012
- SQL Server TSQL Aggregate Functions
- Our complete tutorial list
Last Update: 2016-07-26
About the author
View all my tips