[postgis-users] PostGIS Raster Performance

Dan Gast dan at stormpulse.com
Wed Aug 20 16:04:53 PDT 2014

Some information that would really help:

How slow it is
How fast you'd like it to be
Machine total physical RAM (cat /proc/meminfo | head -1 in linux)
SHOW shared_buffers;
SHOW effective_cache_size;
SHOW work_mem;

An EXPLAIN of the query.

Also, if smaller tiles are better, keep shrinking them. Try 32x32 next

In my experience PostGIS Raster performance gets weirdly and unpredictably
slow when it's short on memory. I just spent a week trying different tuning
parameters on a 2000x1500 raster image on a 1.7gb RAM/magnetic disk AWS
instance. I figured it was a little short on memory but things should fit
well enough for the test at hand. Going to an AWS instance with 16gb RAM
and SSD turned a ~40 minute job into ~40 seconds.



On Wed, Aug 20, 2014 at 4:35 PM, David Haynes <haynesd2 at gmail.com> wrote:

> 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>
> wrote:
>> 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
> _______________________________________________
> 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/847df9ae/attachment.html>

More information about the postgis-users mailing list