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.


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
 the_geom, array_agg( val ) -- all values for given pixel location
  SELECT ST_centroid( the_geom ) AS the_geom, val
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.