Creating a vector based design from a raster database

Question

I have a raster table that I've created using Postgis raster. It has 365 records and the table looks like this:

+-----+------------+---------+------------+
| rid |  rast      |filename |date_of data|
+-----+------------+---------+------------+
| 100 |            | file56  |2002-01-09  |
| 101 |            | file57  |2002-01-10  |
+-----+------------+---------+------------+

The second column rast is raster files in WKT format and data type is raster. All my raster files have the same extents and cellsizes.

I want to turn this raster design into a vector design like the following:

+-----+---------------+----------+
| ID  |pixel_centroid |array_366 |
+-----+---------------+----------+
| 1200|               |{26,20,..}|
| 1201|               |{28,68...}|   
+-----+---------------+----------+

Imagine the raster files have 1600 pixels and accordingly I will have 1600 records in my new table. 'Pixel_centroid' is the point geometry of the pixels and could be calculated using

select ST_centroid((ST_PixelAsPolygons(rast, 1)).geom)  from raster_table

and array_366 is pixel values of all the records in the order of date_of_data:

select (ST_PixelAsPolygons(rast, 1)).val  from raster_table

might be used but I don't know how to put all the values in their place and populate the array.

Answer 1

Well I could not understand the vector design? Did you mean an array design?

Vector for me would be a geometry column for the pixel values.

To do this in a one shot you would do something like this based on what you wrote, this a lot hypothetical since I did not have a postgis 2 dataset yet:

TRUNCATE vector_design; -- your results table
INSERT INTO vector_design ( pixel_centroid, pixel_data ) -- pixel data array type must match the band type or be a double precision to hold all types
SELECT 
 the_geom, array_agg( val ) -- all values for given pixel location
FROM
(
  SELECT ST_centroid( the_geom ) AS the_geom, val
  FROM 
  (
    SELECT (ST_PixelAsPolygons( rast, 1 )).geom AS the_geom, (ST_PixelAsPolygons( rast, 1 )).val AS val
    FROM raster_table
    ORDER BY date_of -- relevant ??
  ) AS source
) centroid_grouped
GROUP BY the_geom -- will aggregate by pixel location