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.

up vote 3 down vote accepted

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 (
    VALUES
    (184,192)
    ,(185,192)
) 
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 pairs
  • 1
    I have created some performance comparison - your query vs single query N times - perry.cz/clanky/postgis.html – Martin Perry Nov 1 '15 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. – Jakub Kania Nov 1 '15 at 16:23

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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