ST_Value, multiple rasters - need unique columns for rast values

Question

I have a grid of cells that I am converting to centroids to 'sample' raster values in the same locations as the centroids. All rasters (4) are in the same table with same resolution and SRID.

I want to return rast values for each surface at each point. The rast values need to be placed in their own unique column based on the value in the 'filename' column and aliased to something more useful than 'rast' - slope, aspect, elev, and tpi. Each centroid will be one record and will have values in each of the aliased rast columns with no empty fields.

This query gets close:

WITH centroid AS ( 
 SELECT gid, ST_Centroid(grid.geom26912) as centroid_geom
 FROM production.grid
 WHERE field = 'sre'
 AND resol_ft = 60
)

SELECT DISTINCT gid, centroid_geom,
 CASE WHEN filename 
  LIKE 'sre10_a%' 
  THEN ST_Value(rast, centroid_geom) 
 END AS aspect_deg, 
 CASE WHEN filename 
  LIKE 'sre10_s%' 
  THEN ST_Value(rast, centroid_geom) 
 END AS slope_deg,
 CASE WHEN filename 
  LIKE 'sre10_e%' 
  THEN ST_Value(rast, centroid_geom) 
 END AS elev_m, 
 CASE WHEN filename 
  LIKE 'sre10_t%' 
  THEN ST_Value(rast, centroid_geom) 
 END AS tpi     
FROM production.surface10m, centroid
WHERE ST_Intersects(centroid_geom, rast)
 AND filename LIKE 'sre10%' 
ORDER BY gid;

However, this results in each gid being repeated 4 times, once for each raster. In each of the four columns there is one (aliased) rast value and 3 NULL values:

My question is how can I avoid the repeating GID and combine the rast values into one line?

Answer 1

You will need to group the results of the query you are doing. The query you are doing is typically called a cross-tab or pivot query.

Altering your query as follows should give you the result you want

WITH centroid AS ( 
 SELECT gid, ST_Centroid(grid.geom26912) as centroid_geom
 FROM production.grid
 WHERE field = 'sre'
 AND resol_ft = 60
)
SELECT gid, 
       ST_Union(centroid_geom) centroid_geom,
       MAX(aspect_deg) aspect_deg,
       MAX(slope_deg) slope_deg,
       MAX(elev_m) elev_m,
       MAX(tpi) tpi
FROM (
   SELECT gid, centroid_geom,
    CASE WHEN filename 
     LIKE 'sre10_a%' 
     THEN ST_Value(rast, centroid_geom) 
    END AS aspect_deg, 
    CASE WHEN filename 
     LIKE 'sre10_s%' 
     THEN ST_Value(rast, centroid_geom) 
    END AS slope_deg,
    CASE WHEN filename 
     LIKE 'sre10_e%' 
     THEN ST_Value(rast, centroid_geom) 
    END AS elev_m, 
    CASE WHEN filename 
     LIKE 'sre10_t%' 
     THEN ST_Value(rast, centroid_geom) 
    END AS tpi     
   FROM production.surface10m, centroid
   WHERE ST_Intersects(centroid_geom, rast)
    AND filename LIKE 'sre10%'
   ) A
GROUP BY GID
ORDER BY gid;

You may also want to have a look into the tablefunc extension for PostgreSQL.