Intersecting polygons with raster in PostgreSQL using PostGIS and pg_raster

Question

My goal is to use elevation data I loaded into PostgreSQL using pg_raster and intersecting it with a polygon. Within this polygon I would like to find the largest value in the pg_raster.

My first step is to get the intersection to work.

SELECT ST_Intersection(ST_Polygon('LINESTRING(75 29, 75.1 29, 75.1 28.1, 75 28.1,75 29)'::geometry, 4326),rast) 
FROM srtm 
WHERE ST_Intersects(rast,ST_Polygon('LINESTRING(75 29, 75.1 29, 75.1 28.1, 75 28.1,75 29)'::geometry, 4326));

However it is currently giving me the following error

ERROR: array size exceeds the maximum allowed (1073741823)

Each raster record is 3601x3601

I'm using PostgreSQL 11

• Timothy Dalton: Have you tried a smaller polygon to see if it yields any results before using this rather big one?
• Tommie Jones: This was actually a shrunkened polygon. I've gone as small as a square with the two opposite corners being 75,29-> 75.001,29.001 .

Answer 1

The problem was the size of the raster and not the size of the polygon (which was pretty obvious in hindsight) To resolve this I sacrificed precision and rescaled the data which got me past this issue.

insert into srtmup select rid, st_rescale(rast,0.003) from srtm;

However though this helped with the st_intersection function I realized that this was turning the rast into a geometry and using the st_interesection on the geometry which is not what I wanted.

Instead what I used was st_clip which would create a new raster clipped to the polygon of interest and this worked with the rescaled and original data.

select rid,(st_summarystats(st_clip(rast,ST_Polygon('LINESTRING(75 29, 75.1 29, 75.1 28.1, 75 28.1,75 29)'::geometry, 4326)))).*
from srtmup 
where st_intersects(rast,ST_Polygon('LINESTRING(75 29, 75.001 29, 75.001 28.001, 75 28.001,75 29)'::geometry, 4326))

;