[postgis-users] Optimize query

[Pierre Racine]

You should check with EXPLAIN if ST_Intersects() is using any index. I'm pretty sure it doesn't.

You might want to create an intermediate table with your buffers and index it and perform the ST_Union and ST_Clip in a second query.

> -----Original Message-----
> From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-
> bounces at lists.osgeo.org] On Behalf Of Nathaniel Clay
> Sent: Wednesday, August 21, 2013 4:03 AM
> To: PostGIS Users Discussion
> Subject: [postgis-users] Optimize query
> 
> Hi all,
> 
> I am having trouble geting the following query to run in a efficient manner:
> 
> Select ST_AsText(point) as point,ST_WorldToRasterCoordX(raster,point) as
> cx,ST_WorldToRasterCoordY(raster,point) as
> cy,ST_AsGDALRaster(raster,'GTiff') as image  from ( SELECT geom as
> point, ST_UNION(ST_CLIP(rast,ST_BUFFER(geom,10000))) as raster from
> wv_ned_utm83 , pointsouth where
> ST_INTERSECTS(rast,ST_Buffer(geom,10000)) and ST_VALUE(rast,geom) != 0
> GROUP BY geom limit 1 ) as foo"
> 
> Any suggestions or help would be greatly appreciated!!
> 
> Thanks,
> 
> Nathaniel Hunter Clay
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users