<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;}
p.m-7197725885516056396m-2868782805378049337msolistparagraph, li.m-7197725885516056396m-2868782805378049337msolistparagraph, div.m-7197725885516056396m-2868782805378049337msolistparagraph
{mso-style-name:m_-7197725885516056396m_-2868782805378049337msolistparagraph;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle18
{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'>Osahon,<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'>What is the original size of your file tiles? I haven't looked at the code that closely of raster2pgsql, so not sure how exactly it splits things up.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>From what I remember way back, I think it might consider each tile as separate and scale each tile individually in which case each row of any overview can't contain more than one file. I may be completely wrong though or code might have changed since I last looked. If my assumption is right this would make the counts very sensitive to your file tile size.<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 other consequence is if your factors don't fit in evenly with your tilesize, you may get some odd counts. So yes that would make going by factors of 2 more likely to match expected if your file tile size is even.<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'>I'm really puzzled about your o_3 answer though I would have expected that to be higher than the estimated<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 look at for example the ST_CreateOverview function, which should be doing much the same thing as raster2pgsql, you will see for scale factor of width and height it is x*factor, y*factor.<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'>I'd be very interested to know if ST_CreateOverview against your main table gives you similar results. That one I know does do a lot of unioning and clipping so could behave differently since it would be more sensitive to extent of coverage than original file size.<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'>Thanks,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina<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><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 style='margin-left:.5in'><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'> Osahon Oduware [mailto:osahon.gis@gmail.com] <br><b>Sent:</b> Monday, May 29, 2017 12:38 PM<br><b>To:</b> PostGIS Development Discussion <postgis-devel@lists.osgeo.org><br><b>Cc:</b> Regina Obe <lr@pcorp.us>; Pierre Racine <pierre.racine@sbf.ulaval.ca><br><b>Subject:</b> [URGENT] PostGIS Rasters Overview Factor<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p><div><p class=MsoNormal style='margin-left:.5in'><span style='font-size:10.5pt;font-family:"Arial",sans-serif;color:purple'>Hi All,<br><br>I loaded a raster successfully using the raster2pgsql tool with the following syntax:<br>raster2pgsql -s 26332 -d -Y -e -I -C -F -M -l 2,3,4,5,6,7 /path/to/*.tif -t 250x250 <table_name> | psql -h <host> -U <username> -p 5432 -d <database><br><br>After the loading I got the following for the raster table:<br>Total Rows in <raster_table> = 1,040,000 <br>Tile Size = 250 x 250<br><br>Going by the previous explanation from Regina, I would expect the following number of rows for the overviews:<br><br>o_7_<raster_table> = 1,040,000/Power(2,7) = 1,040,000/128 = 8,125 rows<br><br>o_6_<raster_table> = 1,040,000/Power(2,6) = 1,040,000/64 = 16,250 rows<br><br>o_5_<raster_table> = 1,040,000/Power(2,5) = 1,040,000/32 = 32,500 rows<br><br>o_4_<raster_table> = 1,040,000/Power(2,4) = 1,040,000/16 = 65,000 rows<br><br>o_3_<raster_table> = 1,040,000/Power(2,3) = 1,040,000/8 = 130,000 rows<br><br>o_2_<raster_table> = 1,040,000/Power(2,2) = 1,040,000/4 = 260,000 rows<br><br>However, this is what I got after loading the raster with the raster2pgsql tool following the syntax provided above:<br><br>o_7_<raster_table> = 23,400 rows<br><br>o_6_<raster_table> = 31,850 rows<br><br>o_5_<raster_table> = 41,600 rows<br><br>o_4_<raster_table> = 65,000 rows<br><br>o_3_<raster_table> = 127,400 rows<br><br>o_2_<raster_table> = 260,000 rows<br><br><b>Question 1</b><br>From the above, only the overview tables for overview factor 2 and 4 (i.e. o_4_<raster_table> and o_2_<raster_table>) are given the expected result (number of rows). Is it a coincidence that these overview-factors, 2 and 4, happen to be powers of 2?<br><br><b>Question 2</b><br>I stopped at overview-factor 7, because, going by the previous explanation by Regina, it appears going above that (e.g overview-factor 8) would produce scrap tiles i.e.<br>o_8_<raster_table> = 1,040,000/Power(2,8) = 1,040,000/256 = 4,062 + 128/256 = 4,063 rows (which would result in 128 slots being filled with no data filler)<br>How do I generate more overview tables (e.g o_8_<raster_table>, o_9_<raster_table>, ...) without getting scrap tiles?<br><br><b>NB: </b>When I try to view the raster in QGIS, it is taking 'forever' to load the initial full-extent zoom<br><br>I would be glad if someone could help me understand this by providing answers to the above questions.</span><o:p></o:p></p></div><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p><div><p class=MsoNormal style='margin-left:.5in'>On May 26, 2017 10:07 AM, "Osahon Oduware" <<a href="mailto:osahon.gis@gmail.com">osahon.gis@gmail.com</a>> wrote:<o:p></o:p></p><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><div><p class=MsoNormal style='margin-left:.5in'>Hi All,<br><br>I loaded a raster successfully using the raster2pgsql tool with the following syntax:<br>raster2pgsql -s 26332 -d -Y -e -I -C -F -M -l 2,3,4,5,6,7 /path/to/*.tif -t 250x250 <table_name> | psql -h <host> -U <username> -p 5432 -d <database><br><br>After the loading I got the following for the raster table:<br>Total Rows in <raster_table> = 1,040,000 <br>Tile Size = 250 x 250<br><br>Going by the previous explanation from Regina, I would expect the following number of rows for the overviews:<br><br>o_7_<raster_table> = 1,040,000/Power(2,7) = 1,040,000/128 = 8,125 rows<br><br>o_6_<raster_table> = 1,040,000/Power(2,6) = 1,040,000/64 = 16,250 rows<br><br>o_5_<raster_table> = 1,040,000/Power(2,5) = 1,040,000/32 = 32,500 rows<br><br>o_4_<raster_table> = 1,040,000/Power(2,4) = 1,040,000/16 = 65,000 rows<br><br>o_3_<raster_table> = 1,040,000/Power(2,3) = 1,040,000/8 = 130,000 rows<br><br>o_2_<raster_table> = 1,040,000/Power(2,2) = 1,040,000/4 = 260,000 rows<br><br><br>However, this is what I got after loading the raster with the raster2pgsql tool following the syntax provided above:<br><br>o_7_<raster_table> = 23,400 rows<br><br>o_6_<raster_table> = 31,850 rows<br><br>o_5_<raster_table> = 41,600 rows<br><br>o_4_<raster_table> = 65,000 rows<br><br>o_3_<raster_table> = 127,400 rows<br><br>o_2_<raster_table> = 260,000 rows<br><br><b>Question 1</b><br>From the above, only the overview tables for overview factor 2 and 4 (i.e. o_4_<raster_table> and o_2_<raster_table>) are given the expected result (number of rows). Is it a coincidence that these overview-factors, 2 and 4, happen to be powers of 2?<br><br><b>Question 2</b><br>I stopped at overview-factor 7, because, going by the previous explanation by Regina, it appears going above that (e.g overview-factor 8) would produce scrap tiles i.e.<br>o_8_<raster_table> = 1,040,000/Power(2,8) = 1,040,000/256 = 4,062 + 128/256 = 4,063 rows (which would result in 128 slots being filled with no data filler)<br>How do I generate more overview tables (e.g o_8_<raster_table>, o_9_<raster_table>, ...) without getting scrap tiles?<br><br><b>NB: </b>When I try to view the raster in QGIS, it is taking 'forever' to load the initial full-extent zoom<br><br><br>I would be glad if someone could help me understand this by providing answers to the above questions.<o:p></o:p></p><div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p><div><p class=MsoNormal style='margin-left:.5in'><br><br><br>On Thu, May 11, 2017 at 2:31 PM, Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:<o:p></o:p></p><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Osahon,</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=m-7197725885516056396m-2868782805378049337msolistparagraph style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>1)</span><span style='font-size:7.0pt;color:#1F497D'> </span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>The overview factors do not have to be factors of 2.</span><o:p></o:p></p><p class=m-7197725885516056396m-2868782805378049337msolistparagraph style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>2)</span><span style='font-size:7.0pt;color:#1F497D'> </span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>You get fewer rows the higher the factor because the factor gives you lower resolution.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>So if your original number of rows (tiles) = 1000</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>An overview factor of 3 = 1000/Power(2,3) = 1000/(2*2*2) = 125 rows</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=m-7197725885516056396m-2868782805378049337msolistparagraph style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>3)</span><span style='font-size:7.0pt;color:#1F497D'> </span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>If you tiled your main raster in 250x250 then all factors have tile sizes 250x250, but the higher rasters are packing more of the original tiles (geometry space) in each tile because they are lower resolution.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>So in the over view factor =2 case – each tile contains 4 tiles of the original raster (but at 1/4 the resolution).</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=m-7197725885516056396m-2868782805378049337msolistparagraph style='margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>4)</span><span style='font-size:7.0pt;color:#1F497D'> </span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>You get scrap tiles because each tile must maintain the same size but includes more than 1 tile from original, but must contain a whole tile (not a half tile for example)</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>So lets say you had not 1000 tiles but 1001 tiles, that last tile of your over view 3 would have only 1 tile of the original and the remaining 7 slots would be filled in with no data filler</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>so that the tile size of 250x250</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:.5in'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><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'> Osahon Oduware [mailto:<a href="mailto:osahon.gis@gmail.com" target="_blank">osahon.gis@gmail.com</a>] <br><b>Sent:</b> Thursday, May 11, 2017 6:37 AM<br><b>To:</b> PostGIS Development Discussion <<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a>>; Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>>; Pierre Racine <<a href="mailto:pierre.racine@sbf.ulaval.ca" target="_blank">pierre.racine@sbf.ulaval.ca</a>><br><b>Subject:</b> Fwd: [postgis-users] PostGIS Rasters Overview Factor</span><o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>Could anyone help with the questions below?<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;margin-bottom:12.0pt;margin-left:1.0in'>---------- Forwarded message ----------<br>From: <b>Osahon Oduware</b> <<a href="mailto:osahon.gis@gmail.com" target="_blank">osahon.gis@gmail.com</a>><br>Date: Thu, May 11, 2017 at 11:00 AM<br>Subject: Re: [postgis-users] PostGIS Rasters Overview Factor<br>To: PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>Cc: David Haynes <<a href="mailto:haynesd2@gmail.com" target="_blank">haynesd2@gmail.com</a>>, Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>>, Pierre Racine <<a href="mailto:pierre.racine@sbf.ulaval.ca" target="_blank">pierre.racine@sbf.ulaval.ca</a>><o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>Hi David,<o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>Thanks for your response. Sorry, I forgot to mention that I also performed the loading using the tile flag <b>-t 250x250</b> using the syntax below:<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span style='font-size:9.5pt;color:#500050'>raster2pgsql -s <SRID> -d -Y -e -I -C -F -M -l 2,4,8,16,32,64,128,256,512,1024,2048,4096,8192,16384,32768,65536 /path/to/raster/file </span><o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span style='font-size:9.5pt;color:#500050'>-t 250x250 <table_name> | psql -h <host_address> -U postgres -p 5432 -d <database></span><o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>I obtained the quoted information below from this link:<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><a href="http://postgis.net/docs/manual-2.2/using_raster_dataman.html#RT_Raster_Overviews" target="_blank">http://postgis.net/docs/manual-2.2/using_raster_dataman.html#RT_Raster_Overviews</a><o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>"overview_factor - this is the pyramid level of the overview table. The higher the number the lower the resolution of the table. raster2pgsql if given a folder of images, will compute overview of each image file and load separately. Level 1 is assumed and always the original file. Level 2 is will have each tile represent 4 of the original. So for example if you have a folder of 5000x5000 pixel image files that you chose to chunk 125x125, for each image file your base table will have (5000*5000)/(125*125) records = 1600, your (l=2) o_2 table will have ceiling(1600/Power(2,2)) = 400 rows,<b> your (l=3) o_3 will have ceiling(1600/Power(2,3) ) = 200 rows</b>. If your pixels aren't divisible by the size of your tiles, you'll get some scrap tiles (tiles not completely filled). Note that each overview tile generated by raster2pgsql has the same number of pixels as its parent, but is of a lower resolution where each pixel of it represents (Power(2,overview_factor) pixels of the original)."<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><b>Question 1</b><o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>The example given in the document above states that "<b>... </b>your (l=2) o_2 table will have ceiling(1600/Power(2,2)) = 400 rows,<b> your (l=3) o_3 will have ceiling(1600/Power(2,3) ) = 200 rows ...." </b>It appears the <b>Power </b>function is being used internally to compute the number of rows for the overview table based on the overview_factor i.e. Power(2,<overview_factor>). If this is the case, <b>does it imply that the factors could be numbers other than power of 2 (e.g. 3 in this case), but cannot be greater than 1,000 ?</b><o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><b>Question 2</b><o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>After doing a bulk loading of <b>650 raster source files</b> with a tiling option flag, <b>-t 250x250</b>, and overview option flag, <b><span style='font-size:9.5pt;color:#500050'>-l 2,4,8,16,32,64,128,256</span></b> my raster table had <b>1,040,000 rows</b> and the highest factor overview table, o_256_<table_name>, had <b>650 rows, </b>while the lowest factor table, o_2_<table_name>, had <b>260,000 rows.</b><o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>Going by the example given in the document above, I would expect the opposite of what I am getting i.e. the highest factor overview table with more records (rows) than the lowest factor overview table. <b>What could be responsible for this?</b><o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><b>Question 3</b><o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>The document above states that "... If your pixels aren't divisible by the size of your tiles, you'll get some scrap tiles (tiles not completely filled) ...."<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>When I viewed the raster on QGIS (<b>see attached images</b>), I discovered that I get black portions on a large portion of the image when completely zoomed out. I only get to see the raster image when I zoom-in sufficiently (even for black portions).<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><b>What could be responsible for this black portions (see attached images) as my pixels are obviously divisible by the tile size (250x250)?</b><o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>I would be glad if someone could help out with the above questions.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div></div><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>On Wed, May 10, 2017 at 5:12 PM, David Haynes <<a href="mailto:haynesd2@gmail.com" target="_blank">haynesd2@gmail.com</a>> wrote:<o:p></o:p></p><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>You are correct overviews are powers based. Also it does not look like you specified tile size in your raster2pgsql statement<o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><span style='font-size:9.5pt;color:black'>raster2pgsql -s <SRID> -d -Y -e -I -C -F -M -t <pixels> x <pixels> -l 2,4,8,16,32</span><o:p></o:p></p></div></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>On Wed, May 10, 2017 at 11:04 AM, Osahon Oduware <<a href="mailto:osahon.gis@gmail.com" target="_blank">osahon.gis@gmail.com</a>> wrote:<o:p></o:p></p></div></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><div><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>Hi All,<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>I tried loading a raster with overviews using the raster2pgsql tool using the syntax below:<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>raster2pgsql -s <SRID> -d -Y -e -I -C -F -M -l 2,4,8,16,32,64,128,256,512,1024,2048,4096,8192,16384,32768,65536 /path/to/raster/file <table_name> | psql -h <host_address> -U postgres -p 5432 -d <database><o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>but it gave an error message stating that the overview factor cannot be more than 1,000.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>I would like to know how the Overview-factor works. Must the value for the Overview-factor be in Powers of 2 (i.e. 2,4,8,16,...)?<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>I would be glad if someone could help my understanding.<o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'>_______________________________________________<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><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'><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><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div></div></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:1.0in'> <o:p></o:p></p></div></div></div></div></div><p class=MsoNormal style='margin-left:.5in'><br>_______________________________________________<br>postgis-devel mailing list<br><a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><o:p></o:p></p></blockquote></div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p></div></div></blockquote></div></div></div></body></html>