AnswerBun.com

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).*
FROM (
SELECT
rid,
    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

Connecting to Microsoft SQL Server from QGIS

1  Asked on April 1, 2021 by simon-gis

   

Nav message files clarification

0  Asked on April 1, 2021

   

Create voronoi diagram from line segments

5  Asked on April 1, 2021 by snorfalorpagus

   

Finding format/type of the vector geometry

2  Asked on April 1, 2021 by lyubomir-klyambarski

       

Python: gdal.Warp how to crate mosaic and don’t save?

0  Asked on March 31, 2021 by anotherquesrionplease

   

Rendering only a specific fraction of raster values

2  Asked on March 31, 2021 by ptw716

   

Ask a Question

Get help from others!

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