Hi Tom,<br><br>I tried both functions. The St_AreaWeightedSummaryStats() works great, but it takes a lot of time to complete.<br><br>However, the ST_Clip() i cannot manage to get working. Seems like something is broken. Any idea what can cause this error? <br>
I installed the latest ST_SummaryStatsAgg() and ST_Clip() from <a href="http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql">http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql</a><br><br>The query:<br>
SELECT foo2.gid,<br>(ss).count,<br> (ss).sum,<br> (ss).mean,<br> (ss).min,<br> (ss).max<br>FROM <br>(SELECT foo.gid, ST_SummaryStatsAgg(gv) ss<br> FROM (SELECT p.gid, ST_Clip(r.rast, p.cell) gv<br>
FROM access r, priogrid_land p<br> WHERE ST_Intersects(r.rast, p.cell)<br> ) foo<br> GROUP BY foo.gid<br> ) foo2<br><br>Results in:<br><br>ERROR: function st_minpossiblevalue(text) does not exist<br>
LINE 1: ...esce(nodata, ST_BandNodataValue(rast, bandstart), ST_MinPoss...<br> ^<br>HINT: No function matches the given name and argument types. You might need to add explicit type casts.<br>
QUERY: SELECT coalesce(nodata, ST_BandNodataValue(rast, bandstart), ST_MinPossibleValue(newpixtype))<br>CONTEXT: PL/pgSQL function "st_clip" line 31 at assignment<br><br>********** Error **********<br><br>ERROR: function st_minpossiblevalue(text) does not exist<br>
SQL state: 42883<br>Hint: No function matches the given name and argument types. You might need to add explicit type casts.<br>Context: PL/pgSQL function "st_clip" line 31 at assignment<br><br><br><br><div class="gmail_quote">
2011/12/6 Tom van Tilburg <span dir="ltr"><<a href="mailto:tom.van.tilburg@gmail.com">tom.van.tilburg@gmail.com</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF" text="#000000">
Andreas,<br>
<br>
I didn't have time to reproduce your problem yet. Did you have any
succes by yourself on this issue?<br>
Could it have something to do with counting the non-data values as
value? This is what I experience with a similar function (ST_Clip)
that consequently gave me the value '0' instead of the nodata value.
The result of that is that the mean was often lower than expected.<br>
<br>
Perhaps you could rewrite your previous example to something with
auto-generated values in the script. That saves time in reproducing.<br>
<br>
Cheers,<br><font color="#888888">
Tom</font><div><div></div><div class="h5"><br>
<br>
<br>
On 25-11-2011 18:11, Andreas Forĝ Tollefsen wrote:
</div></div><blockquote type="cite"><div><div></div><div class="h5">Update:
<div><br>
</div>
<div>I think my suspicion is correct. If I do a
ST_Summarystats().sum and divide this on 36 my MAX value will be
1.</div>
<div>Hence, I think the number of values counted and the number of
observations counted is not equal.</div>
<div><br>
</div>
<div>New query:</div>
<div>
<div>DROP TABLE IF EXISTS mountain_phil_cell;</div>
<div><br>
</div>
<div>SELECT </div>
<div>a.gid As gid,</div>
<div>(ST_SummaryStats((ST_Union(ST_MapAlgebraExpr(ST_AsRaster(a.cell,
b.rast, '32BF'), b.rast, 'rast2',
'32BF','INTERSECTION','0','0',0))).rast, false)).sum / 36 As
avgmnt</div>
<div>INTO mountain_phil_cell</div>
<div>FROM </div>
<div>priogrid_land a LEFT JOIN </div>
<div>mountain_phil b</div>
<div> ON ST_Intersects(a.cell, b.rast)</div>
<div>GROUP BY a.gid</div>
<div>ORDER BY a.gid;</div>
</div>
<div>
<br>
</div>
<div><br>
<div class="gmail_quote">2011/11/25 Andreas Forĝ Tollefsen <span dir="ltr"><<a href="mailto:andreasft@gmail.com" target="_blank">andreasft@gmail.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
A small note regarding this issue.
<div><br>
</div>
<div>My problem is that I never get a mean value of 1 even
if all pixels inside the geometry is one.</div>
<div><br>
</div>
<div>Could this be because: 6x6 pixels goes into one polygon
when visually controlling. If each pixel has the value 1,
then this will be calculated as 36 / 36 = 1. However, if
it calculates the sum to be 36 and divide by a number
higher than 36 pixels, then the result will always be
below 1.</div>
<div>What i am thinking is that while it sums up the pixel
values correctly, it does not count only the 36 pixels,
but also neighboring pixels. Therefore: 1+1+1+1...n36 /
Number of pixels higher than 36 will always lead to a
number lower than 1.</div>
<div><br>
</div>
<div>Anyone who knows the functions well could probably
answer this.</div>
<div><br>
</div>
<div>Best regards,</div>
<div>
<div>
<div>Andreas</div>
<div><br>
<br>
<div class="gmail_quote">2011/11/25 Andreas Forĝ
Tollefsen <span dir="ltr"><<a href="mailto:andreasft@gmail.com" target="_blank">andreasft@gmail.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Could
this have to do with the tiling of the raster?
<div>I will try to run the same query with a
untiled mountain raster to see if that changes
anything.</div>
<div><br>
</div>
<div>Btw. When loading a tiled postgis raster into
qgis it shows up with many artifacts and no data
areas. The same raster untiled does not show up
the same way.</div>
<div>Qgis bug?</div>
<div><br>
</div>
<font color="#888888">
<div>Andreas</div>
</font>
<div>
<div>
<div><br>
<div class="gmail_quote">2011/11/25 Andreas
Forĝ Tollefsen <span dir="ltr"><<a href="mailto:andreasft@gmail.com" target="_blank">andreasft@gmail.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div>
<div><span style="font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">Hi,</span>
<div style="font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)"><br>
</div>
<div style="font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">Thanks
for all of the suggestions. I will
do some more testing. However, as
for suggestion 1 i think the pixel
size should be the same as the
original raster or am I wrong?</div>
<div style="font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)"><br>
</div>
<div style="font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">Both
the mean_mnt_bin raster and the
priogrid_land shapefile can be
downloaded as zip (2 mb) here: </div>
<div style="font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)"><a href="http://gisintersect.com/mean_mnt_bin.zip" style="color:rgb(64,100,128)" target="_blank">http://gisintersect.com/mean_mnt_bin.zip</a></div>
<div style="font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)"><a href="http://gisintersect.com/priogrid_land.zip" style="color:rgb(64,100,128)" target="_blank">http://gisintersect.com/priogrid_land.zip</a></div>
<div style="font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)"><br>
</div>
<div style="font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">Any
help on getting the correct values
would be very much appreciated.</div>
<div style="font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)"><br>
</div>
<div style="font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">My
query:</div>
<div style="font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">
<div>DROP TABLE IF EXISTS
mountain_cell;</div>
<div><br>
</div>
<div>SELECT </div>
<div>a.gid As id,</div>
<div>(ST_SummaryStats((ST_Union(ST_MapAlgebraExpr(ST_AsRaster(a.cell,
b.rast, '32BF'), b.rast,
'rast2',
'32BF','INTERSECTION','0','0',0))).rast,
false)).mean As avgmnt</div>
<div>INTO mountain_cell</div>
<div>FROM </div>
<div>priogrid_land a LEFT JOIN </div>
<div>mountain b</div>
<div> ON ST_Intersects(a.cell,
b.rast)</div>
<div>GROUP BY a.gid</div>
<div>ORDER BY a.gid;</div>
</div>
</div>
</div>
</blockquote>
</div>
<br>
</div>
</div>
</div>
</blockquote>
</div>
<br>
</div>
</div>
</div>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset></fieldset>
<br>
</div></div><div class="im"><pre>_______________________________________________
postgis-users mailing list
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</div></blockquote>
<br>
</div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br>