PostGIS raster values become integer when Polygonizing

Posted on 01 November 2011

Lately I have been trying to find out why my raster decimal values are truncated to integer when polygonizing.
After some research I found out that this is a known bug (http://trac.osgeo.org/postgis/ticket/650), and the problem lies in the GDALPolygonize function. This function is used in PostGIS to convert raster to vector, and since this function do not support decimal values, the values are truncated to integers. Particularly problematic if you are working with a raster with pixel values between min 0 and max 1.
The solution is to use GDAL 1.9dev Subversion from trunk. Hence, you have to compile GDAL 1.9dev yourself, and then recompile PostGIS 2.0.0SVN to include GDAL 1.9dev.

Then, when querying raster to vector with values, you get the correct result.
The below query tests this by querying the average pixel values within a quadratic polygon of 0.5 x 0.5 decimal degree’s.

SELECT gid, AVG((foo.geomval).val)
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
ORDER BY gid;

Result
gid;avg
183230;0.404999998294645