[postgis-users] PostGIS Raster Performance

David Haynes haynesd2 at gmail.com
Wed Aug 20 14:35:37 PDT 2014

I would start with something like this...
ST_DumpAsPolygons is an expensive function so it might take a while can you
explain more what you mean by visualize?

SELECT  ST_DumpAsPolygons(ST_Clip(rast,1,geom)) as gval
FROM raster_table r inner join
(select geom from vector_table where gridcode = 18)
poly18  on ST_Intersects(r.rast, poly18.geom)

On Wed, Aug 20, 2014 at 4:00 PM, DanielFranco <dfranco at computacao.ufla.br>

> Hi, I'm a newbie in PostGIS Raster use and GIS applications, so this may
> be a
> dumb question. I'm a little confused by some functionalities and I don't
> know if it's possible to do what i'm trying to. Basically, I'm using
> postgis
> functions either for raster and vector forms of an image and I'm trying to
> visualize parts of the raster by using the informations presented only in
> the shapefile. For example, I have an TIFF file of a land coverage (one
> band) about 49746 x 71819 and it's shapefile with some grid codes. I'm
> doing
> the following query to extract pixel values from an expecific gridcode (18)
> presented in the shapefile imported (vector_table) so I could infer some
> grid codes by the pixel value:
> SELECT (gval).val as pixel_value
> ( SELECT ST_DumpAsPolygons(ST_Clip(rast,1,geom)) as gval
> from raster_table
> join vector_table
> on (st_intersects(rast,geom))
> where geom in (select geom from vector_table where gridcode = 18)
> ) as intersection;
> The query runs too slow and I don't know how to optimize it. The raster
> image is big and I tried different tiles configurations (100x100, 200x200,
> 1000x1000) and 100x100 seemed to have better perfomance. I'm using st_clip
> for a faster answer. I don't know if these pixels values would retrieve
> some
> important information and if PostGIS raster is really suitable with area
> calculations. Is there a way to optimize the query or postgis raster isn't
> suitable for this case?
> --
> View this message in context:
> http://postgis.17.x6.nabble.com/PostGIS-Raster-Performance-tp5006902.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140820/87ef8f38/attachment.html>

More information about the postgis-users mailing list