[postgis-users] raster2pgsql: out_db=false with -F flag (And Showing out_db rasters in QGIS)
Tumasgiu Rossini
rossini.t at gmail.com
Tue May 2 03:08:02 PDT 2017
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>:
> 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} 0103000020E8640000010000000500
> 00000000000080BA154100000000906455410000000080BA154100000000
> 786855410000000000F9154100000000786855410000000000F915410000
> 0000906455410000000080BA15410000000090645541 true
>
>
>
> "rid" "rast" "filename"
> 1 0100000100000000000000E03F000000000000E0BF0000000080BA154100
> 0000008466554100000000000000000000000000000000E8640000A00FA0
> 0F840000433A5C5F47454F444154415F5C32345F64746B31305F33323335
> 365F353630385F325F6E775F636F6C2E74696600 "24_dtk10_32356_5608_2_nw_col.
> tif"
> 2 0100000100000000000000E03F000000000000E0BF0000000080BA154100
> 0000007868554100000000000000000000000000000000E8640000A00FA0
> 0F840000433A5C5F47454F444154415F5C32355F64746B31305F33323335
> 365F353631305F325F6E775F636F6C2E74696600 "25_dtk10_32356_5610_2_nw_col.
> tif"
> 3 0100000100000000000000E03F000000000000E0BF00000000C0D9154100
> 0000008466554100000000000000000000000000000000E8640000A00FA0
> 0F840000433A5C5F47454F444154415F5C33345F64746B31305F33323335
> 385F353630385F325F6E775F636F6C2E74696600 "34_dtk10_32358_5608_2_nw_col.
> tif"
> 4 0100000100000000000000E03F000000000000E0BF00000000C0D9154100
> 0000007868554100000000000000000000000000000000E8640000A00FA0
> 0F840000433A5C5F47454F444154415F5C33355F64746B31305F33323335
> 385F353631305F325F6E775F636F6C2E74696600 "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
> 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/f0d4a021/attachment.html>
More information about the postgis-users
mailing list