2

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?

2

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));
1
  • 1
    The last one is the right solution. – Pierre Racine Sep 30 '16 at 20:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.