1

I want to clip an image with a polygon from postGIS rasters.
What I do:
- Import raster into postGIS(raster2pgsql, tile-size: 500x500, each tile contains 12 bands landsat 8)
- Import a shapefile into postGIS with SRID = 4326.
I have 21309 tiles in my raster table, and 60 pylygons in my shape table. Now, I want to clip a polygon:

SELECT oid,
   lowrite(lo_open(oid, 131072), png) AS num_bytes
FROM (
      VALUES (lo_create(0),
              ST_AsPNG(
                         (SELECT ST_Clip(ST_Transform(
                                           (
                                           SELECT ST_Union(foo.rast)
                                            FROM
                                              (SELECT rast
                                               FROM ldcm15, vnm_adm2
                                               WHERE gid=18
                                                 AND ST_Intersects(rast_geom_4326, geom)) AS foo),4326), vnm_adm2.geom,true)
                          FROM vnm_adm2
                          WHERE gid = 18)))) AS v(oid,png);

rast_geom_4326 is where I stored rast's polygon. Because, I don't want to use ST_Transform(rast, 4326) in the query, it's pain to wait for re-projection.
However, it still takes a long time to get the result.

I'm using PostGIS 2.1 on PostgresSQL 9.3.

Jackie
  • 303
  • 1
  • 3
  • 10

1 Answers1

3

It's generally faster to clip first and then union the clips.

Try changing to 

(SELECT ST_Union(ST_Clip(ldcm15.rast, ST_Transform(geom, ST_SRID(ldcm15.rast) ) ) )
  FROM ldcm15, vnm_adm2
         WHERE gid=18 AND ST_Intersects(rast_geom_4326, geom) )
Regina Obe
  • 10,503
  • 1
  • 23
  • 28
  • You're right, clip first runs faster. Besides, do you know other way to makes ST_Intersects(rast, geom) without using rast_geom_4326), because making column 'rast_geom_4326' also takes time. – Jackie Mar 06 '14 at 08:21