Re: [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-boun...@lists.osgeo.org] On Behalf Of 
David Haynes
Sent: Wednesday, July 5, 2023 9:31 PM
To: PostGIS Users Discussion 
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 mailto:si...@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  
(m) si...@spdba.com.au  

___
postgis-users mailing list
postgis-users@lists.osgeo.org  
https://lists.osgeo.org/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Raster database design

[David Haynes]

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 
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
> (m) si...@spdba.com.au
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users