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

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Michael.Haertel
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:
  1. Processing 1/4: C:\_GEODATA_\24_dtk10_32356_5608_2_nw_col.tif
  2. Processing 2/4: C:\_GEODATA_\25_dtk10_32356_5610_2_nw_col.tif
  3. Processing 3/4: C:\_GEODATA_\34_dtk10_32358_5608_2_nw_col.tif
  4. Processing 4/4: C:\_GEODATA_\35_dtk10_32358_5610_2_nw_col.tif
 
  1. psql.exe --host=localhost --port=5432 --username=xxx --dbname=xxx -f t:\DTK10.sql

Output:
  1. BEGIN
  2. CREATE TABLE
  3. INSERT 0 1
  4. INSERT 0 1
  5. INSERT 0 1
  6. INSERT 0 1
  7. CREATE INDEX
  8. ANALYZE
  9. psql:t:/DTK10.sql:9: HINWEIS:  Adding SRID constraint
  10. psql:t:/DTK10.sql:9: HINWEIS:  Adding scale-X constraint
  11. psql:t:/DTK10.sql:9: HINWEIS:  Adding scale-Y constraint
  12. psql:t:/DTK10.sql:9: HINWEIS:  Adding blocksize-X constraint
  13. psql:t:/DTK10.sql:9: HINWEIS:  Adding blocksize-Y constraint
  14. psql:t:/DTK10.sql:9: HINWEIS:  Adding alignment constraint
  15. psql:t:/DTK10.sql:9: HINWEIS:  Adding number of bands constraint
  16. psql:t:/DTK10.sql:9: HINWEIS:  Adding pixel type constraint
  17. psql:t:/DTK10.sql:9: HINWEIS:  Adding nodata value constraint
  18. psql:t:/DTK10.sql:9: HINWEIS:  Adding out-of-database constraint
  19. psql:t:/DTK10.sql:9: HINWEIS:  Adding maximum extent constraint
  20. addrasterconstraints
  21. ----------------------
  22. 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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: raster2pgsql: out_db=false with -F flag (And Showing out_db rasters in QGIS)

Tumasgiu Rossini
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.

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 <[hidden email]>:
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:
  1. Processing 1/4: C:\_GEODATA_\24_dtk10_32356_5608_2_nw_col.tif
  2. Processing 2/4: C:\_GEODATA_\25_dtk10_32356_5610_2_nw_col.tif
  3. Processing 3/4: C:\_GEODATA_\34_dtk10_32358_5608_2_nw_col.tif
  4. Processing 4/4: C:\_GEODATA_\35_dtk10_32358_5610_2_nw_col.tif
 
  1. psql.exe --host=localhost --port=5432 --username=xxx --dbname=xxx -f t:\DTK10.sql

Output:
  1. BEGIN
  2. CREATE TABLE
  3. INSERT 0 1
  4. INSERT 0 1
  5. INSERT 0 1
  6. INSERT 0 1
  7. CREATE INDEX
  8. ANALYZE
  9. psql:t:/DTK10.sql:9: HINWEIS:  Adding SRID constraint
  10. psql:t:/DTK10.sql:9: HINWEIS:  Adding scale-X constraint
  11. psql:t:/DTK10.sql:9: HINWEIS:  Adding scale-Y constraint
  12. psql:t:/DTK10.sql:9: HINWEIS:  Adding blocksize-X constraint
  13. psql:t:/DTK10.sql:9: HINWEIS:  Adding blocksize-Y constraint
  14. psql:t:/DTK10.sql:9: HINWEIS:  Adding alignment constraint
  15. psql:t:/DTK10.sql:9: HINWEIS:  Adding number of bands constraint
  16. psql:t:/DTK10.sql:9: HINWEIS:  Adding pixel type constraint
  17. psql:t:/DTK10.sql:9: HINWEIS:  Adding nodata value constraint
  18. psql:t:/DTK10.sql:9: HINWEIS:  Adding out-of-database constraint
  19. psql:t:/DTK10.sql:9: HINWEIS:  Adding maximum extent constraint
  20. addrasterconstraints
  21. ----------------------
  22. 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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: raster2pgsql: out_db=false with -R flag (And Showing out_db rasters in QGIS)

Michael.Haertel

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:[hidden email]] 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.

 

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 <[hidden email]>:

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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: raster2pgsql: out_db=false with -R flag (And Showing out_db rasters in QGIS)

Tumasgiu Rossini
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 <[hidden email]>:

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:[hidden email]] 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.

 

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 <[hidden email]>:

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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: raster2pgsql: out_db=false with -R flag (And Showing out_db rasters in QGIS)

Regina Obe-2

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:[hidden email]] On Behalf Of Tumasgiu Rossini
Sent: Tuesday, May 02, 2017 8:26 AM
To: PostGIS Users Discussion <[hidden email]>
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 <[hidden email]>:

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:[hidden email]] 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.

 

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 <[hidden email]>:

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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: raster2pgsql: out_db=false with -R flag (And Showing out_db rasters in QGIS)

Regina Obe-2
In reply to this post by Tumasgiu Rossini

Disregard what I said.  I see that the SQL statements I thought mistakenly were coming from DbManager are generated by GDAL (looking at the GDAL postgis raster code).

 

So the change would need to be done in GDAL driver.   I think having a PostGIS function GDAL can count on calling that would always return the materialized raster tile would simplify things though.

So I'm thinking strongly of implementing that in PostGIS 2.4.  I think all other calls that GDAL is doing to for example get meta data and overviews would be the same for out-db as it is for in-db.

 

Even,

 

Any input you have on the matter would be very welcomed.  I guess this would need to wait for GDAL 2.2 or 2.3 and verify said helper function exists (or version of PostGIS) before it can be used.

 

 

Thanks,

Regina

 

 

 

From: Regina Obe [mailto:[hidden email]]
Sent: Tuesday, May 02, 2017 4:04 PM
To: 'PostGIS Users Discussion' <[hidden email]>
Cc: 'PostGIS Development Discussion' <[hidden email]>
Subject: RE: [postgis-users] raster2pgsql: out_db=false with -R flag (And Showing out_db rasters in QGIS)

 

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 [[hidden email]] On Behalf Of Tumasgiu Rossini
Sent: Tuesday, May 02, 2017 8:26 AM
To: PostGIS Users Discussion <[hidden email]>
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 <[hidden email]>:

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:[hidden email]] 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.

 

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 <[hidden email]>:

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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...