Clip Raster with polygon if different SRID (Postgis)

Question

I have a raster with a SRID of 4326 and a polygon of SRID 2275. The raster was uploaded to postgis with 90x90 tiles using the following raster2pgsql cmd...

raster2pgsql -s 4326 -I -M -b 1 nlcd_2011_clip_wgs84.tif -F -t 90x90 public.nlcd_2011_clip_wgs84 | psql -h localhost -U postgres -d routing

I need to clip raster to extent of polygon (different SRID).

I am having some success using the following postgis query...

DROP TABLE IF EXISTS nlcd_clip_2275;
SELECT
rid,
rast,
filename,
st_intersects(polygon.geom,
    ST_Transform(raster.rast,2275))AS result
INTO nlcd_clip_2275
FROM
public.nlcd_2011_clip_wgs84 as raster,
public.buffer2275 polygon;
DELETE FROM nlcd_clip_2275 WHERE result = false

But the processing is slooooooowww!

Is there a faster postgis logic for both projecting and clipping the raster?

Answer 1

If you don't want to reproject the raster prior to importing into your database you could 1) try the reprojecting of your raster in a subselect query in FROM and 2) use WHERE ST_Intersects(polygon, raster) to speed up it all up. Using a CTE may also help:

WITH raster AS
  (SELECT 
       rid,
       filename,
       ST_Transform(rast,2275) as rast,
  FROM
       public.nlcd_2011_clip_wgs84)
SELECT
    rid,
    filename,
    ST_Clip(rast,1,geom) as rast
FROM raster JOIN
     public.buffer2275 ON ST_intersects(rast,geom);