[postgis-devel] [postgis-users] raster2pgsql: out_db=false with -R flag (And Showing out_db rasters in QGIS)
kit
kit at hypostasis.com
Tue May 2 13:36:39 PDT 2017
Is there a potential issue with how gdal expects to see the raster and
in particular the metadata it gets from raster_columns? I've some
trouble with GDAL and partitioning that I need to work through at some
point - but that for the moment I'm effectvely materializing a view ov
my underlying tables to subsequently generate the statistics and
constraints that GDAL want to see.
On 02/05/2017 21:24, Regina Obe wrote:
>
> 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:lr at pcorp.us]
> *Sent:* Tuesday, May 02, 2017 4:04 PM
> *To:* 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>
> *Cc:* 'PostGIS Development Discussion' <postgis-devel at lists.osgeo.org>
> *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 [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
> <mailto: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
>
>
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20170502/87146f15/attachment.html>
More information about the postgis-devel
mailing list