<div dir="ltr">Hi Regina,<div><br></div><div>Thanks a lot for your help.</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Thu, May 4, 2017 at 1:59 PM, Regina Obe <span dir="ltr"><<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div lang="EN-US" link="blue" vlink="purple"><div class="m_-8648637589937021070WordSection1"><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">I experimented with that and I don't think it helps that much so I abandoned that idea.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Reason being I think the meta data check it does on the raster because it's missing the information in raster_columns is very expensive out-weights the spatial index benefit.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Only way which I haven't tried yet but might work is to replace the  raster_columns view with an entry that is pretty much a copy of your original table entry. <u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">I suppose you can experiment with that <u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><span>1)<span style="font:7.0pt "Times New Roman"">      </span></span></span><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Renaming your raster_columns view to something like zz_raster_columns<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><span>2)<span style="font:7.0pt "Times New Roman"">      </span></span></span><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">SELECT * INTO raster_columns FROM zz_raster_columns;<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Updating the entry for your view with all the meta data it needs<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><span>3)<span style="font:7.0pt "Times New Roman"">      </span></span></span><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">For the index, you'd need to look at the ST_Resize function and change it from STABLE to IMMUTABLE<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">I would instead create a new function which is wrapper around the core function used by ST_Resize so  it doesn't get replaced during upgrade.  Something like<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">CREATE OR REPLACE FUNCTION UPGIS_Materialize(rast raster) RETURNS raster AS<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">$$<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">BEGIN<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">RETURN _ST_gdalwarp(<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">                              <wbr>                  $1,<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">                              <wbr>                  'NearestNeighbour', 0.125,<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">                              <wbr>                  NULL,<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">                              <wbr>                  NULL, NULL,<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">                              <wbr>                  NULL, NULL,<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">                              <wbr>                  NULL, NULL,<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">                              <wbr>                  ST_Width($1), ST_Height($1)<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">                              <wbr>  );<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">END;<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">$$<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">language plpgsql IMMUTABLE strict COST 1000;<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Anyway I haven't had a chance to test this out so wouldn't be surprised if you run into errors.<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Thanks,<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Regina<u></u><u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="m_-8648637589937021070MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></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:<a href="mailto:osahon.gis@gmail.com" target="_blank">osahon.gis@gmail.com</a>] <br><b>Sent:</b> Thursday, May 04, 2017 8:23 AM<br><b>To:</b> Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>><br><b>Cc:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><wbr>>; Pierre Racine <<a href="mailto:pierre.racine@sbf.ulaval.ca" target="_blank">pierre.racine@sbf.ulaval.ca</a>>; PostGIS Development Discussion <<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><wbr>></span></p><div><div class="h5"><br><b>Subject:</b> Re: GDAL OUT-DB RASTER SUPPORT<u></u><u></u></div></div><p></p><div><div class="h5"><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p><div><p class="MsoNormal" style="margin-left:.5in">Hi Regina,<u></u><u></u></p><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">When I tried to create the index using the syntax you provided:<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:blue">CREATE INDEX idx_outdb_table_mat_rast  ON outdb_table USING gist(st_convexhull(ST_Resize (rast,0.8,0.8)) );</span><u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">I get the following error:<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><span style="color:blue">ERROR:  functions in index expression must be marked IMMUTABLE</span><u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">How do I resolve this error?<u></u><u></u></p></div></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p><div><p class="MsoNormal" style="margin-left:.5in">On Thu, May 4, 2017 at 5:48 AM, Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:<u></u><u></u></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="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Osahon,</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">I have some ideas, but nothing I can suggest that is pluggable without changing PostGIS and GDAL.</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">The reason why it's slow is a couple</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="m_-8648637589937021070m-4940504040110931203msolistparagraph" 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">ST_Resize is generally slower than reading cause it's doing some stuff to the raster.  The new function I am thinking of would do nothing aside from reading in the bytes of the raster.</span><u></u><u></u></p><p class="m_-8648637589937021070m-4940504040110931203msolistparagraph" 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">When you use a view, the data in raster_columns view for the view is incomplete, doesn't have the meta data that GDAL relies on, so it then tries to compute it with a query which since the rast column is a black box to it, also requires rerunning ST_Resize or whatever by querying the view which compounds the slowness.</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">To remedy this issue in 2.4, I'm planning to have a side line table like raster_column_additional so users can manually enter things like views with the right params and then the raster_columns view would use this information instead if of recomputing if there is an entry in raster_column_additional.</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Plan to throw in some helper functions to auto-compute for views.</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">This part isn't needed for making out-db readable and speedily readable, but would solve some other issues people have complained about.</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="m_-8648637589937021070m-4940504040110931203msolistparagraph" 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">Since the ST_Resize view uses  a function and GDAL just sees the view as another table, you'd need a spatial index on the functional bits something like:</span><u></u><u></u></p><p class="m_-8648637589937021070m-4940504040110931203msolistparagraph" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">CREATE INDEX idx_outdb_table_mat_rast  ON outdb_table USING gist(st_convexhull(ST_Resize (rast,0.8,0.8)) );</span><u></u><u></u></p><p class="m_-8648637589937021070m-4940504040110931203msolistparagraph" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="m_-8648637589937021070m-4940504040110931203msolistparagraph" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">This wouldn't be necessary at all if we revised GDAL library.</span><u></u><u></u></p><p class="m_-8648637589937021070m-4940504040110931203msolistparagraph" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="m_-8648637589937021070m-4940504040110931203msolistparagraph" 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"> for it to be able to use it seamlessly, unless you did as I mentioned, limit your view to just a small area.</span><u></u><u></u></p><p class="m_-8648637589937021070m-4940504040110931203msolistparagraph" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="m_-8648637589937021070m-4940504040110931203msolistparagraph" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">The fix I had in mind for this as I mentioned in another email is to create a wrapper function (that is not as expensive as ST_Resize that will return a non-outdb raster), </span><u></u><u></u></p><p class="m_-8648637589937021070m-4940504040110931203msolistparagraph" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">and revise GDAL so the query will still use the raw out-db raster for computing the meta data it needs and also using the spatial index on the out-db so no additional index is needed and no additional cost of running the function is incurred.</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">All the above I think should make using out-db raster as transparent and fast as in-db and viewable in QGIS.</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">HINT HINT – If any raster folks are interested in funding this kind of work, let me know.  We will then be able to prioritize it for PostGIS 2.4 and next GDAL release.</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Thanks,</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Regina</span><u></u><u></u></p><p class="MsoNormal" style="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> Wednesday, May 03, 2017 12:02 PM<br><b>To:</b> Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>><br><b>Cc:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><wbr>>; Pierre Racine <<a href="mailto:pierre.racine@sbf.ulaval.ca" target="_blank">pierre.racine@sbf.ulaval.ca</a>><br><b>Subject:</b> Re: GDAL OUT-DB RASTER SUPPORT</span><u></u><u></u></p><div><div><p class="MsoNormal" style="margin-left:1.0in"> <u></u><u></u></p><div><p class="MsoNormal" style="margin-left:1.0in">Hi Regina,<u></u><u></u></p><div><p class="MsoNormal" style="margin-left:1.0in"> <u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:1.0in">I followed the process you described to create a view for the out-db rasters and dragged it to the map. I was able to view the out-db rasters this way. However, <b>it takes very long before the raster is displayed on the map</b> (in QGIS) as compared to the time taken to display an in-db raster.<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:1.0in"> <u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:1.0in">Is there anything that can be done to improve the speed?<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:1.0in"> <u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:1.0in"> <u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:1.0in">NB: indexes were created with the -I flag of the raster2pgsql tool<u></u><u></u></p></div></div><div><p class="MsoNormal" style="margin-left:1.0in"> <u></u><u></u></p><div><p class="MsoNormal" style="margin-left:1.0in">On Fri, Apr 28, 2017 at 4:07 PM, Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:<u></u><u></u></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><div><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Osahon,</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">It depends what you are trying to show and do.</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="m_-8648637589937021070m-4940504040110931203m-8964489869344440318msolistparagraph" style="margin-left:1.0in"><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">For example if you create overviews on your out-db, those will show, so then it would only be when you zoom in that you hit the out-db that you'd get black.</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="m_-8648637589937021070m-4940504040110931203m-8964489869344440318msolistparagraph" style="margin-left:1.0in"><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">If you are looking to inspect a slice of out-db with QGIS, you can create a view that has just the piece you want to show and drag it to your map.</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">You'd need to apply a function that forces that piece into in-db.  Not all raster functions do that and sadly we haven't been good about documenting ones that do vs. ones that don't.</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">I know for sure that ST_Resize would force a materialized in-db and that might actually be a good one to use anyway since it would lower the res allowing you to see more.</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">So something like</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">-- do this with db manager or pgAdmin</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">CREATE OR REPLACE VIEW vw_outdb_sample AS</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">SELECT rid, ST_Resize(rast,0.8, 0.8) AS rast</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">FROM some_outdb_table</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">WHERE ST_Intersects(rast,some_<wbr>geometry);</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Then drag this view to your Map.</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Hope that helps,</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Regina</span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.0in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="MsoNormal" style="margin-left:1.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:<a href="mailto:osahon.gis@gmail.com" target="_blank">osahon.gis@gmail.com</a>] <br><b>Sent:</b> Friday, April 28, 2017 10:13 AM<br><b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><wbr>><br><b>Cc:</b> Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>>; <a href="mailto:pierre.racine@sbf.ulaval.ca" target="_blank">pierre.racine@sbf.ulaval.ca</a><br><b>Subject:</b> GDAL OUT-DB RASTER SUPPORT</span><u></u><u></u></p><div><div><p class="MsoNormal" style="margin-left:1.5in"> <u></u><u></u></p><div><p class="MsoNormal" style="margin-bottom:12.0pt;margin-left:1.5in">Hi All,<br><br>I am getting a black image when I try to display PostGIS out-db rasters. I discovered that it has to do with GDAL support for out-db rasters as stated in the link below for a similar issue (over 1 year back):<br><a href="https://hub.qgis.org/issues/12133" target="_blank">https://hub.qgis.org/issues/<wbr>12133</a><br><br>The last comment by Jürgen Fischer states "... only black images. So it's a dependency issue (GDAL complains ERROR 1: This raster has outdb storage. This feature isn't still available)."<br><br>I have also been told in the link below that "out-db support is not implemented yet in the GDAL PostGISRaster driver.":<br><a href="https://lists.osgeo.org/pipermail/gdal-dev/2017-April/046512.html" target="_blank">https://lists.osgeo.org/<wbr>pipermail/gdal-dev/2017-April/<wbr>046512.html</a><br><br>I would like to know how to solve this issue of displaying postgis out-db rasters in QGIS?<u></u><u></u></p></div></div></div></div></div></blockquote></div><p class="MsoNormal" style="margin-left:1.0in"> <u></u><u></u></p></div></div></div></div></div></blockquote></div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div></div></div></div></div></blockquote></div><br></div>