[postgis-users] Raster pixel value

[Andreas Forø Tollefsen]

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/b2bbb5fc/attachment.html>