Why is tiling messing with the pixel dimensions? I used raster2pgsql to create two tables test_hourly_1 and test_hourly_2 on the same source geotif. The first one doesn't use tiling and the second does. Then I use SELECT ST_Metadata to see the pixel dimensions for the raster in each table. In the first non tiled table it's correct: 2606,1228 In the second table it wrong: 2604,1224.

Example without tiling note Size is 2606, 1228:

raster2pgsql -s 4269 -C -R -F /geo-data/climate_data/hourly_data/temp/urma_2020051600.tif public.test_hourly_1 | psql -h localhost -p 5432 -d climate -U postgres --no-password
SELECT ST_Metadata(rast) FROM public.test_hourly_1;
result -> (-125.0208333,49.9375,2606,1228,0.0224641851880276,-0.0210708469055375,0,0,4269,1)

Example with tiling note Size is 2604, 1224:

raster2pgsql -s 4269 -C -R -F -t 93x72 /geo-data/climate_data/hourly_data/temp/urma_2020051600.tif public.test_hourly_2 | psql -h localhost -p 5432 -d climate -U postgres --no-password
SELECT ST_Metadata(ST_Union(rast)) FROM public.test_hourly_2;
result -> (-125.0208333,49.9375,2604,1224,0.0224641851880276,-0.0210708469055375,0,0,4269,1)

gdalinfo urma_2020051600.tif, note Size is 2606, 1228

Driver: GTiff/GeoTIFF
Files: urma_2020051600.tif
Size is 2606, 1228
Coordinate System is:
        SPHEROID["GRS 1980",6378137,298.2572221010042,
Origin = (-125.020833300000007,49.937500000000000)
Pixel Size = (0.022464185188028,-0.021070846905537)
Image Structure Metadata:
Corner Coordinates:
Upper Left  (-125.0208333,  49.9375000) (125d 1'15.00"W, 49d56'15.00"N)
Lower Left  (-125.0208333,  24.0625000) (125d 1'15.00"W, 24d 3'45.00"N)
Upper Right ( -66.4791667,  49.9375000) ( 66d28'45.00"W, 49d56'15.00"N)
Lower Right ( -66.4791667,  24.0625000) ( 66d28'45.00"W, 24d 3'45.00"N)
Center      ( -95.7500000,  37.0000000) ( 95d45' 0.00"W, 37d 0' 0.00"N)
Band 1 Block=2606x1 Type=Float32, ColorInterp=Gray
  NoData Value=-9999

UPDATE It seems what's happening is that the dimensions of the raster aren't evenly divisible by the tile size and that raster2pgsql just tosses the leftovers out not inserting them into the database.

This only started happening after updating my postres/postgis database to version 10. Before upgrading I was under 9.4 and raster2pgsql would insert the leftover pixels as a row in the database so that after unioning all the tiles you'd get everything back.

Is there a way to get raster2pgsql to retain the leftover pixels? Changing my tile size to 1303x307 fixes the symptom but I get far less tiles so the performance might suffer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.