What is the proper convention for storing rasters in PostgreSQL database?

Geographic Information Systems Asked by JWB on October 13, 2020

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.

2 Answers

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

Answered by Pierre Racine on October 13, 2020

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.

Answered by DPSSpatial on October 13, 2020

Add your own answers!

Related Questions

What is the precision of Geohash?

1  Asked on July 15, 2021 by gundon


Using pyshp to convert .csv file to .shp?

3  Asked on July 15, 2021 by kogia


How to get a more refined and smooth DEM?

0  Asked on July 15, 2021 by abhimanyu-barnwal


Sentinel-2 clouds filtering in GEE

0  Asked on July 15, 2021 by salvatore-pratic


Maxent Error reading output row and column

1  Asked on July 14, 2021 by beryn


Ask a Question

Get help from others!

© 2022 All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP