I am trying to use PostGIS to create something similar to the attribute table produced when two raster files are combined using the ArcGIS Spatial Analyst function "Combine". This shows the count of unique combinations of values associated with each pixel across each raster.
I have a functioning query for a very small (8 pixel by 8 pixel) multiband raster which replicates this function where merged_rasters is my PostGIS raster table with 2 bands.
SELECT b1val, b2val, COUNT(*) FROM( SELECT ST_Value(rast, 1, x, y) As b1val, ST_Value(rast, 2, x, y) As b2val FROM merged_rasters CROSS JOIN generate_series(1, 10) As x CROSS JOIN generate_series(1, 10) As y WHERE x <= ST_Width(rast) AND y <= ST_Height(rast) ) as merged_values GROUP BY b1val, b2val;
However, when I try using the ST_Value on larger rasters, even tiled rasters with a tile size of 256x256, this becomes very slow.
How would I improve performance?