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

Tue May 2 03:18:26 PDT 2017

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-bounces at 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.Haertel at t-systems.com<mailto:Michael.Haertel at 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 at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users

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