[postgis-users] raster2pgsql: out_db=false with -F flag (And Showing out_db rasters in QGIS)

Tue May 2 02:37:39 PDT 2017

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170502/45caff35/attachment.html>