2

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.

3

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
0

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

WITH pairs(x,y) AS (
    VALUES
    (70.5084, 45.1985),
    (70.5094, 45.1995),
    (70.5084, 45.1975),
    (70.5074, 45.1965),
    (70.5064, 45.1955),
    (70.5054, 45.1945)
) 
SELECT 
  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, privacy policy and cookie policy

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