Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question

1 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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