[postgis-users] Raster database design

[Regina Obe]

Simon,

 

The only downsides I can think of are

 

1) you can’t put in constraints, which means the information in raster_columns view will be incomplete.

 

If you are using tools such as QGIS, they use the information like regular blocking, band number and types I think as help in displaying.

But if most of what you are going to use raster data for is using the postgis raster analysis functions, then yes putting all your data in one table is fine.

 

2) It’s hard to purge large amounts of data.  E.g. if for whatever reason you needed to reload the toddriver_2019_3p0m raster.

 

3) The table number of rows will get quite big, but a compound index consisting of the raster_name and the raster should handle that nicely.

 

For that you’d want to install the btree_gist extension so you can have a compound ( ST_ConvexHull(rast)  , raster_name) in a single index.

 

-- There are other approaches, that allow you to query but still keep separate tables.  I admit to being really fond of Partitioned tables and inheritance.

 

Note to achieve the below, you might so want to consider using inheritance or a partitioned table. You’d still have one table per raster but that gives you the option of querying

 

Like so SELECT ST_SummaryStats(rast)
   FROM flood as f
WHERE f.raster_name = 'toddriver_2019_3p0m';



Or like

 

SELECT ST_SummaryStats(rast)
   FROM flood_toddriver_2019 as f;

 

The benefit of using a partitioned or inherited table, is then for loading you can drop a table and reattach it.

Dropping a huge table is much faster than deleting a lot of rows.

 

 

These days I’d probably go the partitioned table route, except that some tools seem to be confused by them.  For older tools inheritance is a safer route.

 

I also need to check if raster_columns properly handles those.  I think I had fixed the issue a while back say in PostGIS 3.2 or so, but I need to double-check on that.

 

Partitioned tables, partition pruning tends to work better than inheritance, but if you are just constaining by raster_name, then I think both should perform about the same.



 

 

 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of David Haynes
Sent: Wednesday, July 5, 2023 9:31 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] Raster database design

 

It would make more sense to use a view than another table. If someone forgets the where f.raster_name check the query time will increase dramatically.

 

On Sun, Jul 2, 2023 at 7:40 PM Simon SPDBA Greener <simon at spdba.com.au <mailto:simon at spdba.com.au> > wrote:

Currently when I load a raster (GDAL) into PostGIS I get a table per 
raster and a row per tile (if tiled).

Is it possible to store multiple rasters within the same table, with a 
descriminator column to identify which rows belong to which raster?

Such storage makes queries like this possible.

SELECT ST_SummaryStats(rast)
   FROM flood as f
WHERE f.raster_name = 'toddriver_2019_3p0m';

Or perhaps a better question: what are the pros/cons of doing so?

create table toddriver_2019_2p5m (
rid integer,
raster_name text,
rast raster );

I would assume that one could not apply the constraints to this table.

regards

Simon

-- 
Simon Greener
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia
(m) +61 418 396 391
(w) www.spdba.com.au <http://www.spdba.com.au> 
(m) simon at spdba.com.au <mailto:simon at spdba.com.au> 

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230705/e92cd411/attachment.htm>