[postgis-devel] Large GeoTIFF ingestion by PostGIS

[Antonio Rodriges]

Hello,

I have a relatively large GeoTIFF file that I would like to process by
PostGIS (other, much larger GeoTIFFs also exist):  24937 × 38673
pixels

So far I am aware of 3 ways to ingest the file into PostGIS.
However, I have concerns about all of these ways:

1: in-db, no tiling:
raster2pgsql raster.tiff public.new_table |  psql -d postgis -U postgis

In fact, we create a single large raster inside PostGIS. Should it be
efficient to process?

2. in-db, with tiling:
raster2pgsql -t 128x128 raster.tiff public.new_table |  psql -d
postgis -U postgis

This results in numerous rasters that are percieved by PostGIS as
separate rasters, not as a single raster object. While this seems to
be the most appropriate way in terms of storage, it is very
inconvenient to process such a list of rasters. All queries should
work with a table of rasters, not with a raster.

3. out-db:
raster2pgsql -R raster.tiff public.new_table |  psql -d postgis -U postgis

We leave the raster outside the DB. I suppose only internal GeoTIFF
tiling might help to manage the I/O efficiently. However, does PostGIS
take internal tiling into account during the I/O? Is this ingestion
option is less efficient in terms of performance compared to in-db
approaches?

The main questions are:
A. What is the best way to ingest a large GeoTIFF into PostGIS -- in
terms of both storage efficiency and query conveniency?
B. Are there any other ways (efficienct and convenient) to put a large
GeoTIFF into PostGIS?

Thank you!