[postgis-users] Raster database design

[Simon Greener]

Regina,

Thanks for taking the tim to respond at length.

All you suggestions are really useful. You are right that the current use case is statistics.

I've experimented with a partitioned solution and like that approach. I need to look into adding the constraints in this environment and checking out in more detail qGIS access.

Regards
Simon
⁣39 Cliff View Drive
Allens Rivulet Tas 7150
(W) 0418 396 391​

On 6 Jul 2023, 12:14, at 12:14, Regina Obe <lr at pcorp.us> wrote:
>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
>
>
>
>------------------------------------------------------------------------
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at lists.osgeo.org
>https://lists.osgeo.org/mailman/listinfo/postgis-users