Regina Obe lr at pcorp.us
Wed May 3 21:48:12 PDT 2017



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.




From: Osahon Oduware [mailto:osahon.gis at gmail.com] 
Sent: Wednesday, May 03, 2017 12:02 PM
To: Regina Obe <lr at pcorp.us>
Cc: PostGIS Users Discussion <postgis-users at lists.osgeo.org>; Pierre Racine <pierre.racine at sbf.ulaval.ca>


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 <lr at pcorp.us <mailto:lr at pcorp.us> > wrote:



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



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,




From: Osahon Oduware [mailto:osahon.gis at gmail.com <mailto:osahon.gis at gmail.com> ] 
Sent: Friday, April 28, 2017 10:13 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> >
Cc: Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> >; pierre.racine at sbf.ulaval.ca <mailto:pierre.racine at sbf.ulaval.ca> 


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):

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.":

I would like to know how to solve this issue of displaying postgis out-db rasters in QGIS?


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170504/9b8c2a4a/attachment.html>

More information about the postgis-users mailing list