# PostgreSQL Queries, how can I handle logic and complex mathematical functions to update a column

I have a Postgres Table of u and v wind velocity components as follows:

``````ugrid  vgrid
-2.7   -6
-2     -6.3
-1.7   -6.4
``````

I need to calculate the speed and direction as per the JScript Psuedocode below and store the result in two new new columns in the table:

``````uu = U Value
vv = V Value

if vv = 0 and uu = 0 then everything is zero
if vv = 0 and uu > 0 dd = 270, ff = uu
if uu < 0 dd = 90, ff = -uu
if vv < 0 dd=Math.atan(uu/vv)*180/Math.PI
else
dd=Math.atan(uu/vv)*180/Math.PI+180;

if dd <0 dd=dd+360
ff = Math.sqrt(uu*uu+vv*vv)
returns dd as wind direction in decimal degrees
returns ff as wind speed in m/s
``````

I have never worked with mathematical functions in Postgres.

What is the best approach, can a Query handle this type of logic, or should I process the data in some other system first?

If a query is possible, how would I construct it?

There's a function `ATAN2` that would greatly simplify things for you. It automatically handles the special cases where the horizontal or vertical components are zero.

``````UPDATE wind00 SET
speed = ROUND((3.6 * SQRT(ugrid*ugrid + vgrid*vgrid)) :: NUMERIC, 1),
direction = ROUND(DEGREES(ATAN2(vgrid, ugrid)) :: NUMERIC, 1)
``````

I also used the shorter `::NUMERIC` cast syntax to make things more compact. And the `DEGREES` function that more compactly does the radians-to-degrees conversion.

`ATAN2` returns values from -180 to 180 (well, from -π to π). So you might also want one more update to keep your values in the range of 0 to 360:

``````UPDATE wind00 SET direction=direction+360 WHERE direction < 0;
``````

And you'll also have to take care that the angles match your conventions for which way is "zero degrees". As written above, `ugrid` is the horizontal component with positive values being to the right, `vgrid` is the vertical component with positive values being up, zero degrees is to the right (due east), and 90 degrees is up (due north). You might have to switch the parameters to `ATAN2` and/or make one of them negated.

EDIT: Stealing an idea from Jakub's answer, the code above lends itself pretty well to making a view so that changes to your data don't require the additional step of performing the `UPDATE`:

``````CREATE VIEW wind00_with_speed_and_direction AS
SELECT ugrid, vgrid,
ROUND((3.6 * SQRT(ugrid*ugrid + vgrid*vgrid)) :: NUMERIC, 1) AS speed,
ROUND(DEGREES(ATAN2(vgrid, ugrid)) :: NUMERIC, 1) AS direction
FROM wind00;
``````

Yes, query can do that but you should rather use a function (PL/pgSQL or other PL/* that suits you). But you really shouldn't do it in that form as it will be hard to keep the columns up to date. You should rather create index on function or create a materialized view.

This is what I came up with. It is a little clumsy, but appears to work!

``````ALTER TABLE public.wind00 ADD COLUMN speed real, ADD COLUMN direction real ;
ALTER TABLE public.wind00 ALTER COLUMN speed SET DEFAULT 0, ALTER COLUMN direction SET   DEFAULT 0;
UPDATE public.wind00 SET direction = 270, speed = ugrid  WHERE (vgrid = 0 AND ugrid > 0 );
UPDATE public.wind00 SET direction = 90, speed = round ( cast ( ugrid * -1 as numeric ) , 1 )  WHERE (vgrid = 0 AND ugrid < 0);
UPDATE public.wind00 SET direction = round ( cast (atan(ugrid/vgrid)*180/pi() as numeric) ) WHERE (vgrid < 0 );
UPDATE public.wind00 SET direction = round( cast (atan(ugrid/vgrid)*180/pi()+180 as numeric) ) WHERE (vgrid > 0 );
UPDATE public.wind00 SET direction = round( cast (vgrid+360 as numeric)) WHERE ( vgrid < 0 );
UPDATE public.wind00 SET speed = sqrt(ugrid*ugrid+vgrid*vgrid) WHERE ( speed IS NULL );
UPDATE public.wind00 SET speed = round( cast (speed * 3.6  as numeric) , 1);
``````

(The last line converts to km/h from m/s)