[PostGIS] #5958: Importing tiles with raster2pgsql

[PostGIS]

#5958: Importing tiles with raster2pgsql
---------------------------------+---------------------------
 Reporter:  panden               |      Owner:  robe
     Type:  defect               |     Status:  new
 Priority:  medium               |  Milestone:  PostGIS 3.5.4
Component:  raster               |    Version:  3.5.x
 Keywords:  raster2pgsql raster  |
---------------------------------+---------------------------
 Also sent to the mailing list:

 * 2024-12-12: https://lists.osgeo.org/pipermail/postgis-
 users/2024-December/046620.html
 * 2025-07-24: https://lists.osgeo.org/pipermail/postgis-
 users/2025-July/046819.html

 ----

 == Description
 **NB**: Screenshots, tif-files and script in zip-file attached to ticket.

 I have a dataset with several hundred tiles (GeoTIFFs) that make up a
 digital terrain model.
 Each tile is 0.5d x 0.5d.

 I tried to load these GeoTIFFs to PostGIS with:

 {{{
 $ raster2pgsql -s 32631 -I -M -F -C -t auto -d -l 2,4,8,16,32 tif/*.tif
 (1)
 }}}

 The data was loaded, but when viewing in QGIS, it looked like jagged lines
 with areas of white overlain by pieces of neighboring tiles, etc.
 This didn't look right.
 I also noticed that for each tif, the constraints were printed.
 I expected this to be done once, after the last tile was loaded.

 I then created a virtual raster with gdalbuildvrt:
 {{{
 $ gdalbuildvrt merge.vrt tif/*.tif (2)
 }}}
 And then used raster2pgsql to load in the vrt:

 {{{
 $ raster2pgsql -s 32631 -I -M -F -C -t auto -d -l 2,4,8,16,32 merge.vrt |
 psql (3)
 }}}

 The data was loaded, and looked good in QGIS.

 == Expectation
 I expected commands (1) and (3) to give the same results.
 This was confirmed by one of the replies on the mailing list
 (https://lists.osgeo.org/pipermail/postgis-
 users/2024-December/046621.html).

 I checked the extent of the tiles, and 'NHS-D0309_50M_E25833.tif' has a
 different extent that is incompatible with the other tiles.
 All the other tifs are lined up with coordinates ending in 00, 25, 50 and
 75; 'NHS-D0309_50M_E25833.tif' does not.

 This explains the not aligned-stuff, and if I remove that tile, no
 misalignment notices are given.
 This does not solve my problem regarding the data looking strange in QGIS,
 unfortunately. I still get an image like the one in
 merge_raster2pgsql.png.

 Forcing the size in raster2pgsql to 128x128 does not help.

 == System details

 {{{
 anbj=# SELECT version();
                                         version

 ---------------------------------------------------------------------------------------
  PostgreSQL 17.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.1.1
 20250425, 64-bit
 (1 row)
 }}}
 {{{
 anbj=# SELECT PostGIS_Full_Version();




 postgis_full_version




 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------
  POSTGIS="3.5.2 POSTGIS_REVISION" [EXTENSION] PGSQL="170"
 GEOS="3.13.1-CAPI-1.19.2" PROJ="9.6.0 NETWORK_ENABLED=OFF URL_ENDPOINT=
 https://cdn.proj.org USER_WRITABLE_DIRECTORY
 =/var/lib/postgres/.local/share/proj
 DATABASE_PATH=/usr/share/proj/proj.db"
 (compiled against PROJ 9.6.0) GDAL="GDAL 3.11.3 "Eganville", released
 2025/07/12" LIBXML="2.14.3"
  LIBJSON="0.18" LIBPROTOBUF="1.5.2" WAGYU="0.5.0 (Internal)" (core procs
 from "3.5.2 dea6d0a" need upgrade) RASTER (raster procs from "3.5.2
 dea6d0a" need upgrade)
 (1 row)
 }}}
 {{{
 $ raster2pgsql
 RELEASE: 3.5.2 GDAL_VERSION=311 (POSTGIS_REVISION)
 }}}
 {{{
 $ gdalinfo --version
 GDAL 3.11.3 "Eganville", released 2025/07/12
 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5958>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.