I am new to working with rasters in PostGIS. Although I have spent couple of days trying to find solution for my problem, I couldn't seem to crack it without some help.
Here's my problem:
I have multiple overlapping rasters in a PostGIS table (about 500 records) with each raster size about 1200x1000 (30m res.) but varies since data is dumped from different sources. The table looks as follows:
-- Table "coverage_rast" id | rast (bigint)| (raster) -------------- 5489 | "02301ff..." 5489 | "238000f..." 5489 | "02301ff..." 1234 | "238000f..." 1234 | "02301ff..." 4567 | "238000f..." 4567 | "238000f..."
Each raster record represents a coverage with some DN values(ranging 0-100). When I perform
CREATE TABLE sometable as SELECT ST_UNION(rast,1,'MIN') from coverage_rast; gives me resultant raster with minimum values as DN values at each grid cell and takes about 2 minutes of query time. Instead I want that the resultant raster should be such that the DN value at each grid cell should represent "id" of the raster which has minimum value at that location.
I tried using
SELECT val,id FROM (SELECT ST_Value(rast, 1, geom) val,id FROM coverage_rast,(SELECT st_setsrid(ST_MakePoint(-80.85495, 35.21887),4326) geom)tbl
WHERE ST_Intersects(rast, geom)) as t1 ORDER BY val LIMIT 1 which gets the id and the minimum DN value at given location. Was thinking of extending this by creating a uniform grid of points spaced 30m apart and passing geom column and then creating a raster out of it, but this would be pretty slow and sloppy.
I am hoping to find a solution which can extend
st_union to assign
id value as DN of raster where existing DN value is minimum of all the overlapping rasters.