UPDATE: The SQL below works fine. My problem was a newbie mistake in using the Python psycopg2 functions to execute it. So I think I should just delete this question.
ORIGINAL QUESTION: I have a large number of possibly overlapping rasters and need to be able to sample all those that intersect a point at that point. My first attempt was to create a single table containing all of the rasters, and query as such:
SELECT ST_Value(rast, 1, ST_MakePoint(-122.0, 38.0)) FROM table_name WHERE ST_Intersects(rast, ST_MakePoint(-122.0, 38.0));
This returns a value for one raster, but not all of the intersecting rasters. It seems that a single raster coverage table is not designed to be used this way.
Is the proper approach to create separate tables, grouping together non-overlapping rasters?
What is the best strategy?
I am hoping to keep everything in a single table if possible. I am pretty new to PostGIS and SQL.