Where are raster pixel values stored in PostGIS?

Question

I have several raster images of temperature, they are stored as geotiffs and I import them to a PostgreSQL database using raster2pgsql.
raster2pgsql -s $SRID -t auto -I $file $SCHEMA.$(basename $file .tiff) | psql -U $USERNAME -d $DATABASE -h $HOST -p $PORT

But when I look at the data using pgadmin I see only an ID and rast column and no temperature values.
Using QGIS I can display original geotiff without any problems.
Any idea what is going wrong?

Ali · Accepted Answer

The values are stored in the raster object itself. You can access them using the ST_Value function which accepts column and row indexes or point geometries.
The ST_Value documentation has some good examples.

Tekson · Answer

For getting all the values of the pixel you can do something like the following:
SELECT x, y, ST_Value(rast, 1, x, y) As value_band1,
    ST_Value(rast, 2, x, y) As value_band2, 
    ST_Value(rast, 3, x, y) As value_band3
FROM your_raster_table_name 
CROSS JOIN generate_series(1, 1000) As x 
CROSS JOIN generate_series(1, 1000) As y
WHERE rid=1 AND x <= ST_Width(rast) AND y <= ST_Height(rast);

If your raster have only one band, then only select the value_band1. You can check some metadata and also the number of band used in your raster table by following code.
SELECT rid As r, (rm).upperleftx As ux, (rm).numbands As nb, (rbm).*
FROM (
SELECT
rid,
    ST_MetaData(rast) As rm,
ST_BandMetaData(rast,1) As rbm
FROM ear_agri) as r;

Where are raster pixel values stored in PostGIS?

2 Answers

Add your own answers!

Ask a Question