[postgis-users] Raster catalog for tiled rasters (out of database)

[Johannes Sommer]

Dear list,

we use PostGIS Raster for storing raster datasets out-of-database. Each
Raster has similar height and width (10000*10000 pixels) an consists of
several bands.
For faster access we use tiling both on the original raster datasets
and registered it with the same tile size in the database as out-of-db
raster.
We started with a tile size of 1000x1000 but experienced increased
raster access performance with smaller tile sizes.

Now we are facing the following issue: our tiled table grew very large
in size and number of rows. For example 25'000 raster
datasets result in many millions rows and a size of 100 GB of the tiled
raster table.

I looked into the source code of PostGIS Raster [1] to get an
understanding how the library loads an out-of-database raster and it
seems that it uses a in memory GDAL Virtual Raster Table defined with
height and width of the current the raster row and converts this
dataset to a PostGIS raster object. As tiled rasters in PostGIS are not
really different from regular rasters in the database I assume this is
the mechanism to pass the search window from the tiled raster to the
out-of-database raster in the filesystem.

Are there any methods to the reduce the size of a tiled raster table
with out-of-database rasters or alternate ways to store a out-of-
database raster catalog (with or without PostGIS - but Open Source)?

Maybe this is too simple, but in my understanding it would be enough
for a tiled raster catalog to store only the tile size (width and
height) and find the rest of the information (bands, path, etc) in some
parent untiled raster table.

I experimented a bit with extracting the raster as binary VRT with
ST_AsGDALRaster(rast, 'VRT') to store this output in the database and
it seemed to reduce the size on disk for the new table compared to the
reference with (factor 0.6 of the original raster column) but faced
these problems:
- ST_AsGDALRaster(rast, 'VRT') did not return the out of band raster
path; but even if I set the band's path with ST_SetBandPath() and tried
to serialize the binary VRT back to PostGIS raster in SQL with
ST_FromGDALRaster() PostGIS treated this as in-database-raster, so it
did not load values.

Any ideas are welcome!

Best regards,
Johannes

[1]
https://github.com/postgis/postgis/blob/master/raster/rt_core/rt_band.c#L429

--