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 <[email protected]> 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:[email protected]] On >Behalf Of David Haynes >Sent: Wednesday, July 5, 2023 9:31 PM >To: PostGIS Users Discussion <[email protected]> >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 <[email protected] ><mailto:[email protected]> > 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) [email protected] <mailto:[email protected]> > >_______________________________________________ >postgis-users mailing list >[email protected] <mailto:[email protected]> >https://lists.osgeo.org/mailman/listinfo/postgis-users > > > >------------------------------------------------------------------------ > >_______________________________________________ >postgis-users mailing list >[email protected] >https://lists.osgeo.org/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
