Re: Importing tiles with raster2pgsql

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
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)

$ 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 <[email protected]> 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 <[email protected]>
> *Sent:* Friday, December 13, 2024 6:48 AM
> *To:* [email protected]
> *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
>
>
>
> Best,
>
> Andreas
>
>
>
> On Thu, Dec 12, 2024 at 3:26 PM Regina Obe <[email protected]> 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 <[email protected]>
> *Sent:* Thursday, December 12, 2024 6:17 AM
> *To:* [email protected]
> *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
>
>