PostGIS raster geoTiff simple Lat/Lon query example

Question

I've successfully loaded a geoTif into PostGIS (Postgresql 9.3.9) using the following command on my Ubuntu 14.04 server:-

sudo -u postgres raster2pgsql -s 4326 -d -I -C -M -R -l 4 /path/to/my_raster.tif -F -t 10000x10000 my_raster | sudo -u postgres psql -d my_database

It's a 1 banded grayscale raster (lat/lon) with cell values representing some environmental factor on the ground. Hence, I simply want to get the cell value at a given lat/lon.

Following some examples I found I came up with the following SQL:-

SELECT
  ST_Value( rast, ST_SetSRID(ST_MakePoint(0,51.5),4326) ) AS rast_val
FROM 
  my_raster
WHERE
  ST_Value( rast, ST_SetSRID(ST_MakePoint(0,51.5),4326) ) IS NOT NULL;

This seems to get the right value for me when I cross reference the same location loading the raster in QGIS.

My question is basically a sanity check if the above SQL is correct as I am not 100% sure of the WHERE clause.

Answer 1

A faster way is to cut your raster into small tiles while loading it with the -t option (e.g. 100x100). Make sure also to build an index on the tiles with the -I option.

Then you can query the pixel values with a query like this taking advantage of the indexed tiles:

SELECT ST_Value(rast, ST_SetSRID(ST_MakePoint(0,51.5),4326)) AS val
FROM my_raster
WHERE ST_Intersects(rast, ST_SetSRID(ST_MakePoint(0,51.5),4326));

Should be way faster....