[postgis-users] Raster geomval return integer

[Andreas Forø Tollefsen]

Replying to my own post.

Just to confirm my belief I used the raster calculator in QGIS and
calculated 'rast*100'.
Then imported it to postgis and ran the query again:

SELECT gid,(foo.geomval).val,
CAST(SUM(ST_Area((foo.geomval).geom)) AS decimal(8,5)) as area,
CAST(SUM(ST_Area((foo.geomval).geom))/0.25*100 AS decimal(6,3)) as
percentarea FROM (SELECT p.gid,
ST_Intersection(ST_SetSRID(p.cell,4326), ST_SetSRID(r.rast,4326), 1)
AS geomval FROM mountain r, priogrid_land p WHERE
ST_Intersects(ST_SetSRID(p.cell,4326), ST_SetSRID(r.rast,4326),1)) AS
foo WHERE gid = 183230 AND (foo.geomval).val >= 0 GROUP BY gid,
(foo.geomval).val ORDER BY gid;

Result:
183230;74;0.00000;0.000
183230;33;0.00694;2.778
183230;80;0.00000;0.000
183230;16;0.00694;2.778
183230;9;0.00694;2.778
183230;100;0.04167;16.666
183230;73;0.00694;2.778
183230;89;0.00694;2.778
183230;71;0.00694;2.777
183230;22;0.00694;2.778
183230;35;0.00000;0.000
183230;48;0.00000;0.000
183230;59;0.00694;2.778
183230;0;0.13889;55.555
183230;12;0.00000;0.000
183230;6;0.00694;2.778
183230;1;0.00695;2.778

I can of course do this with any raster including a decimal pixel
value, but I really would like to not have to.

Any ideas?

Andreas


2011/10/27 Andreas Forø Tollefsen <andreasft at gmail.com>:
> Hi all,
>
> I have been working on this issue previously, but now we have updated
> to rev 8001 and the ticket 650 is fixed.
> Hence, I wanted to see if I can calculate the average pixelvalue
> within my grid cells (.5x.5 decimal degrees).
>
> However, I now experience that any value above 0 becomes 1, and i do
> not get the actual averaged value.
> I also tested to group by the polygonized pixel value, and this
> revealed that everything other than 0 is 1.
> According to my raster this is not correct, since I have many other
> values within the cells borders (see jpg).
>
> I have tried to use the ST_Value(rast, ST_SetSRID(ST_Point(lat,long))
> and this give me the correct pixel values.
> However, I do not understand what is giving me integer values using
> the query below.
>
> Any ideas on what is wrong with my query?
>
> Query:
> SELECT gid,CAST(((foo.geomval).val) AS decimal(8,6)) AS mntval,
> CAST(SUM(ST_Area((foo.geomval).geom)) AS decimal(8,5)) as area,
> CAST(SUM(ST_Area((foo.geomval).geom))/0.25*100 AS decimal(6,3)) as percentarea
> FROM (SELECT p.gid, ST_Intersection(ST_SetSRID(r.rast,4326),
> ST_SetSRID(p.cell,4326)) AS geomval FROM mountain r, priogrid_land p
> WHERE ST_Intersects(ST_SetSRID(p.cell,4326), ST_SetSRID(r.rast,4326))) AS foo
> WHERE gid = 183230 AND (foo.geomval).val >= 0
> GROUP BY gid, (foo.geomval).val
> ORDER BY gid;
>
> Result:
> 183230;1.000000;0.06945;27.778
> 183230;0.000000;0.18055;72.222
>
>
> Best,
> Andreas
>