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

Question

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?

score 5 · Accepted Answer · answered Oct 23 '13 at 14:30

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.

All of your updates could be combined into something like this:

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;

score 3 · Answer 2 · answered Oct 23 '13 at 12:34

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.

Mark Cupitt · Answer 3 · 2013-10-23T14:05:36.827

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)

If anyone has some better suggestions or code, please add an additional answer

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

3 Answers3