[postgis-devel] [postgis-users] Large GeoTIFF ingestion by PostGIS

[Regina Obe]

Storing a large raster in the database as a single raster record is pretty much out of the question.  You'd quickly exceed the max rowxcol pixel allowed.  I forget exactly what that limit is.  In addition  the spatial index on the rasters would be useless so any join operation you do or filtering by spatial location will be painfully slow.

What kind of operations are you planning to do with the rasters?  That will determine the tile size you should use.

128x128 is most suitable for intensive analysis of small areas.   If you are doing less granular stuff like just unioning or clipping rasters, then you'd probably want to go with higher pixel sizes.  I tend to use 256x256 and rarely exceed 1000x1000

Out-db operations I haven't done benchmarks recently on it, but some operations are faster with it than in-db and some are faster without-db.

Generally processes like converting to other raster formats, tilling, and clipping as I recall are faster without-db. Operations like reclass / map algebra flavor of things are slower without-db. 

-----Original Message-----
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Antonio Rodriges
Sent: Thursday, May 03, 2018 6:11 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>; postgis-devel at lists.osgeo.org
Subject: [postgis-users] Large GeoTIFF ingestion by PostGIS

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!
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users