Just to add the bug regarding raster2pgsql will be fixed in upcoming 2.3.3
And the work-around is to use –Y with –s as detailed in the ticket. https://trac.osgeo.org/postgis/ticket/3738 Regarding the QGIS issue with GDAL not supporting out-db. I've been thinking that the easier fix might be to make the change in QGIS DbManager instead of PostGIS. The ultimate problem is that PostGIS is feeding an out-db raster instead of the raster bytes. QGIS just makes queries to the PostGIS database and uses GDAL to consume the raw bytes. If QGIS dbmanager were to instead of doing SELECT rast … did something like SELECT ST_Materialize(rast) AS rast …. FROM somerast table … Where yet to be implemented PostGIS function ST_Materialize or whatever we decide to call it does nothing with in-db, but returns the raster bytes of out-db, then QGIS DbManager can treat out-db like any in-db raster. I'm thinking this would be a faster fix than mucking with PostGIS GDAL driver and in the end might help simplify the PostGIS GDAL driver handling of out-db since it can use the same function. strk, I think you are familiar with the internal workings of QGIS Db Manager. You think this proposed option is feasible and less taxing or we'd still need to do something on the GDAL end to make this work? Thanks, Regina From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Tumasgiu Rossini Sent: Tuesday, May 02, 2017 8:26 AM To: PostGIS Users Discussion <postgis-users@lists.osgeo.org> Subject: Re: [postgis-users] raster2pgsql: out_db=false with -R flag (And Showing out_db rasters in QGIS) I think that raster2pgsql is indeed a mature tool, but it is the out-db feature of postgis which is maybe not widely used, and so the bugs takes more time to appear because of the fewer users. 2017-05-02 12:18 GMT+02:00 <michael.haer...@t-systems.com <mailto:michael.haer...@t-systems.com> >: Hello Tumasgiu Rossini, thank you very much for your fast and comprehensive response. I was not aware of that bug yet but found so many references to raster2pgsql that I just assumed that it was a mature tool already. (And noted that I gave wrong parameter in the subject line of my mail, should have been R and not F). Good luck with your programming efforts. I guess it’s somewhat complex since you have to deal with the real pixel data. Regards, Michael Von: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org <mailto:postgis-users-boun...@lists.osgeo.org> ] Im Auftrag von Tumasgiu Rossini Gesendet: Dienstag, 2. Mai 2017 12:08 An: PostGIS Users Discussion Betreff: Re: [postgis-users] raster2pgsql: out_db=false with -F flag (And Showing out_db rasters in QGIS) Hi, To answer your first question : the combination of the -s and -R options actually transforms out-db raster into in-db, it is a recent known bug, see https://lists.osgeo.org/pipermail/postgis-users/2017-April/042003.html for more information. For the latter question, Qgis uses gdal to fetch raster data from postgis and the out-db support is not implemented. You can though maybe use workarounds, more info here : https://lists.osgeo.org/pipermail/postgis-users/2017-April/042077.html https://lists.osgeo.org/pipermail/postgis-users/2017-April/042053.html Using out-db rasters and Qgis at my company, I am facing myself this problem and I am trying to implement out-db support in gdal, though I am no gdal developer and it is not a high priority feature. 2017-05-02 11:37 GMT+02:00 <michael.haer...@t-systems.com <mailto:michael.haer...@t-systems.com> >: Dear users, I hope that somebody can tell me if I found a bug or simply don’t use the command correctly. Given: * A directory with four GeoTIFF files * PostgreSQL 9.6.1, compiled by Visual C++ build 1800, 64-bit * 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 * OS is Windows 7 I try to import the Tiff files as OUT_DB raster files using this command: 1. raster2pgsql.exe -s 25832 -I -R -n myFilename -C -M -F C:\_GEODATA_\*.tif raster2x2 > t:\DTK10.sql Output: a. Processing 1/4: C:\_GEODATA_\24_dtk10_32356_5608_2_nw_col.tif b. Processing 2/4: C:\_GEODATA_\25_dtk10_32356_5610_2_nw_col.tif c. Processing 3/4: C:\_GEODATA_\34_dtk10_32358_5608_2_nw_col.tif d. Processing 4/4: C:\_GEODATA_\35_dtk10_32358_5610_2_nw_col.tif 2. psql.exe --host=localhost --port=5432 --username=xxx --dbname=xxx -f t:\DTK10.sql Output: a. BEGIN b. CREATE TABLE c. INSERT 0 1 d. INSERT 0 1 e. INSERT 0 1 f. INSERT 0 1 g. CREATE INDEX h. ANALYZE i. psql:t:/DTK10.sql:9: HINWEIS: Adding SRID constraint j. psql:t:/DTK10.sql:9: HINWEIS: Adding scale-X constraint k. psql:t:/DTK10.sql:9: HINWEIS: Adding scale-Y constraint l. psql:t:/DTK10.sql:9: HINWEIS: Adding blocksize-X constraint m. psql:t:/DTK10.sql:9: HINWEIS: Adding blocksize-Y constraint n. psql:t:/DTK10.sql:9: HINWEIS: Adding alignment constraint o. psql:t:/DTK10.sql:9: HINWEIS: Adding number of bands constraint p. psql:t:/DTK10.sql:9: HINWEIS: Adding pixel type constraint q. psql:t:/DTK10.sql:9: HINWEIS: Adding nodata value constraint r. psql:t:/DTK10.sql:9: HINWEIS: Adding out-of-database constraint s. psql:t:/DTK10.sql:9: HINWEIS: Adding maximum extent constraint t. addrasterconstraints u. ---------------------- v. t Problem is that the view “raster_columns” always shows FALSE for out_db, even though I used the F flag: "r_table_catalog" "r_table_schema" "r_table_name" "r_raster_column" "srid" "scale_x" "scale_y" "blocksize_x" "blocksize_y" "same_alignment" "regular_blocking" "num_bands" "pixel_types" "nodata_values" "out_db" "extent" "spatial_index" "gismobile" "public" "raster2x2 " "rast" 25832 0.5 -0.5 4000 4000 true false 1 {8BUI} {NULL} {f} 0103000020E864000001000000050000000000000080BA154100000000906455410000000080BA154100000000786855410000000000F9154100000000786855410000000000F9154100000000906455410000000080BA15410000000090645541 true "rid" "rast" "filename" 1 0100000100000000000000E03F000000000000E0BF0000000080BA1541000000008466554100000000000000000000000000000000E8640000A00FA00F840000433A5C5F47454F444154415F5C32345F64746B31305F33323335365F353630385F325F6E775F636F6C2E74696600 "24_dtk10_32356_5608_2_nw_col.tif" 2 0100000100000000000000E03F000000000000E0BF0000000080BA1541000000007868554100000000000000000000000000000000E8640000A00FA00F840000433A5C5F47454F444154415F5C32355F64746B31305F33323335365F353631305F325F6E775F636F6C2E74696600 "25_dtk10_32356_5610_2_nw_col.tif" 3 0100000100000000000000E03F000000000000E0BF00000000C0D91541000000008466554100000000000000000000000000000000E8640000A00FA00F840000433A5C5F47454F444154415F5C33345F64746B31305F33323335385F353630385F325F6E775F636F6C2E74696600 "34_dtk10_32358_5608_2_nw_col.tif" 4 0100000100000000000000E03F000000000000E0BF00000000C0D91541000000007868554100000000000000000000000000000000E8640000A00FA00F840000433A5C5F47454F444154415F5C33355F64746B31305F33323335385F353631305F325F6E775F636F6C2E74696600 "35_dtk10_32358_5610_2_nw_col.tif" What I found out: As soon as I omit the s flag (to provide a SRS) the column shows TRUE. I guess that this is not the intended behaviour is it? 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? Interestiung is also that gdalinfo is not showing any extents although it shows correct SRS and the “extent” column is populated (see above): C:\_PortableApps_\pgsql\bin>"c:\QGIS\bin\gdalinfo.exe" PG:"dbname='xxx' host='localhost' port='5432' user='mhaerte2' password='xxx' table='raster2x2'" Driver: PostGISRaster/PostGIS Raster driver Files: none associated Size is 0, 0 Coordinate System is: PROJCS["ETRS89 / UTM zone 32N", GEOGCS["ETRS89", DATUM["European_Terrestrial_Reference_System_1989", SPHEROID["GRS 1980",6378137,298.257222101, AUTHORITY["EPSG","7019"]], TOWGS84[0,0,0,0,0,0,0], AUTHORITY["EPSG","6258"]], PRIMEM["Greenwich",0, AUTHORITY["EPSG","8901"]], UNIT["degree",0.0174532925199433, AUTHORITY["EPSG","9122"]], AUTHORITY["EPSG","4258"]], PROJECTION["Transverse_Mercator"], PARAMETER["latitude_of_origin",0], PARAMETER["central_meridian",9], PARAMETER["scale_factor",0.9996], PARAMETER["false_easting",500000], PARAMETER["false_northing",0], UNIT["metre",1, AUTHORITY["EPSG","9001"]], AXIS["Easting",EAST], AXIS["Northing",NORTH], AUTHORITY["EPSG","25832"]] Subdatasets: --- snip! -- Corner Coordinates: Upper Left ( 0.0, 0.0) Lower Left ( 0.0, 0.0) Upper Right ( 0.0, 0.0) Lower Right ( 0.0, 0.0) Center ( 0.0, 0.0) Does anyone have a clue? Thank you for your comments, Michael _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org> https://lists.osgeo.org/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org> https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
