Where are raster pixel values stored in PostGIS?

Geographic Information Systems Asked by fjboogert on November 2, 2020

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?

2 Answers

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.

Correct answer by Ali on November 2, 2020

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).*
    ST_MetaData(rast) As rm,
ST_BandMetaData(rast,1) As rbm
FROM ear_agri) as r;

Answered by Tekson on November 2, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP