0

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?

  • Try projecting the raster first with GDALWarp to a local filesystem raster then upload to PostGIS unprojected but tiled.. if it's still slow then it's likely to be a DB/Filesystem/Network issue. I would suggest if the TIFF is compressed it could slow things down, perhaps warp to ERDAS IMG (hfa driver) as an uncompressed format prior to loading. – Michael Stimson Mar 2 '17 at 23:38
2

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);
  • Thank you @scabecks for the recommendation although this runs in about the same amount of time? Down from 846k ms to 837k ms. Still room for improvement? – wtgeographer Mar 7 '17 at 16:07
  • 1
    Have you reconfigured any of the settings in postgres.conf? The default settings for shared_buffers and work_mem are often inadequate for anything spatial. Take a look here for some suggestions: wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server – scabecks Mar 7 '17 at 23:55

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.