Line 59 to 77 in the st_summarystatsagg.sql.<br>Could it be that line 69: ($1).sum / ($1).count leads to division by zero error if ($1).count is 0?<br>How could i change this so this is not the case?<br><br>-- raster_summarystatsfinal<br>
-- Final function used by the ST_SummaryStatsAgg aggregate <br>CREATE OR REPLACE FUNCTION raster_summarystatsfinal(ss summarystats)<br> RETURNS summarystats <br> AS $$<br> DECLARE<br> ret summarystats;<br>
BEGIN<br> ret := (($1).count,<br> ($1).sum,<br> ($1).sum / ($1).count,<br> null,<br> ($1).min,<br> ($1).max<br> )::summarystats;<br>
RETURN ret;<br> END;<br> $$<br> LANGUAGE 'plpgsql';<br><br><div class="gmail_quote">2011/12/19 Andreas Forĝ Tollefsen <span dir="ltr"><<a href="mailto:andreasft@gmail.com">andreasft@gmail.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Updated to latest trunk, and now it works.<br>However, I ran into a new "division by zero" error. This halts the query.<br>
Could it be that something is divided by integer rather than decimal in the query, or what else causes this:<br>
<br>ERROR: division by zero<br>CONTEXT: PL/pgSQL function "raster_summarystatsfinal" line 5 at assignment<br><br>********** Error **********<br><br>ERROR: division by zero<br>SQL state: 22012<br>Context: PL/pgSQL function "raster_summarystatsfinal" line 5 at assignment<br>
<br>Best,<br><font color="#888888">Andreas</font><div><div></div><div class="h5"><br><br><div class="gmail_quote">2011/12/19 Tom van Tilburg <span dir="ltr"><<a href="mailto:tom.van.tilburg@gmail.com" target="_blank">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>
If you got the latest ST_Clip from the repository, I found this
information with it:<br>
<br>
<p>
"Addition of C-based ST_MinPossibleValue to replace the existing
ST_MinPossibleVal which uses hard-coded values. Updated dependent
functions and scripts/plpgsql to use new function. Deleted
scripts/plpgsql/st_minpossibleval.sql to stop people from using
it. Associated ticket is <a href="http://trac.osgeo.org/postgis/ticket/1298" title="task:
[raster] ST_MinPossibleValue (closed: fixed)" target="_blank">#1298</a>."<br>
</p>
<br>
Probably this means you need the very newest version of rtpostgis
with the minpossiblevalue included in C. Not sure wheter is
available for windows yet.<br>
At the moment I don't have time to test but I have the feeling this
new function in C might make things quicker and perhaps fix the
error with nodata values I mentioned earlier. Let me know.<br>
<br>
Regards,<br><font color="#888888">
Tom</font><div><div></div><div><br>
<br>
On 19-12-2011 10:01, Andreas Forĝ Tollefsen wrote:
<blockquote type="cite">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" target="_blank">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" target="_blank">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><br>
<br>
<br>
On 25-11-2011 18:11, Andreas Forĝ Tollefsen wrote: </div>
</div>
<blockquote type="cite">
<div>
<div>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>
<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" target="_blank">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>
<br>
<fieldset></fieldset>
<br>
<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>
</blockquote>
<br>
</div></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">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>
</div></div></blockquote></div><br>