<div dir="ltr">Some information that would really help:<div><br></div><div>How slow it is</div><div>How fast you'd like it to be</div><div>Machine total physical RAM (cat /proc/meminfo | head -1 in linux)</div><div>SHOW shared_buffers;</div>
<div><div>SHOW effective_cache_size;</div><div>SHOW work_mem;</div><div><br></div><div>An EXPLAIN of the query.</div><div><br></div><div>Also, if smaller tiles are better, keep shrinking them. Try 32x32 next maybe.</div><div>
<br></div>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.</div>
<div><br></div><div>Thanks,</div><div><br></div><div>Dan</div><div><br></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Wed, Aug 20, 2014 at 4:35 PM, David Haynes <span dir="ltr"><<a href="mailto:haynesd2@gmail.com" target="_blank">haynesd2@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div>I would start with something like this... </div><div>ST_DumpAsPolygons is an expensive function so it might take a while can you explain more what you mean by visualize?</div>
<div><br></div><div><br></div>
<div>SELECT <span style="font-size:13px;color:rgb(0,0,0);font-family:arial,sans-serif"> ST_DumpAsPolygons(ST_Clip(</span><span style="font-size:13px;color:rgb(0,0,0);font-family:arial,sans-serif">rast,1,geom)) as gval</span></div>
<div>FROM raster_table r inner join </div><div class="">(select geom from vector_table where gridcode = 18)</div><div>poly18 on ST_Intersects(r.rast, poly18.geom)</div></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra">
<br><br><div class="gmail_quote">On Wed, Aug 20, 2014 at 4:00 PM, DanielFranco <span dir="ltr"><<a href="mailto:dfranco@computacao.ufla.br" target="_blank">dfranco@computacao.ufla.br</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi, I'm a newbie in PostGIS Raster use and GIS applications, so this may be a<br>
dumb question. I'm a little confused by some functionalities and I don't<br>
know if it's possible to do what i'm trying to. Basically, I'm using postgis<br>
functions either for raster and vector forms of an image and I'm trying to<br>
visualize parts of the raster by using the informations presented only in<br>
the shapefile. For example, I have an TIFF file of a land coverage (one<br>
band) about 49746 x 71819 and it's shapefile with some grid codes. I'm doing<br>
the following query to extract pixel values from an expecific gridcode (18)<br>
presented in the shapefile imported (vector_table) so I could infer some<br>
grid codes by the pixel value:<br>
<br>
SELECT (gval).val as pixel_value<br>
FROM<br>
( SELECT ST_DumpAsPolygons(ST_Clip(rast,1,geom)) as gval<br>
from raster_table<br>
join vector_table<br>
on (st_intersects(rast,geom))<br>
where geom in (select geom from vector_table where gridcode = 18)<br>
) as intersection;<br>
<br>
The query runs too slow and I don't know how to optimize it. The raster<br>
image is big and I tried different tiles configurations (100x100, 200x200,<br>
1000x1000) and 100x100 seemed to have better perfomance. I'm using st_clip<br>
for a faster answer. I don't know if these pixels values would retrieve some<br>
important information and if PostGIS raster is really suitable with area<br>
calculations. Is there a way to optimize the query or postgis raster isn't<br>
suitable for this case?<br>
<br>
<br>
<br>
--<br>
View this message in context: <a href="http://postgis.17.x6.nabble.com/PostGIS-Raster-Performance-tp5006902.html" target="_blank">http://postgis.17.x6.nabble.com/PostGIS-Raster-Performance-tp5006902.html</a><br>
Sent from the PostGIS - User mailing list archive at Nabble.com.<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>