[postgis-users] Optimize query

[Nicolas Ribot]

Hi,

I imagine you define suitable index on raster table ?
You could maybe try to add a functional index on ST_VALUE(rast,geom) != 0.

I'm wondering if grouping by geom is very efficient ?
If your data are clearly separated, you could try to group by the
geometry's bbox instead, to see if it speeds up the query.

Are your data large ? Creating a buffer of 10000 unit can take some time.
If your data are points, you should use st_dwithin(geom, distance) to
test if points are within the raster or not.

Nicolas

On 21 August 2013 10:03, Nathaniel Clay <clay.nathaniel at gmail.com> wrote:
> 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