3

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?

|improve this question|||||
  • Could you explain why you would ever want to look at many times 10000 values? – Pierre Racine Feb 24 at 14:43
  • Actually, I want to store the pixel value of each cell in a tabular form. and reclassify the raster values in 3 classes as low, medium and high. After that, I want to clip those raster with some administrative boundaries (district) and calculate the number of pixels having low, medium and high value as a form of three different column for each administrative boundary. – Tekson Feb 24 at 16:25
  • 2
    Then why not use ST_Reclass() to reclass the rasters to three values (1, 2, 3)? And then ST_ValueCount() to count the number of each value? – Pierre Racine Feb 24 at 17:09
  • Thank you very much for the suggestion. But I want to check the value first then I am planning to reclass and count the value. Thank you again I will do that. – Tekson Feb 26 at 1:54
3

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 use filenamein 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.

|improve this answer|||||
  • Thank you for your answer. Then you mean i have to remove -t 100×100 while uploading? – Tekson Feb 24 at 2:22
2

You can play around with ST_PixelAsPoints which returns almost exactly what you want:

select (ST_PixelAsPoints(rast)).* from raster_table

This will produce rows with geometry, value, x and y coordinate of each pixel in your raster_table. You can then, of course, include where clause to limit results only to some rasters (or geographic area, etc.)

|improve this answer|||||
  • The query takes lots of time to run. I am unable to see the result. – Tekson Feb 24 at 8:09
  • 1
    @Tekson It produces one row for each pixel in your raster dataset. As user30184 stated in previous answer, this can produce huge amount of rows, even for a relatively small raster... – DavidP Feb 24 at 8:30
  • Thank you for explanation. But I am still unable to see the result of query. May be my raster file is too big. – Tekson Feb 24 at 16:28
  • 1
    @Tekson Try to limit evaluated number of raster with where clause, something like "where rid = 1" should work. This will reduce total number of returned rows by limiting query to only one raster tile. – DavidP Feb 25 at 7:17
  • Ok thank you for the suggestion. – Tekson Feb 26 at 1:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.