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!

Related Questions

Adding gdal nextgis borsch as a cmake external project

0  Asked on January 17, 2021 by arnaud-becheler


Cell/polygon equivalency issue

1  Asked on January 16, 2021 by arthur_morgan


Geoserver HTTP ERROR 500 on layer preview

1  Asked on January 16, 2021 by pablo-villa


Band calculation error with rasterio in Python

0  Asked on January 16, 2021 by ballybee


QGIS crashes when setting new Symbol to layer with python

1  Asked on January 16, 2021 by blinxen


Exporting a region to Drive with an Earth Engine App?

0  Asked on January 16, 2021 by adriano-matos


Ask a Question

Get help from others!

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