3

I have a table of weather data as follows:

Table Name: WindGrib

UGRD    -179    -90 2.9
VGRD    -179    -90 5.9
UGRD    -179    -89 1.6
VGRD    -179    -89 6.4
UGRD    -179    -88 -4.2
VGRD    -179    -88 11.3
UGRD    -179    -87 -4.6
VGRD    -179    -87 10.3
UGRD    -179    -86 -12.2
VGRD    -179    -86 13.1

UGRD and VGRD are at the same lon/lats in every case

The source for this data is a wgrib2 extract from a grib file and it looks like this:

"2013-10-21 00:00:00","2013-10-21 00:00:00","UGRD","planetary boundary layer",0,-90,-3

That is all we get ..

The date columns and the "planetary boundary layer" all have the same data.

I need to be able to move the above data into a new table as follows

New table Name: Wind

Geom(POINT,4326)    UGRD    VGRD
-179/-89            2.9     5.9
-179/-88            1.6     6.4

and so on

What PostGres/PostGis Query should I use?

4
  • You have what is called an EAV model which is notoriously difficult to query. Ideally there is another field, mostly for TimeStamp, or Observation number, which can be used to group the data in a query. It is missing in your Data(For example, how do we know that UGRD=2.9 & VGRID=5.9 belong together)? Oct 23, 2013 at 8:35
  • @Devdatta Tengshe I assume, hopefully correctly, that the lat and lon will be unique and could be used to "group" the points. I have updated the question to show the source data from wgrib2 Oct 23, 2013 at 8:53
  • It might be helpful to confirm your assumptions by doing a SQL Query with Distinct. It might also be possible that your source data have multiple data at one Lat long for different timestamps. Oct 23, 2013 at 9:05
  • Ran it and so far so good. Will keep an eye out for that, thanks Oct 23, 2013 at 10:04

2 Answers 2

5

Ok, I'm assuming your Table is built is like this:

Create TABLE ObsData (Type Text, X smallint, Y smallint, Val real);

Now you want to make a self join based on the X & Y Fields. Hence you should use a sql Join like this:

Select ST_SetSRID(ST_MakePoint(a.X, a.Y), 4326) as geom,  a.Val as UGRD, b.Val as VGrid
from ObsData a 
inner Join ObsData b on a.X=b.X AND a.Y=b.Y
where a.Type='UGRD' AND b.Type='VGRD'

Here is a working SQL Fiddle.

It might also be possible to do an Aggregate Query, Like this:

Select ST_SetSRID(ST_MakePoint(a.X, a.Y), 4326) as geom,
    Max(Case When Type='UGRD' Then CAST (val as real) END) as UGRD,
    Max(Case When Type='VGRD' Then CAST (val as real) END) as VGRD
from ObsData
Group BY X, Y;

Do note that these kinds of queries are horrendously inefficient and might take some time. The query could be speed-ed up by using Indexes on the X & Y Fields.

If you are really concerned about Performance, and you should be only if you need to do this query repeatedly, you should have a look at the following questions on our sister sites:

2
  • worked like a charm .. Oct 23, 2013 at 9:16
  • I would only put a ST_SetSRID(ST_MakePoint(a.X, a.Y) as geom in the SELECT, since @Mark C wanted a geometry int result. Oct 23, 2013 at 9:18
2

The way I thought about your question was that I would need to separate the two sets of data (UGRD, VGRD) using somekind of subquerys of your original table:

SELECT
        ST_SetSRID(ST_MakePoint(lon, lat), 4326) as geom
    FROM
        "WindGrib"
    WHERE
        name = "UGRD";

and

SELECT
    ST_SetSRID(ST_MakePoint(lon, lat), 4326) as geom
FROM
    "WindGrib"
WHERE
    name = "VGRD";

Then, starting from one ot the sub-queries, I would need to join the other by their common field, in this case the 'geom', using the ST_Equals() function like this:

WITH f as
    (SELECT
        ST_SetSRID(ST_MakePoint(lon, lat), 4326) as geom,
        grib as grib
    FROM
        "WindGrib"
    WHERE
        name = "UGRD")
SELECT
    ST_SetSRID(ST_MakePoint(g.lon, g.lat), 4326) as geom,
    f.grib as "UGRD"
    g.grib as "VGRD"
FROM
    "WindGrib" as g JOIN f ON ST_Equals(f.geom, g.geom)
WHERE
    g.name = "VGRD";

If you want to save the query result as a table, all you need to do is add the CREATE TABLE AS statement:

CREATE TABLE new_gribwind AS
WITH f as
    (SELECT
        ST_SetSRID(ST_MakePoint(lon, lat), 4326) as geom,
        grib as grib
    FROM
        "WindGrib"
    WHERE
        name = "UGRD")
SELECT
    ST_SetSRID(ST_MakePoint(g.lon, g.lat), 4326) as geom,
    f.grib as "UGRD"
    g.grib as "VGRD"
FROM
    "WindGrib" as g JOIN f ON ST_Equals(f.geom, g.geom)
WHERE
    g.name = "VGRD";

Give it a try and see if works.

Hope it helps!

2
  • Geometric Operations are usually much more resource Intensive that simple numeric comparisons. Hence the Efficiency could be increased by using Numeric equality, instead of ST_Equals Oct 23, 2013 at 9:10
  • You are right, your answer is much better and cleaner. I will keep my answer just for the sake of the reasoning. Oct 23, 2013 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.