<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Simon,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>The only downsides I can think of are<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>1) you can’t put in constraints, which means the information in raster_columns view will be incomplete.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>-- 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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Like so </span>SELECT ST_SummaryStats(rast)<br> FROM flood as f<br>WHERE f.raster_name = 'toddriver_2019_3p0m';<br><br><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Or like<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal>SELECT ST_SummaryStats(rast)<br> FROM flood_toddriver_2019 as f;<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>The benefit of using a partitioned or inherited table, is then for loading you can drop a table and reattach it.<o:p></o:p></p><p class=MsoNormal>Dropping a huge table is much faster than deleting a lot of rows.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>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.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>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.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>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.<br><br><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>David Haynes<br><b>Sent:</b> Wednesday, July 5, 2023 9:31 PM<br><b>To:</b> PostGIS Users Discussion <postgis-users@lists.osgeo.org><br><b>Subject:</b> Re: [postgis-users] Raster database design<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>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.<o:p></o:p></p></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>On Sun, Jul 2, 2023 at 7:40 PM Simon SPDBA Greener <<a href="mailto:simon@spdba.com.au">simon@spdba.com.au</a>> wrote:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><p class=MsoNormal>Currently when I load a raster (GDAL) into PostGIS I get a table per <br>raster and a row per tile (if tiled).<br><br>Is it possible to store multiple rasters within the same table, with a <br>descriminator column to identify which rows belong to which raster?<br><br>Such storage makes queries like this possible.<br><br>SELECT ST_SummaryStats(rast)<br> FROM flood as f<br>WHERE f.raster_name = 'toddriver_2019_3p0m';<br><br>Or perhaps a better question: what are the pros/cons of doing so?<br><br>create table toddriver_2019_2p5m (<br>rid integer,<br>raster_name text,<br>rast raster );<br><br>I would assume that one could not apply the constraints to this table.<br><br>regards<br><br>Simon<br><br>-- <br>Simon Greener<br>39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia<br>(m) +61 418 396 391<br>(w) <a href="http://www.spdba.com.au" target="_blank">www.spdba.com.au</a><br>(m) <a href="mailto:simon@spdba.com.au" target="_blank">simon@spdba.com.au</a><br><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></p></blockquote></div></div></div></body></html>