Only select non null values using st_value on Postgis tiled raster

Question

I have a table tmax_2016 that contains tiled raster data.

I believe the following query retrieves the raster value at a point on the given rast_date:

SELECT ST_Value(rast, ST_SetSRID(ST_MakePoint(-83.54005,31.59668),4269)) AS tmax FROM tmax_2016 WHERE rast_date='2016-09-28';

However since it's tiled, I always get 1 valid result and a bunch of nulls for the tiles that don't contain the asked for lat/long. How do I modify the query to not return the nulls?

The following pukes I believe because ST_Value is an agregate function?

SELECT ST_Value(rast, ST_SetSRID(ST_MakePoint(-83.54005,31.59668),4269)) AS tmax FROM tmax_2016 WHERE rast_date='2016-09-28' WHERE tmax IS NOT NULL;

I could get rid of the nulls using a nested select but is there a better way?

Answer 1

I just realized taking the union of the tiles would work:

SELECT ST_Value(ST_Union(rast), ST_SetSRID(ST_MakePoint(-83.54005,31.59668),4269)) AS tmax FROM tmax_2016 WHERE rast_date='2016-09-28';

EDIT - While the above works I was able to improve the query speed from about 3000ms to 30ms by doing an intersection in the WHERE clause instead of Unioning the tiles. So here is a better solution:

SELECT ST_Value(rast, ST_SetSRID(ST_MakePoint(-83.54005,31.59668),4269)) AS tmax FROM tmax_2016 WHERE rast_date='2016-09-28' AND ST_Intersects(rast, ST_SetSRID(ST_MakePoint(-83.54005,31.59668),4269));