Osahon,
I have some ideas, but nothing I can suggest that is pluggable without changing PostGIS and GDAL. The reason why it's slow is a couple 1) ST_Resize is generally slower than reading cause it's doing some stuff to the raster. The new function I am thinking of would do nothing aside from reading in the bytes of the raster. 2) When you use a view, the data in raster_columns view for the view is incomplete, doesn't have the meta data that GDAL relies on, so it then tries to compute it with a query which since the rast column is a black box to it, also requires rerunning ST_Resize or whatever by querying the view which compounds the slowness. To remedy this issue in 2.4, I'm planning to have a side line table like raster_column_additional so users can manually enter things like views with the right params and then the raster_columns view would use this information instead if of recomputing if there is an entry in raster_column_additional. Plan to throw in some helper functions to auto-compute for views. This part isn't needed for making out-db readable and speedily readable, but would solve some other issues people have complained about. 3) Since the ST_Resize view uses a function and GDAL just sees the view as another table, you'd need a spatial index on the functional bits something like: CREATE INDEX idx_outdb_table_mat_rast ON outdb_table USING gist(st_convexhull(ST_Resize (rast,0.8,0.8)) ); This wouldn't be necessary at all if we revised GDAL library. 4) for it to be able to use it seamlessly, unless you did as I mentioned, limit your view to just a small area. The fix I had in mind for this as I mentioned in another email is to create a wrapper function (that is not as expensive as ST_Resize that will return a non-outdb raster), and revise GDAL so the query will still use the raw out-db raster for computing the meta data it needs and also using the spatial index on the out-db so no additional index is needed and no additional cost of running the function is incurred. All the above I think should make using out-db raster as transparent and fast as in-db and viewable in QGIS. HINT HINT – If any raster folks are interested in funding this kind of work, let me know. We will then be able to prioritize it for PostGIS 2.4 and next GDAL release. Thanks, Regina From: Osahon Oduware [mailto:[email protected]] Sent: Wednesday, May 03, 2017 12:02 PM To: Regina Obe <[email protected]> Cc: PostGIS Users Discussion <[email protected]>; Pierre Racine <[email protected]> Subject: Re: GDAL OUT-DB RASTER SUPPORT Hi Regina, I followed the process you described to create a view for the out-db rasters and dragged it to the map. I was able to view the out-db rasters this way. However, it takes very long before the raster is displayed on the map (in QGIS) as compared to the time taken to display an in-db raster. Is there anything that can be done to improve the speed? NB: indexes were created with the -I flag of the raster2pgsql tool On Fri, Apr 28, 2017 at 4:07 PM, Regina Obe <[email protected] <mailto:[email protected]> > wrote: Osahon, It depends what you are trying to show and do. 1) For example if you create overviews on your out-db, those will show, so then it would only be when you zoom in that you hit the out-db that you'd get black. 2) If you are looking to inspect a slice of out-db with QGIS, you can create a view that has just the piece you want to show and drag it to your map. You'd need to apply a function that forces that piece into in-db. Not all raster functions do that and sadly we haven't been good about documenting ones that do vs. ones that don't. I know for sure that ST_Resize would force a materialized in-db and that might actually be a good one to use anyway since it would lower the res allowing you to see more. So something like -- do this with db manager or pgAdmin CREATE OR REPLACE VIEW vw_outdb_sample AS SELECT rid, ST_Resize(rast,0.8, 0.8) AS rast FROM some_outdb_table WHERE ST_Intersects(rast,some_geometry); Then drag this view to your Map. Hope that helps, Regina From: Osahon Oduware [mailto:[email protected] <mailto:[email protected]> ] Sent: Friday, April 28, 2017 10:13 AM To: PostGIS Users Discussion <[email protected] <mailto:[email protected]> > Cc: Regina Obe <[email protected] <mailto:[email protected]> >; [email protected] <mailto:[email protected]> Subject: GDAL OUT-DB RASTER SUPPORT Hi All, I am getting a black image when I try to display PostGIS out-db rasters. I discovered that it has to do with GDAL support for out-db rasters as stated in the link below for a similar issue (over 1 year back): https://hub.qgis.org/issues/12133 The last comment by Jürgen Fischer states "... only black images. So it's a dependency issue (GDAL complains ERROR 1: This raster has outdb storage. This feature isn't still available)." I have also been told in the link below that "out-db support is not implemented yet in the GDAL PostGISRaster driver.": https://lists.osgeo.org/pipermail/gdal-dev/2017-April/046512.html I would like to know how to solve this issue of displaying postgis out-db rasters in QGIS?
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
