I have a set of rasters in DTED format which I load into PostGIS database using raster2pgsql command line tool.
Each of rasters is simply stored in a row and described by rid and a value of raster format.
Now, I want to create a database function which takes longitude and latitude of a point and returns a value of pixel corresponding to this point.
The problem I have is that it takes quite a lot of time to do that (3-4 secs) because the database works on Odroid board.
I know that the dataset I process is quite big (the rasters cover the whole UK area) but as I am not very familiar with PostgreSQL and PostGIS I suspect it can be done faster.
This is what I've done so far:
SELECT ST_Value(rast, ST_GeomFromText(CONCAT('POINT(', $1, ' ', $2, ')'), 4326)) FROM ( SELECT * FROM rasters WHERE rast && ST_GeomFromText(CONCAT('POINT(', $1, ' ', $2, ')'), 4326) ) x;
$2 are long and lat respectively.