postgis - How to speed up queries for raster databases?

I have a raster database in postgresql/postgis with these columns:

(ID, rast, data_of_data).

'rast' is the column that has raster files in WKT format. An example query to find the DN value of a point in WGS84 system (30.424, -1.66) and for 2002-01-09 is the following:

SELECT 
     st_value(rast,(st_GeomFromText('POINT(30.424 -1.66)', 4326))) as val
FROM 
     my_table
WHERE
     date_of_data='2002-01-09'

Is there a method (eg. spatial index) to speed up those kind of queries?

Answer

This is an exciting question! How big is the raster you want to query? WKTRaster is stored in the database as a BLOB. In order to find the value at a specific point, from a known (x_0, y_0) corner coordinate row/column indices (i, j) are computed using (dx, dy) steps and rotation. With (i, j) known, the ST_Value() function can access the actual data at the correct byte offset.

This means that the DB has to read on average at least half of the data blob when answering a query for a point (depending on the implementation it may actually read all of the data at all times). I would therefore guess that WKTRaster performance suffers when the data BLOBs get too large. Tiling the dataset should speed up queries. Have a look at how SRTM data (coming in 6000x6000 pixel chunks) is handled in this tutorial. They actually tile the data into really small 50x50 pixels, which is a clear hint that my guessing may be not too far from the truth.

Spatially indexing raster data will probably just index the bounding box, which is no real help for your problem.