[postgis-users] raster2pgsql: out_db=false with -R flag (And Showing out_db rasters in QGIS)
Tumasgiu Rossini
rossini.t at gmail.com
Tue May 2 05:26:23 PDT 2017
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>:
> 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>:
>
> 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}
>
> 0103000020E864000001000000050000000000000080BA15410000000090
> 6455410000000080BA154100000000786855410000000000F91541000000
> 00786855410000000000F9154100000000906455410000000080BA15410000000090645541
>
> 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
>
>
>
> _______________________________________________
> 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/5d418f8e/attachment.html>
More information about the postgis-users
mailing list