I want to calculate the proportion of each pixel value in a raster after clipping a much larger raster to my area of interest. However, after clipping and saving the raster from PostgreSQL database/PostGIS, the table created does not calculate the pixel number or proportion as expected.
Step 1: clip larger raster (Minnesota gridded soils) based on extent of "plotbounds" (polygon geometry table):
CREATE TABLE out.eonr_gaylord_2012_soils AS SELECT ST_Clip(rast, p.geom) FROM gssurgo_mn.mapunitraster_mn_10m, temp.eonr_gaylord_2012_plotbounds_data AS p WHERE ST_Intersects(p.geom, rast);
Step 2: Calculate the proportion of each pixel
SELECT DISTINCT pvc.VALUE, SUM(pvc.COUNT) AS tot_pix, 100 * SUM(pvc.COUNT) / SUM(SUM(pvc.COUNT)) OVER () AS prop FROM out.eonr_gaylord_2012_soils, ST_ValueCount(rast, 1) AS pvc GROUP BY pvc.VALUE, pvc.COUNT ORDER BY pvc.VALUE
Why are there multiple rows (6) for each value?
I can load this raster into QGIS and it displays fine. I tried exporting to my local hard drive as a geotiff (from QGIS), then reloaded into PostgreSQL using
raster2pgsql in the command prompt, and ran the same query to calculate pixel number and proportion (with the imported geotiff raster), and it calculates as expected:
When looking at the Info from QGIS DB Manager, the original raster (the one that doesn't behave as expected) has a few differences from the imported raster (the one that works):
- Pages: 3 vs 1
- Rows: 128 vs 1
- Spatial ref: [none] vs "WGS 84 / UTM zone 15N (32615)"
- Constraints: [none] vs many (e.g., enforce_max_extent_rast, enforce_out_db_rast, etc.)
PostgreSQL 9.5.10, compiled by Visual C++ build 1800, 64-bit
POSTGIS="2.3.5 r16110" GEOS="3.6.2-CAPI-1.10.2 4d2925d" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.2, released 2017/09/15" LIBXML="2.7.8" LIBJSON="0.12" RASTER