[postgis-users] PostGIS Raster Performance

[Rémi Cura]

>From what I have seen (not much),
finding which tile is in your shape is proven to be very fast given the
right index, table structure, server tuning & so.
Then if the image is in the server it is going to be slow to get the data.
(I guess if the image is in filesystem this should be not so slow).
Most imortantly extraction if pixel information (pixel value) is slow.

Next I don't know what you intend to do with the pixel values, but if it is
something that can be done by image processing and not in postgis,
you _have to_ do it in the image space if you care about speed.

Cheers,
Rémi-C



2014-08-21 1:04 GMT+02:00 Dan Gast <dan at stormpulse.com>:

> 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
> maybe.
>
> 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.
>
> Thanks,
>
> Dan
>
>
>
> 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
>>> FROM
>>> ( 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
>>
>
>
> _______________________________________________
> 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/20140821/fccb0349/attachment.html>