I am trying to build a fast query to PostgreSQL. My raster (DEM) is 1.6 GB, about 2.5 cm precision...With a smaller DEM it was ok, about 20 seconds to query a 300m radius elevations. Now with this larger raster it is not even responding, or at least not within a day... My queries are structured this way:
WITH pairs(x,y) AS ( VALUES (-74.61067886106919,45.60987140366732), (-74.61068132465692,45.60987191852093) ) SELECT ST_Value(rast, ST_SetSRID(ST_MakePoint(x, y), 4326)) AS height FROM alfred2 rs CROSS JOIN pairs WHERE ST_Intersects(rs.rast, ST_SetSRID(ST_MakePoint(x, y), 4326));
I was thinking of using
ST_Clip to clip the raster prior to query but can't figure out how to do that for a circle of 250 meters around a lat,lng.
NOTE: I reloaded the DEM with 10x10 and The result of EXPLAIN ANALYSE:
Gather (cost=1000.00..6964763.37 rows=1890080 width=8) (actual time=7500.324..7505.978 rows=2 loops=1) Workers Planned: 2 Workers Launched: 2 -> Nested Loop (cost=0.00..6774755.37 rows=787533 width=8) (actual time=6970.136..7435.628 rows=1 loops=3) Join Filter: st_intersects(rs.rast, st_setsrid(st_makepoint(("*VALUES*".column1)::double precision, ("*VALUES*".column2)::double precision), 4326), NULL::integer) Rows Removed by Join Filter: 1890075 -> Parallel Seq Scan on alfred5_10x10 rs (cost=0.00..200821.00 rows=1181300 width=472) (actual time=0.038..227.874 rows=945038 loops=3) -> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=64) (actual time=0.000..0.001 rows=2 loops=2835113) Planning Time: 0.156 ms JIT: Functions: 12 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 2.133 ms, Inlining 123.100 ms, Optimization 150.016 ms, Emission 83.455 ms, Total 358.705 ms Execution Time: 7506.925 ms