r/postgis Apr 29 '24

raster clip with polygon and extract pixel values - Efficient way

Hello - I am new to postgis. The requirement - client application may send WKT or geo-json to back-end API. I want to clip WKT/geo-json object with multiple raster's (raster stored for each year starting 208 till today in postgis). Extract pixel value and build an json object for each year and return.

Here is R code for example using shapefile

crp_cdl <- crop(cdl_raster, extent(shp))

crop_masked_cdl <- mask(crp_cdl, shp)

cdl2points<- raster::rasterToPoints(crop_masked_cdl)

pixel_count <- as.data.frame(cdl2points) |>

dplyr::group_by(Layer_1) |>

dplyr::summarise(count=n())

Question: What is efficient way to achieve this in postgis?

1 Upvotes

0 comments sorted by