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

[Guido Lemoine]

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.

 

GL

 

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
crashes

 

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
database";

Here's my query:

SELECT ST_Union(rast,
ARRAY[ROW(2,'LAST'),ROW(3,'LAST'),ROW(4,'LAST')]::unionarg[])

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>