Importing tiles with raster2pgsql

[Rainer Hurling]

Am 19.12.2024 um 10:09 schrieb Andreas B:
> 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.
> 
> I also tried with a (more) recent version of PostgreSQL/PostGIS and raster2pgsql, but the issue is still there:
> 
> # SELECT version();
>   PostgreSQL 16.3 [..]
> 
> # SELECT PostGIS_full_version();
> POSTGIS="3.4.2 POSTGIS_REVISION" [EXTENSION] (liblwgeom version mismatch: "3.4.2 c19ce56") PGSQL="160" GEOS="3.13.0-CAPI-1.19.0" (compiled against GEOS 3.12.2) PROJ="9.5.1
> NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org <https://cdn.proj.org/> USER_WRITABLE_DIRECTORY=/var/lib/postgres/.local/share/proj DATABASE_PATH=/usr/share/proj/proj.db" GDAL="GDAL 3.10.0, r
> eleased 2024/11/01" LIBXML="2.13.5" LIBJSON="0.18" LIBPROTOBUF="1.5.0" WAGYU="0.5.0 (Internal)" (core procs from "3.4.2 c19ce56" need upgrade) RASTER (raster procs from "3.4
> .2 c19ce56" need upgrade)

Maybe that I missed something here. But did you try

SELECT PostGIS_Extensions_Upgrade();

before using the database with your new combo of PostgreSQL/PostGIS and raster2pgsql?

HTH,
Rainer

> $ raster2pgsql
> RELEASE: 3.4.2 GDAL_VERSION=310 (POSTGIS_REVISION)
> 
> Another observation is that the choice of tile size varies (I'm using -t auto):
> 
> With vrt as input, always uses 128x128:
> 
> INFO: Using computed tile size: 128x128
> 
> With *.tif as input the tile size varies depending on which tile is processed first, e.g.:
> 
> INFO: Using computed tile size: 215x274
> 
> or
> 
> INFO: Using computed tile size: 97x46
> 
> or
> 
> INFO: Using computed tile size: 18x10
> 
> Just testing and thinking out loud!
> 
> Best,
> Andreas
> 
> 
> On Sun, Dec 15, 2024 at 12:35 AM Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us>> wrote:
> 
>     Sorry haven’t had a chance to look thru this yet.  Going to be tied up for the next two weeks so I might not get a chance until much later.____
> 
>     __ __
> 
>     If any others are interested in investigating, please don’t wait for me.____
> 
>     __ __
> 
>     Thanks,____
> 
>     Regina____
> 
>     __ __
> 
>     *From:*Andreas B <panden at gmail.com <mailto:panden at gmail.com>>
>     *Sent:* Friday, December 13, 2024 6:48 AM
>     *To:* postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>     *Subject:* Re: Importing tiles with raster2pgsql____
> 
>     __ __
> 
>     Regina,____
> 
>     __ __
> 
>     I'm linking to a folder with a zip containing a subset of tiles that demonstrates my problem, a script to run the commands, and two screenshots.
>     Note that I'm a PostGIS beginner, so it's possible I'm doing something wrong!____
> 
>     The issues encountered differs a bit from what I described yesterday, but the essence is the same.____
> 
>     __ __
> 
>     Link to folder: https://www.dropbox.com/scl/fo/bk8cwktahry0oj0ab2rsz/AHdTD73unb4Efxtgf7exfm0?rlkey=esjtw2iyzysan2hdqklwmj5e3&st=hupv5tsr&dl=0 <https://www.dropbox.com/scl/fo/bk8cwktahry0oj0ab2rsz/AHdTD73unb4Efxtgf7exfm0?rlkey=esjtw2iyzysan2hdqklwmj5e3&st=hupv5tsr&dl=0>____
> 
>     __ __
> 
>     Best,____
> 
>     Andreas____
> 
>     __ __
> 
>     On Thu, Dec 12, 2024 at 3:26 PM Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us>> wrote:____
> 
>         Andreas,____
> 
>         ____
> 
>         Yes I would expect them to give the same result.____
> 
>         ____
> 
>         What version of raster2pgsql are you running?  It should tell you if you run raster2pgsql without any args.____
> 
>         ____
> 
>         Also what platform are you on?  Any chance you have some of those tiles available so we can check it out?____
> 
>         ____
> 
>         *From:*Andreas B <panden at gmail.com <mailto:panden at gmail.com>>
>         *Sent:* Thursday, December 12, 2024 6:17 AM
>         *To:* postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>         *Subject:* Importing tiles with raster2pgsql____
> 
>         ____
> 
>         Hi all,
> 
>         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.
> 
>         Shouldn't commands (1) and (3) give the same results, or am I misunderstanding?
> 
>         Best, Andreas____
>