[postgis-users] ST_Union() about 1179 raster-tiles cause ProgreSQL crashes

Guido Lemoine guido.lemoine at jrc.ec.europa.eu
Mon Mar 17 09:26:49 PDT 2014

I use 


st_union(st_band(rast, '2,3,4') )


instead of your ARRAY[ROW. construct.


If polygon comes from a table of geometries (e.g. adminbounds), you would
need to add a JOIN (FROM landsat8, adminbounds).

Using WHERE rast && polygon would seem to be a good idea as well.


If your polygon is a big as the entire Landsat image, your query will return
several 100 Mb of data, and may close the connection before completion.




From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Jackie
Sent: 17 March 2014 17:16
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] ST_Union() about 1179 raster-tiles cause ProgreSQL


Hi PostGIS users, 

I have a PostGIS database, which stores raster from Landsat 8. Each scene
was divided with tile-size 500x500. Then each row in database will keep 1
tile with 11 bands (except band No. 8).

I'm trying to ST_Union() about 1179 raster-tiles with 3 bands (2,3,4) and
pgAdminIII shows a window said "Connection lost - Try to reconnect

Here's my query:

SELECT ST_Union(rast,

FROM landsat8

WHERE ST_Overlaps(polygon, rast_geom_4326)


I'm using PostGIS 2.1, my raster is retrieved from
<http://earthexplorer.usgs.gov/> earthexplorer.usgs.gov

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140317/c17d6700/attachment.html>

More information about the postgis-users mailing list