3

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

2
  • 1
    Have you tried a smaller polygon to see if it yields any results before using this rather big one? Aug 28 at 20:33
  • 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 . Aug 29 at 20:03
2

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))

;

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.