Is there a way, how to obtain more points from raster in one query faster than this?

SELECT x, y, ST_Value(raw_data, 1, x, y) AS val_0, 
      ST_Value(raw_data, 2, x, y) AS val_1 
FROM data 
CROSS JOIN generate_series(1, 256) As x 
CROSS JOIN generate_series(1, 256) As y 
WHERE ((x = 184 AND y = 192) OR (x = 185 AND y = 192) OR ....) 

Speed of this is the same as if I call ST_Value for every pair manually (but that is not, what I want)

Also, I have two bands and need value from both. But ifthere is something faster for only one band data, I can separate them.

2 Answers 2


It will be faster if you don't generate series of rows but rather pre-define the data you want to join with.

WITH pairs(x,y) AS (
  ,ST_Value(raw_data, 1, x, y) AS val_0
  ,ST_Value(raw_data, 2, x, y) AS val_1 
    CROSS JOIN pairs
  • 1
    I have created some performance comparison - your query vs single query N times - perry.cz/clanky/postgis.html Nov 1, 2015 at 11:09
  • @MartinPerry I don't understand why you tried to use ST_ConvexHull in an index there, You probably should expand that bit. Nov 1, 2015 at 16:23

I think the revized version of the query as follows may perform better, is it?

WITH pairs(x,y) AS (
    (70.5084, 45.1985),
    (70.5094, 45.1995),
    (70.5084, 45.1975),
    (70.5074, 45.1965),
    (70.5064, 45.1955),
    (70.5054, 45.1945)
  ST_Value(rast, ST_SetSRID(ST_MakePoint(x, y), 4326)) AS height
FROM dem_raster rs
    CROSS JOIN pairs
    WHERE ST_Intersects(rs.rast,  ST_SetSRID(ST_MakePoint(x, y), 4326));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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