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;
My question is how can I avoid the repeating GID and combine the rast values into one line?