Extract raster pixel values along a polyline in PostGIS

Question

I have a PostGIS database with a table containing rasters, and I want to extract the values of the pixels along a polyline (an arbitrary number of coordinate points) at a specific time. So the objective is:

1. Select the relevant tile(s) given a polyline and timestamp
2. Return the values of all pixels that are crossed by the polyline

So each output row would contain a coordinate pair and the corresponding pixel value.

From what I have googled so far I understand that I either

1. convert the line to a raster and then do an intersection, or
2. convert the raster to a vector thing and then do an intersection

However, I am unable to apply the few examples I have found to my case. Can someone give a hint?

The raster table is called air_temperature and the relevant columns are rast and timestamp. I have learned that I can provide a proper geometry as, for example, "st_transform(st_setsrid(st_geomfromtext('MULTILINESTRING ((7.294832 52.697831, 7.294074 52.698534))'), 4326), 3857)".

I think I can select the relevant tiles and timestamps by including a where caluse as follows:

where st_intersects(st_transform(st_setsrid(st_geomfromtext('MULTILINESTRING ((7.294832 52.697831, 7.294074 52.698534))'), 4326), 3857), rast) and timestamp='2017-01-01 01:00:00'

But how does the select part look like?

Answer 1

I guess, you should upload your rasters into database and than employ st_value() to extract pixel bands. That would be faster than converting raster into geometry and further processing it, but the drawback is that you, most probably, will be able to analyze line vertices only (not each and every point).