title: Postgres/Postgis - Querying all values in clipped raster
body: <p>I'm currently using Postgres 9.1</p>
  
  <p>My goal is to clip a PostGIS raster with a polygon.  Then I would like either an postgres array or delimited set of values for each of the raster pixels that are contained within that polygon. Here is the query I have gotten so far: </p>
  
  <pre><code>SELECT  p.gid, (ST_ValueCount(ST_Clip(r.rast, p.geom))).value AS fval
  FROM temp_raster AS r, temp_shapefile AS p
  WHERE (r.start_time = 1384516800) 
  GROUP BY gid, fval;
  </code></pre>
  
  <p>This will output: </p>
  
  <pre><code> gid | fcstval 
  -----+---------
     1 |       0
     1 |       2
     2 |       0
     2 |       2
     3 |       5
     4 |       0
     4 |       1
     4 |       2
     4 |       3
     4 |       5
  </code></pre>
  
  <p>This data is good, but I would like a set of values for each gid like this:</p>
  
  <pre><code> gid |  fcstval 
  -----+----------
     1 |       0,2
     2 |       0,2
     3 |         5
     4 | 0,1,2,3,5
  </code></pre>
  
  <p>The trouble I'm having is trying to then get these values aggregated into either an array or delimited string.  Here's my attempt at an array:</p>
  
  <pre><code>SELECT  p.gid, array_agg((ST_ValueCount(ST_Clip(r.rast, p.geom))).value) AS fval
  FROM temp_raster AS r, temp_shapefile AS p
  WHERE (r.start_time = 1384516800)
  GROUP BY gid;
  </code></pre>
  
  <p>This doesn't work, and provides the error:</p>
  
  <pre><code>ERROR:  set-valued function called in context that cannot accept a set
  </code></pre>
  
  <p>My guess is this is because I can't call array_agg in this way.  I'm having a bit of difficulty figuring out how to do this otherwise.  A subquery perhaps? I haven't been able to come up with anything yet though to get this working.</p>
  
  <p>Thanks for any help!</p>