380960 (6) [Avatar] Offline
#1
I've imported monthly precipitation into postgis following the book.
I have a table with 2700 rows (225 for 12 months from january to december).
So I have in my table 12 raster of precipitation for part of Europe.

Question 1
What data I see when I load the table in Qgis using dbmanager?
(What kind of interpolation Qgis does when I load the 2700 rows raster?)

Question 2
what is the correct way to handle these 12 raster stored in the same table of postgis?
Maybe I must create a 12 bands (1 for each month) raster?
regina.leo (259) [Avatar] Offline
#2
> Question 1
> What data I see when I load the table in Qgis using dbmanager?
Well when you load the table since each month overlaps another, it's not predictable what you would see since I think QGIS just overlays the data on top as
it queries it and there is no guaranteed default sort.

If you do an ad-hoc query and drag that, then its whatever raster tiles your ad-hoc query returns that are used.
From what I recall, QGIS uses the raster over view tables if they are present. If they are not then it would just load the data in the main table.
Which overview (or if it uses the main table directly) it uses would depend on the extent of your map. This only applies to dragging a raster table.


> (What kind of interpolation Qgis does when I load the 2700 rows raster?) Again no interpolation unless you have over view tables . Which you would have if you loaded data with the -l option with raster2pgsql

> Question 2:

> what is the correct way to handle these 12 raster stored in the same table of postgis?
> Maybe I must create a 12 bands (1 for each month) raster?
I think for most cases its speedier and more practical to keep them as separate rows instead of store as separate bands. I tend to store them all in the same table
though you could opt for storing each month in a separate table. I tend to go with the single table since a spearate table would require 12 joins if you needed to see all bands as a single raster where as a single table you can employ aggregate arraying to add the bands and never have to do a join (just group by rast::geometry) )
This works fine as long as the gridding of each is the same, then you can always make them multi-band if needed with ST_AddBand
for one-off cases such as if you are disseminating the data to another group that would prefer to work with multi-band

You generally want to minimize on the number of bands you have in your raster
a) The more bands you have the fatter each row is which makes network pull slower if you don't need all 12 months viewed at the same time.
b) Updates become much slower too since every update basically requires delete and insert of all bands (an update in PostgreSQL via its MVCC implementation ends up being a delete and insert)

c) If you keep them as bands, you'd have to update more frequently - like as each month comes along instead of just adding new rows. Just adding new rows is less likely to mess up your existing data and also will be much faster since you don't have the delete penalty (required by Update).