Consolidating Table rows in Postgres/PostGis

Question

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?

• Devdatta Tengshe: 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)?
• Mark Cupitt: @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
• Devdatta Tengshe: 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.
• Mark Cupitt: Ran it and so far so good. Will keep an eye out for that, thanks

Answer 1

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:

• Most performant SQL query needed
• What is best performance for Retrieving MySQL EAV results as Relational Table

• Mark Cupitt: worked like a charm ..
• Alexandre Neto: 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.

Answer 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!

• Devdatta Tengshe: 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
• Alexandre Neto: You are right, your answer is much better and cleaner. I will keep my answer just for the sake of the reasoning.