<div dir="ltr"><div><div>Hi,<br><br></div>To answer your first question : <br></div><div>the combination of the -s and -R options actually transforms out-db raster into in-db,<br></div><div>it is a recent known bug, see <a href="https://lists.osgeo.org/pipermail/postgis-users/2017-April/042003.html">https://lists.osgeo.org/pipermail/postgis-users/2017-April/042003.html</a> <br>for more information.<br><br></div><div>For the latter question,<br></div><div>Qgis uses gdal to fetch raster data from postgis and the out-db support is not implemented.<br></div><div>You can though maybe use workarounds, more info here :<br><a href="https://lists.osgeo.org/pipermail/postgis-users/2017-April/042077.html">https://lists.osgeo.org/pipermail/postgis-users/2017-April/042077.html</a><br><a href="https://lists.osgeo.org/pipermail/postgis-users/2017-April/042053.html">https://lists.osgeo.org/pipermail/postgis-users/2017-April/042053.html</a><br></div><div><br></div><div>Using out-db rasters and Qgis at my company, I am facing myself this problem and<br></div><div>I am trying to implement out-db support in gdal, though I am no gdal developer<br></div><div>and it is not a high priority feature.<br></div></div><div class="gmail_extra"><br><div class="gmail_quote">2017-05-02 11:37 GMT+02:00 <span dir="ltr"><<a href="mailto:Michael.Haertel@t-systems.com" target="_blank">Michael.Haertel@t-systems.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div>
<font size="2" face="Calibri"><span style="font-size:11pt">
<div>Dear users,</div>
<div> </div>
<div>I hope that somebody can tell me if I found a bug or simply don’t use the command correctly.</div>
<div> </div>
<div>Given:</div>
<ul style="margin:0;padding-left:36pt">
<li>A directory with four GeoTIFF files </li><li>PostgreSQL 9.6.1, compiled by Visual C++ build 1800, 64-bit</li><li>POSTGIS="2.3.1 r15264" GEOS="3.6.0-CAPI-1.10.0 r4265" SFCGAL="1.3.0" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.1.1, released 2016/07/07" LIBXML="2.7.8" LIBJSON="0.12" RASTER</li><li>OS is Windows 7</li></ul>
<div> </div>
<div>I try to import the Tiff files as OUT_DB raster files using this command:</div>
<div> </div>
<ol style="margin:0;padding-left:36pt">
<font face="Courier New">
<li>raster2pgsql.exe -s 25832 -I -R -n myFilename -C -M -F C:\_GEODATA_\*.tif raster2x2 > t:\DTK10.sql<br>
<br>
<font face="Calibri">Output:</font></li></font>
</ol>
<ol style="margin:0;padding-left:72pt" type="a">
<li>Processing 1/4: C:\_GEODATA_\24_dtk10_32356_<wbr>5608_2_nw_col.tif</li><li>Processing 2/4: C:\_GEODATA_\25_dtk10_32356_<wbr>5610_2_nw_col.tif</li><li>Processing 3/4: C:\_GEODATA_\34_dtk10_32358_<wbr>5608_2_nw_col.tif</li><li>Processing 4/4: C:\_GEODATA_\35_dtk10_32358_<wbr>5610_2_nw_col.tif</li></ol>
<div style="padding-left:72pt"> </div>
<ol start="2" style="margin:0;padding-left:36pt">
<font face="Courier New">
<li>psql.exe --host=localhost --port=5432 --username=xxx --dbname=xxx -f t:\DTK10.sql</li></font>
</ol>
<div style="padding-left:36pt"><br>
Output:</div>
<ol style="margin:0;padding-left:72pt" type="a">
<li>BEGIN</li><li>CREATE TABLE</li><li>INSERT 0 1</li><li>INSERT 0 1</li><li>INSERT 0 1</li><li>INSERT 0 1</li><li>CREATE INDEX</li><li>ANALYZE</li><li>psql:t:/DTK10.sql:9: HINWEIS: Adding SRID constraint</li><li>psql:t:/DTK10.sql:9: HINWEIS: Adding scale-X constraint</li><li>psql:t:/DTK10.sql:9: HINWEIS: Adding scale-Y constraint</li><li>psql:t:/DTK10.sql:9: HINWEIS: Adding blocksize-X constraint</li><li>psql:t:/DTK10.sql:9: HINWEIS: Adding blocksize-Y constraint</li><li>psql:t:/DTK10.sql:9: HINWEIS: Adding alignment constraint</li><li>psql:t:/DTK10.sql:9: HINWEIS: Adding number of bands constraint</li><li>psql:t:/DTK10.sql:9: HINWEIS: Adding pixel type constraint</li><li>psql:t:/DTK10.sql:9: HINWEIS: Adding nodata value constraint</li><li>psql:t:/DTK10.sql:9: HINWEIS: Adding out-of-database constraint</li><li>psql:t:/DTK10.sql:9: HINWEIS: Adding maximum extent constraint</li><li>addrasterconstraints</li><li>----------------------</li><li>t</li></ol>
<div> </div>
<div>Problem is that the view “raster_columns” always shows FALSE for out_db, even though I used the F flag:<br>
</div>
<table style="width:1589.15pt" width="2648">
<colgroup><col style="width:63.5pt" width="105">
<col style="width:64.8pt" width="108">
<col style="width:58.8pt" width="98">
<col style="width:66.6pt" width="111">
<col style="width:29.5pt" width="49">
<col style="width:38.45pt" width="64">
<col style="width:38.6pt" width="64">
<col style="width:50.65pt" width="84">
<col style="width:50.75pt" width="84">
<col style="width:66.45pt" width="110">
<col style="width:66.65pt" width="111">
<col style="width:52.5pt" width="86">
<col style="width:50.7pt" width="84">
<col style="width:60.5pt" width="100">
<col style="width:38.35pt" width="63">
<col style="width:736.5pt" width="1227">
<col style="width:56.3pt" width="93">
</colgroup><tbody><tr>
<td>"r_table_catalog"</td>
<td>"r_table_schema"</td>
<td>"r_table_name"</td>
<td>"r_raster_column"</td>
<td>"srid"</td>
<td>"scale_x"</td>
<td>"scale_y"</td>
<td>"blocksize_x"</td>
<td>"blocksize_y"</td>
<td>"same_alignment"</td>
<td>"regular_blocking"</td>
<td>"num_bands"</td>
<td>"pixel_types"</td>
<td>"nodata_values"</td>
<td>"out_db"</td>
<td>"extent"</td>
<td>"spatial_index"</td>
</tr>
<tr>
<td>"gismobile"</td>
<td>"public"</td>
<td>"raster2x2 "</td>
<td>"rast"</td>
<td>25832</td>
<td>0.5</td>
<td>-0.5</td>
<td>4000</td>
<td>4000</td>
<td>true</td>
<td>false</td>
<td>1</td>
<td>{8BUI}</td>
<td>{NULL}</td>
<td>{f}</td>
<td>0103000020E8640000010000000500<wbr>00000000000080BA15410000000090<wbr>6455410000000080BA154100000000<wbr>786855410000000000F91541000000<wbr>00786855410000000000F915410000<wbr>0000906455410000000080BA154100<wbr>00000090645541</td>
<td>true</td>
</tr>
</tbody></table>
<div> </div>
<div> </div>
<div> </div>
<table style="width:481.1pt" width="801">
<colgroup><col style="width:17.45pt" width="29">
<col style="width:398.5pt" width="663">
<col style="width:65.6pt" width="109">
</colgroup><tbody><tr>
<td>"rid"</td>
<td>"rast"</td>
<td>"filename"</td>
</tr>
<tr>
<td>1</td>
<td>0100000100000000000000E03F0000<wbr>00000000E0BF0000000080BA154100<wbr>000000846655410000000000000000<wbr>0000000000000000E8640000A00FA0<wbr>0F840000433A5C5F47454F44415441<wbr>5F5C32345F64746B31305F33323335<wbr>365F353630385F325F6E775F636F6C<wbr>2E74696600</td>
<td>"24_dtk10_32356_5608_2_nw_col.<wbr>tif"</td>
</tr>
<tr>
<td>2</td>
<td>0100000100000000000000E03F0000<wbr>00000000E0BF0000000080BA154100<wbr>000000786855410000000000000000<wbr>0000000000000000E8640000A00FA0<wbr>0F840000433A5C5F47454F44415441<wbr>5F5C32355F64746B31305F33323335<wbr>365F353631305F325F6E775F636F6C<wbr>2E74696600</td>
<td>"25_dtk10_32356_5610_2_nw_col.<wbr>tif"</td>
</tr>
<tr>
<td>3</td>
<td>0100000100000000000000E03F0000<wbr>00000000E0BF00000000C0D9154100<wbr>000000846655410000000000000000<wbr>0000000000000000E8640000A00FA0<wbr>0F840000433A5C5F47454F44415441<wbr>5F5C33345F64746B31305F33323335<wbr>385F353630385F325F6E775F636F6C<wbr>2E74696600</td>
<td>"34_dtk10_32358_5608_2_nw_col.<wbr>tif"</td>
</tr>
<tr>
<td>4</td>
<td>0100000100000000000000E03F0000<wbr>00000000E0BF00000000C0D9154100<wbr>000000786855410000000000000000<wbr>0000000000000000E8640000A00FA0<wbr>0F840000433A5C5F47454F44415441<wbr>5F5C33355F64746B31305F33323335<wbr>385F353631305F325F6E775F636F6C<wbr>2E74696600</td>
<td>"35_dtk10_32358_5610_2_nw_col.<wbr>tif"</td>
</tr>
</tbody></table>
<div> </div>
<div>What I found out: As soon as I omit the s flag (to provide a SRS) the column shows TRUE.</div>
<div> </div>
<div>I guess that this is not the intended behaviour is it?</div>
<div> </div>
<div>My QGIS 2.18 only shows black boxes for out_db rasters, the rasters themselves (pixel values) only appear for “in-DB” rasters. Did anybody succeed in showing out_db rasters in QGIS?</div>
<div> </div>
<div>Interestiung is also that gdalinfo is not showing any extents although it shows correct SRS and the “extent” column is populated (see above):</div>
<div> </div>
<div><font face="Courier New">C:\_PortableApps_\pgsql\bin>"<wbr>c:\QGIS\bin\gdalinfo.exe" PG:"dbname='xxx' host='localhost' port='5432' user='mhaerte2' password='xxx' table='raster2x2'"</font></div>
<div> </div>
<div>Driver: PostGISRaster/PostGIS Raster driver</div>
<div>Files: none associated</div>
<div>Size is 0, 0</div>
<div>Coordinate System is:</div>
<div>PROJCS["ETRS89 / UTM zone 32N",</div>
<div> GEOGCS["ETRS89",</div>
<div> DATUM["European_Terrestrial_<wbr>Reference_System_1989",</div>
<div> SPHEROID["GRS 1980",6378137,298.257222101,</div>
<div> AUTHORITY["EPSG","7019"]],</div>
<div> TOWGS84[0,0,0,0,0,0,0],</div>
<div> AUTHORITY["EPSG","6258"]],</div>
<div> PRIMEM["Greenwich",0,</div>
<div> AUTHORITY["EPSG","8901"]],</div>
<div> UNIT["degree",0.<wbr>0174532925199433,</div>
<div> AUTHORITY["EPSG","9122"]],</div>
<div> AUTHORITY["EPSG","4258"]],</div>
<div> PROJECTION["Transverse_<wbr>Mercator"],</div>
<div> PARAMETER["latitude_of_origin"<wbr>,0],</div>
<div> PARAMETER["central_meridian",<wbr>9],</div>
<div> PARAMETER["scale_factor",0.<wbr>9996],</div>
<div> PARAMETER["false_easting",<wbr>500000],</div>
<div> PARAMETER["false_northing",0],</div>
<div> UNIT["metre",1,</div>
<div> AUTHORITY["EPSG","9001"]],</div>
<div> AXIS["Easting",EAST],</div>
<div> AXIS["Northing",NORTH],</div>
<div> AUTHORITY["EPSG","25832"]]</div>
<div>Subdatasets:</div>
<div> --- snip! --</div>
<div>Corner Coordinates:</div>
<div>Upper Left ( 0.0, 0.0)</div>
<div>Lower Left ( 0.0, 0.0)</div>
<div>Upper Right ( 0.0, 0.0)</div>
<div>Lower Right ( 0.0, 0.0)</div>
<div>Center ( 0.0, 0.0)</div>
<div> </div>
<div>Does anyone have a clue? </div>
<div> </div>
<div>Thank you for your comments,</div>
<div> </div>
<div>Michael</div>
<div> </div>
</span></font>
</div>
<br>______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>