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 <[email protected]>: > 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 > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
