Two things: 1) For ST_Intersection to be fast the total area covered by nbr_polygons must be relatively smaller than the modis area. If the intersecting surface is similar to your modis area, ST_Intersection will try to vectorize all the raster and this takes generally too much time and produce too much vector data.
2) For ST_Intersection to be fast with ST_Intersects your raster must be tiled when you import it with raster2pgsql using the -k option If the polygon area is relatively small comparing with the raster area AND your raster is tiled, this kind of query should be (relatively) fast. Pierre > -----Original Message----- > From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- > boun...@postgis.refractions.net] On Behalf Of Bryce L Nordgren > Sent: Tuesday, June 07, 2011 5:05 PM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] How do you use PostGIS Raster? > > > > postgis-users-boun...@postgis.refractions.net wrote on 06/07/2011 08:15:43 > PM: > > > The first step is a > > >"global" (image-wide) analysis. It was very slow when I tried it. > > What if you tiled your landsat in smaller tiles (100x100)? > > > > I didn't get past the global analysis. On my little tutorial thing, the step > that took > 8 hours before I killed it was: > > DATABASE=> CREATE TABLE tmp_burned AS > DATABASE-> SELECT gid, ST_Collect(ST_Intersection(polygon, utm_geom)) as > tmp_burned > DATABASE-> FROM nbr_polygons, modis > DATABASE-> WHERE ST_Intersects(polygon, utm_geom) > DATABASE-> GROUP BY gid ; > > > This is under "Calculate burned area in polygon coverage" on: > https://collab.firelab.org/software/projects/modisfire/wiki/Calculating_histogr > ams_with_PostGIS_Raster > <https://collab.firelab.org/software/projects/modisfire/wiki/Calculating_histog > rams_with_PostGIS_Raster> > > Bryce _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
