7

I'm trying to store a large raster timeseries on PostGIS-2.2 / PostgreSQL 9.5. The data comes as a collection of HDF-EOS (basically HDF4) files. Each file contains 24 bands, representing hourly values of the whole grid for one day. There is 16 years of data (5844 files).

At present, I'm storing each day as a raster with 24 bands, basically because it's the easiest to implement the import for it. The raster is split into 12 tiles, 42x45 (this was decided by raster2pgsql's -t auto option). I then add an extra field to the table for the date of the raster and after importing each file, UPDATE swgdn SET raster_date='XXXXXX' WHERE raster_date IS NULL.

It is, in a word, slow. So far the import's been running for around 2.5 hours and it's processed nearly 3% of the import data. I'm not measuring it, but it feels like it's getting slower as more data is inserted.

Is this a reasonable strategy for storing this type of data? Is there one that will perform better?

Would storing the whole timeseries as bands of a single raster perform better? That would be 140256 bands (and likely to grow).

For reference, here's a sample of the script I'm using to import the data:

raster2pgsql -I -t auto -c 'HDF4_EOS:EOS_GRID:"file1.hdf":EOSGRID:SWGDN' swgdn | psql
psql -c "alter table swgdn add raster_date text;"
psql -c "update swgdn set raster_date='20000101';"
raster2pgsql -I -t auto -a 'HDF4_EOS:EOS_GRID:"file2.hdf":EOSGRID:SWGDN' swgdn | psql
psql -c "update swgdn set raster_date='20000102';"

and so on.

Improve this question
4
  • 1
    Are you planning to store in Postgres for the purposes of analytics or simply as a data store? I have spoken to a few Postgres/Postgis people about this and the general impression I got was that storing large amounts of rasters was to be avoided, for all sorts of reasons, one being speed. I am about to embark upon a similar journey, as I have to process a century's worth of rain data which is many gigabytes of netCDF currently, so my question is more than one of idle curiousity.
    – John Powell
    Sep 2, 2016 at 13:40
  • Hi @JohnPowell, I was wondering whether you'd gained any insights from solving the problem with the rain data. Did you find an approach that worked for you?
    – cchristelis
    Dec 18, 2018 at 13:39
  • I'd be curious to hear about any for your gained experience with this as well. @JohnPowell
    – ryanjdillon
    Feb 28, 2019 at 13:37
  • 1
    @ryanjdillon. Use partitioning on date or use a db, like, Timescaledb, that sits on top of Postgres and does this for you.
    – John Powell
    Mar 5, 2019 at 7:03

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.