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>