Hi Regina, Regarding funding as mentioned in your mail below, I spoke with the C.E.O of the GIS firm for whom I am undergoing this research and *he would like to know how much funding would be required for these functionalities to be implemented*.
The firm already have these functionalities working on ORACLE (spatial) database, but I am undergoing this research so that a decision can be made for a possible switch to PostgreSQL/PostGIS for future projects. A lot depends on these functionalities, especially the Out-DB rasters being viewed on QGIS with similar performance (in terms of speed) as currently being achieved with ORACLE/GeoMedia. The PostgreSQL/PostGIS performance with Vector data has been satisfactory so far. I look forward to hearing from you. On Thu, May 4, 2017 at 5:48 AM, Regina Obe <[email protected]> wrote: > 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]> 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]] > *Sent:* Friday, April 28, 2017 10:13 AM > *To:* PostGIS Users Discussion <[email protected]> > *Cc:* Regina Obe <[email protected]>; [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
