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?
Best Answer
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
usefilename
in 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.