<div dir="ltr"><div><div><div><div>From what I have seen (not much),<br></div>finding which tile is in your shape is proven to be very fast given the right index, table structure, server tuning & so.<br></div>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).<br>
</div>Most imortantly extraction if pixel information (pixel value) is slow.<br><br></div><div>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, <br>
</div><div>you _have to_ do it in the image space if you care about speed.<br></div><div><br></div>Cheers,<br>Rémi-C<br><div><div><br></div></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2014-08-21 1:04 GMT+02:00 Dan Gast <span dir="ltr"><<a href="mailto:dan@stormpulse.com" target="_blank">dan@stormpulse.com</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><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="HOEnZb"><div class="h5"><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>(select geom from vector_table where gridcode = 18)</div><div>poly18 on ST_Intersects(r.rast, poly18.geom)</div></div><div><div><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" 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>