Getting all the Raster pixel value from PostGIS

Question

I just uploaded the raster using following code,

raster2pgsql -s 4326 -I -C -M raster.tif -F -t 100x100 public.raster_table | psql -U postgres -p 5432 -h localhost -d raster

It takes around 15 min to upload to database. After complete the process, I checked the table using SELECT * FROM public.raster_table;. It has lots of rows (around 4000) with serial rid. I want to view the each pixel value using ST_value. I already checked the documentation. In documentation, It provides following method to show all the pixel value.

SELECT x, y, ST_Value(rast, 1, x, y) As raster_val,
FROM raster_table CROSS JOIN
generate_series(1, 1000) As x CROSS JOIN generate_series(1, 1000) As y
WHERE rid =  2 AND x <= ST_Width(rast) AND y <= ST_Height(rast);

But I am confused with WHERE condition. In example it only use rid=2. But I have lots of rid. Is this condition really checks the all the pixel value?

Answer 1

You imported the raster as tiled and then each tile is getting its own row into the raster table and also an unique primary key value into the "rid" column.

Your query returns all the pixel values from the tile 2 of your raster. You should see that from the number of returned rows that is 100x100=10000. If you want to get values from all the pixels of the whole raster with one query you can either

1) Import data without tiling. Then you'll get just one row into the raster table. I assume it will have rid=1 but you can check that from your database.

2) Import data as tiled but instead of rid use filenamein the query.

WHERE filename='raster_table'...

Notice that the number of rows may be hard to handle: million rows for a raster with just 1000 x 1000 pixels.

Answer 2

You can play around with ST_PixelAsPoints which returns almost exactly what you want:

select (ST_PixelAsPoints(rast)).* from raster_table

This will produce rows with geometry, value, x and y coordinate of each pixel in your raster_table. You can then, of course, include where clause to limit results only to some rasters (or geographic area, etc.)