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

Tue May 2 13:04:02 PDT 2017

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-bounces at lists.osgeo.org] On Behalf Of Tumasgiu Rossini
Sent: Tuesday, May 02, 2017 8:26 AM
To: PostGIS Users Discussion <postgis-users at 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.Haertel at t-systems.com <mailto:Michael.Haertel at 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-bounces at lists.osgeo.org <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

_______________________________________________
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/063da47b/attachment.html>