[postgis-users] Raster pixel value

[Andreas Forø Tollefsen]

I also as you see tried to divide on 3600 as that was the possible number
of pixel count per geometry polygon (foo2.gid).

Andreas

2011/12/19 Andreas Forø Tollefsen <andreasft at gmail.com>

> Thanks Tom,
>
> I tried to select WHERE (ss).count > 0 and (ss).sum > 0, but i still got
> the same error. I assume that this is something that need to be changed in
> the script.
>
> I will do some more testing.
>
> Does anyone have an idea for something that could bypass this in my query?
>
> SELECT foo2.gid, (ss).sum / 3600 AS meanttime
> INTO accessgrid FROM (SELECT foo.gid, ST_SummaryStatsAgg(gv) ss
>             FROM (SELECT p.gid, ST_Clip(r.rast, p.cell) gv FROM access r,
> priogrid_land p WHERE ST_Intersects(r.rast, p.cell)) foo
>             GROUP BY foo.gid) foo2
> ORDER BY foo2.gid;
>
> Thanks!
>
> Andreas
>
> 2011/12/19 Tom van Tilburg <tom.van.tilburg at gmail.com>
>
>>  Yes, well found.
>>
>> I'm not familiar with plpgsql, but I'm sure it's easy to include some
>> conditional statement. Just check the postgres documentation.
>> You can also check if you can put the conditional into your SQL query
>> (something like 'WHERE count(rast) > 0') so that you don't feed empty
>> rasters to the function.
>>
>> all the best,
>>  Tom
>>
>>
>> On 19-12-2011 11:13, Andreas Forø Tollefsen wrote:
>>
>> Line 59 to 77 in the st_summarystatsagg.sql.
>> Could it be that line 69: ($1).sum / ($1).count leads to division by zero
>> error if ($1).count is 0?
>> How could i change this so this is not the case?
>>
>> -- raster_summarystatsfinal
>> -- Final function used by the ST_SummaryStatsAgg aggregate
>> CREATE OR REPLACE FUNCTION raster_summarystatsfinal(ss summarystats)
>>     RETURNS summarystats
>>     AS $$
>>     DECLARE
>>         ret summarystats;
>>     BEGIN
>>         ret := (($1).count,
>>                 ($1).sum,
>>                 ($1).sum / ($1).count,
>>                 null,
>>                 ($1).min,
>>                 ($1).max
>>                )::summarystats;
>>         RETURN ret;
>>     END;
>>     $$
>>     LANGUAGE 'plpgsql';
>>
>> 2011/12/19 Andreas Forø Tollefsen <andreasft at gmail.com>
>>
>>> Updated to latest trunk, and now it works.
>>> However, I ran into a new "division by zero" error. This halts the query.
>>> Could it be that something is divided by integer rather than decimal in
>>> the query, or what else causes this:
>>>
>>> ERROR:  division by zero
>>> CONTEXT:  PL/pgSQL function "raster_summarystatsfinal" line 5 at
>>> assignment
>>>
>>> ********** Error **********
>>>
>>> ERROR: division by zero
>>> SQL state: 22012
>>> Context: PL/pgSQL function "raster_summarystatsfinal" line 5 at
>>> assignment
>>>
>>> Best,
>>> Andreas
>>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111219/56445b59/attachment.html>