Returning raster values from multiple raster tables via Lat/Lon in PostGIS?

Question

I am new to PostGIS and am unsure if I'm thinking of this correctly, but I'd like to return values from multiple raster tables in a PostGIS database in one go.

The purpose is to query legend values for a webmap based on the lat/long values of a cursor click. So, if there are multiple raster tables in the database e.g., raster1 and raster2, I would like to shoot lat and lon to the DB and return the values from both rasters in some sort of dictionary.

How would I go about doing so with an SQL command? Would I need to do some sort of spatial join? Multiple select statements?

Answer 1

I have no knowledge on how the GeoDjango raster model works but querying a raster table goes roughly the same way as a geometry table. I would take the following approach, provided that you know the names of your raster tables and each raster has only 1 band:

WITH mypoint AS (
  SELECT ST_SetSrid(ST_MakePoint(<x>,<y>),<mysrid>) geom
)
SELECT 
  ST_Value(a.rast, geom) AS value1,
  ST_Value(b.rast, geom) AS value2,
  ST_Value(c.rast, geom) AS value3
FROM mypoint p
LEFT JOIN table1 a ON (ST_Intersects(p.geom, a.rast))
LEFT JOIN table2 b ON (ST_Intersects(p.geom, b.rast))
LEFT JOIN table3 c ON (ST_Intersects(p.geom, c.rast))

Documentation and examples on ST_Value: https://postgis.net/docs/RT_ST_Value.html

Side note: make sure the raster table is tiled and indexed (it also works without but can make a large difference in speed)