how to deal with raster2pgsql tiles?

Question

I imported raster file into PostGIS using raster2pgsql, I set the -t 50x50 and it generated about 500 rows. I know these -t divides the raster into small tiles and the rid can index them. I saw many examples using rid=2 in the where clause to specify the tile. But my question is how to process the entire raster rather than specific tile. For instance, when I use ST_SummaryStats, it generates stats about 500 row results for each tile. When I ST_Clip, it also generates about 500 rows of clipping results for each tile. How do I clip for the whole raster? Thanks!!

Answer 1

I guess ST_Union is what you're looking for. You first need to merge all tiles and then apply your operation.

Example using ST_SummaryStats.

db=# SELECT ST_SummaryStats(rast) FROM t;
               st_summarystats               
---------------------------------------------
 (100,24638,246.38,14.0176888251951,216,255)
 (100,23866,238.66,16.7488626479531,216,255)
 (100,22052,220.52,3.85092196752934,218,235)
 (100,22495,224.95,10.0173599316387,216,255)
 (100,22508,225.08,8.46720733181846,216,255)
 (100,22034,220.34,2.08911464501113,218,228)
 (100,22113,221.13,2.26121648676105,219,228)
 (100,22172,221.72,2.24089267926869,218,228)
 (100,22163,221.63,2.11969337405201,219,228)
 (100,22332,223.32,2.42024792118494,219,227)
(10 Zeilen)

Now the same operation using ST_Union:

db=# SELECT ST_SummaryStats(ST_Union(rast)) FROM t;
                st_summarystats                 
------------------------------------------------
 (1000,226373,226.373,11.8122762835958,216,255)
(1 Zeile)

For summary stats you can alternatively use ST_SummaryStatsAgg:

db=# SELECT ST_SummaryStatsAgg(rast,1,false) FROM t;
               st_summarystatsagg               
------------------------------------------------
 (1000,226373,226.373,11.8122762835958,216,255)
(1 Zeile)