<div dir="ltr">Hi Regina,<div><br></div><div>When I tried to create the index using the syntax you provided:</div><div><span style="font-family:calibri,sans-serif;font-size:14.6667px"><font color="#0000ff">CREATE INDEX idx_outdb_table_mat_rast  ON outdb_table USING gist(st_convexhull(ST_Resize (rast,0.8,0.8)) );</font></span><br></div><div><br></div><div>I get the following error:</div><div><font color="#0000ff">ERROR:  functions in index expression must be marked IMMUTABLE</font><br></div><div><br></div><div>How do I resolve this error?</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Thu, May 4, 2017 at 5:48 AM, 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_-4940504040110931203WordSection1"><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Osahon,<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">I have some ideas, but nothing I can suggest that is pluggable without changing PostGIS and GDAL.<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">The reason why it's slow is a couple<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_-4940504040110931203MsoListParagraph"><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">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.<u></u><u></u></span></p><p class="m_-4940504040110931203MsoListParagraph"><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">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.<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" style="margin-left:.5in"><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.<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><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"><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.<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><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"><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.<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><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"><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"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="m_-4940504040110931203MsoListParagraph"><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">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:<u></u><u></u></span></p><p class="m_-4940504040110931203MsoListParagraph"><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)) );<u></u><u></u></span></p><p class="m_-4940504040110931203MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="m_-4940504040110931203MsoListParagraph"><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.<u></u><u></u></span></p><p class="m_-4940504040110931203MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="m_-4940504040110931203MsoListParagraph"><u></u><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><span>4)<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 it to be able to use it seamlessly, unless you did as I mentioned, limit your view to just a small area.<u></u><u></u></span></p><p class="m_-4940504040110931203MsoListParagraph"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="m_-4940504040110931203MsoListParagraph"><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), <u></u><u></u></span></p><p class="m_-4940504040110931203MsoListParagraph"><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.<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"><u></u> <u></u></span></p><p class="MsoNormal"><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.<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">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.<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">Thanks,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Regina<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> 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<u></u><u></u></span></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">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:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">Is there anything that can be done to improve the speed?<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"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">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:.5in"><u></u> <u></u></p><div><p class="MsoNormal" style="margin-left:.5in">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-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">It depends what you are trying to show and do.</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_-4940504040110931203m-8964489869344440318msolistparagraph" 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">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:.5in"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><u></u><u></u></p><p class="m_-4940504040110931203m-8964489869344440318msolistparagraph" 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">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:.5in"><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:.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 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:.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">So something like</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">-- do this with db manager or pgAdmin</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">CREATE OR REPLACE VIEW vw_outdb_sample AS</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">SELECT rid, ST_Resize(rast,0.8, 0.8) AS rast</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">FROM some_outdb_table</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">WHERE ST_Intersects(rast,some_<wbr>geometry);</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">Then drag this view to your Map.</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">Hope that helps,</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:.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: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> 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.0in"> <u></u><u></u></p><div><p class="MsoNormal" style="margin-bottom:12.0pt;margin-left:1.0in">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:.5in"><u></u> <u></u></p></div></div></div></div></div></blockquote></div><br></div>