Just starting to implement PostgreSQL at work and want to make sure I'm building something that will be efficient down the road. Let's say I have a raster of surface temperature for every hour of every day for the past 10 years. What is the best way to store this? Could I do multiple hourly rasters per table? For instance could I store a year's worth of hourly rasters in each table (10 total tables) or should I just store each hourly raster as its own table (87,600 total tables)? Does anyone have any resources on the theoretical aspects of creating a database?

In the future I will be referencing the rasters for mapping, querying, and analysis using python.


Too long for a comment, but: I'm starting to get the sense that postgis rasters should be treated like any other geometry or other data types in your postgresql database.

In the case above, if the data type was polygon geometry, you would query a single table based on the date in the DATE column, rather than picking a single polygon record out of its own table.

By that method, I would think dumping all your TEMPERATURE (for example) rasters into one TEMPERATURE table and attributing them using a DATE column (and/or any other columns you would want to associate with your data) would be the way to go.

I say all this without having used much raster data in postgis (read:none) but reading people's posts and looking at how postgresql tools operate on the data in addition to the postgis tools, I would consider that a way to go.

  • So you're saying I'd have multiple rasters in one table and each raster would have a timestamp attribute? Would this allow me to query by date and then by lat/lon for example? – JWB Sep 11 '20 at 5:30
  • @JWB - yes - each raster can have as many other columns (date, name, etc.) in addition to the RASTER (similar to geometry) columns. Querying can be done on any of those columns, including the location. – DPSSpatial_BoycottingGISSE Sep 11 '20 at 15:47

All rasters tiled together in the same table with a timestamp column and just do the right query to get what you want.

  • Do you have any links explaining a process like this? Would each raster than be stored as a column? – JWB Sep 11 '20 at 5:29
  • All raster tiles stored in only one column (using the -t raster2pgsql option) and one timestamp column (that can be produced from the raster filename with the -F raster2pgsql option). – Pierre Racine Sep 12 '20 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.