[postgis-users] Basic postgis raster questions

[Pierre Racine]

>1] Globally (i.e., not for an individual tile, but for the entire scene) I want one histogram of raster values for pixels which are inside the fire perimeter, and one histogram of raster values for pixels which are outside. 

Stats functions like ST_Histogram() do not allow yet to compute stats restricted to the area of a polygon directly from raster objects. We are working on that. But if your raster dataset is small enough you can convert everything to vector and compute those stats in vector mode.

>2] For each individual tile, I want separately computed histograms for pixels inside and outside the fire perimeter. 

>My understanding is that I cannot do #2 because ST_Intersection() always returns a setof geomval, and never returns a "subset raster". 

Right. But you can compute stats on the result of ST_Intersection() using ST_Area(geom)/areaOfOnePixel.  Since the results are vector, the ST_histogram function is useless here.

>I believe I cannot do #1 because in addition to this ST_Intersection behavior, there is no way to re-assemble the tiles into one giant raster prior to feeding it to ST_Histogram. It appears I can run ST_Histogram only on individual, whole tiles.

It is true that PostGIS raster do not allow yet to reassemble raster tiles into bigger rasters but you don't have to do that in order to compute histogram on a tiled coverage. Just compute the histogram for every tiles and aggregate the results using a GROUP BY query. (Bborie: shouldn't we have a ST_Histogram variant working on a whole table like we have for the other stat functions?)

>I am currently pursuing a vector solution using ST_DumpAsPolygons() but this is very slow. Hence I have time to send questions to the list. :) 

If the polygon area is small comparing with the raster area you should retile your raster coverage into smaller tiles so the query has to vectorize less useless raster area. It is important to index the coverage and to add "WHERE ST_Intersects()" in this case. Make sure also that both your raster coverage and your vector coverage have indexes. This makes a HUGE difference. I see that you don't do that in your tutorial. You can do it while loading your data with the "-I" option or afterward with a "CREATE INDEX" query.

>I have outlined my vector plan of attack here: https://collab.firelab.org/software/projects/modisfire/wiki/Calculating_histograms_with_PostGIS_Raster  If you believe this to be a worthy tutorial-in-progress, you may copy or link to it as you see fit. I expect it will change as I pursue this activity further. 

As I said we are very near to have a geometry parameter to the stats functions restricting the computation to defined area. Once we have this all your tutorial should be much shorter... Converting raster to vector solutions are easy when raster coverages are small but impracticable on large raster datasets (this is why we developed PostGIS raster!).

Pierre